SQL Help (1 Viewer)

[spalmateer]

Hi,
I'm in the process of creating a search form for my database. Works great but its showing multiple lines for each customer in the result. Is there a way I can modify this to eliminate duplicate customers in the results. I've tried SELECT DISTINCT but that doesn't work.

SELECT [tblCustomer].[CustomerID], [tblCustomer].[CompanyName], [tblCustomer].[LastName], [tblCustomer].[FirstName], [tblCustomer].[Address1], [tblCustomer].[Address2], [tblCustomer].[City], [tblCustomer].[PostalCode], [tblCustomer].[HomePhone], [tblCustomer].[Model], [tblCustomer].[Builder],[tblOrderDetail].[ProductID], [tblOrderDetail].[SerialNumber]
FROM (tblCustomer INNER JOIN tblOrder ON [tblCustomer].[CustomerID]=[tblOrder].[CustomerID]) INNER JOIN tblOrderDetail ON [tblOrder].[InvoiceID]=[tblOrderDetail].[InvoiceID]
WHERE ([tblCustomer].[Model] Like [Forms]![frmSearch]![Model] Or [Forms]![frmSearch]![Model] Is Null) AND ([tblCustomer].[PostalCode] Like [Forms]![frmSearch]![txtPostalCode] Or [Forms]![frmSearch]![txtPostalCode] Is Null) AND ([tblOrderDetail].[ProductID] Like [Forms]![frmSearch]![ProductID] Or [Forms]![frmSearch]![ProductID] Is Null) AND ([tblOrderDetail].[SerialNumber] Like [Forms]![frmSearch]![SerialNumber] Or [Forms]![frmSearch]![SerialNumber] Is Null)

Any help would be appreciated!
Scott

[This message has been edited by spalmateer (edited 05-03-2002).]

 

[llkhoutx]

Cut and paste your query to the QBE frame and try to run it there.

If that doesn't run, build your query in the QBE frame, convert it to SQL and cut & paste it into your program.

 

[ChampionDuy]

I am having the same problem that you did were you able to fix it? If so How did you do it?

 

[spalmateer]

No,
I haven't been able to get it to work as of yet. The problem is that when including other tables in the query results (tblOrders and tblOrderDetails), its showing only customers who have an order record instead of showing all customers. I'll keep at it and hopefully come up with a solution. What are you attempting to do Championguy? Someone of the like?
Scott

 

[ChampionDuy]

what am I attempting to do? I am not sure. Yet... I will get back though.

 

[Travis]

You are using an INNER Join, this is a join that only includes data from both tables if there is a match.

What you are wanting will be a RIGHT/LEFT Join. This will include all the records from one table and only records from the other table that match the joined field. For those records that don't have a match the fields in the joined table will return "NULL".

 

[spalmateer]

Thanks for your reply Travis,
Would I need to change the actual relationships in my database or could I specify the join type in the query. Or would it simply require replacing the above SQL statement to read Left join instead of Inner?