Query Problems

[Guest]

Hi all,i am a new user of access,i am now using access 2007,i wanted to link
the 3 tables together,to create a query that will returns records of
company,part number,description,selling price and date of purchase.below are
my tables in datasheet view;
Customer details Products detail sales detail
id id id
Company part number company
Address alternate part number
Telephone model description
description selling price
date of
purchase

Maybe it will be a simple thing,but it seems difficult to me as i am a
newbie to access.hope you all can help.if there are any solutions,please give
the details too,thanks alot in advance

 

[Allen Browne]

Is there any chance that a customer could buy more than one product at once?

If so, you need a structure very similar the the example in the Northwind
sample database that installs with Access. Open Northwind. The click
Relationships on the Tools menu. This gives you a graphical example of how
to create the tables for customers, products, orders, and order details (the
line items on the order.)

That's how you need to relate your tables.

 

[Pat Hartman \(MVP\)]

As Allen said, there is a missing table in this structure. The missing
table is the OrderHeader which would normally hold the CompanyID field. The
SalesDetail table would hold the id of the OrderHeader and you would join
the SalesDetail to the OrderHeader to get the CompanyID and use that to join
to the CompanyDetails table..

To create the query, add all three tables to the QBE grid. If you have
defined relationships, Access will automatically create the joins for you.
(Relationships should ALWAYS be defined and Referential Integrity should
ALWAYS be enforced unless this is a data warehouse application which is not
updated except by bulk appends/updates). To create the joins on the fly,
just click on the ID field and drag it to the matching field in the child
table. So CustomerID in CustomerDetails goes to CompanyID in SalesDetail
and ProductID goes to ProductID in SalesDetail -- notice the changes I made
to your names. ID is meaningless. Use descriptive names for your primary
key fields and use the name consistently wherever it appears. Also, do not
use spaces or special characters, except for the underscore "_" in any of
your names. you will thank me for this advice if you ever need to write
VBA. Lots is written regarding naming conventions. Pick one and stick to
it. Also avoid using one word names that might be reserved words for Access
or SQL. Names in this category are things like "Name", "Date", "Month",
etc. You can find a list in help if you have a question.

 

[Guest]

Thanks alot for your help,have a nice day

Is there any chance that a customer could buy more than one product at once?

If so, you need a structure very similar the the example in the Northwind
sample database that installs with Access. Open Northwind. The click
Relationships on the Tools menu. This gives you a graphical example of how
to create the tables for customers, products, orders, and order details (the
line items on the order.)

That's how you need to relate your tables.

 

[Guest]

Thanks alot for your help.have a nice day

As Allen said, there is a missing table in this structure. The missing
table is the OrderHeader which would normally hold the CompanyID field. The
SalesDetail table would hold the id of the OrderHeader and you would join
the SalesDetail to the OrderHeader to get the CompanyID and use that to join
to the CompanyDetails table..

To create the query, add all three tables to the QBE grid. If you have
defined relationships, Access will automatically create the joins for you.
(Relationships should ALWAYS be defined and Referential Integrity should
ALWAYS be enforced unless this is a data warehouse application which is not
updated except by bulk appends/updates). To create the joins on the fly,
just click on the ID field and drag it to the matching field in the child
table. So CustomerID in CustomerDetails goes to CompanyID in SalesDetail
and ProductID goes to ProductID in SalesDetail -- notice the changes I made
to your names. ID is meaningless. Use descriptive names for your primary
key fields and use the name consistently wherever it appears. Also, do not
use spaces or special characters, except for the underscore "_" in any of
your names. you will thank me for this advice if you ever need to write
VBA. Lots is written regarding naming conventions. Pick one and stick to
it. Also avoid using one word names that might be reserved words for Access
or SQL. Names in this category are things like "Name", "Date", "Month",
etc. You can find a list in help if you have a question.