date difference calculation

[sumhungl0]

im not sure how to even start this. im using access 03 to build a db to store my water district meter readings. i have 2 tables so far, 1 consumer info and 2 monthly meter readings. i need a query to bring up the difference between current month meter reading and last months meter reading for each consumer. thats where im stuck because ive never used access to do much of anything at all. so i figured id ask, and here i am. table 1 consumer info doesnt store much except name and address. i may add more info later if needed. table 2 is where the usefull information is stored. it has consumer name, date and meter reading. what i am trying to figure out is how to get the difference between monthly meter readings so i can make a report that will print up a bill to send out to the consumers. first i need the difference then i can try to get access to calculate the bills cost. just trying to make things more user friendly on my part. thanks in advance.

[sumhungl0]

maybe i need a table for each consumer?

[Ares6881]

im not sure how to even start this. im using access 03 to build a db to store my water district meter readings. i have 2 tables so far, 1 consumer info and 2 monthly meter readings. i need a query to bring up the difference between current month meter reading and last months meter reading for each consumer. thats where im stuck because ive never used access to do much of anything at all. so i figured id ask, and here i am. table 1 consumer info doesnt store much except name and address. i may add more info later if needed. table 2 is where the usefull information is stored. it has consumer name, date and meter reading. what i am trying to figure out is how to get the difference between monthly meter readings so i can make a report that will print up a bill to send out to the consumers. first i need the difference then i can try to get access to calculate the bills cost. just trying to make things more user friendly on my part. thanks in advance.

You don't need a table for each consumer, dealing with dates can be pretty tricky especially when it comes to year changes. I take it you want to deal directly with months and not days? Days can be a bit easier when dealing with date ranges, but months is still possible. Start off making a query that gets the sum of all your bills for the current month, but in your consumer name as one of your columns, then have it sum up your meter readings in another column and in the last column put the month of your date field, I believe you can use the datepart function to get that. That should get you your first step, sorry I can't get you any more right now, but it's lunch time ;P

[sumhungl0]

well im gonna try a few things. but the meter reading is done monthly. so i only have one meter reading per consumer per month. so thats why i thought i would make a table for each consumer, what do you think? my date column is in the format of mmddyyy just for ease, but the month is all im looking for. i work with an older lady on this part in which her only job on this deal is to input the monthly meter readings for the consumers. we do not get paid for this. im just trying to make things easier on us in the long run. in the end, the only info that gets put in monthly is the meter reading and month for each consumer. i need this db to give me the difference between the reading from the current month and the last month. then ill try to figure out how to make the db calculate the bill and print it from a report. that way i am not doing all this on a calculator and word document. does that clear things up a bit?

[Ares6881]

well im gonna try a few things. but the meter reading is done monthly. so i only have one meter reading per consumer per month. so thats why i thought i would make a table for each consumer, what do you think? my date column is in the format of mmddyyy just for ease, but the month is all im looking for. i work with an older lady on this part in which her only job on this deal is to input the monthly meter readings for the consumers. we do not get paid for this. im just trying to make things easier on us in the long run. in the end, the only info that gets put in monthly is the meter reading and month for each consumer. i need this db to give me the difference between the reading from the current month and the last month. then ill try to figure out how to make the db calculate the bill and print it from a report. that way i am not doing all this on a calculator and word document. does that clear things up a bit?

Is the date column set as a date value, or is it a number value with mmddyyy (only 3 y for year? or was that a mistype?) as the format. If it's just a number value it's handled a bit differently. If you don't know how to tell go to the table and go into design view, it should tell you on the right hand side what kind of value it is. Could you give me the names of each column so I can make queries that you can just plug in? Are the readings always done on the same day every month, or does it change? Were you able to figure out the datepart function (this doesn't work if it's a number value)?

[sumhungl0]

