Sum values in a "chronlogical date list" from "transactions table"

[Mikael Lindqvist]

Hi everyone,

What I'm asking for here would be so easy in Excel but my skill in Access
just doesn't cut it yet.

I have 2 tables, first table (Table1) only contain one field (a list of
dates, starting from "2005-01-01" and ending "2007-10-31"). The second table
(Table2) has 3 fields:

* Date1
* Date2
* Amount (values ranging from 0 to 1200 USD)

Now, I want to run a conditional sum for each date in Table1.

For example, for first value in Table1: "2005-01-01" I want to run a query
that adds up the values of all transactions in Table 2 that have "2005-01-01"
in the interval between "Date1" and "Date2".

That is, in Table2:

Date1, Date2, Value
2004-12-10 2005-05-01 279
2005-01-01 2005-01-02 300
2005-01-02 2005-01-10 500

Would add up the first 2 as "2005-01-01" is in their interval, thus, the
query would return 579 next to "2005-01-01" (assuming there are no more
transactions in this interval).

And so on for all the other dates (2005-01-02 -- 2007-10-31).

Any help as usual much appreciated!

Kindly,
Mikael
Sweden

 

[Marshall Barton]

I have 2 tables, first table (Table1) only contain one field (a list of
dates, starting from "2005-01-01" and ending "2007-10-31"). The second table
(Table2) has 3 fields:

* Date1
* Date2
* Amount (values ranging from 0 to 1200 USD)

Now, I want to run a conditional sum for each date in Table1.

For example, for first value in Table1: "2005-01-01" I want to run a query
that adds up the values of all transactions in Table 2 that have "2005-01-01"
in the interval between "Date1" and "Date2".

That is, in Table2:

Date1, Date2, Value
2004-12-10 2005-05-01 279
2005-01-01 2005-01-02 300
2005-01-02 2005-01-10 500

Would add up the first 2 as "2005-01-01" is in their interval, thus, the
query would return 579 next to "2005-01-01" (assuming there are no more
transactions in this interval).

And so on for all the other dates (2005-01-02 -- 2007-10-31).

SELECT Table1.datefield, Sum(valuefield) As SumOfValue
FROM Table1 LEFT JOIN Table2
ON Table1.datefield >= Date1 And Table1.datefield <= Date2
GROUP BY Table1.datefield

Note that the query design grid can not represent this kind
of join, so you **must** work on this entirely in SQL view.

I hope you don't really have a field named Value, which is a
reserved word.

 

[Mikael Lindqvist]

Hi,

It looked good but when I try to save the SQL code I get error:

"JOIN-expression is not supported"

And then the code:

"Table1.datefield >= Date1"

Is highlighted.

Any ideas what's wrong?

Kindly,
Mikael

 

[Marshall Barton]

You need to replace my made up field and table names with
the ones you are using.

I also forgot the table names in the On clause. It should
be:

 

[Mikael Lindqvist]

Now it works like a charm. Thanks! :>

Is it possible to tweak this SQL so that I "count" (instead of "sum") a
specific value in another field (say "Apples" in Table2, field "Fruits")?

Kindly,
Mikael

 

[Marshall Barton]

Is it possible to tweak this SQL so that I "count" (instead of "sum") a
specific value in another field (say "Apples" in Table2, field "Fruits")?

It probably is possible, but I need more details about the
values, fields and what else the query is doing.

Lacking sufficient information, here's an example that might
or might not be what you want:

SELECT Table1.datefield, Fruits,
Count(*) As CountOfFruit,
Sum(valuefield) As SumOfValue
FROM Table1 LEFT JOIN Table2
ON Table1.datefield >= Table2.Date1
And Table1.datefield <= Table2.Date2
GROUP BY Table1.datefield, Fruits