EASY - Select from multiple tables

V

Vacation's Over

I have a database with one table which is related to 5 other tables

ie. field productid in main table ties to product table with all details on
that product

question is how do i structure SQL code to return productname from product
table instead of productid from main table.

In one query I would do this to all 5 fields returning names instead of id

Thanks
 
J

John Vinson

I have a database with one table which is related to 5 other tables
Click to expand...

Five other tables? or to the Product table five times?
ie. field productid in main table ties to product table with all details on
that product

question is how do i structure SQL code to return productname from product
table instead of productid from main table.

In one query I would do this to all 5 fields returning names instead of id

Thanks
Click to expand...

I *THINK* - and I may not understand your structure correctly - that
you just need to add the Product table to the query five times, linked
to each of the five (improperly normalized IMO :-{( ) fields. Select
the five name fields from the five linked instances.

John W. Vinson[MVP]
 
V

Vacation's Over

John -
Thanks. - I think I better explain in more detail what I am asking.

I'll work with 3 tables to get the structure across:

Product table has primary key of "productID" - (internal unique number)
other fields pertain only to that product details - like "ProductName"

Location table has primary key of "LocationID" - (internal unique number)
other fields pertain only to that Location details - like "LocationName"

Transaction table is updated regularly with "transactions" :
productID, LocationID, ....TSale

The queries get more complicated but i am first looking for a query to
return :
From a query of TSale
LocationName , ProductName, TSale ...

Then I can order and sort, and then report based on alpha text instead of
the ID #
It seems to me to be some sort of lookup or substitution requirement.

Thanks and sorry I was not clearer my first post.

PS: i do know VBA Excel quite well but just a newbie with Access :) Thought
the above structure, and question now better explianed were rather Normal
(ized).
 
V

Vacation's Over

John -
After some more research i am now using 5 nested inner joins and it seems to
work although a long SQL is required.
 
Top