Problem discarding an (almost) duplicate row from a query because of ORDER BY clause. 1

[PeterMAS]

Access 2013, Windows 7
I am using expressions and Duane Hookom's concatenate function in SQL queries to convert database fields into readable text. My problem is I can't use SELECT DISTINCT to discard a duplicate row because of the distinct field required for the ORDER BY clause.

Queries B & C further down are where the problem lies. I have included query A for reference.
If you're not familiar with Duane's concatenate function that I used in Query A:
faq701-4233

Query A

SELECT DISTINCT qcnDhmSsn1No1.RecID, qcnDhmSsn1No1.Ssn, qcnDhmSsn1No1.Dayab, Concatenate("SELECT FROM qcnDhmSsn1No1 WHERE Dayab =""" &  & """") AS HmCon, qcnDhmSsn1No1.DayaID
FROM qcnDhmSsn1No1
ORDER BY qcnDhmSsn1No1.DayaID;

Sample output from query A

RecID Ssn              Dayab    HmOpCl       DayaID
3     Mar 30-Nov 30    Mon      Closed       1
3     Mar 30-Nov 30    Tue-Thu  09:30-17:15  2
3     Mar 30-Nov 30    Fri      09:30-20:45  5
3     Mar 30-Nov 30    Sat-Sun  09:30-17:15  6

Query B

SELECT qcnDhmSsn1No2.RecID, qcnDhmSsn1No2.Ssn, Concatenate("SELECT [Dayab] FROM qcnDhmSsn1No2 WHERE HmCon =""" & [HmCon] & """"," & ") AS DayCon, qcnDhmSsn1No2.HmCon, qcnDhmSsn1No2.DayaID
FROM qcnDhmSsn1No2
ORDER BY qcnDhmSsn1No2.DayaID;

Sample output from query B

RecID  Ssn              DayCon            HmCon          DayaID
3      Mar 30-Nov 30	Mon	          Closed         1
3      Mar 30-Nov 30	Tue-Thu & Sat-Sun 09:30-17:15    2
3      Mar 30-Nov 30	Fri	          09:30-20:45    5
[highlight #CC0000]3      Mar 30-Nov 30	Tue-Thu & Sat-Sun 09:30-17:15    6[/highlight]

This is where my problem is. I want to discard the (almost) duplicate (in red) but SELECT DISTINCT doesn't help because DayaID is different. And if I remove DayaID from the query, it orders the fields alphabetically by DayCon so that Fri appears before Mon.

Query C

SELECT DISTINCT qcnDhmSsn1No3.RecID, qcnDhmSsn1No3.Ssn, [DayCon] & ": " & [HmCon] AS Dhm, qcnDhmSsn1No3.DayaID
FROM qcnDhmSsn1No3
ORDER BY qcnDhmSsn1No3.DayaID;

Sample output from query C

RecID   Ssn             Dhm                             DayaID
3	Mar 30-Nov 30	Mon: Closed                     1
3	Mar 30-Nov 30	Tue-Thu & Sat-Sun: 09:30-17:15  2
3	Mar 30-Nov 30	Fri: 09:30-20:45                5
3	Mar 30-Nov 30	Tue-Thu & Sat-Sun: 09:30-17:15  6

Thank you

Peter

 

[Golom]

You could try something like this

SELECT RecID, Ssn, [DayCon], [HmCon], MIN(DayaID) As MINDayaID
FROM [red](Query B) As X[/red]
Group By RecID, Ssn, [DayCon], [HmCon]
ORDER BY 5;

Obviously though, you are presenting only a small sample of your data and grouping like that may have unpleasant implications for other parts of the query.

 

[PeterMAS]

Thank you Golom. I would never have been able to do this. It worked like a charm. I didn't use it as code, I pasted it into SQL design view and it worked (the design grid reinterpreted your code).

An explanation in case it's useful to someone else: These are part of a series of queries. There are more queries before and after my examples. The first query in the series (not shown) is generated by code as the form moves from record to record.

I placed the new query between Query B & C above. Here's the SQL:
qcnDhmSsn1No3A

SELECT X.RecID, X.Ssn, X.DayCon, X.HmCon, Min(X.DayaID) AS MINDayaID
FROM qcnDhmSsn1No3 AS X
GROUP BY X.RecID, X.Ssn, X.DayCon, X.HmCon
ORDER BY 5;

I had to remove DISTINCT from the following query because it conflicts with GROUP BY, but it is no longer needed.

Query C now looks like this

SELECT qcnDhmSsn1No3A.RecID, qcnDhmSsn1No3A.Ssn, [DayCon] & ": " & [HmCon] AS Dhm, qcnDhmSsn1No3A.MINDayaID
FROM qcnDhmSsn1No3A
ORDER BY qcnDhmSsn1No3A.MINDayaID;

Thanks again
Peter