Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-02-2019, 11:46 PM   #1
MushroomKing
Newly Registered User
 
MushroomKing's Avatar
 
Join Date: Jun 2018
Location: The Netherlands
Posts: 86
Thanks: 55
Thanked 1 Time in 1 Post
MushroomKing is on a distinguished road
Create more complicated queries in SQL instead of design view

Hey everyone! I have kind of a silly question:

Im counting some values in a date range.
I would like to sum the values afterwards. But I cant seem to do this in 1 single query. It becomes too "complicated". So what I always do, I make a new query of the current query and sum it there.


My question is, is this the right approach? Its just my way...?


I have this query for example and maybe its possibe in the SQL code to do this?


Code:
SELECT Count(lines.orders) AS CountOfLines, lines.STATUS, Format([SHIPBYDATE],"dd\/mm\/yyyy") AS shipdate
FROM lines
GROUP BY lines.STATUS, Format([orderdate],"dd\/mm\/yyyy")
HAVING (((Format([orderdate],"dd\/mm\/yyyy")) Between Nz([Forms]![main_form]![startdate]) And Nz([Forms]![main_form]![enddate])));

So what i ultimately try to do:

Pick a date range, and give me the amount of lines.
But now its doing a count, and show me the total for every date...


Im sorry if this is a strange one. Let me know. Thanks in advance!

__________________
Humor, also has it's fun sides...
MushroomKing is offline   Reply With Quote
Old 06-03-2019, 12:13 AM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,461
Thanks: 106
Thanked 2,537 Times in 2,330 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Create more complicated queries in SQL instead of design view

Use Sum(Orders) to get total value
Note that as your query only has one table you can omit the Lines.

If you omit the grouping, this could be done in one query e.g.

Code:
SELECT Count(orders) AS CountOfLines, Sum(orders) AS SumOfLines
FROM lines
WHERE (((Format([orderdate],"dd\/mm\/yyyy")) Between Nz([Forms]![main_form]![startdate]) And Nz([Forms]![main_form]![enddate])));
BTW Normally with Nz you need to specify an alternative if null e.g. current date

Code:
SELECT Count(orders) AS CountOfLines, Sum(orders) AS SumOfLines
FROM lines
WHERE (((Format([orderdate],"dd\/mm\/yyyy")) Between Nz([Forms]![main_form]![startdate], Date()) And Nz([Forms]![main_form]![enddate],Date())));
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 06-03-2019 at 12:30 AM. Reason: Added code
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
MushroomKing (06-03-2019)
Old 06-03-2019, 12:32 AM   #3
MushroomKing
Newly Registered User
 
MushroomKing's Avatar
 
Join Date: Jun 2018
Location: The Netherlands
Posts: 86
Thanks: 55
Thanked 1 Time in 1 Post
MushroomKing is on a distinguished road
Re: Create more complicated queries in SQL instead of design view

You're an absolute legend!

Very clear. Thanks!!

__________________
Humor, also has it's fun sides...
MushroomKing is offline   Reply With Quote
Old 06-03-2019, 03:24 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,461
Thanks: 106
Thanked 2,537 Times in 2,330 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Create more complicated queries in SQL instead of design view

You're welcome and thanks for the comment.

Also I have a utility that you may find useful for swopping between query design and SQL SQL to VBA and back again

__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Reply

Tags
count , date range , sql , sum

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Unable to view object properties in form design view (Access 2010) Groundrush Forms 3 05-16-2014 05:54 AM
Lock or Disable Design View for Queries oblivion86 Queries 1 04-15-2013 01:57 PM
Help Please!: complicated design RainX Reports 3 01-19-2007 11:36 AM
!!??? All my forms are blank under form view - but fine under Design view!!! RickDB Forms 2 07-13-2006 07:30 AM
Cannot create or view existing reports in design view TessB General 6 03-05-2004 06:05 PM




All times are GMT -8. The time now is 01:27 AM.


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

Featured Forum post


Sponsored Links


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