Crosstab query with multiple value fields (1 Viewer)
- Thread starter eacollie
- Start date
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.
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.
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.
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
MVP / VIP
- Local time
- Today, 08:53
- Joined
- Jan 14, 2017
- Messages
- 18,216
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
See this link for one method of doing this: https://www.access-programmers.co.uk/forums/showthread.php?t=297837
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!
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!
Last edited:
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 15:53
- Joined
- May 7, 2009
- Messages
- 19,233
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.
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.
Last edited:
isladogs
MVP / VIP
- Local time
- Today, 08:53
- Joined
- Jan 14, 2017
- Messages
- 18,216
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
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
Attachments
-
Capture.PNG
Sorry, database corrected, (problem a (master) date control in the report should have been unbound).
And Colin don't feel sorry about your missing time, I think we are plenty of members around here!
isladogs
MVP / VIP
- Local time
- Today, 08:53
- Joined
- Jan 14, 2017
- Messages
- 18,216
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.
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.
