DISTINCT value within statement

[muppet123]

HI,
I have the following SQL query statement that I need to attach a distinct value onto one of the fields i.e.[Tbl_Decision Maker].[Decision Maker Surname] but cannot seem to get it right. Any help would be GREATFULLY received.


SELECT Tbl_CusDetails.[Company Name], [Tbl_Decision Maker].[Decision Maker Title], [Tbl_Decision Maker].[Decision Maker First Name], [Tbl_Decision Maker].[Decision Maker Surname], [Tbl_Decision Maker].[Job Title], Tbl_CusDetails.Telephone, Tbl_mailing_campaign.[Mailing Campaign Name], tbl_Stage_2_Status.Stage_2_Status, Tbl_CusDetails.Stage_1_CapturedBy

FROM tbl_Stage_2_Status, (Tbl_CusDetails INNER JOIN [Tbl_Decision Maker] ON Tbl_CusDetails.CompanyID = [Tbl_Decision Maker].[Customer ID]) INNER JOIN Tbl_mailing_campaign ON Tbl_CusDetails.CompanyID = Tbl_mailing_campaign.ClientId

WHERE (((tbl_Stage_2_Status.Stage_2_Status)="2QCIAH" Or (tbl_Stage_2_Status.Stage_2_Status)="2QCIAW" Or (tbl_Stage_2_Status.Stage_2_Status)="2QCITH" Or (tbl_Stage_2_Status.Stage_2_Status)="2QCITW"));


TIA

Rob

 

[PCLewis]

You have a query that will return a cartesian product of two tables. Is that your intention? If there are 4 records returned from tbl_Stage_2_Status (as the where clause suggests there could be) and say, 10 unique [Decision Maker Surname] in [Tbl_Decision Maker] then you will get 4 copies of each surname in the returned recordset. There is no way to guarantee a distinct [Decision Maker Surname] given your query.

Is tbl_Stage_2_Status related to any of the other tables in any way?

 

[muppet123]

Hi PCLewis

Thanks for your info'. I am trying to get one name [Decision Maker Surname] that meets the criteria from tbl_Stage_2_Status. So the result can eventually used for a mail merge.

Would it be best if I created a DISTINCT query first on [Decision Maker Surname] and then ran another query with the remaining fields, possibly ?

Not wishing to excuse myself here, but am still very much in the starting area of the learning curve when it comes to anything other than basic Access stuff.

TIA

Rob

 

[PCLewis]

The big problem for me is in understanding the link between those tables. Without that knowledge, it's impossible to give a definitive answer. I can describe what the answer will look like however:

SELECT Tbl_CusDetails.[Company Name], [Tbl_Decision Maker].[Decision Maker Title], [Tbl_Decision Maker].[Decision Maker First Name], [Tbl_Decision Maker].[Decision Maker Surname], [Tbl_Decision Maker].[Job Title], Tbl_CusDetails.Telephone, Tbl_mailing_campaign.[Mailing Campaign Name], tbl_Stage_2_Status.Stage_2_Status, Tbl_CusDetails.Stage_1_CapturedBy

FROM tbl_Stage_2_Status inner join (Tbl_CusDetails INNER JOIN [Tbl_Decision Maker] ON Tbl_CusDetails.CompanyID = [Tbl_Decision Maker].[Customer ID]) INNER JOIN Tbl_mailing_campaign ON Tbl_CusDetails.CompanyID = Tbl_mailing_campaign.ClientId) on tbl_Stage_2_Status.somefield = tbl_CustDetails.somefield

WHERE (((tbl_Stage_2_Status.Stage_2_Status)="2QCIAH" Or (tbl_Stage_2_Status.Stage_2_Status)="2QCIAW" Or (tbl_Stage_2_Status.Stage_2_Status)="2QCITH" Or (tbl_Stage_2_Status.Stage_2_Status)="2QCITW"));

In other word, lose the comma and describe the join in the SQL. If you had no knowledge other than what was in your original post, I am sure you would see the problem. There is simply nothing in there to let anyone know how those tables are related.