Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-12-2018, 08:58 AM   #1
eacollie
Newly Registered User
 
Join Date: May 2011
Posts: 152
Thanks: 23
Thanked 0 Times in 0 Posts
eacollie is on a distinguished road
Calculation in query

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

eacollie is offline   Reply With Quote
Old 08-12-2018, 09:24 AM   #2
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 808
Thanks: 0
Thanked 182 Times in 182 Posts
June7 will become famous soon enough
Re: Calculation in query

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))
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 08-12-2018 at 10:40 AM.
June7 is offline   Reply With Quote
Old 08-12-2018, 09:25 AM   #3
plog
AWF VIP
 
Join Date: May 2011
Posts: 8,937
Thanks: 10
Thanked 2,147 Times in 2,102 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Calculation in query

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.

plog is online now   Reply With Quote
Old 08-12-2018, 09:34 AM   #4
eacollie
Newly Registered User
 
Join Date: May 2011
Posts: 152
Thanks: 23
Thanked 0 Times in 0 Posts
eacollie is on a distinguished road
Re: Calculation in query

How do you want the data - in an attached file or just typed in this message?
eacollie is offline   Reply With Quote
Old 08-12-2018, 09:50 AM   #5
eacollie
Newly Registered User
 
Join Date: May 2011
Posts: 152
Thanks: 23
Thanked 0 Times in 0 Posts
eacollie is on a distinguished road
Re: Calculation in query

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
eacollie is offline   Reply With Quote
Old 08-12-2018, 10:41 AM   #6
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 808
Thanks: 0
Thanked 182 Times in 182 Posts
June7 will become famous soon enough
Re: Calculation in query

Did you try the calc suggested in post 2?
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
June7 is offline   Reply With Quote
Old 08-12-2018, 11:31 AM   #7
eacollie
Newly Registered User
 
Join Date: May 2011
Posts: 152
Thanks: 23
Thanked 0 Times in 0 Posts
eacollie is on a distinguished road
Re: Calculation in query

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

eacollie is offline   Reply With Quote
Old 08-12-2018, 11:42 AM   #8
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 808
Thanks: 0
Thanked 182 Times in 182 Posts
June7 will become famous soon enough
Re: Calculation in query

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.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
June7 is offline   Reply With Quote
Old 08-12-2018, 12:01 PM   #9
eacollie
Newly Registered User
 
Join Date: May 2011
Posts: 152
Thanks: 23
Thanked 0 Times in 0 Posts
eacollie is on a distinguished road
Re: Calculation in query

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.
eacollie is offline   Reply With Quote
Old 08-12-2018, 12:19 PM   #10
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 808
Thanks: 0
Thanked 182 Times in 182 Posts
June7 will become famous soon enough
Re: Calculation in query

If you show SQL of attempted query, we might be able to help.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
June7 is offline   Reply With Quote
Old 08-12-2018, 12:29 PM   #11
eacollie
Newly Registered User
 
Join Date: May 2011
Posts: 152
Thanks: 23
Thanked 0 Times in 0 Posts
eacollie is on a distinguished road
Re: Calculation in query

Thanks!...will post back in a bit.
eacollie is offline   Reply With Quote
Old 08-12-2018, 02:45 PM   #12
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 12,468
Thanks: 62
Thanked 1,174 Times in 1,074 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Calculation in query

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

Quote:
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/...ions-of-access
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-12-2018, 03:07 PM   #13
eacollie
Newly Registered User
 
Join Date: May 2011
Posts: 152
Thanks: 23
Thanked 0 Times in 0 Posts
eacollie is on a distinguished road
Re: Calculation in query

Thank you! I've attached a database with table and attempted query.
Attached Files
File Type: zip Database7.zip (12.9 KB, 7 views)
eacollie is offline   Reply With Quote
Old 08-12-2018, 04:00 PM   #14
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 808
Thanks: 0
Thanked 182 Times in 182 Posts
June7 will become famous soon enough
Re: Calculation in query

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;
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 08-12-2018 at 05:53 PM.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
eacollie (08-12-2018)
Old 08-12-2018, 04:13 PM   #15
eacollie
Newly Registered User
 
Join Date: May 2011
Posts: 152
Thanks: 23
Thanked 0 Times in 0 Posts
eacollie is on a distinguished road
Re: Calculation in query

Thank you! This is great!

eacollie is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Calculation Evenflow Queries 1 05-16-2005 03:53 AM
Query calculation Evenflow Queries 1 05-02-2005 03:51 AM
Calculation in Query - help please ravynfaire Queries 1 02-16-2005 04:29 PM
Calculation in Query Jim W Queries 2 12-23-2003 01:56 AM
Calculation in a Query bsdixon Queries 19 12-10-2003 04:31 AM




All times are GMT -8. The time now is 09:43 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World