Hi Jeff,
I'll try and explain the whole story. The database is for fuel
records for a fleet of trucks. The fuel system is a "keylock" system
so when an operator goes to the pump, they put a key in a certain hole
(there are 30 keys and theirty holes), turn the key, and fuel up their
machine. The key is a security feature to prevent fuel theft. The
key is linked to a meter inside the pump which will run a total as to
how much fuel that key pumps. After each fueling, the operator will
go into the office and write down how much fuel he took, what key he
has, his name, and the date. For example, John Doe fueled up on
August 1, 2007 with key A1 and took 400 Litres of fuel.
The meters that total the amount used by each key are behind a locked
door on the pump itself. These are aggregate totals menaing they're
alway rising and never reset. For example, for me to know that John
pumped 400 Litres of fuel, I would have to know the meter reading
before and after the fueling that day. It, for example, would read
1200 litres before he fueled up, and 1600 litres after.
So here's how the operation works. The operators write down everytime
they fuel up. At the end of the month, we take this sheet and enter
it in the computer. We also go out (at the end of the month) and
record the meter readings from behind the locked pump door.
We currently do this in excel but we are trying to track an abundance
of other information (maintenance, production, parts, etc.). I have
only recently come to this business and found that this information
was stored (poorly) and was all over the server. I am trying to
consolidate it into a user friendly database rather than have a whole
bunch of spreadsheets. I personally like excel over access, but this
database needs to be user friendly because I wont do the data entry
(for some reason I've found that data entry personel seem to favour
the access interface over excel). As well, I like the benefit of "one
click" reports for the managers.
So back to the fuel part. The reason why we record the data from each
fill up and check it against the pump meters is that we have found
that fuel seems to go "missing". We use this check to ensure that
operators are taking an acceptable amount of fuel and that they are
writing down each time they take the fuel.
I store this information in two tables. The first is called
'tblDieselConsumption" and has the fields: IDDiesel (AutoNumber),
DateofPump (Date), UnitNumber (Text), Operator (text), Litres
(number), and Key (text). This information is stored for each record.
I have another table which stores the 30 meter readings every month.
It is called tblDieselKeylock. The fields are: IDKeylock
(AutoNumber), MonthNo (Text), YearNo (Text), and A1...C30
(Number...stores the numberic reading off of each meter on the pump -
A1-A10, B1-B10, C1-C10).
I only need to see the data at the end of each month. I guess this
could also be true for any previous month. Therefore, I created a
small form and am planning to use it to reference a query. The form
has two combo boxes: MonthToSearch, and YearToSearch. The form is
called frmDieselConsumptionSummary. Any user will say to themselves
"I want to see the fuel useage from last July". They will select
"July" and "2006" and hit OK. A report will be generated, for all the
keys, stating (for example):
Key: A1
Operator: John Doe
Total Diesel recorded By Operator for Month: 400 Litres
Total Diesel Recorded By Pump for Month: 410 Litres
This way I would know that there is a 10 Litre difference. Sometimes
there is a zero litre difference and other times there can be upwards
of a 400 litre difference so its important info to know.
I am trying to calculate everything through queries. I have three of
them. The first is qryDieselSummaryPrevious. The query is based on
tblDieselKeylock. I use this query to look at the Keylock Pump Meters
and find the values that the keylock had the month prior to the one
that the user is interested in (because I'm eventually gonna need to
do some subtraction). I accomplish this by using the combo boxes on
frmDieselConsumptionSummary and subtracting one from the month that
the user is interested in (and one from the year in the case of
January). So in this query, the field I use are MonthNo (with the
condition that the only omnth pulled equals that on the user input
form), YearNo (same condition as the month), and A1-C30. I then use
the query to pull that particular month's record and call each one
"PRE" through the use of an expression (ie) A1PRE, A2PRE, etc.
I have a second query called qryDieselSummaryDifference which is based
on tblDieselKeyLock and qryDieselSummaryPrevious. I have the fields
MonthNo and YearNo (with the same input conditions as the last query -
except the "minus one" condition for getting the previous months
data). I then have expressions for each key that find the difference
between the currents months pump reading and the previous months
reading. The expressions look like this: DIFFA1: [A1]-[A1PRE] where
A1 comes from tblDieselKeyLock and [A1PRE[ is from the
qryDieselSummaryPrevious. This all works out perfect.
By doing all of that, I now know the pump meter readings for any month
that I want. What I tried to do know is compare that to a monthly
summary of the individual records located in tblDieselConsumption. I
did this through another query (or so I thought). I made a query
called qryDieselSummaryOverall. I based it on tblDieselConsumption
and qryDieselSummaryDifference. I pulled the fields: DateofPump,
Operator, Key, and Liters from tblDieselConsumption. I worked the
DateofPump so that I only pulled the records based on the user input
from frmDieselConsumptionSummary. I thought I could link it to the
records from qryDieselSummaryDifference to get the corresponding
"DIFF.." record. What I did is listed above but basically I am trying
to call the "DIFF" record based on the Key showing in the Key field.
Like I said, I'm stuck. I understand that this is probably confusing
reading this but I'm not sure how else to store the data and make it
user friendly. I'd like to avoid using excel in this case just
because of the computer experience from the data entry person. I
tried the union query above but it gives me the error message that I
stated above. I thought about VBA and searched this forum on it but I
dont really know what to write in order to call a specific function.
Any help would be greatly appreciated at this point.
Thanks,
John
.