Access 2010 NEWB in NEED of some help!!

lakes_it_guy

New Member
Joined
Oct 26, 2011
Messages
19
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!
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
[MonthlyUsage].[Usage]>=[WRWSD Test Table].[StartofRange] And [MonthlyUsage].[Usage]<=[WRWSD Test Table].[EndofRange]

This is what i'm working with:
5


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
 
Upvote 0
[MonthlyUsage].[Usage]>=[WRWSD Test Table].[StartofRange] And [MonthlyUsage].[Usage]<=[WRWSD Test Table].[EndofRange]

This is what i'm working with:
5


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)
 
Upvote 0
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!:)
 
Last edited:
Upvote 0
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.
 
Upvote 0
Here is how I did it:

1) create and save the following query:

Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,483
Messages
6,172,525
Members
452,463
Latest member
Debz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top