Calculation in query

[eacollie]

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]

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))

 

[plog]

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]

How do you want the data - in an attached file or just typed in this message?

 

[eacollie]

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]

Did you try the calc suggested in post 2?

 

[eacollie]

Thank you. I think that is giving a count of all the records, not by date.

 

[June7]

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]

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]

If you show SQL of attempted query, we might be able to help.

 

[eacollie]

Thanks!...will post back in a bit.

 

[The_Doc_Man]

Side note not relevant to the problem on which you are getting help from others:

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

Don't use a variable with a name that matches a keyword or Access function. "Date" is one such word. Here is an article you can use to watch out for keywords.

https://support.microsoft.com/en-us...n-access-2002-and-in-later-versions-of-access

 

[eacollie]

Thank you! I've attached a database with table and attempted query.

 

[June7]

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;

 

[eacollie]

Thank you! This is great!