hi guys, i have an asp app i'm trying to write. i have 4 tables. Categories, Products, Jobs and Orders. i enter a job, then i go to the categories, choose the category i want, that takes me to the products in whatever category i chose. From here i can enter an amount, for instance 7 juju beans. this takes me to a page that inserts the info into my database and displays what's in the database for that particular job. Ok, here's the problem. (by the way, everything is stored in the Orders table so this is the one i'm pulling data from to display on this last page.) I have a foreign key in the orders table from the products table (ProductID) how would i display the manufacturer (a field in my Products table that contains the manufacturers name) instead of the ProductID number? TIA
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Displaying a field in another table based on froeign key
- Thread starter gmagerr
- Start date
=DLOOKUP("MFGNAME", "PRODUCTS", "PRODUCTID = ORDERS!PRODUCTID"
or words to that effect. Substitute your field and tables names.
Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:
or my site,
or words to that effect. Substitute your field and tables names.
Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:
or my site,
- Thread starter
- #3
Sorry old chap.
The DLOOKUP function retrieves a non-key value from a table when you feed it the field you want, the table name, and the KEY to that table.
If you want to extract the MFG name from the PRODUCTS table while you're at the ORDERS level, you'd use the PRODUCTID you've stored in the ORDER to look up in the PRODUCTS table to find the match (hopefully, there'll be one...) and then display the MFG NAME that goes with him.
DLOOKUP("MFGNAME", "[PRODUCT TABLE]", "[PRODUCTS-TABLE-KEY FIELD] = " {ORDER-RECORD PRODUCTID VALUE)
FOr example, you enter an order for Product #36D. It's made by Friggemall Industries. You want to get "Friggemall Industries" out of the Products table.
=DLOOKUP("MFGNAME", "[Products]", "[Products.PRODID] = '36D'"
Very often, you'll be substituting a form control for that last actual value, so it might look more like this:
=Dlookup("MFGNAME", "[PRODUCTS]", "[PRODUCTS]![PRODID] = ' & Me!OPRODID & " ' " )
It starts to get a little messy with all those quotes and everything..be careful.
Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:
or my site,
The DLOOKUP function retrieves a non-key value from a table when you feed it the field you want, the table name, and the KEY to that table.
If you want to extract the MFG name from the PRODUCTS table while you're at the ORDERS level, you'd use the PRODUCTID you've stored in the ORDER to look up in the PRODUCTS table to find the match (hopefully, there'll be one...) and then display the MFG NAME that goes with him.
DLOOKUP("MFGNAME", "[PRODUCT TABLE]", "[PRODUCTS-TABLE-KEY FIELD] = " {ORDER-RECORD PRODUCTID VALUE)
FOr example, you enter an order for Product #36D. It's made by Friggemall Industries. You want to get "Friggemall Industries" out of the Products table.
=DLOOKUP("MFGNAME", "[Products]", "[Products.PRODID] = '36D'"
Very often, you'll be substituting a form control for that last actual value, so it might look more like this:
=Dlookup("MFGNAME", "[PRODUCTS]", "[PRODUCTS]![PRODID] = ' & Me!OPRODID & " ' " )
It starts to get a little messy with all those quotes and everything..be careful.
Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:
or my site,
- Status
Similar threads
- Locked
- Question
- Locked
- Question