Summing in A Query

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

0
J
6/21/2007 6:41:53 PM
access 16762 articles. 3 followers. Follow

7 Replies
583 Views

Similar Articles

[PageSpeed] 12

Post your table  and field names with their datatypes.   A sample of data 
would help.
-- 
KARL DEWEY
Build a little - Test a little


"J. Trucking" wrote:

> 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
> 
> 
0
Utf
6/21/2007 9:32:00 PM
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

0
J
6/21/2007 11:48:47 PM
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.

-- 
KARL DEWEY
Build a little - Test a little


"J. Trucking" wrote:

> 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
> 
> 
0
Utf
6/22/2007 4:17:01 AM
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.

0
J
6/22/2007 1:29:31 PM
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

0
J
6/22/2007 4:36:45 PM
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



0
J
6/22/2007 11:40:08 PM


0
J
6/25/2007 8:24:16 PM
Reply:

Similar Artilces:

Query help 11-28-07
I posted about part of this yesterday and got some great advice, but I just found out there are other issues that no one told me about. Here's the problem: I'm trying to create a query that will calculate how much someone has contributed year-to-date to their supplemental health care plan. Basically the employee agrees to contribute an amount of their choosing. This amount is then divided out so that the individual pays a certain portion of that out of each bi-weekly paycheck. We also need to stop calculating a running total for those employees who terminate, and instead c...

RPC over HTTP/S on Exchange 2003
I have been configuring my single server with exchange to use RPC over https I have followed the instructions in MS guide and another simplified guide at http://www.petri.co.il/configure_rpc_over_https_on_a_single_server.htm Server spec is: Server 2003 standard SP1, Exchange 2003 SP1, XP client SP2 with outlook 2003 sp2 The bottom line is that when testing from the WAN, the outlook client will not connect and say that the exchange server is unavailable. I have a lot of experience configuring rpc over http/s with sbs2003 but this is the first time for server 2003 standard. I have outlook ...

The Sum from 1 worksheet cell to another worksheet cell
the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula qwerty: To sum the value on Sheet1, cell A10 with the cell value Sheet2, cell B20, enter =Sheet1!A10 + Sheet2!B20 (or you can enter '=' sign and click on A10, then enter the plus sign and click on B20) jeff >-----Original Message----- >the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula >. > ...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

CRM Queries
Hi, I have a few queries that one of our customers has put to us. Can someone let me know their thoughts. Our customer wants to know whether it is possible to have something where within CRM you can view others users calendars? They would like to view it like you would in MS Outlook, the customer is going to be using the CRM Browser Client not Outlook for Sales. Also, within MS CRM I arn't sure whether this is a fault but I have managed to create 2 identical accounts, same name, contact numbers and account number. Is this what I should expect surely its something that needs fixing? D...

Address query
I would like to create a spreedsheet that will check street adresses entered in an Excel spreadsheet against a master street index and then produce a new result in a new column. As an example if Post Street is in the master strret index then the result in the new column is Zone 4. If Birchwood Drive is in the master street index then the result in the new coloum is Zone 3. Any suggestions? Thanks, -- Bob On Sat, 12 Nov 2005 12:06:01 -0800, Bob <Bob@discussions.microsoft.com> wrote: >I would like to create a spreedsheet that will check street adresses entered >in an Exc...

Summing in A Query
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 mon...

Query fields
Is it possible to write a criteria where the value of an empty field is "0.00"? Background: I have three queries with different customer account groups. Not every salesperson has customer accounts in every accountgroup - so, he will not shown up in that query. But he has accounts included in another query. Now, I would like to get a sum of commission earned by each salesman calculated from all three queries together. Since the salesman has no record in one query the total sum of that specific salesman is not shown. Any idea how to solve that problem? Thanks Klaus On Wed, 29 A...

