Query with heading and row and listing

[Harris@Z]

Hope someone can help!
I have a query that produces results for DateReportDue, SampleID and TestedFor.

I would like to produce either a query or report where I have the row name as TestedFor, the column name as DateReportDue and list the SampleIDs that are contained in both.

In other words (not representative of data below)

	10 November	11 November	12 November	13 November
Peanut	56973 56965	56985
Milk	56965	56965 56966 56967	56968	56969 56970
Celery		56967		56971

Data query/table looks like this

DateReportDue	Sample_ID	TestedFor
05-Nov-2025	Soy
05-Nov-2025	Soy
05-Nov-2025	Peanut
06-Nov-2025	Peanut
06-Nov-2025	Peanut
07-Nov-2025	Peanut
07-Nov-2025	Peanut
07-Nov-2025	Peanut
07-Nov-2025	Celery
07-Nov-2025	Milk
07-Nov-2025	Soy
07-Nov-2025	Peanut
07-Nov-2025	Milk
07-Nov-2025	Celery
07-Nov-2025	Peanut
07-Nov-2025	Peanut
07-Nov-2025	Peanut
07-Nov-2025	Peanut
07-Nov-2025	Peanut
10-Nov-2025	Peanut
11-Nov-2025	Peanut
13-Nov-2025	Mustard
17-Nov-2025	Celery
19-Nov-2025	Total Milk

[td]

56875​

[/td]​

[td]

56876​

[/td]​

[td]

56789​

[/td]​

[td]

56912​

[/td]​

[td]

56913​

[/td]​

[td]

56925​

[/td]​

[td]

56926​

[/td]​

[td]

56927​

[/td]​

[td]

56928​

[/td]​

[td]

56929​

[/td]​

[td]

56930​

[/td]​

[td]

56935​

[/td]​

[td]

56935​

[/td]​

[td]

56935​

[/td]​

[td]

56938​

[/td]​

[td]

56931​

[/td]​

[td]

56932​

[/td]​

[td]

56933​

[/td]​

[td]

56934​

[/td]​

[td]

56973​

[/td]​

[td]

56985​

[/td]​

[td]

56692​

[/td]​

[td]

56802​

[/td]​

[td]

56922​

[/td]​

Can anyone please advise?

 

[theDBguy]

I can’t see all the sample data but maybe investigate using a crosstab query.

Sent from phone…

 

[cheekybuddha]

You will also need a group concatenation function for the output you show.

@theDBguy can point you towards an example on his website.

 

[Harris@Z]

Thanks, I tried the crosstab query but for data only allows Count, Sum, Average, etc.
I am not an expert coder, so know nothing about the group concatenation function.
TheDBGuy, is this a possible solution and can you please guide me to where on your site I can find this?

 

[arnelgp]

see Query1.
note that you can only have 255 columns on your table/query.

 

[theDBguy]

Thanks, I tried the crosstab query but for data only allows Count, Sum, Average, etc.
I am not an expert coder, so know nothing about the group concatenation function.
TheDBGuy, is this a possible solution and can you please guide me to where on your site I can find this?

Still only using my phone, so can’t see what Arnel provided, but I have a feeling it would include a similar function if it was needed. If you’re still curious to see what my function looks like, you can go to this link.
SimpleCSV()

 

[cheekybuddha]

This is Arnel's query (using Allen Browne's ConcatRelated() ):

TRANSFORM First(
  ConcatRelated(
    "TestedFor",
    "Table1",
    "DateReportDue=#" & Format([DateReportDue],"m/d/yyyy") & "# And Sample_ID = '" & [Sample_ID] & "'",
    "TestedFor",
    Chr(13) & Chr(10)
  )
) AS TFor
SELECT Table1.Sample_ID
FROM Table1
GROUP BY Table1.Sample_ID
PIVOT Format(Table1.DateReportDue, "dd mmm yyyy");

Just note that with the "dd mmm yyyy" formatting of the date, the dates may appear in a strange order!

To test, add a record to Table1 for a date in December 2025.

When you run the query it will appear before all the other dates in November.

 

[Ken Sheridan]

Just note that with the "dd mmm yyyy" formatting of the date, the dates may appear in a strange order!

Using the ISO standard for date notation of YYYY-MM-DD would solve that. Otherwise an IN clause with a value list of all dates in the desired order can be added. This also guarantees the return of all dates in the range if any are unrepresented in the data, so would show any dates where no tests were undertaken.