Relationship/Join Type(s) to be Established 1

[Mike555]

I have 2 tables: tblAdhesives & tblPurchasesAdhesives

Table tblAdhesives contains a record for every product that we use. It has fields such as ID, Manufacturer, Type, etc.

Table tblPurchasesAdhesives contains purchase records for products that we purchase. This table contains a field named AdhesiveType which is a combo box listing the records of tblAdhesives. When the user makes a selection from the combo box, the ID of the record (from tblAdhesives) is returned for the AdhesiveType of the tblPurchasesAdhesives table.

What type of relationship/join should be used if I wish to create a query that would allow me to query on a field in the tblAdhesives table (such as Manufacturer), and see all applicable records in the tblPurchasesAdhesives table? Or would a different table structure be required to do this?

Thanks.

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[lespaul]

Paste this in the SQL view:

Select * from tblPurchasesAdhesives INNER JOIN tblAdhesives on tblPurchasesAdhesives.AdhesiveID = tblAdhesives.AdhesiveID WHERE Manufacturer = 'Joe Blow Adhesive'

HTH

leslie

 

[Mike555]

LesPaul, Thanks for the response. However, I'm getting a "TYPE MISMATCH IN EXPRESSION" error.

SELECT *
FROM tblPurchasesAdhesives INNER JOIN tblAdhesives ON tblPurchasesAdhesives.AdhesiveType=tblAdhesives.AdhesiveID
WHERE Manufacturer='Test';


Thanks!

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[lespaul]

Are AdhesiveType in tblPurchasesAdhesives and AdhesiveID in tblAdhesives the same field type?


Leslie

 

[Mike555]

No - AdhesiveID in tblAdhesives is TEXT
AdhesiveID in tblAdhesives is AUTONUMBER (and its also the primary key for tblAdhesives).

Thanks.

--
Mike

Why make it simple and efficient when it can be complex and wonderful?

 

[lespaul]

then you need to change the AdhesiveId in tblAdhesives to a long integer in order to have a relationship between those two fields.

If you can't change the field in the table, I'm not sure what you should/can do in order to make the query work!

Leslie