MS Access - How to show dates officers participate in skill test

Question

I have a MS Access query that shows officer ID's with test scores and the dates they took each type of annual certification test. However, I'm trying to group by officer ID and list on a single row for each officer the dates for each type of test they took so I can flag when they are due to take the same type of test again. The officers don't take all types of tests on the same day, and typically not on on the same day as other officers. As an example, an officer may qualify for a daytime shoot and nighttime shoot on one day, then 4 months later qualify at a rifle shoot. I would like to show all the dates for each officer where scores were entered regardless of the score.

The fields in my query are:

OfficerID, TestDate, DayScore, NightScore, RifleScore

I'm trying to get it to obtain results like these:

JohnDoe123, 03/15/2010, 03/15/2010, 10/04/2010

FJacobs123, 02/01/2010, 04/27/2010, 11/11/2010

I'm basically substituting the qualification date for the score. It seems like it should be pretty simple, but I'm stuck. Please help.

Thanks.

score 0 · Answer 1 · edited May 23 '17 at 10:34

You need a function to concatenate the values. See Microsoft Access condense multiple lines in a table

Your query would then look something like:

Select OfficerId
    , GetList( "Select Format( CertificateDate, 'm/d/yyyy' ) 
                    From OfficerCertificates As O1 
                    Where O1.OfficerID = " & Officers.ID & " 
                    Order By CertificateDate" ) As CertificateDates
From Officers

score 0 · Answer 2 · answered May 11 '11 at 19:49

Another way to get around this is to build a report table that you could fill the rows as you go, instead of a function to build the result on the fly.

For me, the choice comes down to how often you run the query, and how often the data changes.

If you only run it once a month, or something, or if the data is changing often, then use the special query.

If you show the results often, and the data does not change often, you could really benefit from having a report table instead.

Brett Rossier · Answer 3 · 2011-05-16T20:03:27.193

Perhaps not exactly what you want, but look into CrossTab queries. That can pull in all values in a single column and display them as their own fields in line with their associated record. What you'd end up with is a matrix of names and dates, and your query could put a "Y" in that date field if a test was taken on that date. This would also vertically align officers that took tests on the same days.

Officer Name | 02/01/2010 | 03/15/2010 | 03/15/2010 | 04/27/2010 | 10/04/2010 | 11/11/2010
JohnDoe123   |            |     Y      |     Y      |            |     Y      | 
FJacobs123   |     Y      |            |            |     Y      |            |     Y

MS Access - How to show dates officers participate in skill test

3 Answers3