Access 2010 NEWB in NEED of some help!!

[lakes_it_guy]

Hello all! I need some help with an Access 2010 database. Here is my issue:
I have two tables, table1, and table2. In table1, there is a # of gallons used/month column. The rows in this column,in descending order, are as follows: 0-5, 6-10, 11-15, 16-20.
In table 2, there is a Usage column that holds the value of a members monthly usage.

Can ANYONE PLEASE explain how I can go about sorting these two columns correctly????

EXAMPLE: Member has a USAGE value of 5, so I need to create a "# of Customers" column that will hold the count of all members in the database that has a 0-5 gallons used/month value, 6-10, etc.. Hope I didnt confuse you! Good day mate!

 

[HiTechCoach]

Hello all! I need some help with an Access 2010 database. Here is my issue:
I have two tables, table1, and table2. In table1, there is a # of gallons used/month column. The rows in this column,in descending order, are as follows: 0-5, 6-10, 11-15, 16-20.
In table 2, there is a Usage column that holds the value of a members monthly usage.

Can ANYONE PLEASE explain how I can go about sorting these two columns correctly????

EXAMPLE: Member has a USAGE value of 5, so I need to create a "# of Customers" column that will hold the count of all members in the database that has a 0-5 gallons used/month value, 6-10, etc.. Hope I didnt confuse you! Good day mate!

First in Table 1 ( I think that is the one - please use the real name) you will need to split the field into two fields. One field for the start of the range. another field for the end of the range. Once you do this it will be a lot easier.

 

[lakes_it_guy]

Thanks for the help!
table1 = "WRWSD_Table"
table2 = "Monthly Usage"

Once i split this up into two different fields, how would I use those fields to sort my members into the # of Customers column?

 

[HiTechCoach]

Now you can see if the monthly usage is within the range boundaries.


Somehtng like:

MonthlyUsage >= StartOfRange and MonthlyUsage <= EndOfRange

or

MonthlyUsage between StartOfRange and EndOfRange

 

[lakes_it_guy]

[MonthlyUsage].[Usage]>=[WRWSD Test Table].[StartofRange] And [MonthlyUsage].[Usage]<=[WRWSD Test Table].[EndofRange]

This is what i'm working with:

but Im getting this error: Data type mismatch in criteria expression?

Like I said before I am a newb to ACCESS, so PLEASE try and be as specific as you can. This is not my field of expertise

 

[lakes_it_guy]

Heres the link to my screenshot of my access 2010 database
http://tinypic.com/r/10z23j7/5
For some reason the image didnt show up in my last post

 

[HiTechCoach]

[MonthlyUsage].[Usage]>=[WRWSD Test Table].[StartofRange] And [MonthlyUsage].[Usage]<=[WRWSD Test Table].[EndofRange]

This is what i'm working with:

but Im getting this error: Data type mismatch in criteria expression?

Like I said before I am a newb to ACCESS, so PLEASE try and be as specific as you can. This is not my field of expertise

Are the fields [MonthlyUsage].[Usage], [WRWSD Test Table].[StartofRange], and [WRWSD Test Table].[EndofRange] all the same numeric data type?

I would recommend using a subquery to get the usage grouping.


IMPORTANT TIP: Avoid using spaces or non-alpha/numeric characters other than an underscore ( _ ) in the name of user defined object named (tables, fields, etc)

 

[lakes_it_guy]

http://tinypic.com/r/104mdfk/5
image above is what I am working with now. No more errors But still not getting the results that I need:/

http://tinypic.com/r/15dtlip/5
this is my query datasheet view above, below is what im trying to achieve:

StartofRange
0
1
1001
2001
etc.

EndofRange
0
1000
2000
etc.

CountofMembers
2
24
67
99
etc.

sorry for the format but when i submitted the post the first time it threw the numbers around and f*cked everything up. HOPE you can help me!

 

[HiTechCoach]

I don't see how you query will work. I would use a sub query to look up the value form the Test table.
e
I will put together an example and post the SQL shortly.

 

[HiTechCoach]

Here is how I did it:

1) create and save the following query:

SELECT (SELECT WRWSD_Test_Table.RangeDesc
FROM WRWSD_Test_Table
WHERE (((WRWSD_Test_Table.StartOfRange)<=MonthlyUsage.[Usage]) AND ((WRWSD_Test_Table.EndOfRange)>=MonthlyUsage.[Usage]));
 ) AS RangeDesc, MonthlyUsage.Usage, MonthlyUsage.ID
FROM MonthlyUsage;

2) create a new totaling query that uses the above saved query as the record source


Also see: Subquery basics