Crosstab query with multiple value fields (1 Viewer)

[eacollie]

I'm trying to create a crosstab query with multiple values and need some help.

I have two crosstab queries (SM and RM) with the following fields:
[Meal], [MealLocation],[Date1],[Date2]...[Date7]

The value in the queries for each date is the sum of RMMealCount (in crosstab query RM) and sum of SMMealCount (in crosstab query SM).

Is there a way to combine these two queries so that I get the both values (sum of RMMealCount) and (sum of SMMealCount) for each date?

Hope this makes sense.

 

[isladogs]

Just create a new query based on the two crosstabs.

But is there a reason why you can't do both in the same query?
e.g. by basing your crosstab on aggregate query

 

[eacollie]

Thanks Ridders.
I'm not sure how to create a crosstab query as you suggest giving multiple values for each date (column).

 

[isladogs]

Without seeing your data and your query sql, I can't really help further.
Do you want to upload a stripped down copy of your database?

 

[eacollie]

Thank you.
Here's the table, two "normalizing" queries and two crosstab queries. The form "select date" has to be open to run the queries. The data (in a temporary table) has the data from 8/5/2018 to 8/11/2018.

I am trying to create a report from a crosstab query which shows for each date and for each meal type (breakfast, lunch...) and for each meal location, the number of RM (regular meals) and the number of SM (special meals).

 

[isladogs]

You didn't make that very easy to look at
Entering a date on the form creates an invalid use of null error!

Try this which joins your crosstabs(Query1):

SELECT qryNormalizedRecordsRM_Crosstab.Meal, qryNormalizedRecordsRM_Crosstab.MealLocation, qryNormalizedRecordsRM_Crosstab.[Total Of RMMealCount], qryNormalizedRecordsSM_Crosstab.[Total Of SMMealCount]
FROM qryNormalizedRecordsRM_Crosstab INNER JOIN qryNormalizedRecordsSM_Crosstab ON (qryNormalizedRecordsRM_Crosstab.MealLocation = qryNormalizedRecordsSM_Crosstab.MealLocation) AND (qryNormalizedRecordsRM_Crosstab.Meal = qryNormalizedRecordsSM_Crosstab.Meal);

Or this aggregate query (Query2)

SELECT tblTEMPMealCount.MealDate, tblTEMPMealCount.MealLocation, Sum(tblTEMPMealCount.BreakfastRM) AS SumOfBreakfastRM, Sum(tblTEMPMealCount.BreakfastSM) AS SumOfBreakfastSM, Sum(tblTEMPMealCount.LunchRM) AS SumOfLunchRM, Sum(tblTEMPMealCount.LunchSM) AS SumOfLunchSM, Sum(tblTEMPMealCount.DinnerRM) AS SumOfDinnerRM, Sum(tblTEMPMealCount.DinnerSM) AS SumOfDinnerSM, Sum(tblTEMPMealCount.Refreshments) AS SumOfRefreshments
FROM tblTEMPMealCount
GROUP BY tblTEMPMealCount.MealDate, tblTEMPMealCount.MealLocation;

Or this which omits the dates (Query3)

SELECT tblTEMPMealCount.MealLocation, Sum(tblTEMPMealCount.BreakfastRM) AS SumOfBreakfastRM, Sum(tblTEMPMealCount.BreakfastSM) AS SumOfBreakfastSM, Sum(tblTEMPMealCount.LunchRM) AS SumOfLunchRM, Sum(tblTEMPMealCount.LunchSM) AS SumOfLunchSM, Sum(tblTEMPMealCount.DinnerRM) AS SumOfDinnerRM, Sum(tblTEMPMealCount.DinnerSM) AS SumOfDinnerSM, Sum(tblTEMPMealCount.Refreshments) AS SumOfRefreshments
FROM tblTEMPMealCount
GROUP BY tblTEMPMealCount.MealLocation;

Hopefully one of these is what you want. If not, feel free to adapt.

BTW this is only complicated because your table isn't normalised
When you can only get a result by creating a union query followed by a crosstab its very much an indication of poor table design

Your data table should have these fields
MealID - autonumber PK (no PK in your current table!)
MealDate -datetime
MealCount - number
MealType - text e.g. LunchSM, DinnerRM
MealLocation - text

If you change to that, everything else becomes EASY!

 

