Select single record

[tizwaz]

I have a form which is going to search a table of contracts for anything with a supplier in the main supplier table or in the related supplier table and to return that record. the idea being the supplier might be the main supplier or a sub-contractor. However when I enter a supplier who is a main supplier it returns several records - one for each of the sub-contractors. My SQL follows: How can I prevent this happening?

SELECT [TblContract].[ContractID], [TblContract].[FileRef], [TblContract].[Title], [TblContract].[StartDate], [TblContract].[AnticEndDate], [TblContract].[BusTeam], [TblContract].[RelatedFileRef], [TblContract].[ContractClosed], [qrysupplier].[Supplier], [qrysupplier].[SupplierID], [qrysupplier].[Supplier], [TblRelatedSupplier].[SupplierID], [TblRelatedSupplier].[Work], [TblContract].[Location]
FROM (TblContract LEFT JOIN qrysupplier ON [TblContract].[ContractID]=[qrysupplier].[ContractID]) LEFT JOIN TblRelatedSupplier ON [TblContract].[ContractID]=[TblRelatedSupplier].[ContractID]
WHERE ((([TblContract].[ContractClosed]) Is Null) And (([forms]![frmsearchsupplier]![cbosupplierid]) In ([qrysupplier].[SupplierID],[tblrelatedsupplier].[supplierid]))) Or ((([forms]![frmsearchsupplier]![cbosupplierid]) In ([qrysupplier].[SupplierID],[tblrelatedsupplier].[supplierid])) And (([forms]![frmsearchsupplier]![chkclosed])=True));

 

[orna]

SELECT TOP 1 [TblContract].[ContractID], [TblContract].[FileRef]......

Are you sure you need two different tables?

 

[lespaul]

how is a main supplier different, in terms of the data stored in the database, from a sub-contractor?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[tizwaz]

Thanks for this but what I need is for it to return all records for the supplier that I specify in the box in my form. That supplier could appear in either the main contract details table which has the fields contract ID, Supplier ID etc. However if there is also a subcontractor I have a table which contains the fields contract ID, supplier ID and type of work supplied.

Both supplier IDs are contained in the supplier table.
Adding TOP 1 to the SQL gives only one record regardless of how many contracts there are for a supplier. This is not what I need. I need all contracts for the specified supplier. What I don't need is the same contract shown several times because it has more than one sub-contrator. Maybe I haven't set out my table in the best format - is there anyway I can do what I want without re-gigging the database

 

[lespaul]

perhaps you can share some sample records from tblSupplier, tblcontractor and tblcontracts and what the results you would expect from those samples to return.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi