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).
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.
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 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"
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.
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.
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?
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.
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.
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.
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???
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?
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.
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)?
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?
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.
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.
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?
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.
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?
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.
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.
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 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?
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.
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.
