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
Sample output from query A
Query B
Sample output from query B
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
Sample output from query C
Thank you
Peter
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
SQL:
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
Code:
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
SQL:
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
Code:
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]
Query C
SQL:
SELECT DISTINCT qcnDhmSsn1No3.RecID, qcnDhmSsn1No3.Ssn, [DayCon] & ": " & [HmCon] AS Dhm, qcnDhmSsn1No3.DayaID
FROM qcnDhmSsn1No3
ORDER BY qcnDhmSsn1No3.DayaID;
Sample output from query C
Code:
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