autosum in a query

[danielc]

Hi,

I'm new on this forum and I'll appreciate your help.

I have a table that looks like that:

id transaction date
a / -20 /01.01.06
a / 500 /01.01.06
a / 500 /01.01.06
a / 230 / 02.01.06
a / -160 / 02.01.06
b / -400 / 01.01.06
b / 450 /01.01.06
b / -20 / 02.01.06
b / 300 / 02.01.06

I want to sum all transaction on each date for each id like:
a / 980 / 01.01.06
a / 1050 / 02.01.06
b / 50 / 01.01.06
b / 330 / 01.01.06
.......
I know how to do that in a report, but it's possible to do it directly in a query? I need the data in a new table.
Any clue?

Thanks.
Daniel

 

[namliam]

You do it the same way in a query as in a report?

Select ID, sum(transaction), Date
From yourtable
group by ID,Date

That should be your query. I sure do hope that Date is not your real Column name....

 

[MStef]

Hello Daniel, How are you!

Look at "DemoAutosumQA2000.mdb" (a new one).
Open "Query2" and look at column Expr1.
Look at Module1.

 

[danielc]

HI,

Thanks a lot. It works fine.

Daniel

 

[danielc]

But only for 2 dates. Otherwise takes as reference only the first date.

Dny

 

[namliam]

Isnt the simple query, I provided, what you are looking for?

 

[MStef]

Hello Daniel!

Ok, it was my mistake. I adapted Module1, it's OK now.

 

[EMP]

Ok, it was my mistake. I adapted Module1, it's OK now.

Using a variable to store a running sum will fail when there are more records than one query screen can display because a query needs to refresh/repaint itself when the cursor is moved or when the query is run for a second time.

You can easily see for yourself if you add more records with different dates to your table and repeatedly run the query.

^

 

[MStef]

Hello EMP!

Thanks EMP, i have forgot on this problem.
I remember you warned me to this any time before.
The problem is solved via REPORT.
I suggest Daniel to make a report on Query2.
When I make something like this, I always do it via report.