Date range for a query

  • Thread starter Thread starter sergio.pringle
  • Start date Start date
S

sergio.pringle

Hi,

Let's say I have this table with 100 customers each with one
transaction per day. So the whole table with data for a month would be
100 * 30 = 3,000 records.

I have this query that basically returns how much each customer has
spent to date, taking into account every single transaction. All I did
was leave out the DATE field and then use the SUM function for the
fields I needed, so all I get is 100 records (one for each customer)
with their totals.

How can I get the same info, but for a specific date range that I can
choose as needed.

Thank you for your help.
 
Here's a generic query:

PARAMETERS [Enter start date:] DateTime, [Enter end date:] DateTime;
SELECT CustomerID, Sum(CustomerSpending) AS SumSpent
FROM TableName
WHERE DateFieldName Between [Enter start date:] And
[End end date:]
GROUP BY CustomerID;
 
Let's say I have this table with 100 customers each with one
transaction per day. So the whole table with data for a month would be
100 * 30 = 3,000 records.

I have this query that basically returns how much each customer has
spent to date, taking into account every single transaction. All I did
was leave out the DATE field and then use the SUM function for the
fields I needed, so all I get is 100 records (one for each customer)
with their totals.

How can I get the same info, but for a specific date range that I can
choose as needed.
Click to expand...


A quick and dirty way you can do that is to use a criteria
for the date field:
Between [Start Date] And [End Date]

Because Totals queries sometimes get confused about Having
and Where clauses, switch the query to SQL View and make
sure it's using WHERE.
 
Here's a generic query:

PARAMETERS [Enter start date:] DateTime, [Enter end date:] DateTime;
SELECT CustomerID, Sum(CustomerSpending) AS SumSpent
FROM TableName
WHERE DateFieldName Between [Enter start date:] And
[End end date:]
GROUP BY CustomerID;

--

Ken Snell
<MS ACCESS MVP>


Hi,
Click to expand...
Let's say I have this table with 100 customers each with one
transaction per day. So the whole table with data for a month would be
100 * 30 = 3,000 records.
Click to expand...
I have this query that basically returns how much each customer has
spent to date, taking into account every single transaction. All I did
was leave out the DATE field and then use the SUM function for the
fields I needed, so all I get is 100 records (one for each customer)
with their totals.
Click to expand...
How can I get the same info, but for a specific date range that I can
choose as needed.
Click to expand...
Thank you for your help.
Click to expand...
Click to expand...



Hey that was easy. Thank you both for your help. That was exactly what
I needed...
 
[email protected] said:
Let's say I have this table with 100 customers each with one
transaction per day. So the whole table with data for a month would be
100 * 30 = 3,000 records.
Click to expand...
I have this query that basically returns how much each customer has
spent to date, taking into account every single transaction. All I did
was leave out the DATE field and then use the SUM function for the
fields I needed, so all I get is 100 records (one for each customer)
with their totals.
Click to expand...
How can I get the same info, but for a specific date range that I can
choose as needed.
Click to expand...

A quick and dirty way you can do that is to use a criteria
for the date field:
Between [Start Date] And [End Date]

Because Totals queries sometimes get confused about Having
and Where clauses, switch the query to SQL View and make
sure it's using WHERE.
Click to expand...

thank you...
 
Back
Top