Count field not counting properly?!?

[lakes_it_guy]

Hello mates,
heres an image of my problem:
http://tinypic.com/r/3162f81/5

Im trying to get the count of three fields, but unfortunately it keeps repeating the data when it should just be giving me one total amount number per field. HElP! Thanks in advance.

 

[Joe4]

I am totally baffled at why you are trying to do, and the method you are using.

You have three objects which do not appear to be linked (joined), and are
Grouping by one field in each (no Count function us being used).

When you have two or more tables/queries in a query and they are not joined, the resulting data set is a Cartesian Product of all the records. The Grouping will just weed out duplicates (where all three values are the same).

Why don't you just describe the data that you are working with (maybe post some samples of what the data looks like) and explain exactly what you want to return.

 

[lakes_it_guy]

I am fairly new to Access 2010 so i apologize for the confusion. But I have two tables: WRWSD_Tester, and MonthlyUsage.WRWSD_Tester table has two fields, StartofRange, and EndofRange.
MonthlyUsage simply holds members values for how many gallons of water they have used. This all pertains to surveying water for a private gated community.
I created multiple queries to find the count for how many members have used a certain amount of gallons of water. e.g. one query has the total amount of members that have used >0 but less than 1000 gallons of water for the month. The next query is >1000 but < 2000, then >2000 but < 3000. And this continues till 20000. So multiple queries give me the count of how many members have used that specific amount of gallons of water.
Im simply trying to create a query or report that will show how many members have used that many gallons of water for the month.
Report Example:

No. gallons used No. of Customers
0 ---------------------- 2
1-1000 ---------------- 45
1001-2000 ------------ 77
etc. ------------------ etc

But I am not getting these results in my final query of the queries and I am not an expert in Access, hope this helps you understand what im trying to do! Thanks for your time.

 

[Joe4]

Why do you have a separate query for each amount range?

Let's say that you have a table/query that looks like the following:

Cust_Num    Water_Usage
    1              555
    2              853
    3             1600
    4             2099
    5             2473
...

Then you could create a new query off of this table/query that just has the following calculated fields:

0_1000: IIf([Water_Usage]>0 And [Water_Usage]<=1000,1,0)
1000_2000: IIf([Water_Usage]>1000 And [Water_Usage]<=2000,1,0)
2000_3000: IIf([Water_Usage]>2000 And [Water_Usage]<=3000,1,0)
...

Then, if you click the Totals icon in Query Design Model (looks like a Sigma), it will add a Totals Rows under each of the Calculated Fields with the value of "Group By" under each one. Change all those to "Sum".

Then if you go to Datasheet View, it will return a single row, with the counts of each under each data range field.

 

[lakes_it_guy]

I AM NOT AN EXPERT IN ACCESS. I just started working in it like a week ago, which is why I signed up on this website lol Your like a level 10, and im a level 2.
I have a seperate query because thats the only way I know how to do it. Ive gotten pointers from others, just getting advice and trying out different things. I am confused on a couple things you just posted.
(1) -
In the code below: I know what this does--> 0_100: (declares a new field)
I dont know what this means: IIf
and also what the numbers at the end means: ,1,0

if you could clarify exactly what this does i would appreciate it very much. Thanks again for your time.

0_1000: IIf([Water_Usage]>0 And [Water_Usage]<=1000,1,0)</pre>

 

[Joe4]

These function are not expert function, they are basic Access functions. The function is the "immediate IF function". There is a good explanation of how it works here: http://office.microsoft.com/en-us/access-help/iif-function-HA001228853.aspx

All we are doing with the first expression is checking if our water usage is in between 0 and 1000. If it is, return a "1", otherwise return "0".
Return similar calculations for the other fields.
For each record, this will return a "1" under the range that values is found in, and a zero for all the other ranges.
Then, we are just Summing up all those "1"s to get a total count.

If you are very green in Access, I would HIGHLY recommend taking a class and/or getting a good introductory book and work through it. It is very important to understand the concepts of Relational Databases, and Data Normalization. Otherwise, if you try wading too far out into the waters of Access without having a good understanding of these concepts, Access will drive you crazy (trust me, I have been there!). Access is much more complex and not nearly as intuitive as Excel, but very powerful when you know how to use it right.

There is a really good article on Relational Databases and Data Normalization here: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

As far as learning how to do different types of queries in Access, most introductory Access books should do. I learned by using the Microsoft Step-by-Step Access book.

 

[lakes_it_guy]

Joe, Thanks so much for explaining in such a professional and curtious way. This helped me out tremendously, and also thanks for the articles! Have a great friday mate!

 

[Joe4]

Glad to help! I always hope that I can help a few people avoid the horror story that I experienced.

My first exposure to Access was when I was asked by my employer to create a client billing database. At the time, I was very proficient in Excel and VBA, and thought it should be no problem to hop right into Access. Well, after a few months of frustration and not getting anywhere fast, someone recommended that I educate myself first. So I picked up the Access book, a and Relational Database book, and eventually took a few short classes in Access.

I discovered that my whole database structure was wrong, my tables were not normalized. So I ended up scrapping most of what I did and starting over. Once I corrected that, tasks which were causing me fits now became much, much easier. And the datbase ended up being a big success (they are still using it, almost 10 years later!).

Now I love Access, and actually use it more than Excel (more the nature of my business). But it definitely has a much bigger learning curve than Excel.

 

[lakes_it_guy]

Yea I know how you feel, for the most part. I was asked by my employer to create a monthly report for water usage in the community. I am an I.T. admin. Job duties: programmer, webmaster, network admin. Needless to say I have never worked in access and I thought this task wouldnt be too difficult. WELLLLLLL lol turns out, the other I.T. guy who is on retainer decided to just let me wing it and tells me nothing about our access database. So a week into looking into all the tables and queries, I have familiarized myself with the different databases and am now building what my company wants. But seriously thanks for your time and effort into helping another out. The world needs people like you! haha Enjoy your week mate!!

 

[Joe4]

I am an I.T. admin. Job duties: programmer, webmaster, network admin.

I work in a small IT department myself, a total of 3 people who have to be able to cover for each other. My "specialty" is Programming, though we all have to be "Swiss Army IT guys" to a certain extent. Its the systems and hardware stuff that gives me the most problems, as I was never officially trained on any of that stuff.

I'm always learning something new...