By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Total $ spent over floating period with fixed range

P: 27
Hi folks,

I'm hoping somebody here might be able to help me figure out this query (I'm using Access).

What I'd like to do is answer this question: who has spent a total of $500 over the course of a five consecutive-year period? This five year period could mean the last five years, or it could mean 1992-97, etc.

I have transactional data (purchase amount and date of purchase). I would just like to find those who fit this criteria.

I'm not quite sure where to start.

Thank you!

John
Oct 7 '08 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,679
  1. Here is the general idea, but I would wait for the 'real' SQL guys to arrive:
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblTest.Seller, Sum(tblTest.[Purchase Amount]) AS [Total Purchase Amount for Interval]
    2. FROM tblTest
    3. WHERE tblTest.[Date of Purchase] Between DateAdd("yyyy",-5,Date()) And Date()
    4. GROUP BY tblTest.Seller
    5. HAVING Sum(tblTest.[Purchase Amount])>500;
  2. Assumptions:
    1. Table Name: tblTest
    2. Field 1: Seller (TEXT 50)
    3. Field 2: Purchase Amount (CURRENCY)
    4. Field 3:Date of Purchase (DATE/TIME)
Oct 7 '08 #2

P: 27
thank you for the advice!

if I were to substitute my table/field names etc for the ones here, and just pasted that into the SQL editor of Access, would that run the query? I've never queried by code before.

thank you again.

John
Oct 14 '08 #3

ADezii
Expert 5K+
P: 8,679
thank you for the advice!

if I were to substitute my table/field names etc for the ones here, and just pasted that into the SQL editor of Access, would that run the query? I've never queried by code before.

thank you again.

John
if I were to substitute my table/field names etc for the ones here, and just pasted that into the SQL editor of Access, would that run the query?
Yes
  1. Substitute your Table and Field Names as you previously indicated.
  2. Create a New Query.
  3. Select View ==> SQL View.
  4. Copy and Paste the SQL Statement into this Window.
  5. Access will do the rest, if you now switch to Design View you will see the Layout, Grouping, and Criteria in the Query Grid.
  6. Save the Query.
  7. Run the Query to verify results.
Oct 14 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.