Calculation in query (1 Viewer)

eacollie

Registered User.
Local time
Yesterday, 23:33
Joined
May 14, 2011
Messages
159
I have a table that I want to query for a report but need to do some calculations in the query.

My table has:
Date (date)
Location (text)
Breakfast (numeric)
Details (text)

Essentially, if the field [Details] has something in it, that means this is a "special meal" and I need to count it separately for each date. I can't figure out how to do this in the query. Any help would be very appreciated!

Thanks
 

June7

AWF VIP
Local time
Yesterday, 22:33
Joined
Mar 9, 2014
Messages
5,424
Post the attempted query.


In aggregate query: Sum(IIf([Details] Is Null, Null, 1)) AS CountSpecial

Alternatively, do the calc in textbox on form or report: =Sum(IIf(IsNull([Details]), Null, 1))
 
Last edited:

plog

Banishment Pending
Local time
Today, 01:33
Joined
May 11, 2011
Messages
11,613
The best way to communicate what you want to occur in a query is to provide data. 2 sets: Show us what you are starting with and what you hope to end up.

A. Starting sample data from all tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Manually show us what data you expect returned by your query when you feed it the data in A.

Again, 2 sets of actual data--starting and ending data.
 

eacollie

Registered User.
Local time
Yesterday, 23:33
Joined
May 14, 2011
Messages
159
How do you want the data - in an attached file or just typed in this message?
 

eacollie

Registered User.
Local time
Yesterday, 23:33
Joined
May 14, 2011
Messages
159
Data from table:

Date Location Breakfast Details
7/15/2018 Location1 1 Gluten free
7/15/2018 Location1 1
7/15/2018 Location1 1
7/16/2018 Location 1 Gluten free
7/16/2018 Location1 1
7/16/2018 Location1 1

Format of Report:
Date Location Breakfast (regular) Breakfast (special)
7/15/2018 Location1 2 1
7/16/2018 Location1 2 1
 

June7

AWF VIP
Local time
Yesterday, 22:33
Joined
Mar 9, 2014
Messages
5,424
Did you try the calc suggested in post 2?
 

eacollie

Registered User.
Local time
Yesterday, 23:33
Joined
May 14, 2011
Messages
159
Thank you. I think that is giving a count of all the records, not by date.
 

June7

AWF VIP
Local time
Yesterday, 22:33
Joined
Mar 9, 2014
Messages
5,424
The calc must be done in an aggregate (GROUP BY) query that groups on Date field and possibly the Location as well or in a report using Sorting & Grouping features with calc in a group based on the Date field.

Date is a reserved word and really should not use reserved words as names of anything.
 

eacollie

Registered User.
Local time
Yesterday, 23:33
Joined
May 14, 2011
Messages
159
Thank you. I was attempting to do this in the query but can't seem to get it to work. I'll try the report grouping.
 

June7

AWF VIP
Local time
Yesterday, 22:33
Joined
Mar 9, 2014
Messages
5,424
If you show SQL of attempted query, we might be able to help.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:33
Joined
Feb 28, 2001
Messages
27,001

eacollie

Registered User.
Local time
Yesterday, 23:33
Joined
May 14, 2011
Messages
159
Thank you! I've attached a database with table and attempted query.
 

Attachments

  • Database7.zip
    12.9 KB · Views: 43

June7

AWF VIP
Local time
Yesterday, 22:33
Joined
Mar 9, 2014
Messages
5,424
You might want to modify the calc to sum for only regular breakfast as well:


SELECT tblTEMPMealCount.MealDate, tblTEMPMealCount.MealLocation, Sum(Breakfast) AS BreakfastAll, Sum(IIf([Details] Is Null,1,Null)) AS BreakfastRegular, Sum(IIf([Details] Is Null,Null,1)) AS BreakfastSpecial
FROM tblTEMPMealCount
GROUP BY tblTEMPMealCount.MealDate, tblTEMPMealCount.MealLocation;
 
Last edited:

Users who are viewing this thread

Top Bottom