[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

query to sum fields over a date range.

Posted on 2014-08-29
15
Medium Priority
?
610 Views
Last Modified: 2014-09-02
I need a query that will sum the netwtadded and netwtremoved fields over a data range.  The query will be used for a report.

Use the equipment number 2-40-21-001.
Start date: 8/6/2014
End date: 8/14/2014

The query I’ve been using is: qry_rptEqDateRange

Note that I’m querying on the End date – 1.
I've attached the database.
Thanks,
Brooks
Chemical.zip
0
Comment
Question by:gbnorton
  • 6
  • 4
  • 3
  • +1
15 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40292807
<Note that I’m querying on the End date – 1.>

why?

shouldn't it be   End date + 1
0
 

Author Comment

by:gbnorton
ID: 40292837
I want to exclude the last entry from the results.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40292887
if your date range is 8/1/2014 and 8/28/2014,
your criteria will be

between 8/1/2014 and 8/27/2014

you will exclude all records with date of 8/28/2014


be more specific about your criteria



.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 40

Expert Comment

by:PatHartman
ID: 40292937
SomeDT Between startDT and EndDT INCLUDES dates at both ends.  So it is the same as
SomeDT >= StartDT and SomeDT <= EndDT

If you want to exclude the EndDT, then use
SomeDT >= StartDT and SomeDT < EndDT
OR
SomeDT Between startDT and EndDT - 1

I didn't look at your attachment.  I can't download them here.  Your problem could be that your date includes time and that changes your criteria.  You can use the DateValue() function to pull out only the date part of a date/time field.

DateValue(SomeDT) Between startDT and EndDT
0
 

Author Comment

by:gbnorton
ID: 40292949
Pat, I'll use the < as you described.  Simpler. The date range works fine.  It is the summing of the netwtadded and netwtremoved fields that I need.  Sum the netwtadded field for the records between start date and end date.  Thanks,]
Brooks
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40292962
ok., i'll leave the two of you..
you seems to be not interested to respond in my comment.
0
 

Author Comment

by:gbnorton
ID: 40293034
I apologize Rey.  That was not intended.  In my mind, the date range is not my problem.  It is the summing of the netwtadded and netwtremoved fields.  

The criteria should be all records from start date to < end date.
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40293070
Rey, FYI - Between included dates at both ends of the range.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40293090
<Rey, FYI - Between included dates at both ends of the range. >

I know that long time ago.
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 40293115
I thought so but your previous answer indicated that Between excludes the end date.  If I misunderstood it, I apologize.
0
 

Author Comment

by:gbnorton
ID: 40293317
In the database I uploaded, the table is Data.  Using the criteria:
Equipment: 2-40-21-001
Date Range: 8/6/2014 to 8/14/2014

The selected records are:
Date Used             Operator              Net Wt Added             Net Wt Removed            Notes
8/6/2104                0326                         15                                      0              
8/10/2014              0326                         25                                      0        

I need the query to sum the Net Wt Added records.  And sum the Net Wt Removed records.  In the end I'll subtract the removed from the added and divide by the number of days.

Current query:
SELECT data.DateUsed, data.Operator, data.EquipmentNumber, data.EquipmentDescription, data.ChemicalName, data.NetAddedWT, data.NetRemovedWT, data.Notes
FROM data
WHERE ((data.[DateUsed]) >= [Forms]![Switchboard]![cboStartDate]) And ((data.[DateUsed]) < [Forms]![Switchboard]![cboEndDate]) AND ((data.[EquipmentNumber])='2-40-21-001')
ORDER BY data.[DateUsed];

I hope this helps.
Thanks,
Brooks
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 40294700
This should do:

SELECT
    Min(data.DateUsed) AS FromDate,
    Max(data.DateUsed) AS ToDate,
    data.Operator,
    data.EquipmentNumber,
    data.EquipmentDescription,
    data.ChemicalName,
    Sum(data.NetAddedWT) AS TotalNetAddedWT,
    Sum(data.NetRemovedWT) AS TotalNetRemovedWT,
    ([TotalNetAddedWT]-[TotalNetRemovedWT])/DateDiff("d",
        DateValue([Forms]![Switchboard]![cboStartDate]),
        DateValue([Forms]![Switchboard]![cboEndDate])) AS AvgNetWT
FROM
    data
WHERE
    (((data.[DateUsed]) Between
        DateValue([Forms]![Switchboard]![cboStartDate])
        And
        DateValue([Forms]![Switchboard]![cboEndDate])+#12/30/1899 23:59:59#))
GROUP BY
    data.Operator,
    data.EquipmentNumber,
    data.EquipmentDescription,
    data.ChemicalName,
    data.[EquipmentNumber]
HAVING
    (((data.[EquipmentNumber])='2-40-21-001'));

/gustav
0
 

Author Comment

by:gbnorton
ID: 40298305
Perfect!  Thank you!
Brooks
0
 

Author Closing Comment

by:gbnorton
ID: 40298306
Perfect!  Thank you!
Brooks
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 40298356
You are welcome!

/gustav
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question