Calculating Monthly Sales Totals

[Paul Fenton]

We have an Access 2k database that tracks home remodeling jobs. There
are about 16,000 client records going back about 8 years and about
2,000 of these are actually sold jobs, each with a sale price and a
sale date in the client record.

Here's what the boss wants...

He wants to be able to view (not a report) a display of his monthly
sales for every year for which we have data. Something like this...

Jan Feb Mar Apr May etc

1998 $xxx $xxx $xxx $xxx $xxx
1999 $xxx $xxx $xxx $xxx $xxx
2000 $xxx $xxx $xxx $xxx $xxx

etc. to current date.

Frankly, I have no idea how to even start this and could use some
suggestions as to how to attack it.


Paul Fenton
(e-mail address removed)

 

[James A. Fortune]

We have an Access 2k database that tracks home remodeling jobs. There
are about 16,000 client records going back about 8 years and about
2,000 of these are actually sold jobs, each with a sale price and a
sale date in the client record.

Here's what the boss wants...

He wants to be able to view (not a report) a display of his monthly
sales for every year for which we have data. Something like this...

Jan Feb Mar Apr May etc

1998 $xxx $xxx $xxx $xxx $xxx
1999 $xxx $xxx $xxx $xxx $xxx
2000 $xxx $xxx $xxx $xxx $xxx

etc. to current date.

Frankly, I have no idea how to even start this and could use some
suggestions as to how to attack it.


Paul Fenton
(e-mail address removed)

tblSales
SaleID Autonumber
SalePrice Currency
SaleDate Date/Time

qrySales:
SELECT Sum(tblSales.SalePrice) AS MonthlySales, Format([SaleDate],"mmm")
AS SaleMonth, Format([SaleDate],"yyyy") AS SaleYear FROM tblSales GROUP
BY Format([SaleDate],"yyyy"), Format([SaleDate],"mmm");

qrySales_Crosstab:
TRANSFORM Sum([MonthlySales]) AS [The Value] SELECT [SaleYear],
Sum([MonthlySales]) AS [YearlySales] FROM qrySales GROUP BY [SaleYear]
PIVOT [SaleMonth];

!qrySales_Crosstab:
SaleYear YearlySales Apr Aug Dec Feb Jan Jul Jun Mar May Nov Oct Sep
2003 $105.00 $5.00 $9.00 ...
2004 $131.00 $25.00 $18.00 ...
2005 $122.00 $8.00 $5.00 ...

Note: The help file should have information about how to place the
columns in the order you want.

This should at least get you started.

James A. Fortune

 

[Paul Fenton]

James, thank you very, very much. Your post did precisely what I
wanted to do. And thank you for the Crosstab lesson!


Paul Fenton

We have an Access 2k database that tracks home remodeling jobs. There
are about 16,000 client records going back about 8 years and about
2,000 of these are actually sold jobs, each with a sale price and a
sale date in the client record.

Here's what the boss wants...

He wants to be able to view (not a report) a display of his monthly
sales for every year for which we have data. Something like this...

Jan Feb Mar Apr May etc

1998 $xxx $xxx $xxx $xxx $xxx
1999 $xxx $xxx $xxx $xxx $xxx
2000 $xxx $xxx $xxx $xxx $xxx

etc. to current date.

Frankly, I have no idea how to even start this and could use some
suggestions as to how to attack it.


Paul Fenton
(e-mail address removed)

tblSales
SaleID Autonumber
SalePrice Currency
SaleDate Date/Time

qrySales:
SELECT Sum(tblSales.SalePrice) AS MonthlySales, Format([SaleDate],"mmm")
AS SaleMonth, Format([SaleDate],"yyyy") AS SaleYear FROM tblSales GROUP
BY Format([SaleDate],"yyyy"), Format([SaleDate],"mmm");

qrySales_Crosstab:
TRANSFORM Sum([MonthlySales]) AS [The Value] SELECT [SaleYear],
Sum([MonthlySales]) AS [YearlySales] FROM qrySales GROUP BY [SaleYear]
PIVOT [SaleMonth];

!qrySales_Crosstab:
SaleYear YearlySales Apr Aug Dec Feb Jan Jul Jun Mar May Nov Oct Sep
2003 $105.00 $5.00 $9.00 ...
2004 $131.00 $25.00 $18.00 ...
2005 $122.00 $8.00 $5.00 ...

Note: The help file should have information about how to place the
columns in the order you want.

This should at least get you started.

James A. Fortune