Grouping monthly data

[Tamara]

Hi, I've received an access data table which I need to use for reporting but
require help with modifying it (excellent with Excel but novice with access).
It currently has a separate record detailing a store, the serial number of a
product, the date the product was received, the date it was sold. What I
need is to extract into excel the quantity of products received by each store
with the qty that was sold for each month. Here is an example of how the
access table looks:

Store Product Received Sold
A 122 01/11/08 04/11/08
A 144 01/11/08
A 133 04/12/08 06/12/08
B 244 04/10/08 11/11/08
C 545 05/12/08 06/12/08

The final result exported to excel should look like:
Oct Nov
Dec
Store Qty Rec Qty Sold Qty Rec Qty Sold Qty Rec Qty Sold
A 0 0 2 1 1
0
B 1 0 0 0
0 0
C 0 0 0 0
1 1


So I need to somehow add another column to convert the date over to the
month and then run a query over that. I think .....
Any help much appreciated!!

 

[Tamara]

When I posted the below, the second table mis-aligned itself due to the width
being so long. Sorry for any confusion, here's a revised one which hopfully
will fit:
Oct Nov Dec
Store Q Rec. QSold QRec. Qsold Q Rec. Qsold
A 0 0 2 1 1 0
B 1 0 0 0 0 0
C 0 0 0 0 1 1

 

[Arvin Meyer [MVP]]

To do that in Access you'll need a crosstab query, and my guess is that your
need to collect the data you want with a query, then run the crosstab
against the data in that query. Adding columns in the data gathering query
like:

MonthRecd: Format([Received], "mmm yy")
MonthSold: Format([Sold], "mmm yy")

Notice I didn't create a column with the name Month, because that's a
reserved word.

 

[Tamara]

Thankyou. I've created the crosstab query but unsure how to add the column
with the formulas you inputted. This probably is a basic question but I'm
new access.

To do that in Access you'll need a crosstab query, and my guess is that your
need to collect the data you want with a query, then run the crosstab
against the data in that query. Adding columns in the data gathering query
like:

MonthRecd: Format([Received], "mmm yy")
MonthSold: Format([Sold], "mmm yy")

Notice I didn't create a column with the name Month, because that's a
reserved word.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi, I've received an access data table which I need to use for reporting
but
require help with modifying it (excellent with Excel but novice with
access).
It currently has a separate record detailing a store, the serial number of
a
product, the date the product was received, the date it was sold. What I
need is to extract into excel the quantity of products received by each
store
with the qty that was sold for each month. Here is an example of how the
access table looks:

Store Product Received Sold
A 122 01/11/08 04/11/08
A 144 01/11/08
A 133 04/12/08 06/12/08
B 244 04/10/08 11/11/08
C 545 05/12/08 06/12/08

The final result exported to excel should look like:
Oct Nov
Dec
Store Qty Rec Qty Sold Qty Rec Qty Sold Qty Rec Qty
Sold
A 0 0 2 1
1
0
B 1 0 0 0
0 0
C 0 0 0 0
1 1


So I need to somehow add another column to convert the date over to the
month and then run a query over that. I think .....
Any help much appreciated!!

 

[Arvin Meyer [MVP]]

The crosstab query needs to be based upon another query that provides the
data. It is that query which contains the columns I mentioned.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thankyou. I've created the crosstab query but unsure how to add the
column
with the formulas you inputted. This probably is a basic question but I'm
new access.

To do that in Access you'll need a crosstab query, and my guess is that
your
need to collect the data you want with a query, then run the crosstab
against the data in that query. Adding columns in the data gathering
query
like:

MonthRecd: Format([Received], "mmm yy")
MonthSold: Format([Sold], "mmm yy")

Notice I didn't create a column with the name Month, because that's a
reserved word.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi, I've received an access data table which I need to use for
reporting
but
require help with modifying it (excellent with Excel but novice with
access).
It currently has a separate record detailing a store, the serial number
of
a
product, the date the product was received, the date it was sold. What
I
need is to extract into excel the quantity of products received by each
store
with the qty that was sold for each month. Here is an example of how
the
access table looks:

Store Product Received Sold
A 122 01/11/08 04/11/08
A 144 01/11/08
A 133 04/12/08 06/12/08
B 244 04/10/08 11/11/08
C 545 05/12/08 06/12/08

The final result exported to excel should look like:
Oct Nov
Dec
Store Qty Rec Qty Sold Qty Rec Qty Sold Qty Rec Qty
Sold
A 0 0 2 1
1
0
B 1 0 0 0
0 0
C 0 0 0 0
1 1


So I need to somehow add another column to convert the date over to the
month and then run a query over that. I think .....
Any help much appreciated!!