How many relationships to define for details table (1 Viewer)

redridinghood

New member
Local time
Today, 04:15
Joined
Jul 13, 2018
Messages
7
This is my first access project.....
Lets say I have the typical invoice project. There is the invoice table and form, and in the middle of it, there are multiple rows from the details table that list the products that make up this invoice. So far, I got that part worked out.
All the tutorials show that the details table would include a lookup link to the ProductID, Description -- good so far, got it working.
Here is what I dont understand -- lets say the products table contains ProductID, Description, Country of Origin, Color, Size, etc. If all of these are to show up on the invoice and all of these attributes are separate columns on the invoice, does the Details table have to include a lookup link for each attribute? So if the Details table contains lookup for ProductID and 5 of product attributes, thats a lookup for each attribute? Is that how it works or am I missing something?

I would like to think that a record lookup for a product occurs just once, and then each piece of record data retrieved would get stuffed into different columns of my details subform.
Thank you, folks
 

redridinghood

New member
Local time
Today, 04:15
Joined
Jul 13, 2018
Messages
7
Follow up question..... If all the attributes of a product are to show up among details on the invoice, does that mean that these attributes should have been included in the details table schema? The fact that ProductID was included in the Details schema is not enough?
 

jdraw

Super Moderator
Staff member
Local time
Today, 04:15
Joined
Jan 23, 2006
Messages
15,364
Here is a free data model for a typical Customer, Order, Invoice, Product application.
It is generic -you can add/ignore/change/delete anything to fit your situation.

Get your tables set up and tested first, before jumping to forms. Make sure you can access the data you need for reports, forms. Once you get it tested with test data and scenarios --then build your physical database from your "tested blueprint".

Work through 1 or 2 of the tutorials from RogersAccessLibrary mentioned in this link on Database Planning, Design etc.


Good luck.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:15
Joined
Feb 28, 2001
Messages
27,001
If all the attributes of a product are to show up among details on the invoice, does that mean that these attributes should have been included in the details table schema?

That old book about "Everything I Need to Know, I Learned in Kndergarten" has lots of good adivce about this. The applicable rule is that "you put similar things together, which is where they belong." E.g. You don't put a baseball in a doll house.

So if you have product description and it includes a size, color, weight, dimensions, etc., and if another product that differs by even a little bit in only ONE of those attributes has a different product ID, then you are doing it right - because the unique product attributes go with the unique product ID. It is OK for two products with two different ID codes to both be candy-apple red as long as some other attribute differs between the two.

An example of what DOESN'T go in the product table is the answer to the question "How many Left Handed Veeblefetzers were delivered from the Gaines company last week?" That goes into a separate transaction table that is related to but not identical with the product table. Does that help?
 

Cronk

Registered User.
Local time
Today, 19:15
Joined
Jul 4, 2013
Messages
2,770
Redridinghood,


You have been offered good advice.

To answer your specific question
Code:
does the Details table have to include a lookup link for each attribute?
the answer is no.
Your Details table would include a ProductID for each line. A relational join to the Products table will provide the "attributes" for inclusion on the invoice.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:15
Joined
Feb 19, 2002
Messages
42,981
Being a little more specific.
1. Do NOT use a table level lookup. This is a crutch that will come back to haunt you once you know better. Remove the lookup from the table.
2. Use a combo on the form to select a product from the list.
3. Use a query as the RecordSource for the details subform. The query joins the details table to the "lookup" table. You can select the columns you need to update from the details table and the columns you want for reference from the product table. One thing you should do though is to set the locked property to Yes for all the "lookup" controls. You do NOT want to accidentally change a product description on the details form because that would change the description on EVERY instance of that product. So, do be careful with forms that are based on queries that include lookup data.

Taking the higher level view, we normalize data by separating it into tables where we can ensure that each piece of information is stored only once. What we don't talk about as much is how we bring back all this correctly normalized data so that it is "information" again. We do this by using queries that join tables (or other queries) so that we can get columns from multiple tables/queries returned together in the same row.
 

Users who are viewing this thread

Top Bottom