Home Forum Contact

 Access World Forums Calculation in query
 Register FAQ Members List Social Groups Top Posters Search Today's Posts

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

 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 Re: Calculation in query How do you want the data - in an attached file or just typed in this message?
 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 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
 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 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.
 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 Re: Calculation in query Thank you. I think that is giving a count of all the records, not by date.
 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 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.
 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 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.
 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 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.
 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 Re: Calculation in query Thanks!...will post back in a bit.
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
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.

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
Re: Calculation in query

Thank you! I've attached a database with table and attempted query.
Attached Files
 Database7.zip (12.9 KB, 7 views)

 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 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.
 The Following User Says Thank You to June7 For This Useful Post: eacollie (08-12-2018)
 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 Re: Calculation in query Thank you! This is great!

 Thread Tools Display Modes Rate This Thread Linear Mode Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Access World     Access World News     Site Suggestions     Introduce Yourself     The Watercooler Microsoft Access Discussion     General     Tables     Queries     Forms     Reports     Macros     Modules & VBA     Theory and practice of database design     Access Web Microsoft Access Reference     Access FAQs     Code Repository     Sample Databases     Microsoft Access Tutorials     Microsoft Access User Groups Apps and Windows     SQL Server     Crystal Reports     Visual Basic     VB.NET     Word     Excel     Web Design and Development         ASP and ASP.NET         PHP & MySQL     Windows     Other Software     Hardware Questions and Answers Non-Access Issues     Politics & Current Events     Debates     Gaming     Sports, Health & Fitness     Gadgets     Small Business

 Similar Threads Thread Thread Starter Forum Replies Last Post Evenflow Queries 1 05-16-2005 03:53 AM Evenflow Queries 1 05-02-2005 03:51 AM ravynfaire Queries 1 02-16-2005 04:29 PM Jim W Queries 2 12-23-2003 01:56 AM 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