Summative query on a single tabled database

[ayupchap]

Hi all,



So wonder if anyone can help me with this. I have a flat-file database and have recorded students who are trying to register to attend the school I work at. Now I need to create a summary query that I can make into a report which counts how many students have applied for each grade. All the gardes are listed in the same field.

Is this something I should do in a query or a report actually? I am not sure.

Thanks for any help.

[Ruegen]

Hi all,

So wonder if anyone can help me with this. I have a flat-file database and have recorded students who are trying to register to attend the school I work at. Now I need to create a summary query that I can make into a report which counts how many students have applied for each grade. All the gardes are listed in the same field.

Is this something I should do in a query or a report actually? I am not sure.

Thanks for any help.

You could make another table with grades and each grade has an ID

then you could have another field that uses a ID number to represent the grade record in your original table to the corresponding ID number for that table using an update query ( you don't want to delete the original data, just make another field ).

so like

gradefield correspondingNumber
Year 1 1
Year 2 2
Year 5 5

Later on you can count the numbers using totals in a query (you can do this over "gradefield" already but it is better to sort it out first)

[ayupchap]

OK great thanks! Maybe you can with something else, sorry if this sounds long-winded

I have to summarize many different fields for this summative report. For example I need to group together the following and have no idea how to do this in one query, I will list the field and what it needs to show:

Grade - Summarized by number in each grade
Accepted - Those that have accepted (marked as YES in the database in the ACCEPTED field)
Declines - Those that have not accepted (marked as NO in the database in the ACCEPTED field)
Not replied - Those that have not let us know yet (marked as blank in the database in the ACCEPTED field)


Now I can run these as individual queries and get the right results but I can not do it in one query, is there anyway of doing this? Simple step by steps would be great as I am not great at queries. Also When I did make them individually I couldn't put them together in a report as the queries were not linked?

Any help very much appreciated.

[Ruegen]

OK great thanks! Maybe you can with something else, sorry if this sounds long-winded

I have to summarize many different fields for this summative report. For example I need to group together the following and have no idea how to do this in one query, I will list the field and what it needs to show:

Grade - Summarized by number in each grade
Accepted - Those that have accepted (marked as YES in the database in the ACCEPTED field)
Declines - Those that have not accepted (marked as NO in the database in the ACCEPTED field)
Not replied - Those that have not let us know yet (marked as blank in the database in the ACCEPTED field)


Now I can run these as individual queries and get the right results but I can not do it in one query, is there anyway of doing this? Simple step by steps would be great as I am not great at queries. Also When I did make them individually I couldn't put them together in a report as the queries were not linked?

Any help very much appreciated.

Again it's the same scenario except you only have 3 possibilities.

If you had only two possibilities then you could have used a bool true/false like a checkbox field (or even a date field because if it is null you could refer to that being false and if not null then you have the date it was accepted at the same time) but since you don't you will need three choices and the first choice as a 'default' would be ID 1

So you could make another table that refers to a new field in your current table. That new table would have the three rows, "Not replied", "ACCEPTED", "NOT ACCEPTED".

This way if you build a form (if you do) then you can use a combo box with the default being "not replied" on any particular record that is new or hasn't replied yet, then you can update it to the other two choices when the record changes.

As for summarising it - what do you want it to summarise to? For example if you want to you can add a new field in the query (note: not the table), call it count1, and have it so that if the record field "grade" = "Accepted" then count 1 else do not count 1 and give it a 0

that way you could use this method IIF()

so

IIF([grade]="ACCEPTED",1,0)

then you could repeat this for the other two (not accepted, not replied) and see 3 fields that have counted.

You would only have those 3 - count1, count2, count3 - fields and group total everything.



The reason you would make the table is asking a query to run an if on a string "ACCEPTED" is more intensive than an integer/number like 1, 2 or 3.

so instead you could have

IIF([grade]=1,1,0)