Mutiple table query

[Guest]

I would greatly appreciate someone showing me an example of how I can query two or more tables that share the same fields in each table

For exampl
Table 1 Feild
Invoice
Stock
Qt
Pric

Table 2 Feild
Ship to addres
Invoice
Ship Dat
Qt

I need to query Table 1 for the Invoice # and Price and Table 2 for the Ship Date. But when I do I get back two lines for each record.

Example
Invoice # Price Ship Dat
1001 50.00 2/2/0
1001 50.00 2/2/0
1002 10.00 2/12/0
1002 10.00 2/12/0

and so on

I think that I may need to run a query in a query but I have no clue about how to so

Thank you in advance for your help

 

[Wayne Morgan]

You should be able to do this in the design grid. Add both tables to the
grid. Drag the Invoice# field from Table1 to the Invoice# field in Table2
and drop it. You should get a link line between the 2 tables connecting this
field. Next, double click each of the fields you want to show in the query
and they will be added for you. The SQL for the query should look like this:

SELECT Table1.Invoice#, Table1.Price, Table2.[Ship Date]
FROM Table1 INNER JOIN Table2 ON Table1.Invoice# = Table2.Invoice#;

It is recommended to not use symbols, such as #, in the names of items. They
can cause you problems in certain circumstances. Any table and field names
with spaces in the, such as Ship Date above, will have to have brackets
around them. If the don't have spaces, you don't need the brackets, but they
can be there.


--
Wayne Morgan
Microsoft Access MVP

I would greatly appreciate someone showing me an example of how I can

query two or more tables that share the same fields in each table.

For example
Table 1 Feilds
Invoice #
Stock #
Qty
Price

Table 2 Feilds
Ship to address
Invoice #
Ship Date
Qty

I need to query Table 1 for the Invoice # and Price and Table 2 for the

Ship Date. But when I do I get back two lines for each record.