Is the date column set as a date value, or is it a number value with mmddyyy (only 3 y for year? or was that a mistype?) as the format. If it's just a number value it's handled a bit differently. If you don't know how to tell go to the table and go into design view, it should tell you on the right hand side what kind of value it is. Could you give me the names of each column so I can make queries that you can just plug in? Are the readings always done on the same day every month, or does it change? Were you able to figure out the datepart function (this doesn't work if it's a number value)?

ok as for the date question....
i dont care what format it is i can change it to whatever you suggest, because all i need for the date is that it happens once monthly. we have a guy who goes around my small town and takes water meter readings. the data is input into a excell spreadsheet right now. it only needs to be input monthly, so i dont care what format it is in the end. i just thought it would be better to use the full date format. and yes i mistyped... sorry. i could even make it into a dropdown list to pick the month if need be, but i didnt know how to do the year because i wanted the db to keep history.

the names of the columns are....
consumer name / date / meter reading
thats it, thats really all i need from that table. that data is the only info taken from consumer homes. then i need to know the difference between meter readings to find water usage. then i can calculate the bill on that difference. does that help?

[Ares6881]

ok as for the date question....
i dont care what format it is i can change it to whatever you suggest, because all i need for the date is that it happens once monthly. we have a guy who goes around my small town and takes water meter readings. the data is input into a excell spreadsheet right now. it only needs to be input monthly, so i dont care what format it is in the end. i just thought it would be better to use the full date format. and yes i mistyped... sorry. i could even make it into a dropdown list to pick the month if need be, but i didnt know how to do the year because i wanted the db to keep history.

the names of the columns are....
consumer name / date / meter reading
thats it, thats really all i need from that table. that data is the only info taken from consumer homes. then i need to know the difference between meter readings to find water usage. then i can calculate the bill on that difference. does that help?

Yeah, that helps, alrighty one neat trick in case you didn't already know how to do it is that you can actually import from your excel sheet directly into access. It's under file/get external data/import
You'll then select Microsoft Excel from the type at the bottom then pick your file. Your file should be in the format of having your headers across the top and all your data below it to import properly. You may want to change your date header to say something other than date as date can be used as a function in access and you don't want to confuse the two. Anyway, that should save you time on data entry.

Back to your query, once you have your table made start making your query of the new table. The columns should be something like this:

consumer name / meter reading / ReadMonth: Format([dateread],"yyyymm")
This will give you your customer's name, what the meter reading was, and a number that represents the year and month, for instance for todays date it would be 200707

Under your criteria for ReadMonth you want to put this:

Between IIf(Format(Date(),"mm")="01",Format(Date(),"yyyymm ")-89,Format(Date(),"yyyymm")-1) And Format(Date(),"yyyymm")

I've tested this and it should work (this actually helped me work out some stuff with my own databases ;) ) Here's how it works, the way Between works is like this:
Between [First Number] And [Second Number]

Then in order to calculate the first number we first off make sure that it isn't january, if it is the first number is the numerical version of todays date minus 89, if it isn't January then it's only minus one. the IIf statement determines this for you:

IIf([condition], [if condition is true], [if condition is false])

the final bit just gets todays numerical value for this month, like I said earlier for today it would be 200707

So, here's how it goes with some actual data, say it's January 2008 the first thing it does is it sees if it's January, it is so it does the numerical value 200801, subtracts 89 from it which should be 200712 and then it calculates the current months value, which is 200801 then it enters both numbers in the between, so it comes up with all numbers between 200712 and 200801. This should get all your records in December 2007 and January 2008.

This should work as long as you run it during the month you're calculating, the Date() is always your computer's date. Anyway, hope that isn't too overwhelming, let me know if you have any questions.

[sumhungl0]

this is very interesting. i read through everything and ill give it a shot come brake time. thank you very much for the help and teaching me a bit. ill give it a shot shortly and ill post again. thanks for your help....

[Boxcar74]

Not 100% sure what you are looking for

Do you want the results like this:

Example: Columns Headings
Customer, JAN, FEB , MAR, etc.....

