Quote

sh2...@yahoo.com wrote in message <3765f081.5541...@news.insync.net>...
>Table1 has 3 fields: Name, commission and Date. A name may appear more
>than once with various amounts of commission on different dates.
>Example:

>Name Commission Date
>John Smith $100 2/4/99
>Sue Jones $90 1/9/99
>John Smith $40 3/2/99
>Sam Reed $100 1/11/99
>Sue Jones $150 3/3/99
>Sue Jones $60 3/5/99
>.
>.
>.
>I run a query against Table1 which groups all the names in the table
>and sums up the commission for each name (one record p/name). So if I
>ran the query for the example table, the result would be:

>Name Sum of Commission
>John Smith $140
>Sam Reed $100
>Sue Jones $300

>So far so good, but I'm having problem with this next part: I need to
>add a third field (a calculated field) to the query that sums up the
>commission *for a certain period of time (for instance for January
>1999)*.  I still need to keep the original Sum of Commission field in
>the query, which sums up the commission for *all records in the table
>belonging to an employee *.  I ran the following Select statement for
>the calculated field, but it didn't do what I expected:

>SELECT Sum(Commission) AS JanCommission FROM Table1 WHERE (Date
>Between #1/1/99# And #1/31/99#) AND (Table1.Name=Query1.Name);

>The second part of the Where clause (Table1.Name=Query1.Name) is
>incorrect.  How should I state it? Keep in mind that this is a
>calculated field and must be run in conjunction with the original
>query as described earlier.  I'm trying to avoid running two separate
>queries and combining their results.  I would appreciate any help.

>Thanks,

>A.