Create Output Query numbers in brackets

[Number11]

Hello,

So i need to have a query output the total figures of orders by age brackets as per the below.

Number < week old

Number >1 week but < 1 month old

Number >1 month but < 3 months old

Number >3 months but < 6 months old

Number > 6 months

whats the best way to do this please?

 

[Gasman]

Use IIF() and DateAdd()?

 

[cheekybuddha]

Use IIF() and DateAdd()?

along with SUM()

 

[cheekybuddha]

Just to get you started:

SELECT
  SUM(IIf(DateOrdered < DateAdd("ww", 1, Date), 1, 0)) AS LessThanOneWeek,
  SUM(IIf(DateOrdered >= DateAdd("ww", 1, Date) AND DateOrdered < DateAdd("m", 1, Date), 1, 0)) AS GTOneWeekLTOneMonth,
  ...
FROM Orders

 

[plog]

Number >1 week but < 1 month old

Your logic is not complete, you haven't categorized the cases that exactly meet your endpoints. What happens when it is exactly 1 week? Or exactly 1 month? Etc. In each of those ranges you either need to start with >= or end with <=.

Further, 'month' is an ill defined unit if time. In days it's 28 sometimes, infrequently 29, possibly 30 and often 31. It's fine to use labels like 'More than a month', 'More than 6 months', but in the logic when exactly do those move from one group to the next?

 

[DHookom]

I would not use nested Iif()s. Consider creating a small table to store the ranges with a Title, Min, and Max.

 

[cheekybuddha]

I would not use nested Iif()s.

The IIf()'s aren't nested but, I agree, this technique is probably no the best, since you end up with a single row where the field names contain 'values'.

A table of buckets is preferable, but a similar method to simulate it could be something like:

SELECT
  t.Period,
  Count(*)
FROM (
  SELECT
    Switch(DateDiff("d", DateOrdered, Date) < 7, "1. Less Than One Week", DateDiff("m", DateOrdered, Date) <= 1, "2. Between One Week And One Month", DateDiff("m", DateOrdered, Date) <= 3, "3. Between One Month And Three Months", DateDiff("m", DateOrdered, Date) <= 6, "4. Between One Three And Six Months", True, "5. Greater than Six Months") AS Period
  FROM Orders
  WHERE OrderDate <= Date
) t
GROUP BY
  t.Period
ORDER BY
  t.Period
;

 

[DHookom]

If a table isn’t used, I would encapsulate the logic in a small used-defined function so you could add comments and modify WHEN the ranges are updated.

A final answer probably requires more specifications and an example.

 

[The_Doc_Man]

There are various ways to do this.

IF you are trying to do this in an SQL statement, see...

Switch Function - Microsoft Support

Use the Switch function in Acess to evaluate a list of expressions.

support.microsoft.com

Be aware that the SWITCH function is very clunky and the expressions you need will be lengthy.

You did say "QUERY". However, IF this were to be done in the context of VBA, see...

Select Case statement (VBA)

Office VBA reference topic

learn.microsoft.com

It IS possible but not ALWAYS most efficient for an SQL statement to call a VBA function, so the hybrid case also exists in which you have a VBA routine that is called from SQL, evaluates your input, and returns some sort of code that you can use for a GROUP BY statement.

DHookom's answer also works if you have a short table of ranges with values like this:

AGEMIN	AGEMAX	AGECODE	AGENAME
0	7	1	"< 1 week"
8	30	2	"> 1 week to < 1 month"
31	92	3	"> 1 month to < 3 month"
93	183	4	"> 3 month to < 6 month"
184	1000000	5	"> 6 month"

Put whatever you want as the name of the range. Then in your query you compute age in days and do a non-standard JOIN such as

SELECT (Date() - M.AgeBase) AS AgeDays, C.AgeCode, C.AgeName, etc.
FROM MainTable AS M, AgeCodeTable AS C
WHERE AgeDays BETWEEN C.AgeMin AND C.AgeMax etc.

WARNING: Without the WHERE clause to constrain what comes back, this becomes a Cartesian query and returns entirely too many records, some of which are flat-out wrong. What you really want might resemble this method. "Bells and whistles" for this approach might include either a GROUP BY of the AgeCode or an ORDER BY of the AgeDays computed field. Since AgeDays is computed, I don't think it needs a prefix. But the other fields will since you have what is actually an improper JOIN.