[eacollie]

Thank you so much Ridders. I'll give this a try.

 

[eacollie]

I think what I need is a crosstab query (for my report) and these three are summary queries. I had created the two crosstab queries and was trying to joint them into one crosstab query. is this possible?

 

[eacollie]

This is what I need my report to look like.

 

[plog]

First and least:

This is what I need my report to look like.

To achieve that I would move your data to Excel. Then run a pivot table on it. You can achieve that report in 2 minutes with that method.

Second and most importantly--fix your table. You've got a horrible Rube Goldberg data machine trying to overcome this error. Your UNION queries are simply hacks around your poor table structure. and aren't necessary.

Your table should be structured like this:

MealID, autonumber, primary key
MealDate, date, same as existing data
MealName, text, will hold "Breakfast", "Lunch" or "Dinner"
MealType, text, this will hold if meal is SM or RM
MealRefreshments, number, holds whatever it holds now
MealLocation, text, holds value it holds now
MealNumber, number, this will hold the number of meals

That's it. All those fields composed of the MealName and RM/SM are eliminated. Those are values pertinent to your data, which means they go into records, not into field names. So, you eliminate those 6 fields and instead add 6 records in the new table to accomodate your data for a day of meals.

You should fix the structure of your table as prescribed above and not continue on with this issue because its just a symptom of your larger structure problem.

 

[isladogs]

Ah. That wasn't what you appeared to be asking for.

What you are doing is creating a spreadsheet in Access. It doesn't work well.
Either do this in Excel or redesign your table as previously instructed.

You could probably create something like this by creating a UNION of your two union queries, then doing a crosstab of that.
BUT it will be a mess and in a week's time you will be back asking to solve another issue which is difficult due to your non-normalised table structure.

Quite honestly, I won't be doing you any favours in the long run if I create a report for you based on what you have supplied.

I'll be happy to assist if you redesign your table(s) first.

 

[eacollie]

Thank you Plog.
The data is in a temporary table created each time the report is run. I'll have to go back and rewrite the vb that inserts the data into the table and get it into the format you suggest.

In the meantime, how would I do a "pivot table" is Excel? Can I run this Excel report while the Access database is open?

In your suggestion wouldn't put "refreshment" as a meal name and not in a separate field? Also, if this is a temporary table what's the point of having a primary key?

 

[plog]

Here's a pivot table link:

https://support.office.com/en-us/ar...eet-data-a9a84538-bfe9-40a9-a8e9-f99134456576

The thing is, to use that you need a properly structured data like I described above. For this method you would create a query to generate the data necessary, paste it into Excel and then create a pivot table using the data as its source.

I have no idea what refreshment is, I just saw that you were storing values in your field names (Breakfast, lunch dinner) and know that's a big sign of a poor structure. You should read up on normalization (https://en.wikipedia.org/wiki/Database_normalization) and determine where it should go.

 

[eacollie]

Thank you Plog.
I can follow your suggestions and try to create an Excel file from a query of the temporary table.

That being said, this report is to run in the kitchen where meals are prepared. They will need to somehow see what "special meal" is needed (in the SM column in my example). In Access I can create an on-click event and bring up a pop-up report displaying that information. Is that something possible in Excel?

 

[eacollie]

Thank you ridders.
I'll try to get the vb updated as soon as I can to normalize the table and get back.

 

[arnelgp]

its been awhile, but if youre still interested here is your report.
just open the frmSelectDate as usual and input the date for the report.

special thanks to mr.ridders for this link.

 

[isladogs]

Well done arnel for creating the report requested by the OP.

However as I suggested in post #11, doing so may just result in putting off the day that this gets redesigned properly from scratch.

I hope the OP won't postpone that as it will be easy to put right now but will get harder the longer it is delayed

 

[eacollie]

Thank you all! I have redesigned my table as follows:
MealID
MealDate
MealName
MealType
MealLocation
MealCount

How do I get into the format I need on the report? I know its through grouping but not quite sure how/what to group.

Thank you all so much!

 

[isladogs]

That's good news!

Suggest you post a stripped down copy of your db for someone to look at.
Include the data needed to create that report

 

[eacollie]

Thank you!
Attached is database with some data.
The report format I need is in post #9.

Thank you so much - I truly appreciate all your help.