Create more complicated queries in SQL instead of design view (1 Viewer)

MushroomKing

Registered User.
Local time
Today, 04:45
Joined
Jun 7, 2018
Messages
93
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!
 

isladogs

CID Moderator
Staff member
Local time
Today, 12:45
Joined
Jan 14, 2017
Messages
13,403
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())));
 
Last edited:

MushroomKing

Registered User.
Local time
Today, 04:45
Joined
Jun 7, 2018
Messages
93
You're an absolute legend!

Very clear. Thanks!! :)
 

isladogs

CID Moderator
Staff member
Local time
Today, 12:45
Joined
Jan 14, 2017
Messages
13,403
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
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom