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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mikael Lindqvist - 11 Nov 2007 22:21 GMT

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

Reply to this Message

Marshall Barton - 11 Nov 2007 23:47 GMT

>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
[quoted text clipped - 22 lines]
>
>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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Mikael Lindqvist - 12 Nov 2007 07:38 GMT

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

> >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
[quoted text clipped - 33 lines]
> I hope you don't really have a field named Value, which is a
> reserved word.

Reply to this Message

Marshall Barton - 12 Nov 2007 20:40 GMT

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:

ON Table1.datefield >= Table2.Date1 And Table1.datefield <=
Table2.Date

>It looked good but when I try to save the SQL code I get error:
>
[quoted text clipped - 8 lines]
>> Note that the query design grid can not represent this kind
>> of join, so you **must** work on this entirely in SQL view.

>> >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
[quoted text clipped - 22 lines]
>> >
>> >And so on for all the other dates (2005-01-02 -- 2007-10-31).

Signature

Marsh
MVP [MS Access]

Reply to this Message

Mikael Lindqvist - 13 Nov 2007 14:08 GMT

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

> You need to replace my made up field and table names with
> the ones you are using.
[quoted text clipped - 4 lines]
> ON Table1.datefield >= Table2.Date1 And Table1.datefield <=
> Table2.Date

Reply to this Message

Marshall Barton - 13 Nov 2007 17:16 GMT

>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

Signature

Marsh
MVP [MS Access]

Reply to this Message