Re: A Challenge: Summary and Append Queries

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 Vinson wrote:
On 15 Jan 2007 21:46:57 -0800, ShogiJoe@xxxxxxxxx wrote:

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.

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]

.

Relevant Pages

  • Re: Help PLEASE! Not sure what answer is: Match? Index? Other?
    ... you need what Excel calls a list. ... would contain the name Area, B1 the name Store, C1 would contain the name ... you establish a pivot table. ... type the name of the sales data range -- I suggested SalesData ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Astronomy vendors and their "sales"
    ... their sales continue to dry up. ... electronics store in my area that was closing down a few years back. ... knew Circuit City was going out of business, I decided to make a trek ... Since the economic distress took hold this autumn, liquidators have ...
    (sci.astro.amateur)
  • Re: Thank you with Design Assistance but...
    ... (StoreID in the sales table) ... caused entirely by treating a database as a spreadsheet. ... Also how would I write the queries for, lets say, One Store, week ... the RecordSource for a report so you can pretty up your analysis. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Thank you with Design Assistance but...
    ... Although each Access database is limited to 2G, ... (StoreID in the sales table) ... You saw the simple queries that I ... Also how would I write the queries for, lets say, One Store, week ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Todays Priceless Supermarket Moment
    ... look like they are working hard to keep your business. ... You've never worked in a grocery store, ... waiting to bring in the next customer. ... And most of our sales come from repeat customers. ...
    (rec.food.cooking)