Query help

Allan320 · June 16th, 2006, 09:13 AM

I have a query that worked fine. I needed to add 1 more table in and when i did it went haywire. When i select one record it is setting all the records with Create CSV to true and picking them all up.

Here it is

Code:

SELECT [Clients].[Title], [Clients].[Full Name], [Clients].[Address], [Clients].[Post Code], [Proposals].[Agreement Date (Supplier Paid Date)], [Proposals].[Doc Fee], [Proposals].[Commission (Â£)], [Proposals].[VAT on Commission], [Proposals].[Fixed /Variable], [Proposals].[OTP Fee], [Proposals].[Asset Description], [Proposals].[Registration Number], [Proposals].[Chassis Number], [Proposals].[Serial Number], [Proposals].[Year Of Manufacture], [Proposals].[New / Used], [Proposals].[Cash Price], [Proposals].[VAT], [Proposals].[Deposit], [Proposals].[Drawdown], [Proposals].[Term], [Proposals].[Instalment Interval], [Proposals].[Instalment Value], [Proposals].[Final Installment - Balloon], [Proposals].[Final Payment], [Proposals].[Date to First Payment], [Proposals].[Date of Balloon], [Clients].[Sort Code], [Clients].[Acc number], [Clients].[Acc Name], [Proposals].[Create CSV]
FROM Clients, Proposals
WHERE ((([Proposals].[Create CSV])=True));

Anyone have any ideas on how to just get the one record. When i was using it with just the one table getting 1 record was no problem

Allan

kindler · June 16th, 2006, 02:33 PM

You never indicate that you just want to show the Client entry taht corresponds to the Proposal entry. I assume that somewhere in the Proposals table there is a field that identifies the Client making the proposal (call it ClientID for now), and that it matches up with some field in the Clients table (call it ID for now).

Your query should look like:

SELECT ...
FROM Proposals LEFT JOIN Clients ON Proposals.ClientID = Clients.ID
WHERE [Proposals].[Create CSV] = True;