Multi Table query

[Guest]

I have put this question into the new users as it is probably a very simple
one for the experts out there and I am a new user .

I am in the process of trying to create a suppliers/products database and to
create a purchase order from that. I have checked out Northwind and the
inventory control template for the way the forms and queries are structured,
and to help me create my purchase order.

My question is this, and please forgive me if it sounds stupid but i really
do have to ask :- when you create a multi table query from the order detail
and products tables, why do you have to have the product name from the
products table and everything else from the order details table? I was
playing around with the query fields to see what would happen, and when i
selected the productsID from the product table, it would not let me add any
data to the form i made out of this query. (order detail subform).
One final question - as I have said, i have my query set up pretty much like
the northwind one and on my subform, the Unit Price field is not showing any
data. I changed the query slightly for the product ID to select Product ID,
Unit Price from etc..., and on the unit price field I selected
=ProductID.column(2) and it is now showing but off the top of your head, any
ideas why it didnt work out when i did it the way i was supposed to??

Thanks in advance

 

[Guest]

Hi. Thanks for your reply. I'll try your suggestion. I have Microsoft
Office Access 2003 step by step plus a whole box file of printed forum topics
etc which was kind of helpful and i'm gonna buy The Access Bible tomorrow.
What I wanted to know was the sort of "bare bones" as to WHY i have to pick
the product ID from the products table. Why not the order details table?
its really bugging me!! I think its something to do with the relationships
and the join properties, but I cant get my head round it yet!! Thanks for
your suggeston though.
Best wishes

 

[Guest]

You need to look at a book on SQL. A query is written in the SQL language
(behind the scenes if you use the query builder). You may join 2 tables on
one or more columns and in the result set you may include one or more columns
from one or both tables. E.g.
Select P.one, O.one, etc. etc. from Products as P, Orders as O
where P.ID = O.ID