Query/Form Problem With Lookups
Oct 18, 2004
I am trying to create a query for a form which returns a 'calculated' value.
One table contains ProjectNo, SchoolType1 and SchoolType2
Second table contains SchoolTypeID and SchoolTypeName
The SchoolType1 and 2 are number values which reference the SchoolTypeID.
If I query for project number and school type 1 and 2, I get the actual names for the school types (i.e. 33.3333, elementary school, learning center). But, not all project numbers have two school types (i.e. some are just elementary schools). If I create a query for project number and [iif (SchoolType2 is null, SchoolType1, SchoolType1&", "&SchoolType2)], I get the number ID's returned instead of the names (i.e. 33.3333, 3, 6) instead of what I'm looking for which is the names. (I get number ID's returned if I do something as simple as 'SchoolType1 & " " & SchoolType2')
I hope this makes sense. How can I get it to return the school type names when I do any kind of calculation on the school types?
Thanks
View Replies
ADVERTISEMENT
Aug 18, 2005
I have a subform in continuous mode which has a list of people. I have created an unbound field which has a dlookup into a telephone number table.
What I want to happen is for each person in the list to have their telephone number displayed (not recorded - just displayed) on the form alongside their name. On a single form this works fine, but on a continuous form it displays the same phone number on all the rows (presumably the first person in the lists number).
Can anybody advise how I can get the dlookup working for each record in the continous form.
Thanks in advance,
Adam.
View 14 Replies
View Related
May 19, 2005
Hi All
I am trying to develop a database for recording project risks and issues and need to assign an owner to each one. My design stage has three tables for project resources, issues and risks. The Resource table is basically ResourceID; Surname; Forename, GivenName (forename and surname concatenated) Status; Role and DateAdded. When I allocate a risk or issue I want to be able to select the ResourceID from a drop down list that references the Resource table (I have got this working) and then from that ResourceID value to automatically populate another field with the matching GivenName and that is where I am getting stuck. I have setup relationships between the tables based on ResourceID but can't see where I need to be with a query to do this. Someone a suggetsed a union query but not sure of the syntax for this. I know I could have a separate lookup for the GivenName but that invites data capture error and requires the user to undertake an extra input step that could be avoided with a bit of slick programming.
Hopefully the above provides enough information to identify the problem and possible solution - any help on this appreciated.
Thanks
Andy
View 4 Replies
View Related
May 18, 2006
I am a total newbie and possibly in over my head already.
I have 2 tables:
tblEmployees
..EmployeeID autonumber PK
..FirstName text
..LastName text
..DepartmentID number
..HourlyRate currency
tblDepartments
..DepartmentID autonumber PK
..DepartmentName text
Many-to-one relationship tblEmployees to tblDepartments
I need to create a form to enter employees in tblEmployees.
FirstName, LastName, HourlyRate are all straightforward. I'm stuck (probably a HUGE mental block!) on how to get the DepartmentName into the tblEmployees from the input in the form.
Please help!
View 1 Replies
View Related
Oct 20, 2006
In an attempt to eliminate user input error,I am Looking Up values from a field in a separate table as input for my SEMINAR field. The table containing my Look Up field has only one other field, which is an auto-numbered key field.
When I view my SEMINAR field in datasheet view, the data is as I would expect to see. However, when I use my SEMINAR field in a report or query, it returns the value of the autonumbered key field. The data type for both the SEMINAR field and the Lookup field are text. - Any Thoughts?
Golfer
View 3 Replies
View Related
Apr 27, 2006
Hi Guys,
First off, a big thanks to everyone on the site. I have learnt a lot since first discovering this site a few weeks back.
Problem:
Having understood that it is better to create SQL lookups to queries of tables rather than to the tables directly, I am having trouble understanding what value I should store in my main table, a text value or the ID (number) (of the text value.)
I have an asset table with a field Equipment Type. This field looks at a query of the EquipmentType Table.
Would it better to store the text value "Printer" in the main asset table (in which case I can query the table directly but the field will use up more space (i.e. 25 char)?)
or
Store the Equipment_Type_ID "1" relating to the Printer (will use up less space, but mean any queries querying the actual name would have to include the EquipmentType table).
Any advise would be much appreciated.
:confused: My thought was that I should go with the ID as otherwise I will be storing duplicate data. If this is the case, when would a text value be more suitable.
View 1 Replies
View Related
Oct 25, 2006
Probably an easy one that I just can't think my through it. I've been trying to create some (for lack of a better term) cascading fields using the lookup wizard (to eventually be used in a datasheet view/form).
In the main table, the user needs to select a Team (A, B, C), Sub-Team (A1, A2, A3, B1, B2, etc), and a Family (A1 contains bolts, screws, and washers).
The first lookup of selecting a Team (A, B or C) was easy. However after this point I'm stuck. If the user chooses A, I only want the "A" related sub-teams to show in the next lookup. Then based on the sub-team chosen, I want the Family list restricted again.
Any ideas would be appreciated. Thanks!
View 5 Replies
View Related
Dec 4, 2006
Simple questions I hope.
1.
What I'd like to do is do a lookup based on 2 fields in my table.
I'm tracking inventory for the company I am currently working with.
I have a computer table with 3 relevant fields:
compID, areaId, locationId
computer table
compID = PK for this table
areaId = FK from area table
locationId = FK from location table
area table
areaId
location table
locationId
areaId
There is a distinct relationship between area + location.
I want a lookup for the locationID, based on the area they have selected.
Thanks for any help on this one. On to the next question related to this.
2. As mentioned above I have an area table controlling the general departments (for lack of a better word). When an area is selected in the table, they have the ability to do a lookup for the relevant locations (implemented in the combo box).
I'd like the user to be able to add a new location item in the computer table, and have that value be added into the location table with the corresponding areaId as well. Am I looking for cascade update or something of this sort?
Thanks anyone. This is my first post here, I'm quite new to microsoft access as a database tool.
View 1 Replies
View Related
Jan 2, 2015
We are on Win7 with Office 2013 (32-bit). I have had to migrate my application away from a network that is open to the web. I am now on a more isolated network. My problem is that I can no longer use Outlook for e-mail.
I knew that you can use CDO to send SMTP via a Mail Relay server. We've got one of those on this isolated network... but there is a show-stopper here. When setting up the configuration portion of the CDO message, there are these references using
CDO.Configuration.Fields.Item("HTTP:schema.micro soft.com.... etc.) = value
These references are how you define the SMTP server, server port, authentication method, etc. My problem with this is I can't get to that Microsoft site from the isolated network. The SMTP Relay won't do this lookup for me and I have no general internet access.
When I looked into the CDO library, I found that there were definitions for the fields like cdoSMTPAuthenticate and cdoSMTPServer and cdoSMTPServerPort - but when I attempt to use them, Access doesn't seem to like them even though the CDO library is checked in my references list and those values are defined. For what it is worth, the library file says it is version 6.6.7501 (or something close to that), so it is not the older version. The revision date is 6/6/2012, just about right for a file that would have been included in Office 2013's library set.
How to use CDO when you can't do the HTTP lookups? (I know how to use it when the lookups work...)
View 5 Replies
View Related
Apr 25, 2014
I have one attribute in the table and the table is normalized. But we have the same attribute in two different forms, one with a table lookup and the other with a free form text input. I'm trying to rationalize this for a dashboard output.
Do I keep it as one attribute or do I make 2 - one for the table lookup and 1 for the free form input? What are the pros and cons of each?
View 5 Replies
View Related
Jan 23, 2006
Hi,
I'm fairly new to access.
I'm trying to design a database for my work network which will show how nodes are connected. I currently have the following tables:
section - refers to agreggate cable links between buildings/sites
media - refers to individual links
Network_element - refers to switches/routers
section (section_id PKEY,media_type, start_site, start_building, start_cabinet,
end_site, end_building, end_cabinet).
media (section_id PKEY, from_pair, to_pair, from_ip, to_ip)
Network_element( Name PKEY, MAC, IP)
I'd like to be able to make sure access will only allow from_ip and to_ip to be entered if there is a corresponding value in the Network_element table, however I do not know how to enforce this.
Any help much appreciated.
Thanks
Dan
ps - if anyone has done something similar, I'd be keen to see how they gone about it.
View 1 Replies
View Related
Jan 27, 2006
Can someone please tell me if there is a way to perform lookups in Excel from an Access table without first exporting the data into Excel? I have a form that is being exported into Excel from a pricing tool our company uses from an outside vendor. It is important the users have the ability to perform complicated calculations and are not at all experienced in Access. There are several data sources that are well over the 65,000+ rows limited by Excel. I need to either prove or disprove whether it is possible to perform lookups between Office applications. If it is possible to lookup between applications, I would appreciate any sources known to learn how to do this. If not possible, I would like to be able to confirm that fact to my manager.
Thanks for any help that can be offered.
tschultz
View 1 Replies
View Related
Oct 22, 2013
I have a DB for clients that contains all kinds of info. It is getting bigger and harder to keep track of things by their ID number. The problem I am running into is that ComboBoxes and Lookups can only bind 1 column. My DB is based on Categories, then Sub-Categories, then Sub-Sub-Categories. I have done this because there is multiple criteria for each customer.
For Example, I have a Customer = ID; Location = Location_ID; Employee = Employee_ID so everything is tied to the ID, but on my phone list I need to have a list of Employee's that show the name that is tied to ID and Employee_ID.
ID is unique to each customer but I have duplicate Employee_ID for example Customer 1 has Employee 1 and Customer 2 has Employee 1, so when I use the ComboBoxes or Lookups I get Customer 1 - Employee 1 for Customer 2 - Employee 1 if I bind to Employee_ID, if I switch that to ID it is completely messed up. Now I don't care if the table stores a number, but I would like the form to display the name.
I am trying to get my forms to display a name and not an ID number and when you enter new information you can select a name and not a number.
View 14 Replies
View Related
Oct 21, 2012
I have 3 tables: [SurveyVendor], [Surveys] and [SurveyResults]
SurveyVendor has 2 fields:
[VendorID] - PK
[VendorName]
Surveys has 3 fields:
[SurveyID] - PK
[VendorName] - FK
[SurveyName]
SurveyResults has 3 fields:
[SurveyResultID] - PK
[SurveyName] - FK
[SurveyQuantity]
I used the LookUp wizard to establish the relationships, and I chose to include 3 fields when linking [SurveyResults].[SurveyName] to [Surveys].[SurveyID]. I'd like the ID, Survey Name and Vendor Name to be displayed when making a choice in the combo box. And I would like the column to only display the SurveyName.
However, what's happening is the VendorID shows up instead of the VendorName when I click the combo box for [SurveyName]. And the SurveyID shows up in the column results, rather than the Survey Name.
View 3 Replies
View Related
Jan 7, 2013
I am trying to link two fields in a table here is an example ....
Field A
Values stored could be....
AAA, BBB, CCC, DDD etc
LIST TO LOOK UP
AAA = UDC2
BBB = UDC1
CCC = UDC4
DDD = UDC3
Field B
Needs to lookup listi n Field A
and get correct UDC1, UDC2 etc...
View 3 Replies
View Related
May 31, 2007
Hi there,
I'm pretty new to Access so I hope this question is not to simple/stupid ;)
Ok, I have an Excel Worksheet that I want to import into an Access Table. The column headers are identical! The only problem is:
- the Access table looks up a couple of the columns from other Access tables. When I try to import the Excel data, Access does (properly) report an error.
- Error: the records from the Excel file were added to the Access table but not all values were imported (no values were imported into the lookup columns...)
Any idea how I should procede so that I can import my data into Access?
Thanks!
Steve
View 3 Replies
View Related
Aug 7, 2013
We have our access database with a bit of a messy structure?
We use our database to record sizes of our product. I am hoping to improve it by adding the items we have in stock to prevent manufacturing more.
Basically our main database called "Make & Model 1" has a list of various makes and model numbers, each model number lists various information needed to manufacture a replacement part.
Customers order the part and these part details are entered into a table called "Order Detail" What I would like to happen is that when the details are entered a calculated field adds the data entered to a text string. I need to match the text string to the same text string in a table called "stock" as there could be one part that matches hundreds of models.
Basically the "profile" "Colour" "height" and "width" make the string and this is what I need to match and tick a box / populate the number of items in stock. Eventually I would like this to reduce by the amount ordered but lets do one step at a time.
I am using Access 2010
View 1 Replies
View Related
Oct 19, 2004
What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?
I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.
So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.
Thanks in advance
Todd
View 1 Replies
View Related
Jul 23, 2015
I have a form which will be used as the basis to print a label.
It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.
What I want to do is to update the form/query without updating the underlying tables to the query.
View 14 Replies
View Related
Jun 15, 2013
Is there an easy way of entering a value in a text box, passing to a query to do a count function and then return the value of count function in to anther text box?
View 5 Replies
View Related
Aug 17, 2014
I have a big table, EquipmentDetails and separately I have job plans.Job plans can have many pieces of equipment. I store the relationship in EquipmentDetails_JobPlanDetails, but am feeling frustrated as I can't seem to get Access to build the SQL query from this in the way I want.
I am generating a List Box in a form which is populated from a query.The query calls on a table which lists ItemID from EquipmentDetails and JobID and in the current form (where the list box is) I want to pick up and display the ItemIDs associated with that JobID. The current JobID reaches my form correctly (I've proven this by displaying it in a text box) from the previous form.
The problem I have is that I can't seem to get the SQL query to only get ItemIDs that are linked to the current JobID. No matter what I try, it either gets all of the ones in that table, or I can't compose one.What kind of SQL do I need to only grab the ones relating to the current JobID please?I've tried building it in the design view but it says that it can't do it because the outer joins are ambiguous.
View 2 Replies
View Related
May 13, 2005
First, I am a beginner with Access. I am a graphics designer that has been assigned to cover for a db programmer that quit!
Here is my problem:
I have a database that we use to hold customer information. There are 22 fields in each record, and we are now well over 3000 records.
Once upon a time, to find a specific customer, we would just go to the bottom of the page, and use the arrow buttons to scroll through them all. This is no longer possible as the size is too big to manually search.
What I would like to do:
Upon Access startup, display a form that has a single input field and a button titled "Search". The input field is titled [UserName], as this field is the unique key identifier for the record. When the user types in the UserName and clicks the SEARCH command button, another form appears to display JUST THAT RECORD in the easy to read form!
If this is really basic, I appologize. I have 20 years experience with commercial illustration and only 7 days experience with Access. I have purchased books, and hit the forums, but I am not a VB programmer, and my skills this area are REAL weak!
My resourse books include the following:
Wiley - Access 2003 Bible [Prague, Irwin & Reardon]
Osbourne - How to do Everything with Access 2002 [Anderson]
O'Reilly - Access Cookbook [Getz, Litwin & Baron]
Thanks in advance for any assistance.
View 1 Replies
View Related
May 13, 2005
First, I am a beginner with Access. I am a graphics designer that has been assigned to cover for a db programmer that quit!
Here is my problem:
I have a database that we use to hold customer information. There are 22 fields in each record, and we are now well over 3000 records.
Once upon a time, to find a specific customer, we would just go to the bottom of the page, and use the arrow buttons to scroll through them all. This is no longer possible as the size is too big to manually search.
What I would like to do:
Upon Access startup, display a form that has a single input field and a button titled "Search". The input field is titled [UserName], as this field is the unique key identifier for the record. When the user types in the UserName and clicks the SEARCH command button, another form appears to display JUST THAT RECORD in the easy to read form!
If this is really basic, I appologize. I have 20 years experience with commercial illustration and only 7 days experience with Access. I have purchased books, and hit the forums, but I am not a VB programmer, and my skills this area are REAL weak!
My resourse books include the following:
Wiley - Access 2003 Bible [Prague, Irwin & Reardon]
Osbourne - How to do Everything with Access 2002 [Anderson]
O'Reilly - Access Cookbook [Getz, Litwin & Baron]
Thanks in advance for any assistance.
View 3 Replies
View Related
Sep 6, 2004
HI
I have a form for member details that comes from the member table and I want to
include a button on the form that will remove the member details from the member
table to a member history table and then delete the details from the member table.
When moving the record I also want to include details from another table called 'promotion',
this table is linked to the member table via the member ID (Foriegn Key).
Firstly I want to know if this is possible and if so how I would achieve this. I am having
trouble getting the SQL query to identify the current record that the form is displaying so I can
select additional information and move it into the Member History table.
Any information would be greatly appreciated.
Thanks.
View 2 Replies
View Related
Aug 30, 2005
I have a query that displays results in a form, but if the query is null, I want to display a different form, or just an error message that says something like "your query returned no results" (right now it will display the form with no fields)
I am a beginning Access/VBA user and have searched and browsed the forum for combinations of null/query/form, but haven't found what I need. Can anyone point me in the right direction?
Thanks for any help.
View 5 Replies
View Related
Sep 22, 2006
:confused:
I have a form displaying individual contract information. This form is a continuous form and on each record I have a button that activates a dialog box with two text boxes to facilitate NOTES entry for the current record on the main form. When I close the dialog form - I need the NOTES text entered to be updated in the corresponding text fields on the continuous form.
I have tried the following :
1. After the dialog has closed - set the main forms Bookmark property/ Requery the form and then reset the forms Bookmark.
Problem with this solution is that occasionally I get ' Not a valid bookmark' error message.
2. In the Unload event of the dialog - I have tried Requery calls for each of the text field controls on the constinuous form prior to the dialog closing. This works fine for records that already exist in the table and the data does reflect the changes correctly. For brand new records, the updates are not reflected on the main form and the data only appears when the main (continuous) form is closed and re-opened.
Does anyone have any ideas on how to do this properly? Has someone done something similar?
Thanks
Regards
GUIDO
View 1 Replies
View Related
