Query Help

[jim11]

Is it possible to set up a query that can lookup fields from one table to another table? For example, i have 3 tables:

tblClientMachine:
ClientMachineID (PK)
ClientID
MachineID

tblClient
ClientID (PK)
Surname
Forename
etc

tblMachine
MachineID (PK)
Make
Model
etc

I have a combo box which shows the contents of tblClientMachine, but when i click the arrow it just displays the ClientID number and MachineID number, where as i want it so it displays Client Surname, Forename and Machine Make and Model. This is probably a simple query to set up, its just im not 100% sure on howto do it.

Ne help with this?

Thanks all
Jim

 

[RV]

Make a query based on your 3 tables.
Join the tables in your query and add your colums to the query.
Save the query and use it as Row Source for your combo box.

PS you don't need the column ClientMachineID in your table tblClientMachine assuming this is your junction table between the 2 other tables and you've created relationships between your tables.

RV

 

[jim11]

yeh, ive created relationships using ClientMachineID, trust me m8, this is needed lol. Its a junction table as well.

Right i did what you said, and yeh it worked. However i also forgot to mension i have a 4th table called tblMake in which the machine Make is stored in. tblMake table's structure is MakeID (PK - autonumber) and Make (text). When i run the query i get everything displayed as it should except the Make column is displaying the MakeID and not the actual make. How do i extend the query further to include the make? Ive tried adding the tblMake table and replacing the make from the tblMachine with the make from the tblMake, but then the query shows nothing.

How do i get it showing the Make name instead of the MakeID? Im guessing i need to extend the query further, but how?

Thanks
Jim

 

[jim11]

ne 1 help?