UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Date Calculation, Access 2016    
 
   
OCM
post Oct 31 2019, 11:10 AM
Post#1



Posts: 180
Joined: 12-September 02
From: Eastern, USA


Greetings,

Given the following two fields ‘LetterDate’ & ‘Status’ (dropdown) field with values like open, closed pending etc. I’m trying to find out as of today how many cases remained open since the letter was sent (LetterDate) with the status = open for:

1. 6 months
2. 7-12 months
3. 12- 24 months
4. 25+ months

TIA

Regards,
Go to the top of the page
 
theDBguy
post Oct 31 2019, 12:11 PM
Post#2


UA Moderator
Posts: 76,822
Joined: 19-June 07
From: SunnySandyEggo


Hi. First, you'll need a query showing all the dates for every records where status = open. Then, you can use this query in another query to calculate the time duration. Or you can combine the first two queries into one and then use another query to count each group.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Oct 31 2019, 12:31 PM
Post#3


UA Admin
Posts: 36,172
Joined: 20-June 02
From: Newcastle, WA


You want to get a count of ONLY "Open" status items. You want separate counts for each of four different time frames. Correct?

You mention a "dropdown" field. That probably means something like this, right?

Attached File  lookupfield.png ( 35.65K )Number of downloads: 2


There are probably a few different ways to do this, but I'd probably do it with a query like this. It's a union query.

SQL
SELECT "Less than 6 Months" AS TimeFrame, T.Status, Count(T.ID) AS CountOfID, 1 AS SortOrder
FROM Table1 T
WHERE T.LetterDate>=DateAdd("m",-6,Date())
GROUP BY T.Status
HAVING T.Status="open"
UNION
SELECT "7 - 12 Months" AS TimeFrame, T.Status, Count(T.ID) AS CountOfID ,2 AS SortOrder
FROM Table1 T
WHERE T.LetterDate BETWEEN DateAdd("m",-12,Date()) and DateAdd("m",-7,Date())
GROUP BY T.Status
HAVING T.Status="open"
UNION
SELECT "12 - 24 Months" AS TimeFrame, T.Status, Count(T.ID) AS CountOfID ,3 AS SortOrder
FROM Table1 T
WHERE T.LetterDate BETWEEN DateAdd("m",-24,Date()) and DateAdd("m",-12,Date())
GROUP BY T.Status
HAVING T.Status="open"
UNION
SELECT "25+ Months" AS TimeFrame, T.Status, Count(T.ID) AS CountOfID ,4 AS SortOrder
FROM Table1 T
WHERE T.LetterDate <= DateAdd("m",-25,Date())
GROUP BY T.Status
HAVING T.Status="open"
ORDER BY SortOrder

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
OCM
post Oct 31 2019, 03:34 PM
Post#4



Posts: 180
Joined: 12-September 02
From: Eastern, USA


Hi,

Thank you for the reply post. In my query I filtered status = open and used George’s syntax for as a criteria for the LetterDate as follows:

6 months:
CODE
>=DateAdd("m",-6,Date())

7-12 months:
CODE
BETWEEN DateAdd("m",-12,Date()) and DateAdd("m",-7,Date())

12-24 months:
CODE
BETWEEN DateAdd("m",-24,Date()) and DateAdd("m",-12,Date())

25 + months :
CODE
<= DateAdd("m",-25,Date())

I got result for couple of them, and blanks for the others.

Question: with status = open, I’ve only 50 records that I can manually validate the result. is the following logic correct ?
6 months, LetterDate should be between: 4/1/19 – 4/30/19
7-12 months, LetterDate should be between: 10/1/18 – 3/31/19 etc. correct?

I also noticed the 12-month record could be counted twice:
1. 7-12 months
2. 12-24 months

TIA,

Regards,
Go to the top of the page
 
GroverParkGeorge
post Oct 31 2019, 03:41 PM
Post#5


UA Admin
Posts: 36,172
Joined: 20-June 02
From: Newcastle, WA


I think I made a logic error there, didn't I? It counts the same month either way, the one 12 months back. You'll need to correct my mistake by checking BETWEEN 7 and 12 and between 13 and 24 months prior.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
projecttoday
post Oct 31 2019, 05:43 PM
Post#6


UtterAccess VIP
Posts: 11,253
Joined: 10-February 04
From: South Charleston, WV


A table-driven solution is best. So your table would be

rank / low month / hi month

1 / 0 / 6
2 / 7 / 12
3 / 12 / 24
4 / 25 / 99999

Join that to the data in the first query where status is open using the values in the table to determine your "BETWEEN". I think you can join on a date comparison. Do a count query on the ranks.

If you don't want to use a table create an Iif column for the rank in your query. Do a count query on the ranks.

--------------------
Robert Crouser
Go to the top of the page
 
OCM
post Nov 1 2019, 10:45 AM
Post#7



Posts: 180
Joined: 12-September 02
From: Eastern, USA


Thanks both, I modified my syntax and used what is suggested here and it's working.

Many thanks!

Regards,
Go to the top of the page
 
projecttoday
post Nov 1 2019, 11:52 AM
Post#8


UtterAccess VIP
Posts: 11,253
Joined: 10-February 04
From: South Charleston, WV


I bet you didn't use the table approach. I have attached a sample. It requires simple 2 queries. The one that produces the result is qryCompare.
Attached File(s)
Attached File  MonthsTabledb.zip ( 26.08K )Number of downloads: 7
 

--------------------
Robert Crouser
Go to the top of the page
 
RJD
post Nov 1 2019, 07:57 PM
Post#9


UtterAccess VIP
Posts: 10,145
Joined: 25-October 10
From: Gulf South USA


Hi: Pardon me for jumping in after it looks like this is over, but you can do this without a table or group numbers by using some IIf statements in a Totals query...

CODE
SELECT Status,
Sum(IIf([LetterDate]>=DateAdd("m",-6,Date()) And [LetterDate]<=Date(),1,0)) AS 00_06,
Sum(IIf([LetterDate]>=DateAdd("m",-12,Date()) And [LetterDate]<DateAdd("m",-6,Date()),1,0)) AS GT_06_12,
Sum(IIf([LetterDate]>=DateAdd("m",-24,Date()) And [LetterDate]<DateAdd("m",-12,Date()),1,0)) AS GT_12_24,
Sum(IIf([LetterDate]<DateAdd("m",-24,Date()),1,0)) AS GT_24_XX
FROM tblMyRecords
WHERE Status = "Open"
GROUP BY Status;

You may want to tweak how you make the dividing points between date span groupings.

Just a thought ... in case you are still looking at this ...

HTH
Joe
Attached File(s)
Attached File  DateGroupCounts.zip ( 22.19K )Number of downloads: 4
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    8th December 2019 - 07:41 AM