Hi to all!
I face the following problem: I cannot add amounts per line!
I have one table consisting of many different columns (fields), some of them having amounts. I want to update this table by doing the following:
let's say I have the following records:
1/6/2006 A 12
1/5/2006 B 20
15/6/2006 C 21
22/6/2006 D 17
25/6/2006 E 30.
I want to use a filter (e.g. the date being between 1/6/2006-30/6/2006), sort the records descending according to the amounts and then add in another field of the table A, B or C, according to whether the total amount including the specific record is above 70% of the total or above 90% of the total. In the specific example I want the following result:
25/6/2006 E 30 A
15/6/2006 C 21 A
22/6/2006 D 17 B
1/6/2006 A 12 C
Their total amount is 30+21+17+12=80 and the percentage of each record is: E:0.375 , C:0.2625 , D:0.2125 and A:0.15. Their adding percentage is
E:0.375 , E+C:0.6375 , E+C+D:0.85 and E+C+D+A:1.
How can I do that? I calculate the percentage of each record in a query, but how can I calculate their adding percentage, so that I can use an if-clause in an appending query?
Please help!!
Thanks in advance for your time

Panagiotis

i think you want to create another query add use the first query as the data source.

then you can add the percentages because they will be pre-calculated and available to you.

Yes, but how can I calculate tha adding percentages? I mean that I calculate the percentage of each record to the total, but how can I calculate the percentage of the first two records, then of the first three, etc. ?
How can I do that?
In the new query I will only have the same data? And?

How can I calculate the adding percentages? I cannot make it even through a new query! Any suggestions?

My guess is that you are getting beyond what you can do with simple queries and need to move on to VBA code to accomplish this. You can't do things like running totals with queries, as that would require a much greater of record interaction between elements of a recordset than is possible.