Using A Query To Group & Sum

[J. Trucking]

Hello,

I need some help. I have two tables. One which houses individual
fuel entries and another which houses the monthly recepit values
received from the fuel supplier. I will display some sample data
below:

Sample Individual data (each group of data is an individual record)

Date: Feb 1, 2007
Employee: Tom
Unit Number: 200
Fuel Key Number: A2
Fuel System: Keylock
Number of Liters: 127

Date:Feb 3, 2007
Employee: Joe
Unit Number 187
Fuel Key Number: A1
Fuel System: Keylock
Number of Liters: 145

Date: Feb 8, 2007
Employee: John
Unit Number: 100
Fuel Key Number: 3478
Fuel System: Cardlock
Number of Liters 346

I have 30 employees and two different types of fuel systems (Cardlock
and keylock in two different locations). Each time they fuel up, they
write down how much they take and it is recorded in a table by a data
entry person. At the end of the month, our supplier gives us a
monthly total (ie) Key A1 used 4000 Liters for the month of February
2007, and Key A2 used 3201 Liters, Cardlock 3479 took 2000 Liters. I
store this information in a different table (because there are a lot
of keys/Cards). The data looks like this:

Month: February
Year: 2007
A1: 4000
A2: 3201
Card 3478: 2000
.... All the way up to 30 keys/cards. I have another tab on this form
to which I assign an operator to each key/card. This information only
changes if someone quits, etc. So on the second tab, I have:

A1Operator: Joe
A2Operator: Tom
....All the way up to 30 different operators

In the individual entries (the first set of data), I can have up to
about 200 entries per month. What I need is a query which will
display a summary of the key/card useage for any month. For example,
I would like to know that Key A1 had a total of 4006 Liters taken
based on a summation of the individual records and that compares to
4000 Liters given by our supplier for a difference of 6 Liters. I
would like to group by each key/card. I would like to display the sum
only and not each individual record. I would like to show the key
number, employee using that key, the amount based on the individual
records, the amount based on the supplier, and the difference between
the two in a report. Can anyone help get me started. I am mostly
confused on how I sum the records and show only the sum and not the
individual records. As well, how i link the two tables in the same
query because the month end info from the supplier is in a different
table than the individual records. As well, I should mention that the
date format in the "Individual records" is mm/dd/yy and is simply
"January" and "2007" in the summation table. Any suggestions to get
me started? Any help would be greatly appreciated. Have a great
weekend.

Thanks,

John

 

[Bob Quintal]

Hi john.

Hello,

I need some help. I have two tables. One which houses
individual fuel entries and another which houses the monthly
recepit values received from the fuel supplier. I will
display some sample data below:

Sample Individual data (each group of data is an individual
record)

Date: Feb 1, 2007
Employee: Tom
Unit Number: 200
Fuel Key Number: A2
Fuel System: Keylock
Number of Liters: 127

Date:Feb 3, 2007
Employee: Joe
Unit Number 187
Fuel Key Number: A1
Fuel System: Keylock
Number of Liters: 145

Date: Feb 8, 2007
Employee: John
Unit Number: 100
Fuel Key Number: 3478
Fuel System: Cardlock
Number of Liters 346

I have 30 employees and two different types of fuel systems
(Cardlock and keylock in two different locations). Each time
they fuel up, they write down how much they take and it is
recorded in a table by a data entry person. At the end of the
month, our supplier gives us a monthly total (ie) Key A1 used
4000 Liters for the month of February 2007, and Key A2 used
3201 Liters, Cardlock 3479 took 2000 Liters. I store this
information in a different table (because there are a lot of
keys/Cards). The data looks like this:

Month: February
Year: 2007
A1: 4000
A2: 3201
Card 3478: 2000
... All the way up to 30 keys/cards. I have another tab on
this form to which I assign an operator to each key/card.
This information only changes if someone quits, etc. So on
the second tab, I have:

A1Operator: Joe
A2Operator: Tom
...All the way up to 30 different operators

In the individual entries (the first set of data), I can
have up to
about 200 entries per month. What I need is a query which
will display a summary of the key/card useage for any month.
For example, I would like to know that Key A1 had a total of
4006 Liters taken based on a summation of the individual
records and that compares to 4000 Liters given by our supplier
for a difference of 6 Liters. I would like to group by each
key/card. I would like to display the sum only and not each
individual record. I would like to show the key number,
employee using that key, the amount based on the individual
records, the amount based on the supplier, and the difference
between the two in a report. Can anyone help get me started.
I am mostly confused on how I sum the records and show only
the sum and not the individual records. As well, how i link
the two tables in the same query because the month end info
from the supplier is in a different table than the individual
records. As well, I should mention that the date format in
the "Individual records" is mm/dd/yy and is simply "January"
and "2007" in the summation table. Any suggestions to get me
started? Any help would be greatly appreciated. Have a great
weekend.

Thanks,

John

Start by building the summary query. Open the Query Designer,
and bring in the Individual_Data table.

Hopefully the date entry isn't actually called date, because
date is a reserved word in Access and we may have confusion
trying to perform calculations on it.

