Pivot Table needed that groups part numbers and dates

C

Chris

I have a long list of part numbers with additional data such as quantity in
stock, qty on PO's, qty on SO's, etc. (total of 6 different data types).
Most of these have different due dates from each other. I would like to put
these into a pivot table that summaries by stock code (in rows) and then
groups by dates (in the column headings). These dates need to be grouped
into weekly buckets. Then I want to see the quantities in the data section
where the appropriate stock code and date intersect. I'm able to do this in
Excel but it's very manual. I'd like to have Access do the work once the
queries/reports are set up.

Example:

Raw Data;
Stock Code QtyonPO QtyinWH DueDate
EM0001-00 1 8/10/09
EM0001-00 1 8/17/09
EM0001-00 1 8/24/09
EM0001-00 1 8/10/09
EM0002-00 3 8/17/09
EM0002-00 5 8/24/09
EM0002-00 1 8/10/09
EM0002-00 1 8/17/09

Summary I'm looking for;
8/10 8/17 8/24
Stock Code
EM0001-00
QtyonPO 1 1 1
QtyinWH 1
EM0002-00
QtyonPO 3 5
QtyinWH 1 1

Anyone out there with some helpful hints?
Thx,
Chris
 
J

John Spencer

You have a problem with your data structure. You need to normalize the
data using a Union query and then use that as the source for a crosstab
(pivot) query.

A union query can only be built using the SQL view. It would look like
the following:

SELECT [Stock Code], "QtyOnPo" as QtyType, QtyOnPO as Qty, DueDate
FROM [Table Name]
UNION ALL
SELECT [Stock Code], "QtyInWH" as QtyType, QtyinWH as Qty, DueDate
FROM [Table Name]
UNION ALL
....
UNION ALL
SELECT [Stock Code], "QtyOnSO" as QtyType, QtyOnSO as Qty, DueDate
FROM [Table Name]

Now with that saved query you could make a crosstab query
TRANSFORM Sum(Qty) as Amount
SELECT [Stock Code]
FROM SavedUnionQuery
GROUP BY [Stock Code]
PIVOT DueDate

A report based on this is a bit more problematic, since the DueDates
will change over time. There are ways to handle that by using the query
to give you relative weeks (Week01, WeekO2, etc) and then adjusting the
column labels in the report. The query to get the relative weeks might
look like the following.

TRANSFORM Sum(Qty) as Amount
SELECT [Stock Code]
FROM SavedUnionQuery
GROUP BY [Stock Code]
WHERE DueDate >= #2009-08-10#
PIVOT "Week" & Format(1+DateDiff("d",#2009-08-10#,DueDate)/7,"00")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
C

Chris

John, Thanks, I will make an attempt to try what you suggest.
Chris


John Spencer said:
You have a problem with your data structure. You need to normalize the
data using a Union query and then use that as the source for a crosstab
(pivot) query.

A union query can only be built using the SQL view. It would look like
the following:

SELECT [Stock Code], "QtyOnPo" as QtyType, QtyOnPO as Qty, DueDate
FROM [Table Name]
UNION ALL
SELECT [Stock Code], "QtyInWH" as QtyType, QtyinWH as Qty, DueDate
FROM [Table Name]
UNION ALL
....
UNION ALL
SELECT [Stock Code], "QtyOnSO" as QtyType, QtyOnSO as Qty, DueDate
FROM [Table Name]

Now with that saved query you could make a crosstab query
TRANSFORM Sum(Qty) as Amount
SELECT [Stock Code]
FROM SavedUnionQuery
GROUP BY [Stock Code]
PIVOT DueDate

A report based on this is a bit more problematic, since the DueDates
will change over time. There are ways to handle that by using the query
to give you relative weeks (Week01, WeekO2, etc) and then adjusting the
column labels in the report. The query to get the relative weeks might
look like the following.

TRANSFORM Sum(Qty) as Amount
SELECT [Stock Code]
FROM SavedUnionQuery
GROUP BY [Stock Code]
WHERE DueDate >= #2009-08-10#
PIVOT "Week" & Format(1+DateDiff("d",#2009-08-10#,DueDate)/7,"00")

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a long list of part numbers with additional data such as quantity in
stock, qty on PO's, qty on SO's, etc. (total of 6 different data types).
Most of these have different due dates from each other. I would like to put
these into a pivot table that summaries by stock code (in rows) and then
groups by dates (in the column headings). These dates need to be grouped
into weekly buckets. Then I want to see the quantities in the data section
where the appropriate stock code and date intersect. I'm able to do this in
Excel but it's very manual. I'd like to have Access do the work once the
queries/reports are set up.

Example:

Raw Data;
Stock Code QtyonPO QtyinWH DueDate
EM0001-00 1 8/10/09
EM0001-00 1 8/17/09
EM0001-00 1 8/24/09
EM0001-00 1 8/10/09
EM0002-00 3 8/17/09
EM0002-00 5 8/24/09
EM0002-00 1 8/10/09
EM0002-00 1 8/17/09

Summary I'm looking for;
8/10 8/17 8/24
Stock Code
EM0001-00
QtyonPO 1 1 1
QtyinWH 1
EM0002-00
QtyonPO 3 5
QtyinWH 1 1

Anyone out there with some helpful hints?
Thx,
Chris
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need Help with Counting of Sequence for worked hours 1
Parameter query with expressions and calculations 3
Counting event times 5
Modifying a complex date formual to count months 4
Lookup and average data in an unsorted list 1
Fiscal year 2
Trouble burning ISO's 10
Calculation Total Time With Dates/Times That Overlaps 4

Top