Summing in A Query

[J. Trucking]

Hello,

I have a database which fuel records are stored in. The data is
stored in two tables. The first records the daily logs that operators
use each time they fuel up. It stores their name, the key they used
(keylock fuel system - it's ancient) the unit number of the equipment
using the fuel, and the amount of fuel they took. The second table
stores the month end information retrieved from the key lock print
out. It keeps a running total of the amount of fuel taken with each
key, and the operator using that key.

We have problems making sure all of the fuel is accounted for each
month so we like doing a comparison of how much each operator recorded
for the month vs. what the print out says. You would be surpsrised at
the differences. Anyways, I am beginning to build a query which would
report the required information. I started out by building a form to
which the user can enter in the dates they want to search between (I
would have rather just had them enter in the month and year but I
couldnt figure out how to search on that). I used a "between"
statement on the query and it works quite well. This is where I get
stuck. I have a couple of questions so I will write them
individually:

1. I would now like the query to look through the records between the
specified dates and summarize the amount each employee recorded. For
example, John Doe may have filled up nine times during the month. I
dont care about the individual times, just his total for the month.
This data is in one table.

2. In the other table (the month end keylock print out), I need to be
able to calculate the difference between the total recorded for each
key in the specified month, versus the total the month before. The
keylock system simply has a rolling meter so it's always the total
amount ever pumped. For example, at the end of May, Key A4 could read
1000 Litres. At the end of June, if it reads 1400 Litres, I know that
400 Litres was used in June. The used inputs this information in a
form and the date is reference by the field "month" and "year".

I will then use a report to calculate the difference between the sum
of the operator's log information for the month, and the month end
print out. Does anyone have any ideas on how to accomplish this?

Thanks in advance,

John

 

[KARL DEWEY]

Post your table and field names with their datatypes. A sample of data
would help.

 

[J. Trucking]

Here are the tables/data types:

The Frist table is call "MonthlyKeylock" with the following field
names (data types in brackets)

ID (Autonumber)
Month (Text)
Year (Number)
A1 to A10 (Number) - for each month, there are 10 keys which record
the number of liters of fuel taken for 10 different people

A1USER to A10USER (Text) - for each month, there are 10 users of the
keys.

On the second table, called "DailyDieselLog"

ID (Autonumber)
Date (Date/Time)
UnitNumber (Number)
Operator (Text)
DieselType (text)
Amount (Number)
Key (Text)
FuelCategory (Text)

I think the link of these two tables would be in "A#USER" and
"Operator" as they will be using the same employee reference combo
box. Or it could be in "A#" or "A#USER" and "Key"

I dont have any data but I can give a quick example. John Doe filled
up with fuel on the following dates with respective information:

Date:May 1, 2007
Unit Number: 177
Operator: John Doe
Diesel Type: Clear Diesel
Amount 200 Litres
Key: A2
Fuel Category: Diesel

Date:May 5, 2007
Unit Number: 177
Operator: John Doe
Diesel Type: Clear Diesel
Amount 100 Litres
Key: A2
Fuel Category: Diesel

So if that is the only two times that John Doe fueld up, I should go
out to the pumps and be able to collect the following (as well as the
other keys/operators)

Key A2 reads 1000 Liters at the end of May. I will input this into
the table and if all is well, it should look back at the month of
April and see that key A2 had 700 Liters at the end of the month.
This would match what John Doe has put in for the month of May.

I hope this is what you were looking for Karl. Thanks for helping me.

John

 

[KARL DEWEY]

Change your MonthlyKeylock to look like this.
ID KeyDate Key User Reading
1 5/5/2007 1 Sam 100
Key is a text field to match that in the DailyDieselLog.
Add a new record for each fueling.

DailyDieselLog ---
ID FuelDate UnitNumber Operator DieselType Amount Key FuelCategory
1 5/5/2007 177 Sam 250 1

I work on some queries tomorrow.

 

[J. Trucking]

Thanks for the help Karl,

I'm not sure if I can add that date function to the Monthly Key Lock
as it is onlty recorded at the end of the month. That number is
simply a counter that is recorded at the end of the month. However,
we do look at the key that is associated with it - this will match the
key used on the daily log sheet.

Joe.

 

[KARL DEWEY]

Your second table has DieselType & FuelCategory.
Do you have more than one pump? There is nothing in the first table to
separate fuels.
How can one counter record multiple types of fuel?

 

[J. Trucking]

My apologies. We have two different methods of pumping diesel. We
have two sets of equipment - "off road" and "on road". "On Road"
equipment uses "Clear Diesel" and "Off Road" equipment uses "Dyed
Diesel". Our "On Road" equipment fills up at our key lock station in
our own yard. This is what I will be tracking with this query. THe
"Off Road" equipment fills up at a third party card lock station where
the tracking is much more sophisticated and we wont need to keep on
track of it as much. However, the second table will be a data entry
point for both "On Road" and "Off Road" equipment. "DieselType" will
be where the user enters either "Clear Diesel" or "Dyed Diesel". The
"FuelCtegory" field is where the user will input 'Keylock" or
"Cardlock".

The first table is where I track my own keylock pumping system. I'm
sorry I didnt indicate this sooner.

John

 

[KARL DEWEY]

Post a sample of data from the MonthlyKeylock as you did from the
DailyDieselLog.

 

[J. Trucking]

Hi Karl,

Here is a sample of what we pull for data for the first table (monthly
key lock). I have shown two months of data (two records)

Month: April
Year: 2007
A1: 10165
A2: 8956
A3: 4220
A4: 7334
A5: 4230
A6: 1124
A7: 9802
A8: 1410
A9: 630
A10: 1000

Month: May
Year: 2007
A1:11000
A2: 9200
A3: 4300
A4: 7895
A5: 5123
A6: 1200
A7: 9920
A8: 1625
A9: 1200
A10: 1104

We pull these numbers at the end of each month. The number we record
is the aggregate total for that individual key. For example, A10, the
fuel used for the month of May is 1104-1000 Litres = 104 Litres used.
I hope this is what you are looking for. Thanks again Karl. I
appreciate the help.

John