Using sum and min in the same query

[thread]

Hi all,

In need to build a query that will give the sum of some ammount and
will give the % depending on the last period
for example
1234 1000 04 10%
1234 2000 07 4%
1234 3000 08 9%
1234 4000 10 8%

 

[Allen Browne]

So you want to compare the summed amount for this period against the summed
amount for another period in the same table?

Use a subquery. This example shows how to sum another period from the same
table to get a year-to-date amount:
http://allenbrowne.com/subquery-01.html#YTD
Use the same principle to sum the amount for your preceding period.

You can then calculate the percent from the 2 totals.

 

[thread]

no my problem is that i cannot find the solution to get the maximum or
the minimum presangate(10% or 8*) in one field and in another field i
would like to get the sum of the amount for id 1234
something that its hard for me to solve
for now i'm doing 2 steps
making append table and populating it to another table with the amount
and the minimum period or the maximum
then making another query that is with a relationship between the id
and the period and by this way i'm pulling out the needed
presentage(from maximum period or minimum period)
i would like to find a way to build in one query the 2 steps to append
the data to a table

Allen Browne כתב:

 

[Allen Browne]

Switch the query to SQL View (View menu in query design), and post the SQL
statement you have.

I assume it's something that groups on the ID field, chooses the Min of the
presangate, and sums the Amount.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

no my problem is that i cannot find the solution to get the maximum or
the minimum presangate(10% or 8*) in one field and in another field i
would like to get the sum of the amount for id 1234
something that its hard for me to solve
for now i'm doing 2 steps
making append table and populating it to another table with the amount
and the minimum period or the maximum
then making another query that is with a relationship between the id
and the period and by this way i'm pulling out the needed
presentage(from maximum period or minimum period)
i would like to find a way to build in one query the 2 steps to append
the data to a table

Allen Browne כתב:

 

[thread]

If it was the minimum presentage it was easy,
the issue is that i need to present the presentage of the minimum
period month
if i still little bit in fog i will post the sentence
Allen Browne כתב: