Crosstab query with multiple value fields

[plog]

Report attached as spreadsheet. I took your table and pasted your table data into Excel on the data tab. Then I created a simple pivot on the pivot tab.

 

[eacollie]

Thank you so much Plog.
My concern with using Excel is, as I mentioned above, I need to "drill down" or something so that they can see what constitutes a particular "special meal." I can do that with Access but not sure that's possible with Excel.

 

[plog]

Drill down? The data itself is on the data tab, you can redirect them there. But honestly that pivot table doesn't really aggregate the data, it just displays it per your specs.

 

[eacollie]

Thank you plog.
The data in the report is a summary and only lets the staff know there are, for example, 2 "special meals." They somehow have to be able to find out what these meals are: diabetic, gluten-free, dairy-free....etc. If the report is done in Access I think I can create an on-click event and bring up a report when they click on the "2" which shows the specifics of these "special meals." I've never programmed in Excel and I wouldn't know how to go back and forth from Excel to Access.

 

[isladogs]

Haven't had time to look at creating an Access report for you but you could embed Plog's Excel pivot in an access form.
See this link for one method of doing this: https://www.access-programmers.co.uk/forums/showthread.php?t=297837

 

[eacollie]

Thank you all so much!

I think at this point I would like to use Access if possible to group/sort the report into the format needed. Introducing Excel into this is going to take more time than I have. If anyone could help me group/sort the data I would very much appreciate the help.

I cannot see any other way to format this data into the report format needed unless using a multiple value field crosstab query (my original post), although I trust your expertise when you tell me it is possible.

Thanks again!

 

[arnelgp]

here is your report.

i added another table, MealNameLocation.
the purpose of the above table is to
correctly sort/position the MealName and MealLocation
in order.

created two queries, qryXTAB_Orig, qryXTAB.
they are same. the report uses qryXTAB.
on the open event of the report,
we use the original query string from
qryXTAB_Orig and add a 'Where Clause' to
filter the result.

the resulting SQL (with filter) is then put to qryXTAB.

on the Open Event of the Report, i set the Control Source
of each textbox to the correct field in qryXTAB. therefore
making it dynamic.

its too complicated, so if you have questions, reserved
it and try to study the code first.

use the Form to enter the StartDate of the report.

 

[JHB]

Here is another solution, which create a report depending of the days that table "tblTEMPMealCount" contains.

 

[eacollie]

Thank you so much arnelgp and JHB! I have downloaded your reports and am studying them. You are such elegant programmers!

 

[isladogs]

Hi JHB

Thanks to you & arnelgp for providing solutions for this thread as I've had no time this weekend
However, I've tried running your solution on 2 PCs but get error 3070 when I click Create Report:

The error occurs in the line
DoCmd.OpenReport "CopyReportTEMPMealCount_Crosstab", acViewPreview

 

[eacollie]

arnelgp, I need the report to show Sunday through Saturday of the week of the date selected on "select date" form. Where can I change that code?

 

[JHB]

Hi JHB
..
However, I've tried running your solution on 2 PCs but get error 3070 when I click Create Report:
..

Sorry, database corrected, (problem a (master) date control in the report should have been unbound).

Thanks to you & arnelgp for providing solutions for this thread as I've had no time this weekend
..

And Colin don't feel sorry about your missing time, I think we are plenty of members around here!

 

[isladogs]

Thanks Jorn.
The reason for my comment was that I had encouraged the OP to redesign his table suggesting in an earlier post that I would then deal with his report.
I'm very happy that you and arnelgp both did so.

New version tested successfully.
Of course using your approach the report 'spills' onto separate pages if you add extra dates.
It just depends on which approach suits the OP better.

 

[arnelgp]

add some sorting, i noticed dinner is before lunch. and also the details not as per pdf sample.