Access 2010 NEWB in NEED of some help!!

L

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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
lakes_it_guy said:
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!
Click to expand...


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
lakes_it_guy said:
[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
Click to expand...

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
You must log in or register to reply here.

Similar threads

C
  • Solved
Access Querry Seeking Records that has 0 usage in Three Consecutive Months
Replies
5
Views
483
Chris101
C
H
  • Solved
ACCESS suitability for multiple column "TEMPLATE" concept
Replies
4
Views
650
heathball
H
A
  • Question Question
Access: strSQL Alter Table (Field Types)
Replies
3
Views
1K
Micron
M
A
  • Question Question
Access Newbie - can't work this out.
Replies
6
Views
589
stumac
S
B
  • Question Question
Access Vba to copy table from one database to another
Replies
0
Views
3K
BalloutMoe
B

Forum statistics

Threads
1,224,884
Messages
6,181,556
Members
453,053
Latest member
Kiranm13

Share this page

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