Error Calculation - Need Help!!

[ccflyer]

Hi everyone,

What I wan't to do seems very hard, and am wondering if anyone would be able to help out.

Here is what I am trying to do:
First of all, so you somewhat understand my field headers and table, I work for an oil company. I have a form that is based off a table and some of the fields include "Date", "Well Name", "Stock", "Sales", and "Oil". In my table, data is entered for each well every day.

Here is an example of my table:

Date          Well Name   Stock   Sales   Oil
08/15/2005    Well1        140      44     25
08/16/2005    Well1        120      35     15
08/15/2005    Well2        150      49     22
08/16/2005    Well2        115      40     30

What I want to do is create a text box on my form and I want it to do a calculation to see if the data is correctly entered. Here is the formula that I want to use in this text box:

If  ([Stock] - [StockFromPreviousDay] + [Sales]) = ([Oil])
    {
        Print "OK"
    }
Else
   {
        Print "Error"
   }

I just can't figure out how to get the Stock from the previous day to work with. I have been stuck on this problem for about a week now, so any help would be gratefully accepted.

Thanks,
Chris

 

[RuralGuy]

Referring to a Field in the Previous Record or Next Record

 

[ccflyer]

Will that work for me?

On my form, the user will be looking at the new record they are entering, and I will need to find the record from the same well on the previous day. But records in the table could be mixed up and not sorted by well and date.

In other words, the record I want to get information from, might not be the record directly before the currently opened record.

-Chris

 

[RuralGuy]

You answered your own question. Sort by Well and date.

 

[ccflyer]

For some reason, my table won't let me sort by well AND date at the same time, it either does one or the other.

-Chris

 

[RuralGuy]

You should be using a query not a table directly. Just make sure the Well field is to the left of the Date field.

 

[ccflyer]

Yep, you were right, it did work when I put the Well field to the left of the Date field. So why do you say that I should be using a query instead of a table directly?

-Chris

 

[ScottGem]

Hi everyone,
Here is an example of my table:

Date          Well Name   Stock   Sales   Oil
08/15/2005    Well1        140      44     25
08/16/2005    Well1        120      35     15
08/15/2005    Well2        150      49     22
08/16/2005    Well2        115      40     30

What I want to do is create a text box on my form and I want it to do a calculation to see if the data is correctly entered.
I just can't figure out how to get the Stock from the previous day to work with. I have been stuck on this problem for about a week now, so any help would be gratefully accepted.
Thanks,
Chris

I think you are using the wrong approach here. The proper way to do an Inventory database, which is what it looks like you are doing here, is to base it on a transactions table. Each movement of product should be a record in the transactions table. Something like this:

tblTransactions
TransactionID (PK Autonumber)
TransDate (note: Date is a Reserved word in Access and shouldn't be used as a Field or object name)
Quantity
TransType (Outgoing(Sales), Incoming(Production))

From there you CALCULATE stock by adding the Incoming and subtracting the outgoing. You can then produce an end of day report that lists calculated stocks and compare that with whatever actual totals you have.

 

[RuralGuy]

Queries are far more powerful than tables directly.

 

[ccflyer]

If I was going to try to use the way shown on the ACC2000 webpage (link in earlier post by RuralGuy), would my control source of my text box look something like this (Assuming I'm first going to get the previous "Oil" value):

*Note: My form's name is "Daily"

=PrevRecVal([Daily],"Date",[Date],"Oil")

To Scott: That might be a good idea, but I think that would be A LOT of work. My table has over 40,000 records and about 20 fields (I only listed above the ones required to help with my quesiton)

To RuralGuy: Thanks, I'll try to use queries more often from now on.

-Chris

 

[ScottGem]

To Scott: That might be a good idea, but I think that would be A LOT of work. My table has over 40,000 records and about 20 fields (I only listed above the ones required to help with my quesiton)
-Chris

This isn't an issue of the amount of work, this is an issue of best practices design. You can search the web for Inventory Database Design to confirm this is the correct practice. If you are storing calculated values (and you are) you are violating normalization rules.

In a properly designed database work is generally reduced, not increased. While it may take you some work to design your database properly, it will pay dividends in the long run.

 

[ccflyer]

I don't think I'm storing any calculated values, or at least none of my fields are calculated, unless I am misunderstanding what you mean when you say 'calculated'.

I'm sure that it really would be better in the long run, and I'd give it a try if I had time, but I'm a Senior in H.S. and School and my last day of work is Friday of this week because School starts next week.

I'm just getting a little nervous that I might not get this problem done in time, but it REALLY needs to get completed by Friday.

-Chris

 

[ScottGem]

All the more reason why you should be doing this right. Since you don't have the time to do it right, you should be explaining that to your bosses.

 

[ccflyer]

OK, I'll research it a bit and then talk to my boss, and see if I can somehow get it changed over, but for now can you help me solve my original question so as it gets changed over, the users can still have a working database?

Your help would be greatly appreciated.

-Chris

 

[ScottGem]

Sorry, but the answer is to solve the design problem, not to jury rig.