subtracting numbers in the same field.

Randyr · Jul 15, 2003

Hi all

I have a database that we need to do a calculation in a query that takes the data entered today and subtracts it from the data entered yesterday.

This is data that is entered from a meter that needs to be subtracted from the previous days reading to give us the actual flow for the period. then this number needs to be recorded so we can get a months worth of data at a time.

I have been scratching my head on this for quite awhile but now I need to get this up and running.

Thanks

dcx693 · Jul 15, 2003

Tell us a bit more about how the data is stored in the database. Do you just need the current date's calculation or all the calculations going back? Should be relatively simple with a self-join query, but it will depend on your data stucture.

Randyr · Jul 15, 2003

This is a simple database using 3 tables the data that we need to do the calculations on is stored in a single table called dailyreading. We have been exporting this to an excel spreadsheet to do this single calculation but figured it should be able to be done in access.

we need the query to show the date meter reading for that date
meter reading for the day before and the actual reading of the two subtracted.

we currently have queries setup that give us all the other needed data except for this one data point.

Randy

dcx693 · Jul 15, 2003

Cool. So how is the data set up in that single table? Is it as simple as having two fields with one being the meter reading, the other the date?

Randyr · Jul 15, 2003

Yep, granted other data is in the table but the only ones we are concerned with is the date and flow.

Randy

dcx693 · Jul 15, 2003

Are dates ever skipped? Must the difference in flow be calculated for the precending calendar day or the date of the last reading?

For example, if today is July 15, you'd subtract out yesterday's data. But let's say you're considering July 14th, do you subtract out July 13th or do you skip weekends and need to subtract out July 11th?

And is there only ever one reading per day?

Randyr · Jul 15, 2003

Dates are not skipped, however on rare occasions no data will be entered in the meter reading.

Randy

dcx693 · Jul 15, 2003

OK, I've attached a sample database with 2 solutions depending on your needs. I think the qryFlowDifference2 will serve your needs best though. I've added an autonumber field to the table that feeds into the query. It's an Order field that just keeps track of the order of how things are entered into the database. In case dates are skipped, it won't be a problem as it could be with the qryFlowDifference orignal query.

Check them out and see if they might solve your problem.

Randyr · Jul 15, 2003

Thanks alot I will give them a try when I get to the office tomorrow.

Randy

dcx693 · Jan 8, 2004

For those interested. Here's an Access 97 version.

subtracting numbers in the same field.

Randyr

New member

dcx693

Registered User.

Randyr

New member

dcx693

Registered User.

Randyr

New member

dcx693

Registered User.

Randyr

New member

dcx693

Registered User.

Attachments

Randyr

New member

dcx693

Registered User.

Attachments

Similar threads

Users who are viewing this thread