Access query - Combine multiple values into a single field
I have a table that contains multiple exam events for many examiners, and I want to display this information in a single field in the format Sep 13, Nov 13, Jan 14.
I also do not want to specifiy a limit as new exams are occuring all the time so I just want these added whenever I run the related report.
I attach an extract from the DB as an example, but I want to do the work in Access.
Query2.xlsx
I also do not want to specifiy a limit as new exams are occuring all the time so I just want these added whenever I run the related report.
I attach an extract from the DB as an example, but I want to do the work in Access.
Query2.xlsx
als315
Can you show expected result?
For concatenation you can use function DConcat from Patrick G. Mattews article:
Domain Aggregate for Concatenating
Version adapted for your case is included. There are 2 ways:
1. Query1 - all is in one query, but it is very slow, because function is calculated for each line of source table
2. Query0, Query01 - in first query exam lines are grouped by pID, in second Dconcat is made only for unique pIDs. This version is faster
DBExams.accdb
Domain Aggregate for Concatenating
Version adapted for your case is included. There are 2 ways:
1. Query1 - all is in one query, but it is very slow, because function is calculated for each line of source table
2. Query0, Query01 - in first query exam lines are grouped by pID, in second Dconcat is made only for unique pIDs. This version is faster
DBExams.accdb
ASKER
Hi als315
Thank you for the detailed response.
If I am reading this correctly, Query 0 & 01 are used together to produce the output. I prefer the speed of this one, as the eventual output will form part of a bigger report so speed is essential.
With respect to the sort order of the dates, will I do this at source level before I feed into this query, or can it be done within this query?
Thanks
Andy
Thank you for the detailed response.
If I am reading this correctly, Query 0 & 01 are used together to produce the output. I prefer the speed of this one, as the eventual output will form part of a bigger report so speed is essential.
With respect to the sort order of the dates, will I do this at source level before I feed into this query, or can it be done within this query?
Thanks
Andy
Yes, queries 0 and 01 are used sequentally.
You can sort dates in query, but only if this field is date/time. In your sample it has text type, so we can order it only alphabetically. Try to play with your query (Query2) or upload DB with sample source table.
You can sort dates in query, but only if this field is date/time. In your sample it has text type, so we can order it only alphabetically. Try to play with your query (Query2) or upload DB with sample source table.
ASKER
Herewith with an exported source table.
The actual data is a merge of 2 tables, and i changed the format of the Exams column to show as 'mmm yy', hence it showing as a text value and not date. I have included the original startdate column in the attached sample as i would imagine it is easier to sort by date using this field.
Sample.accdb
The actual data is a merge of 2 tables, and i changed the format of the Exams column to show as 'mmm yy', hence it showing as a text value and not date. I have included the original startdate column in the attached sample as i would imagine it is easier to sort by date using this field.
Sample.accdb
Try this sample. To the original function was added one parameter (Order by)
Sample.accdb
Sample.accdb
ASKER
Hi als315
This example only has Query 1in it, did you post the correct sample?
Andy
This example only has Query 1in it, did you post the correct sample?
Andy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi als315
Thank you so much for your help, I made a couple of tweaks and it is now looking great on my report so I am very grateful for your expertise.
Andy
Thank you so much for your help, I made a couple of tweaks and it is now looking great on my report so I am very grateful for your expertise.
Andy