Parameter based sumif query (i think!)

[Rick84]

Hi

Im reasonably new to access databases so I still to like to build my queries in the design view rather than pure SQL. My question is:

I have a table with the columns; Month_Num | Amount (which is linked to others)

1 | 2000
2 | 3000
3 | 3500
4 | 3600
5 | 2800
6 | 3000
7 | 4000
8 | 0
9 | 0
10 | 0
11 | 0
12 | 0

I want to show the YTD figure when i run the query, so i want to enter a value into a parameter, say 3 and then have the query sum all the amounts where Month_Num is <= 3

Hopefully Im being clear. Ive spend hours trying to solve this. Probably over complicating it. Im hoping someone could point me in the right direction?

Thanks

 

[apr pillai]

1. To try a sample, create a Table with the name Table2 with Fields: Month_Num and Amount.

2. Open a new Query and display its SQL Editing window.

3. Copy and paste the following SQL String and change the view to Design View:

SELECT Sum(Table2.Amount) AS SumOfAmount
FROM Table2
WHERE (((Table2.Month_Num)<=[Enter Month for Total]));

 

[spaddhu]

Rick,

I don't know why you want to stick to Design View. I agree it helps you build queries easier and faster. But to have absolute control over the query I would suggest you also acquaint yourself with the SQL view.

apr pillai is correct in terms of SQL.. but if you want in terms of Design View.. here is what you will have to do..

- Open a new query.
- Drag the Amount field as the first field and Month_Num.
- Click the "Summary" button (or grouping button).
- In the summary row change the "Group By" to "Sum" for Amount.
- For the Month_Num change it to "Where"
- Add a criteria under Month_Num as <[Param_Month]
- Untick the Month_Num, so that it is not projected in the output.

 

[Rick84]

Fantastic guys thanks! it was the "where function i was missing!"