A Challenge: Summary and Append Queries

S

ShogiJoe

Good Day everyone!

This is my first post on Google groups so wish me luck! So here's a
challenge for everyone.

The Query needs to total the last six weeks of data in one column:For
example:
Sales totals in
Area ID(1340) 14-Jan-2007 Store A :10 Sales on
Area ID(1340) 7-Jan-2007 Store A: 5 Sales
Area ID(1340) 31-Dec-2006 Store A: 6 Sales
Area ID(1340) 24-Dec-2006 Store A: 0 Sales
Area ID(1340) 17-Dec-2006 Store A: 3 Sales
Area ID(1340) 10-Dec-2006 Store A: 9 Sales
Totals: Store A had 33 Sales in the last 6 weeks

And then, it needs to show up as a separate column or appended in some
way to be added to a report. So...
Area ID(1340) 14-Jan-2007 Store A 10 Sales 33 Last Six Weeks

If you succeed because of your craftiness and skill in the ways of
relational databases you will be is crowned the Master of Microsoft
Access to reign with an Iron Fist.

Thank you,
Shogi
 
J

John Vinson

Good Day everyone!

This is my first post on Google groups so wish me luck! So here's a
challenge for everyone.

The Query needs to total the last six weeks of data in one column:For
example:
Sales totals in
Area ID(1340) 14-Jan-2007 Store A :10 Sales on
Area ID(1340) 7-Jan-2007 Store A: 5 Sales
Area ID(1340) 31-Dec-2006 Store A: 6 Sales
Area ID(1340) 24-Dec-2006 Store A: 0 Sales
Area ID(1340) 17-Dec-2006 Store A: 3 Sales
Area ID(1340) 10-Dec-2006 Store A: 9 Sales
Totals: Store A had 33 Sales in the last 6 weeks

And then, it needs to show up as a separate column or appended in some
way to be added to a report. So...
Area ID(1340) 14-Jan-2007 Store A 10 Sales 33 Last Six Weeks

If you succeed because of your craftiness and skill in the ways of
relational databases you will be is crowned the Master of Microsoft
Access to reign with an Iron Fist.
Click to expand...

LOL... what if I decline the offer? I prefer the velvet glove...

SELECT AreaID, SaleDate, StoreName, Sales, (SELECT Sum(X.[Sales]) FROM
Salestable AS X WHERE X.StoreName = SalesTable.StoreName AND
X.SalesDate BETWEEN DateAdd("ww", -6, SalesTable.SaleDate) AND
SalesTable.SaleDate) AS [Last Six Weeks]
FROM SalesTable
WHERE <whatever criteria you want>

John W. Vinson[MVP]
 
S

ShogiJoe

All bow down for the wonderful Velvet Gloved Master of Microsoft Access
(Bows Gracefully)

Thank you, that was the exact query I needed. You ROCK!

Shogi


John said:
Good Day everyone!

This is my first post on Google groups so wish me luck! So here's a
challenge for everyone.

The Query needs to total the last six weeks of data in one column:For
example:
Sales totals in
Area ID(1340) 14-Jan-2007 Store A :10 Sales on
Area ID(1340) 7-Jan-2007 Store A: 5 Sales
Area ID(1340) 31-Dec-2006 Store A: 6 Sales
Area ID(1340) 24-Dec-2006 Store A: 0 Sales
Area ID(1340) 17-Dec-2006 Store A: 3 Sales
Area ID(1340) 10-Dec-2006 Store A: 9 Sales
Totals: Store A had 33 Sales in the last 6 weeks

And then, it needs to show up as a separate column or appended in some
way to be added to a report. So...
Area ID(1340) 14-Jan-2007 Store A 10 Sales 33 Last Six Weeks

If you succeed because of your craftiness and skill in the ways of
relational databases you will be is crowned the Master of Microsoft
Access to reign with an Iron Fist.
Click to expand...

LOL... what if I decline the offer? I prefer the velvet glove...

SELECT AreaID, SaleDate, StoreName, Sales, (SELECT Sum(X.[Sales]) FROM
Salestable AS X WHERE X.StoreName = SalesTable.StoreName AND
X.SalesDate BETWEEN DateAdd("ww", -6, SalesTable.SaleDate) AND
SalesTable.SaleDate) AS [Last Six Weeks]
FROM SalesTable
WHERE <whatever criteria you want>

John W. Vinson[MVP]
Click to expand...
 
Top