query to sum fields over a date range.

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
gbnortonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

why?

shouldn't it be   End date + 1
0
gbnortonAuthor Commented:
I want to exclude the last entry from the results.
0
Rey Obrero (Capricorn1)Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PatHartmanCommented:
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
gbnortonAuthor Commented:
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
Rey Obrero (Capricorn1)Commented:
ok., i'll leave the two of you..
you seems to be not interested to respond in my comment.
0
gbnortonAuthor Commented:
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
PatHartmanCommented:
Rey, FYI - Between included dates at both ends of the range.
0
Rey Obrero (Capricorn1)Commented:
<Rey, FYI - Between included dates at both ends of the range. >

I know that long time ago.
0
PatHartmanCommented:
I thought so but your previous answer indicated that Between excludes the end date.  If I misunderstood it, I apologize.
0
gbnortonAuthor Commented:
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
Gustav BrockCIOCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gbnortonAuthor Commented:
Perfect!  Thank you!
Brooks
0
gbnortonAuthor Commented:
Perfect!  Thank you!
Brooks
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.