Totals

[crazymarc]

Hi there, I am fairly new to access and am having trouble doing what I need to do. I have a table that contains 2 number fields, 14 yes/no fields, and one text field. I want to be able to display the total number of yes's per field, and the total count of each individual value for the number and text fields.

I was able to make individual queries that count all of these, however I'm unsure how I can make a query that will do it all together, or how to put them all together.
I may be doing this wrong, or there may be a better way of doing this.

Any help would be greatly appreciated!
Thanks very much!

 

[hmongie]

Hello CrazyMarc,

If you have the queries working already, you can have them display on a report. Create an unbound field and have it sum up the fields.

I would probably tried to create unbound fields on a report to count each of the record set based on your result and than have a total at the bottom(pagefooter) to sum them all together.

I'm guessing something like =[totalfield1]+[totalfield2]+[totalfield3]

Hope that gives you some ideas.

 

[Fernando]

You can totally make a query like this for the first part

SELECT fldText, (fldYesNo1+fldYesNo2+fldYesNo3+...+fldYesNo14)*-1 AS SumOfYes
FROM tblTable;

 

[crazymarc]

Thank you very much for the replies, however I'm not exactly sure that I worded it correctly, I'm extremely sorry for this. I'll try to explain it more clearly!

In my table, I have:

- 2 number fields
- 14 yes/no fields
- 1 text field
​

For the yes/no fields, I need to be able to display the total number of yes's for each field individually. (Eg. A yes/no field named Male, I need to know how many in total are male.) and all the fields need to be done separately

For the number and text fields, I do not know what the possible entries will be. The number field is birth year, so it could be pretty much anything, and there are many possibilities for the text field. I need to be able to count how many of each value there are in the database. (Eg. How many people have a birthday in 1983, 1975, etc...)

As I previously said, I have queries for all of these set up, Im just not sure how I can put them together into one.

I hope that this is somewhat more clear, sorry for the confusion.

Thank you very much for the help!

 

[stopher]

I doubt that you can write a single query to give you all the answers. For instance, what you are asking for with the years is a list of years with the frequency of each year i.e. the query will produce a list...
1964___3
1965___8
1966___5
etc

But then counting y/n fields will give you a single line of info
Male? 26 HomeOwner? 15.....
(btw, imo it's easier to sum y/n fields because you can do them all at the same time - you'll get a negative answer but that's easily adjusted)

It's possible to consider the year as a string then UNION the results with each y/n. But this seems like hard work.

The way to make all this look pretty is to put it on a report. You can use subreports to bring the results of different queries together. Also, take a look at the DCOUNT function which can be useful if queries aren't giving you what you want.

So I think you need three queries:
- a Count aggregate query for the year
- a Sum aggregate query to sum all the y/n
- a Count aggregate query for the text

Then create three small separate reports - one for each query and then create a main query and add the three reports as subreports.

If you're stuck and need an example, let me know.

hth
Chris

 

[crazymarc]

Thank you very very much chris, this has been extremely helpful!
The only thing that I am having trouble with is doing the sum aggregate query for all the y/n. Would it be possible to get an example of this?

Thanks very much, it is greatly appreciated!

Marc

 

[stopher]

Example attached.

Chris

 

[crazymarc]

Thank you very much, Chris! It worked beautifully!

Next thing that I was just informed that would be good to have on this (I cant think of a way that it would be possible, without doing separate tables) :

The data needs to be reported as a monthly statistic. So each month, all of the data would be put in, and it would need to be put as separate months on the report (displaying the totals for each month, as we have just done). Is there any way to do this without making separate Tables, Forms and Queries?​

Thank you for all of the help,
Marc

 

[Rabbie]

At present you don't have any Date field in Table 1 so how do you know which records go in each month?

 

[crazymarc]

At present you don't have any Date field in Table 1 so how do you know which records go in each month?

I can add a date field... First I was just wondering how I would be able to count my totals so that it only includes it by month, so that I could have say... a 12-page report (one for each month)

 

[Rabbie]

If you have a date field then you can use the Query wizard to give you totals by month. You can then use this as a source file for your reports