Lookup Fields
May 18, 2007
I have two tables(tblInvoiceItem and tblItems). On both of these tables there is; ItemNo and ItemCost. tblItems already has various records about items. I want to be able to have a lookup field for ItemNo on tblInvoiceItems which will populate the ItemCost from tblItems.
Basically when I pick an item number(lookup field on tblInoviceItem, looking up from tblItems), I want the price which corrisponds with it to appear in the correct field(ItemCost on tblInoviceItems).
Hopefully I explained that okay :confused:
View Replies
ADVERTISEMENT
Mar 2, 2005
Hello,
I wonder if anybody can help me.
I have a table called ITEM, within ITEM I have three fields ITEM NUMBER (Key Field), Item, Cost,
I have another table called INVOICE ITEMS, Within INVOICE ITEMS I have six Fields, INVOICE NUMBER, ITEM NUMBER, ITEM, UNIT COST, Amount, Total Amount.
I want to use Lookup wizard to complete the fields ITEM NUMBER, ITEM, UNIT COST from the ITEM table.
Is this possible?
Regards
Nathan
View 1 Replies
View Related
May 28, 2007
Hello there,
I've lately come across some posts that condemn using lookup fields in tables. But how bad is this really? I mean it's quite a nifty feature, and it'd be really too bad if it hogs up resources, thus forcing us to leave it alone for performance considerations.
I'd appreciate if any of you guys/gals with more experience on the topic would like to share some, so I can take a better stance for future developement.
Regards,
Jaime
View 10 Replies
View Related
Jan 16, 2008
I have been working with a simple Dlookup to return a value based on the data held in a specific field. I am now trying to develop this further and need some help.
I have a query "QryMainForm" that brings together fields from 2 tables "TblFS" (fields: Ref, score 1, score 2) and "TblFamily" (fields: Familyname). the query adds the values in Score 1 and Score 2 and saves the value as "points".
Seperatly from this i have a table "Tbllevels" which contains the fields Score, Operational, Admin, Technical. with the points scored relating to a value within each field. What i need to do is run the lookup based on the both "points" and Familyname fields contained in the query.
So the TblLevels looks like this:
Score.....Technical.....Operational.......Admin
100........................... Level 1.........Level 2
200 ........Level 1..........Level 2.........Level 3
300.........Level 2..........Level 3
400.........Level 3
So someone who scores 300 points could be assigned to the technical or operational group (this is done manually). What i need the lookup to do is to return the level based on the points and Familyname fields located on "qrymainform"
any ideas on how i can do this one?
View 10 Replies
View Related
Feb 11, 2008
Hi,
I have a table that has 3 columns.
Issue, Review, and Information.
These 3 fields get their data from the same lookup table that holds some code values.
So I'm trying to create a query to export to excel that shows the data for this table, but for the lookup fields, I just get the ID's from the lookup table.
So the table is like this now
ID...Issue...Information....Review
1.......2..........5...............1
2.......1..........12..............6
So I would like to be able to display what the ID numbers actually are, but having trouble getting a query to do this for all 3 fields.
I can run a query that shows 1 field's but not all 3.
what I would like to see
ID.....Issue...Information....Review
1.......2a.........4c.............e5
2.......1a.........3c.............2F
all 3 columns use code_ID from the lookup table.
Is there a way to do this? or should I have used 3 different lookup tables for these?
View 13 Replies
View Related
Feb 12, 2005
I am working on a project that I know I am making more difficult then it need be...... Maybe because I started with a form and tried to build everything else around it. The database looked bad...... Now it looks good... normalized as I can see it... But trying to get the information in is being difficult.
The DB has customers, thats no problem. These customers have inspections done to areas for cleaning.
I have done the areas in a drop down, in one table, and the task to be rated as a dropdown in another table. Then finally a rating table....1 to 5.
It is very easy to go in in datasheet view and input the info for each area, then pick a task from the next dropdown and then pick a rating from the final.
Problem is this won't be as easy for someone else to input. So here is the form problem.
Heres how I would like to lay it out. An over all inspection form. Areas inspected and rating (average)
I will use a command button from the main form to open up say "area1"
On the area one form is the first problem....
I want 6 different task to give a rating to...... But these task are on a dropdown....so I can not bring over the field more then once.... or I dont know how..It's getting confusing and I don't think it should be.... its a pretty simple one.
So can anyone guild me to a databse similiar to this? I need to see how one like this is done.
Thanks
View 1 Replies
View Related
Apr 25, 2015
The lookup has a two column dropdown: FirstName and LastName. This is for the field, Sponsor, however I want to concatenate the First and Last name and have those stored in Sponsor. How do I do that?
Note: I just did it in the Lookup query.
Now a related problem is when I make a change to the table (e.g. Lookup), the change does not show in the form. Is there a way to "refresh" the form? My work around was to delete and re-add the field...I'm sure that is not the correct way to do it.
And when I added the field, the caption comes along with it bound to the field. Is there some magic keystroke to break the bond?
View 8 Replies
View Related
Apr 12, 2005
I have a database that keeps track of inspection records. What I want to be able to do is open the form Inspection, and select a part # in a dropdown. When I do that I want it to fill in all of the critical item/dimension boxes on that same form for that part number that I selected above. I tried to figure it out from the sample database example on here, but I just cannot figure it out. Any help is greatly appreciated. A copie of my database is attached.
View 4 Replies
View Related
Jul 12, 2007
Hi, all! I'm once again needing your sage, wise advice.
I was reading on MVPS.org, and found a page that denounces the use of lookup fields in tables, because of the way it skews relationships, weighing down the database.
http://www.mvps.org/access/tencommandments.htm
(#2)
Do you all agree with this?
What do you recommend I do, in lieu of lookup fields, when I need to maintain integrity of the data.
Thanks SO much. You guys are my heroes!
KellyJo
View 2 Replies
View Related
Oct 13, 2014
When I set up lookup fields that point to a table do I save those tables in the back end with the main tables or is it okay to have in in the front end and not the back end? Users will need to make udpates to the tables for instance an employee table they would need to add employee names.
View 2 Replies
View Related
Jun 5, 2013
I have a query that based on certain codes in a record does multiple joins to pull in other codes... I want the query (based on these pulled in codes) to do a lookup in another table to extract 1 field that has a description...
Can I do this in one query??? Do I need to do a Make Table to first pull in the codes and then do a second query with a DLookup based on those fields???
View 3 Replies
View Related
Oct 21, 2012
I have a member table and donation table. For a donation, I'd like to lookup the last and first names from the member table. I set the lookup for the last name and can see bothe first and last names in the drop down list. When I pick, I get the last name in the DonationT but how to I pick up the first name? That is, how do I fill DonorFirstName in DonationT from the FirstName field in the MemberT?
View 3 Replies
View Related
May 27, 2013
I am developing a database for a school and I have a form for staff addresses where the city is a drop down box that auto fills the state and zip according to the data in the lkuptblCity. All is working fine EXCEPT we have an Address 1 and Address 2 field for the employees.
I have two separate fields in the Staff table to store the data, as needed for reports. I have two separate fields on the form but they will not work due to pulling from the same table.
View 1 Replies
View Related
Apr 26, 2012
So, the Person table has 2 IDs (neither is the primary key of the table, like SSN and HRID). What the customer wants is to show both IDs in the Orders table.I'm having trouble getting the table to populate the second ID field based on what is selected from the drop-down in the first ID field.
Person table (ID, FirstName, LastName, SSN, HRID)
Orders table (ID, PersonSSN, PersonHRID, etc.)
If I populate the Look-up field of PersonSSN as 'Select SSN, HRID from Person' (use column 1 as value), how do I get the HRID field to populate when someone selects a SSN value from the Person drop-down?And the follow-up, can I get the same thing to happen in reverse (i.e. populating the PersonSSN value when the customer selects an HRID from the PersonHRID drop-down)?
View 2 Replies
View Related
Oct 20, 2014
I am using Access 2010 and I have a table containing a list of chemicals (Analytes) that I am working with and their Chemical Abstract Service number (CAS). I am trying to use this table as a basis for generating other tables that will be used to create quality control criteria for specific analysis methods. As such, I will need both fields populated for each subsequent table. I have been able to successfully create a lookup that will pull the Analyte name, but I want the same lookup to fill the CAS number. How do I make this work?
View 14 Replies
View Related
Apr 24, 2007
I'm new to access and trying to figure out how to use a field to look up and return results from multiple tables. I've tried several books and also the northwind traders example. What i'm trying to do is just like the oder form in the orthwind traders example on the order form. I would like to insert the customer name via a combo box then have the address, phone etc. automatically update as well as automatically update the shipping address etc with the current billing address. I know this is pretty basic but I must me missing a step.
Thanks in advance
:confused:
View 2 Replies
View Related
Apr 13, 2014
I have a table that contains about 75 fields. Each field is going to be a Lookup field that will allow the user to enter multiple values. This is for a home inspection service. So the first field name will be ExteriorShingles. The user clicks the dropdown arrow, and is allowed to select multiple values such as "loose", "missing", "rotted" etc... I was wondering if there is an easy way to import all these values from my Excel spreadsheet, or do I have to enter each one individually, which is going to be a lot of work.
View 2 Replies
View Related
Oct 15, 2012
What is the best way to do this without losing data? I have several FK that I made the mistake of setting up as lookup fields. I now want to correct this without losing data if possible.
DataType currently says "Number" under each of these. So I can't just change them to number. Is there another way to do this?
View 2 Replies
View Related
May 16, 2013
I know experienced DB developers say never to use a lookup in a table for a foreign key and instead to use it on the form level. For this reason, I am going through my tables and removing lookups from the table level now.
But how do y'all feel about lookup value lists? (so the list is typed-in instead of looking up a table value)
If you think they shouldn't be used then what should I do instead? Should I make a table for the handful of values and link with a FK field?
Or is there a better way? I would rather not have to make a million tables for these short, stable value lists.
View 2 Replies
View Related
Apr 2, 2015
I created tables that have lookup fields referencing another field. Actually I have several tables that all have relationships and object dependencies in my database. Now that I am trying to create some different reports, when I run the report I get the ID rather than the contents of the field. Also, I have a 'Report Dashboard' so to speak that I can run different reports from. On the form I have Combo boxes that reference one of the tables. I can generate the report showing the information, however I tried using a text box (criteria)(=[Forms]![Reports Form]![Combo47]+" County") in the header of the report to reference the selection made in the form combo box. When I run the report I generate the ID in the header.how do I get rid of the lookup fields in the tables, or is there another work around?
View 5 Replies
View Related
Sep 15, 2011
I am new to access. I have the following code to lookup associated fields with a CourseNumber that is selected from a combobox. The information is then stored in a CourseEnrollment table linked to student info.
The CourseNumber and associated fields are located in a LU_CoursesOffered table. The LU_Courses Offered table has been populated by choosing associated data from combo boxes based on other LU tables.
I am getting the primary keys returned for the associated data rather than the text stored with that key.
Private Sub Combo103_AfterUpdate()
CourseName = DLookup("CourseName", "LU_CoursesOffered", "CollegCourseNum = " & Nz([CollegeCourseNum], 0))
LevelSection = DLookup("LevelSection", "LU_CoursesOffered", "CollegeCourseNum = " & Nz([CollegeCourseNum], 0))
AcadGrad = DLookup("AcadGrad", "LU_CoursesOffered", "CollegeCourseNum = " & Nz([CollegeCourseNum], 0))
Instructor = DLookup("Instructor", "LU_CoursesOffered", "CollegeCourseNum = " & Nz([CollegeCourseNum], 0))
End Sub
View 3 Replies
View Related
Jan 2, 2014
We have a form, which has a field linked to a "Recipients" table through a lookup. The field is bound to the recipient's name, but the lookup displays additional columns for user to identify the correct recipient in the case of duplicate names.
The "Payment" table's Recipient field only displays the recipient's name. So where duplicates occur, it's impossible to know which recipient is the correct one associated to a payment.
I would like to add an additional field to the "Payment" table called "RecipientIDNo". I need a way to auto-populate this field, based on the already existing Recipient field. Is this possible?
Why I just don't modify the recipient field on our payment form to be bound to the user ID number, there are two reasons.
(1) The field is linked to too many other reports and queries at this point. I believe it could cause an enormous amount of work to modify.
(2) the form likes to display whatever it is bound to. There may be a workaround for this, but I'm not aware of it and don't want the form displaying a number instead of a recipient name.
View 7 Replies
View Related
Oct 14, 2014
I've managed to use the lookup wizard to show me two fields in the drop down list, first name and last name, but when i select a record I can only see the first name in the cell, not both.
Is there a way to display them both together?
View 1 Replies
View Related
Apr 16, 2013
Is there another field that can be used beside the lookup field in an Access table? I am currently using the lookup field as a multivalued lookup field and I am limited to the the things I can do with it when creating a report or a query on that field. Is there a similar field in Access 2010 that has the same look as a lookup field and allows for multiple values to be selected?
View 4 Replies
View Related
Oct 28, 2014
I have two tables, one table (1) hold three fields, one of those fields is the master key (index). The other table (2) has a field which I want to lookup from table 1, that part is working. In the combo box I get all three fields displayed as I make my selection. I want to copy the other two fields as text at the time the lookup index is selected. I do not what the fields to automatically update each time the table is displayed. I have looked at update macros, I've looked at VBA.
View 1 Replies
View Related
Jul 17, 2014
I created a table in MS Access using a Lookup & Relationship data-type. This means that my record cells call upon a particular table for values. It creates a LIST of values for that cell.When I query a particular value in this table I get every possible combination of the value. My query and code are below:
OR,
SELECT MainT.Content.Value, MainT.Source, MainT.[Entities Impacted].Value, MainT.Update,
MainT.[Divisions Impacted].Value, MainT.Announced, MainT.Effective, MainT.Stakeholders.Value,
MainT.[Ref#ID], MainT.[Issuing Agencies].Value
FROM MainT
WHERE (((MainT.Content.Value) Like "*" & [Forms]![SearchF]![ContentCB] & "*" Or (MainT.Content.Value) Is Null)
[Code] .....
This means that if I query STATES: "California", I'll get back every possible combination that exists with the name California in that particular field.The issue with this is that I'll get MULTIPLE primary key values rather than just 1. So, if one record, under STATES has California in it, but the other fields in that record have the Lookup & Relationship data-type, then every possible combination of that record will query instead of the multiple field-values form that you'll see in the table I am querying.
notice that my primary key (REF#ID) is repeated numerous times! This obviously causes problems with generating records and forms concerning information for 1 particular primary key. Is there a way around this problem other than specifying search criteria down as much as possible?! Perhaps there is some SQL code.
View 1 Replies
View Related
