How to group within groups in Access

Question

I've been trying for a while and I'm just about to give up. I need to prepare a report that displays Item Numbers, the line they were produced on, and their production date, among other things. So, as you would imagine, each row contains a line number, item number, production date, and information regarding the number of items planned and produced for that entry.

I need to group the rows by line first, that was simple enough, afterwards, I need to group them by week, that also worked like a charm, except the dates were not really in order after this. I would need to apply a sort but by day this time. This works well but it's the next step that causes the most trouble. I also need to group the runs of items produced. For example:

Day - Item
Day 1 - Item A
Day 2 - Item A
Day 3 - Item A

These would be grouped with a footer counting the number of items produced for those consecutive entries. However, sometimes production looks like this:

Day - Item
Day 1 - Item B
Day 2 - Item B
Day 3 - Item A
Day 3 - Item B

I don't see a way to have the items ordered in a particular way that they can be grouped since I'm already ordering/sorting them by date because the date order is messed up by the first group. If I'm to group items at that point I would get one group header/footer per row, meaning it's not working at all.

My client suggests I make it so that when Access "notices the item number changes it gives a total". While that's wonderful in words, it implies that the rows should be sorted by item number and date. He will produce item A for three days, then produce item B for 2 days but part of the problem is that sometimes he will produce A for two and a half days and start B on that third day (following A) so if it's ordered by date, it may put one row above the other since they are on the same day. To my knowledge there is no real way to have Access to just "know" which products are produced first so as to group them after the item number changes. Of course it can keep the order they were entered in but if I ever need them sorted, that order will be lost.

I'm not sure if this is at all possible with this kinda of table structure. If not, can anyone suggest an alternative table structure? Or perhaps there's a way to have the first group by to not mess up the dates, which would allow me to remove the sort by date (although I'm not sure that it would work even if I could do that).

@Steve Kass
Day - Item
Day 1 - Item B
Day 2 - Item B
Day 3 - Item B
Day 3 - Item A
Day 3 - Item C
Day 4 - Item A
Day 5 - Item C

This is how it's laid out in his Excel sheet:

Day - Item
Day 1 - Item B
Day 2 - Item C
Day 3 - Item C
Day 3 - Item A
Day 4 - Item A
Day 4 - Item D
Day 5 - Item D

I've picked letters that represent the alphabetical order of the actual item numbers.

@Abe Miessler, Query so far:

SELECT Planned.Line, 
       Planned.[Production Date], 
       Items.[Item Number], 
       Items.[Bottles/Pallet], 
       Planned.PQ1, 
       Planned.AQ1, 
       Planned.PQ2, 
       Planned.AQ2, 
       Planned.PQ3, 
       Planned.AQ3 
FROM Items 
INNER JOIN Planned 
   ON Items.ID = Planned.ItemID;

@David-W-Fenton: Well I'm being asked to have a production summary per run. A run would be described as consecutive production of the same product. Products are produced on one of two lines and there can be multiple entries per day. The report must be grouped first by line so that each group shows entries for that line. That was done with a simple grouping. Within each line grouping I'm required to separate entries by week. Now, within each week, the days are not appearing in order. If the days are not in order we will not see a run simply because a run will most likely happen with consecutive days. One product will be produced for 3 days in a row for example, if these days are mixed up with the other days of the week, there will not be a consecutive, identifiable run. To have the entries in each week be in the correct order (by day) I applied a sort. What I've noticed is that after applying this sort each entry is handled as a separate "group" but without a header/footer. This results in not being able to group by product number afterwards since each entry is within its own "group".

Answer 1

I think you're asking for something impossible. But just in case you aren't, please let us know what order you want if these are your rows:

Day - Item
Day 1 - Item B
Day 2 - Item B
Day 3 - Item A
Day 3 - Item B
Day 3 - Item C
Day 4 - Item A
Day 5 - Item C

Answer 2

You say in a comment that you started with this:

  Group by=>line
    Group by=>week
      Group by=>product number 

...but it didn't work "because after grouping by week, they're grouped by week but within the week they're no longer ordered." So you (correctly) added a sorting group, thus:

  Group by=>line
    Group by=>week
      Sort by=>day
        Group by=>product number

But you say:

Now it's in order and you can see consecutive days with the same products but grouping results in each row being grouped separately.

Where are the controls displaying the data? In the detail or in the group/sort header? It makes all the difference in the world. To display all records, you use the DETAIL. To show summary data, you use the HEADER. It sounds to me like you're putting your controls in the header instead of the detail.

Can you take a screenshot of your report in design view and insert it into your question? Without it, I don't see how to get any further.