Query between query fields

[Sherry N.]

Hello,
I have these fields: Branch, Date, OldestDate and Received. I need to sum
what was Received between Date and OldestDate sorted by Branch. Appreciate
any help.

 

[KARL DEWEY]

Try this --
SELECT Branch, [Date], OldestDate, Sum([Received]) AS Received_Total
FROM YourTable
GROUP BY Branch, [Date], OldestDate;

 

[Jeff Boyce]

Sherry

If your fieldname is truly "Date", be aware that Access treats this as a
reserved word. What YOU mean by "Date" and what Access thinks it means may
not match... At a minimum, consider renaming this field to something more
descriptive of its contents.

If your table has a field named "OldestDate", is there a chance that this
value gets changed over time? If so, your table structure may benefit from
further normalization.

Is "Received" another date/time field, or a Yes/No field, or ...?

An example of the kinds of data you have in this table would help folks
offer specific suggestions...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Sherry N.]

Branch is a text field, Date is a date field that changes everyday,
OldestDate is a date field that may change or be the same as the previous
record and Received is a number that changes or may be the same.

I need to total what was received between Date and Oldestdate.

 

[Daryl S]

Sherry -

This will sum all records with matching Branch and OldestDate fields. I am
assuming that [Date] is the later of the two dates, so we are summing from
OldestDate to Date. If the [Date] field is the earlier of the two, then
switch the >= to <= in this SQL:

SELECT Branch, OldestDate, Sum([Received]) AS Received_Total
FROM YourTable
WHERE [Date] >= OldestDate
GROUP BY Branch, OldestDate;