QBF/Query on report
Hi folks, I have numerous reports that are based on different queries. What I am looking for is a way that if a user wants to pull a certain report, that when this report is selected from a form, the user has the option based on different fields to drill down the information being returned in the report. For example, one report would give details of all invoices to be paid (considered incomplete). I want to give the option to the user to select the from and to dates that the invoice was received for example. Or again, the user may want to search for all unpaid invoices that belong t...

Simple Access counting queries
Hi, hoping someone can help a relative newbie with a pretty simple query. My database (Access 2007) has three tables: Customers Products Purchases (many-to-one links to both of the other tables, this is basically a linking table) I have two simple queries I'd like to get out of this database, but I'm a bit stuck on how to construct the SQL. Any direction you can give me would be helpful. 1. List of all customers who have purchased 2 or more products (or 3 or more products, or 4+, etc.) 2. List of all customers who have purchased both Product A and Product B (or A, B, and C, or B an...

exchange 2003 query.
Dear support team. I have installed windows 2003 server with exchange 2003 server. I have configured active directoty with the name called as domain_abc.com When i am creating users in my active directory, it is asking to create exchange mailbox. After creating the mail box i have modified the users email address in active directory. (e:g nilesh@domain_abc.com to nilesh@home.com I have another domain in my lan as home.com when i send mail from my mail idm mail should go as nilesh@home.com I am using third party pop connector .There i have mentioned the domain name domain_abc.com. ...

query ar remedy
I am trying to use the parameters to query ar remedy. When i run th query i get a error message Driver parameter missing. then it stops Any help is appreciated. I am using the AR odbc driver -- Message posted from http://www.ExcelForum.com I am also experiencing the same issue when running a parameter query using the AR ODBC driver. The parameter query returns results perfectly when run in MsQuery, but when the query attempts to return those same results to Excel, the Driver]parameter missing error is received. I have not been able to find anything online regarding this specific problem ...

Query to hide duplicate records
I recall that this used to easy in previous versions (Unique values only ??), but in 2007, I can't get this to work at all. I have a table with our companie's job numbers in it. The job numbers show up multiple times because of different phases of the project: ProjNum ProjDescription 3077 Univ. of Vermont/UC/LEED 3077 Univ. of Vermont/University Commons: Building Fee 3077 Univ. of Vermont/University Commons: Excess Professiona... I need to jut have a single listing of each project number, otherwise, I get repeated records in the query that looks at this information (which ...

"Expr 1014" if field of query ?
In attempting to troubleshoot some problems in a query, I noticed that three of the fields (design mode of Access 2000) showed "Expr 1014", "Expr 1015","Expr 1016". Data exists in the fields when I execute the query. What does this mean? Correction: three of the fields (Run mode of Access 2000) BobC wrote: > In attempting to troubleshoot some problems in a query, I noticed that > three of the fields (design mode of Access 2000) showed "Expr 1014", > "Expr 1015","Expr 1016". Data exists in the fields when I execute t...

How to create an "and" rule in Query Based Distribution Groups
Hi, With Exchange 2003 Query Based Distribution groups, is it possible to create an "and" rule? ie, all users who are based in "London" "and" have the first name "John"? Thanks, Curtis. -- Please reply to news group only. Thank you. Sure. (&(attribute1=blah)(attribute2=blah)) http://msdn.microsoft.com/library/en-us/adsi/adsi/search_filter_syntax.asp?frame=true -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "Curtis Fray" <xxx@xxx.com> wrote in message news:OjVc...

Problem with vba code to export query result in excel
Hi, I have a access report that exports to excel with click of a button after choosing parameters. This works well. However I have to modify couple of fields to utilize formula in the export module. I am not sure how to do this. I am writing the above code which seems to cause problem. I appreciate any help to resolve this issue. Thanks. Code: If lngColumn = 12 Then xlc.Offset(0, lngColumn).Value = =([UnitPrice]*[OriginalShippedQty])/1000 End If It seems the fields UnitPrice and OrigianalShippedQty are not being recognized here Jack wrote: >Hi, >I have a acces...

SUM help please
I have created a sheet which is tracking 3 items per client and a number of clients so I have set up a SUM function the only way I know how which is a long a1+a4+a7, etc Its works fine, but if I want to add a new client and 3 new rows, ALL of the + after are now off. Is there a better way to create a formula that will track say every 3rd cell in a column but then let you lengthen or shorten the column? Thanks!! -- ExcelNoob123 In searching her I have figured out it is the DSUM function that I ma needing. I have tried a number of times - seems simple enough but I cant get it to work ...

Return values that sum to a known value
I have a list of data and would like to know if there is a formula that would return any items from that list that sum to a known value. Have a look at this thread for something similar: http://www.microsoft.com/office/community/en-us/default.mspx?pg=7&cat=&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.excel.worksheet.functions&fltr= Regards, Tom "lmattern" wrote: > I have a list of data and would like to know if there is a formula that would > return any items from that list that sum to a known value. ...

How can I time long running queries?
Hello all, Is it possible to time long running select queries? I was looking at creating some code to do this....something like: 1. Store current time in a time variable 2. Try to programmatically open a query ("open" meaning just like manually opening a select query from MS Access to view data) using "Application.CurrentDb.QueryDefs("Query3").OpenRecordset dbOpenTable". 3. Check the current time and compare it to the start time saved at the beginning But, I get "Run-time error '3219': Invalid operation." when I run "...OpenRecord...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

Report on 3 Queries by Date
Hi I have 3 queries which I would like to join into one based on the servicedate/taskdate so that for any given date I can see the totals of each type of task we log for work. I would have joined these using a simple query however my issue is that not everyday I log an event in any one table (Each query based on only one table). Query1 ServiceDate CleanTimeTotal QATimeTotal ESTTimeTotal Query2 ServiceDate RepairTimeTotal Query3 TaskDate TaskTimeTotal Now I suspect I need to rename TaskDate to ServiceDate in Query3 above which I am okay to do but have no idea how to set up the...

Sum Times
I have four fields on a form to show time. I want a seprate "Total" field to add the time between the first two fields and then add the time between the second two fields. Like this: In LunchOut LunchIn Out Total 6:00am 12:00pm 12:30pm 4:30pm 10 hours The first four fields are stored as medium times. Can someone let me know how to do this? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200707/1 "ladybug via AccessMonster.com&quo...

Sum only positive values
Dear all, In my table the values can be positive or negative. I'm trying to do a consult to sum only the positive values... How can I do it? Thanks in advance! Andr=E9. Add the field to the query once more, remove the show flag & enter the condition ie SELECT Sum(MyFieldd) AS TheSum From MyTable Where MyField >0 HhH Pieter <gatarossi@ig.com.br> wrote in message news:1191407027.588326.298520@o80g2000hse.googlegroups.com... Dear all, In my table the values can be positive or negative. I'm trying to do a consult to sum only the positive values... How can I do i...

ODBC database query
I am working with an Access database to keep track of everyone's jobs at work. I have Excel files with macros that pull information from the database and display it in a report form. My problem is that I seem to be running out of space within the macros. I get to the database using an ODBC link. Once I filter the data and complete the macro, the filtered data displays perfectly, but if I save and close the file, then open it again, the macro contains errors. When I open the macro and look at the coding, it looks like it cuts off at a certain point as if I had filled my character quot...

Report Query in RMS
Is there somewhere that I could get some information as to how the reports in RMS are composed. When you look at the various reports they all seem fairly simple in there basic structure. Are the any examples of how these reports are composed. The //--- Columns ---// iformation seems to be the same in most reports, same as the //--- Title Rows ---//. Setting up the flitters and the TablesQueried I need to understand. I have the schema for 1.2. How do I better understand the following or what do I need to learn. I wish I was in college again and had all the time in the world. //--- Repo...