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

MushroomKing

Registered User.
Local time
Today, 09:48
Joined
Jun 7, 2018
Messages
100
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

MVP / VIP
Local time
Today, 16:48
Joined
Jan 14, 2017
Messages
18,186
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, 09:48
Joined
Jun 7, 2018
Messages
100
You're an absolute legend!

Very clear. Thanks!! :)
 

Users who are viewing this thread

Top Bottom