Type this in the field row of the first column in the grid:
dtl_Year: format([datefieldname],"yyyy")
In the next box type dtl_Month: format([datefieldname],"mmmm")
For the third column just double-click the keycard number, then
the Liters column for column 4
Set the sort to descending on the first 2 columns and ascending
for the third..

Now go up to the toolbar and click the Epsilon( looks like a
sideways M) This will add the row Total: to the query grid.
Select SUM in the fourth column from the drop-down choices,
leave the rest of the columns at Group By. Save the query. Give
it a good name like FuelLoadsSummaryByMonth.

Now we will make a second query to bring in the SupplierStatus
Open a new query, select the first query and the vendor table.
Click on the year name in one of the Fields list boxes and while
holding down the mouse button drag across to the year in the
second fields list. Repeat for month and keycard number.

Set the four fields from the query into the Fields row of the
grid. Bring down the liters field from the table. Save and test
that it's workuing before we continue.

Now we want to create a calculated field that shows the
difference
Diff: FuelLoadsSummaryByMonth.Liters - SupplierSummary.liters

save and test again.

If that works, add the following to the criteria row in the
first column [Year to analyse] and [Month to analyse] into the
second column.

That should get you started.

 

[J. Trucking]

Thanks for the help Bob,

I just have a couple of questions. I completed the first query and it
works great. When I get to the second query I run into a few
problems. In my second table, I have labels with all the key numbers
(A1,A2,A3...) and I simply write the total litres for that month
beside that laberl. Therefore, I dont really have a "Key Number" in
the second table. I am not sure how to create a relationship there.
In the second table, I also store the month as a text field, and the
year as a number field. I get a type mismatch, which I understand,
but I'm not sure how to make them match but still have the user keep
the ability to enter in the month and year as it is right now. Thanks
again for the help Bob.

John

 

[Bob Quintal]

Thanks for the help Bob,

I just have a couple of questions. I completed the first
query and it works great. When I get to the second query I
run into a few problems. In my second table, I have labels
with all the key numbers (A1,A2,A3...) and I simply write the
total litres for that month beside that laberl. Therefore, I
dont really have a "Key Number" in the second table. I am not
sure how to create a relationship there. In the second table,
I also store the month as a text field, and the year as a
number field. I get a type mismatch, which I understand, but
I'm not sure how to make them match but still have the user
keep the ability to enter in the month and year as it is right
now. Thanks again for the help Bob.

John

Easy things first: check the code I gave for the year of the first
query. If you store the year as a number in your secnd table,
replace the expression

dtl_Year: format([datefieldname],"yyyy")

With

dtl_Year: year(date([datefieldname])

Or change the type definition of year in your second table to a
text type, 4 characters

You really should not store the year as a number, even though it
contains just digits. There's a rule I learned years ago about "If
you will add, subtract, multiply or divide it, it's a number,
otherwise it's text"

As to the other issue, When I look at your operator record, that
should contain fields for Card, Operator, Date given, DateReturned
with a separate row for each operator.

The monthly summary should have a row for each card, year, month
and consumption,.

When you receive the summary, you run an append query that adds
each row from the operator table where dateReturned is NULL, and
the current month and year.

Put that into a continuous form to add the consumption.

This would be what the computer science profs call a normalized
database. It allows for easy adding/deleting/changing
operators/cards and also lets you do statistics over several months
for any individual card.

Q

 

[J. Trucking]

Hi Bob,

I am still a little confused on how I will link my "key" fields
between the two tables. I have two fuel systems in our organization
to which fuel is recorded. One is a "keylock" system and one is a
"cardlock" system. For the "individual record" table, I have the
columns: ID (AutoNumber), DatePumped (Date/Time), Unit (Number),
Operator (Text), DieselType (Text), Diesel (Number), Key (Text), and
FuelCategory (Text....this is a combo box that gives the user the
option of "Keylock" or "Cardlock". Each row represents a record for
each time a person fuels up. There will be approximately 100 records
per month.

Our keylock system is old so it has a meter for each key located
directly on the pump. At the end of the month, I go out and read the
meter for each key. It is a running total. For example, I will go
out at the end of March and the meter representing key A1 will read
4000. I will go out at the end of April and the same meter will read
4200. Therefore, I will know that 200 liters were pumped in April.
This is done for 30 keys (A1-A10, B1-B10, C1-C10) in the keylock
system. The "cardlock" system is for any of our equipment that is on
the road and cant be fueled at our shop. It is recorded by a third
party computer system and we get a summary each month. I really dont
need to compare the cardlock to the summary.... just the keylock.

The second table (the monthly summary for the keylock), I have the
following colums for each row: ID (AutoNumber), MonthNo (Text), YearNo
(Text), A1-C10 (Number...this is the number of liters read off the
meter each month for each key), A1USER-A10USER (Text...I have a second
tab on this form which records the registered user of each key. This
information rarely changes but I record it anyways).

I have tried to link the "Key" field in the first table to "A1-
C10" (all thirty keys) in the second field but I just cant seem to get
it to work. Any ideas? Thanks again Bob for all your help up to this
point.

John