I am not 100% sure but you COULD try a "real" JOIN like this:

SELECT (Date() - M.AgeBase) AS AgeDays, C.AgeCode, C.AgeName, etc.
FROM MainTable AS M INNER JOIN AgeCodeTable AS C
ON ( AgeDays BETWEEN C.AgeMin AND C.AgeMax )
WHERE ... etc.

The only reference I found for that type of "ON ... BETWEEN" JOIN says the parentheses around the BETWEEN clause are mandatory because otherwise Access won't recognize the JOIN condition. It will flag it as a syntax error.

 

[cheekybuddha]

A final answer probably requires more specifications and an example.

 

[arnelgp]

see query1 and the final query, query2.

 

[Number11]

The IIf()'s aren't nested but, I agree, this technique is probably no the best, since you end up with a single row where the field names contain 'values'.

A table of buckets is preferable, but a similar method to simulate it could be something like:

SELECT
  t.Period,
  Count(*)
FROM (
  SELECT
    Switch(DateDiff("d", DateOrdered, Date) < 7, "1. Less Than One Week", DateDiff("m", DateOrdered, Date) <= 1, "2. Between One Week And One Month", DateDiff("m", DateOrdered, Date) <= 3, "3. Between One Month And Three Months", DateDiff("m", DateOrdered, Date) <= 6, "4. Between One Three And Six Months", True, "5. Greater than Six Months") AS Period
  FROM Orders
  WHERE OrderDate <= Date
) t
GROUP BY
  t.Period
ORDER BY
  t.Period
;

Thanks for the post so i tried this and it didnt work..
SELECT
t.Period,
Count(*)
FROM (
SELECT
Switch(DateDiff("d", DateOrdered, Date) < 7, "1. Less Than One Week", DateDiff("m", DateOrdered, Date) <= 1, "2. Between One Week And One Month", DateDiff("m", DateOrdered, Date) <= 3, "3. Between One Month And Three Months", DateDiff("m", DateOrdered, Date) <= 6, "4. Between One Three And Six Months", True, "5. Greater than Six Months") AS Period
FROM Orders
WHERE DateOrdered <= Date
) t
GROUP BY
t.Period
ORDER BY
t.Period
;

get this prompt

then if click ok?

 

[Gasman]

Date() ?

 

[cheekybuddha]

Yes, it probably needs the brackets after Date, as @Gasman points out, to indicate that it is a function and not a parameter.

Is this any better?

SELECT
  t.Period,
  Count(*)
FROM (
  SELECT
    Switch(DateDiff("d", DateOrdered, Date()) < 7, "1. Less Than One Week", DateDiff("m", DateOrdered, Date()) <= 1, "2. Between One Week And One Month", DateDiff("m", DateOrdered, Date()) <= 3, "3. Between One Month And Three Months", DateDiff("m", DateOrdered, Date()) <= 6, "4. Between Three And Six Months", True, "5. Greater than Six Months") AS Period
  FROM Orders
  WHERE OrderDate <= Date()
) t
GROUP BY
  t.Period
ORDER BY
  t.Period
;

 

[Gasman]

Need it for the WHERE as well?

 

[cheekybuddha]

Need it for the WHERE as well?

Thank you!

(Fixed)

 

[Number11]

Yes, it probably needs the brackets after Date, as @Gasman points out, to indicate that it is a function and not a parameter.

Is this any better?

SELECT
  t.Period,
  Count(*)
FROM (
  SELECT
    Switch(DateDiff("d", DateOrdered, Date()) < 7, "1. Less Than One Week", DateDiff("m", DateOrdered, Date()) <= 1, "2. Between One Week And One Month", DateDiff("m", DateOrdered, Date()) <= 3, "3. Between One Month And Three Months", DateDiff("m", DateOrdered, Date()) <= 6, "4. Between Three And Six Months", True, "5. Greater than Six Months") AS Period
  FROM Orders
  WHERE OrderDate <= Date()
) t
GROUP BY
  t.Period
ORDER BY
  t.Period
;

yes that works

Period	Expr1001
2. Between One Week And One Month
4. Between Three And Six Months
5. Greater than Six Months

Query3

[td]

1​

[/td]​

[td]

1​

[/td]​

[td]

2​

[/td]​

 

[DHookom]

I wouldn’t accept any solution with complex, hardcoded Switch(). When the ranges change, you would have to modify the expression.