With the monthly totals on the grid for each month.

Like :
Joe S , 100202, 102020, 192837, etc...

If so, a simple Crosstab Query will do this.
The Wizard will walk you though it.
But since you are new I'll ad some info.

1. in the queries section press new, select Crosstab Query Wizard.
2. Select the table you want and the Fields for rows. Like Customer.
3. Select Columns heading. Date
4. NEXT tab Select MONTH
5. Then pick the data you want in the grid, Meter reading, I think have to sum them. It should affect the total.

I hope this helps.

If this is how you want it and need to add field from the customer info table that can be easily done in the design view.

Try it, and I'll check back.

-- Boxcar

[Boxcar74]

Also since your DB isn't extremely complicted you might want to look at templates for examples.

Microsoft has some at:
http://office.microsoft.com/en-us/te...3&CTT=6&av=ZAC

But there are plenty out there.

I'm sure they done have one to fit your needs but you probably find one to serve as a guide.

Especially for the reports/invoices, if you are going ot have calculations in them.

Hope this helps!

-- Boxcar

[sumhungl0]

ares
i dont think im getting the format date thingy right. says "data type mismatch in criteria expression". this is what i have in criteria under readmonth:

Between IIf(Format(Date(),"mm")="01",Format(Date(),"yyyymm")-89,Format(Date(),"yyyymm")-1) And Format(Date(),"yyyymm")

know what that means?

boxcar

Do you want the results like this:

Example: Columns Headings
Customer, JAN, FEB , MAR, etc.....

With the monthly totals on the grid for each month.

Like :
Joe S , 100202, 102020, 192837, etc...

that would work perfect. but would i need to put in a new month column in monthly, or 12 yearly or can a column be created automaticly when putting in the meter readings?

[sumhungl0]

i did get the date format yyyymm to work by just typing yyyymm into the format box of column readmonth in my table called "monthly meter readings". that part worked perfect like you said. date shows up as 200701.

[Boxcar74]

The 12 Monthly Columns are automatically created in the Crosstab Query Wizard. They list as JAN, FEB, MAR, etc......


With the formats yyyymm you can use "yyyy/mm" or "mm/yyyyy"
How ever you want it formated.

The Crosstab automatically does it as "mmm" Ex: Jan, Feb, etc... you can change the format however you want i.e yyymm in the SQL text but is kind of a pain.

In the SQL view It is display as:
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

So it you go this route you and want to change the format you need to manually edit Each month there.
EX: PIVOT Format([OPEN_Date],"yyyy/mm") In ("2007/01","2007/02" etc...

Also
"mmm/yyyyy" displays as Jan/2007 and so on.
"mmmm" displays as January
and "mmmm/yyyyy" as January/2007
I think you got the point.

I'd stick with JAN, FEB....

Anyway Good Luck !!

-Boxcar

[sumhungl0]

i need to think back to what data i need from this db and figure the best way to setup my tables. i dont know how i can do my meter readings table having months keep going. i would have a new column for every month. or i could have a column for all 70 something consumers. i cant figure out which would be best. any advise on this?

[Boxcar74]

I think you tables are fine.

I would run a crosstab query off the table with
consumer name / date / meter reading

Having "Consumer name" as the Row Heading, "Date" as the Column heading and "meter reading" as the value (use sum), it will just give you the Customer total.

Then run a query off of that to find the monthly differance.
IT should be something like: Expr1: [query1].[JAN]-[query1].[FEB]= Differance. But that is a crude example.

I'll get back to you on that one I want to check the format to use.

BTW -- How many months are we talking about?

-- Boxcar

[Ares6881]

He was comparing the current month to last month I believe, anyway, sorry for my absence, work and all.

Not sure why the criteria didn't work for you, what it sounds like to me is that for some reason the data that you have in that column may somehow be incompatible, but you already said that it displays properly. Let me know what your tables look like and I'll see if I can't figure out what's going on.