Report won't group by date (1 Viewer)

[Missomissou]

I have a table with multiple multivalue fields. I created a query to list active projects (those with no end date). The query appears to be working fine, but though the report will group by any other (and all other) field in the query, for some reason, it won't group by date. Instead, it's adding a heading "Q4 2022", and then listing every instance of the date below. I assume each of these instances is associated with one or more of the multivalue fields in the original table, but I have no idea how to fix it in the report. Any ideas?

Here's a screen shot of the query design:

And here's one of the report with group by date, and each subsequent field:

 

[Missomissou]

For what it's worth, here is a sample of the table data:

 

[MajP]

 

[plog]

It is and it isn't. My guess is in the Date Grouping Header you put a formatted date field that shows the date formatted to the Quarter and Year. So it's working.

Then you added a Grouping for every other field in the datasource, added each of those fields in their corresponding grouping. And finally you added the datefield in the Details section. The Detail sections will show a value for every record in the datasource, so that's why you have so many 10/19/2022 values.

The real question is, what do you want this report to look like? And why aren't you letting the query do the heavy data lifting and have it show just the data you want instead of doing so much grouping in the Report?

 

[Missomissou]

I would like it to look more or less like this:

but with a list of tasks below each project title.

Currently that looks like:

That's just two of the projects titles, with tasks listed. But there is a date listed with each task. I would like that date to display only once and in line with the title. Here I have grouped by title, but if I also group by date, it does that weird formatting thing above, with a heading for Quarter and the date listed multiple times anyway.

 

[plog]

I found the problem, it's your query.

I was reviewing all your screenshots to post a response and now see that you have the Task table in it and it's doing absolutely nothing. Why's it there? It's not joined to the other data sources and you are not using any of its fields in the query down below. This is the problem. After you built it, did you run it to see the results? Why did you press on when you saw so many duplicates?

Without that table, you probably won't need to group the report at all. Always focus on the data first (tables and queries) and presentation second (forms and reports).

 

[Missomissou]

Hi @plog yes--I noticed the tasks table hanging out in there when I reopened the query design. It didn't make sense for my initial question--think I had added it in because when I started, I was trying to create a query that would list all the tasks for each project (so that I could present a picture of where effort was spent). The new query design (for the report immediately above, posted yesterday at 6:34PM) looks like this (see below). The Tasks table is still in there because now it's in the report.

 

[plog]

Now I'm lost. The task table isn't joined to any other table, so your query is going to multiply tasks by projects. Every task in task is going to get assigned to every project in project. Maybe that's what you want.

With that said, you still have too many groupings in your report. Instead of grouping by every field in Projects you most likely want to group by just the ProjectID (or whatever the primary key of Projects is). You group by ProjectID, add a header for it, then put in all those fields into that header.

My main advice is to start over and go slow. Don't do everything at once, do something that works and then add things one at a time and only move on once they work as well. Here's what you should do:

A. Add the ProjectID to your query.
B. Create a new report based on your query.
C. Decide the most grainular level you want your data at (most likely Task) and put the appropriately field(s) for that in the Details.
D. Add a grouping to the report for just ProjectID along with a header for it.
E. Add the project title to the ProjectID grouping header.
F. Run the report to see if the data looks correct.

If it does, add the project title field to the ProjectID header area. Run and make sure it looks correct. Then, keep adding fields to where you think they should go. If you get stumped post screenshots back here along with the issue.

 

[Missomissou]

Now I'm lost. The task table isn't joined to any other table, so your query is going to multiply tasks by projects. Every task in task is going to get assigned to every project in project. Maybe that's what you want.

With that said, you still have too many groupings in your report. Instead of grouping by every field in Projects you most likely want to group by just the ProjectID (or whatever the primary key of Projects is). You group by ProjectID, add a header for it, then put in all those fields into that header.

My main advice is to start over and go slow. Don't do everything at once, do something that works and then add things one at a time and only move on once they work as well. Here's what you should do:

A. Add the ProjectID to your query.
B. Create a new report based on your query.
C. Decide the most grainular level you want your data at (most likely Task) and put the appropriately field(s) for that in the Details.
D. Add a grouping to the report for just ProjectID along with a header for it.
E. Add the project title to the ProjectID grouping header.
F. Run the report to see if the data looks correct.

If it does, add the project title field to the ProjectID header area. Run and make sure it looks correct. Then, keep adding fields to where you think they should go. If you get stumped post screenshots back here along with the issue.

I think perhaps I've gotten myself into trouble by using multivalue fields in the tables instead of storing that info in junction tables? I've run into this with another table/report too and just split out a mvf from that table into a separate junction table. But I'm definitely in over me head. Hoping this is where I learn to swim? In any case, I appreciate your thoughtfulness on this. The other queries based on this table are working well. Here's an example of one that returns a clean result:

 

[plog]

I don't think this issue has anything to do with multi value fields. It's not grouping your data appropriately. Follow my prior advice and build a report 1 field at a time.