Calculate OTD query in Access 2010

[Stumped2015]

I would like to calculate OTD for each month of the current year seperately. I would like to group for each month the ship to location with a subtotal of OTD for each location. I would also like to subgroup the different jobs/parts for each location with an OTD percentage for those as well if possible. I have fields: Date (date of shipper), Quantity, Ship to, and job number. This would require using the fields: early, on time, and late but would not require these fields to be visible on the query. The calculation needs to be based on the quantities of each part of each shipment NOT based on how many shippers are marked on time, early or late.

Stumped
Access 2010

[zmbd]

Hello Stumped2015,
Sounds like you are looking at a crosstab query (CTQ); however, a bit more complex than usual.

I would suggest that you start with Allen Browne's article on CTQs >> Crosstab query techniques .

Once you have the basic concept we can start to help you tweak your initial SQL structures. Unfortunately we do need you start the basic SQL as we do not have your database to work with :)

You will also find several threads covering various aspects of CTQ implementations and pitfalls:
https://bytes.com/searchresults.php?...ery&gsc.page=1

[hvsummer]

@zmbd: I don't think CTQ can handle his request.
He will need pivottable to have subtotal.

@Stumped2015: 1 best solution for you are to prepair data in Access then pivot it in excel, you can subtotal/analysis everything better in excel.

or you can create subtotal with group by query.

1 idea have been pop up in my mind, wait a minute.

hahahah, okok, I finnally found the way to show up Subtotal in groupby query, thank Stumped2015 for asking, you help me too much :3

SELECT Group, SubGroup, Sum(Qty) as MTD
FROM Table1
GROUP BY Group, SubGroup
ORDER BY Group, SubGroup
UNION ALL
SELECT Group, "Total "&Group as SubGroup, Sum(Qty) as MTD
FROM Table1
GROUP BY Group
ORDER BY Group, SubGroup;
 

yeah, that SQL will give you subtotal in Total/GroupBy Query.

[zmbd]

Stumped2015,
It would be helpful to see how you want your data to be presented. To do this, Click on the [CODE/] button in the post toolbar. Then between the [code][/code] tags insert your table/report using spaces not tabs to setup your columns.

From what I am reading this is what you are after:
(of course, the suffix "N" is any arbitrary number :) )

[         ][January][February][March]...[December]
[Location1][OverT  ][OverT   ][OverT]...[OverT   ][subtotal]
   [Part1 ][%OverT ]...
   [Part2 ][%OverT ]...
   [Part3 ][%OverT ]...
   [PartN ][%OverT ]...
[Location2][OverT  ][OverT   ][OverT]...[OverT   ][subtotal]
   [PartN ][%OverT ]...
   [PartN ][%OverT ]...
   [PartN ][%OverT ]...
[Location3][OverT  ][OverT   ][OverT]...[OverT   ][subtotal]
   [PartN ][%OverT ]...
   [PartN ][%OverT ]...
   [PartN ][%OverT ]...
[Location4][OverT  ][OverT   ][OverT]...[OverT   ][subtotal]
...
[LocationN][OverT  ][OverT   ][OverT]...[OverT   ][subtotal]
   [PartN ][%OverT ]...
   [PartN ][%OverT ]...
   [PartN ][%OverT ]...

This might be better handled in a report; however, I think we need a clearer picture of the desired results.