Help Please! Date calculations in queries moved to reports.

[Suzanne Wyatt]

Ok You all have been a great help in the past. Challenge.
I have a query that calculates dates at 1 month, 3 months 6 months, 1 year and 2 years using the dateAdd function based on Discharge Date. Can I build a union query to join these fields into one column and if so how, and if not, why?
What I want is to be able to find Intervals of a specific date.
Example: Jan 3, 2004, generated on 1 month, 3 month, 6 month, 1 year and 2 year fields. I want to retrieve all Jan 3 2004, from each field as one group Interval.
Current query is based on Client table's discharge date:
Here is the query:
SELECT tblClient.DischargeDate, DateAdd("m",1,[DischargeDate]) AS [1month Interval], DateAdd("m",3,[DischargeDate]) AS [3month Interval], DateAdd("m",6,[DischargeDate]) AS [6month Interval], DateAdd("yyyy",1,[DischargeDate]) AS [1year Interval], DateAdd("yyyy",2,[DischargeDate]) AS [2year Interval]
FROM tblClient;
How would I retrieve these?
Discharge date:varies. Date to retrieve at 1,3,6 months and 1 and 2 years Current Date
Display as one group current date with those in 1 month column first, then 3 month column, then 6 month column, then 1 year, etc.
Can it be done?

 

[lyners]

Suzanne, I'm kinda confused at what you are trying to do, but I'll take
a crack at it....



You want to write a query that looks at 5 calculated fields and pull the
fields with a certain query date that you are looking for.



I created something that looks like this:



SELECT tblClient.DischargeDate, DateAdd("m",1,[DischargeDate]) AS
[1month Interval], DateAdd("m",3,[DischargeDate]) AS [3month Interval],
DateAdd("m",6,[DischargeDate]) AS [6month Interval],
DateAdd("yyyy",1,[DischargeDate]) AS [1year Interval],
DateAdd("yyyy",2,[DischargeDate]) AS [2year Interval]

FROM tblClient

WHERE (((DateAdd("m",1,[DischargeDate]))=[Query Date])) OR
(((DateAdd("m",3,[DischargeDate]))=[Query Date])) OR
(((DateAdd("m",6,[DischargeDate]))=[Query Date])) OR
(((DateAdd("yyyy",1,[DischargeDate]))=[Query Date])) OR
(((DateAdd("yyyy",2,[DischargeDate]))=[Query Date])) OR
(((tblClient.DischargeDate)=[Query Date]));



Try it out.



Why do you want to put all the dates into 1 column? What are you trying
to accomplish by doing that?

 

[Dale Fye]

Suzane, I think I would do this differently. This is much easier to do this way because you only end up with one CheckDate column to query. Rather than having to check multiple columns. Additionally, if you want to add a checkpoint, all you have to do is add a record to the checkpoints table.

Create another table tblCheckPoints
(CheckPoint - Text, Interval - Text, Quantity - int, SortOrder - int)
'1 month', 'm', 1, 1
'3 months', 'm', 3, 2
'6 months', 'm', 6, 3
'1 year', 'yyyy', 1, 4
'2 years', 'yyyy', 2, 5

Create a cartesian join (no lines between the tables) between this table and your Clients table: Then select your clients name and discharge date from the clients table, the Checkpoint and SortOrder columns from tblCheckPoints, and then create a computed column that computes the checkpoint date. Then add a criteria that restricts the result set to only those records where the DischargeDate is not Null. Then, you can sort by CheckPoint data and SortOrder to get the list of people for a single date, ordered by their distance from their discharge date.

Additionally, once you have your CheckDate column computed, you can create criteria for the CheckDate column (like = Date() or Between two dates.

SELECT tblClient.ClientName
, tblClient.DischargeDate
, tblCheckPoints.CheckPoint
, DateAdd([tblCheckPoints].[interval],[tblCheckPoints].[Quantity],[DischargeDate]) AS CheckDate
FROM tblClient, tblCheckPoints
WHERE tblClient.DischargeDate Is Not Null
ORDER BY DateAdd([tblCheckPoints].[interval],[tblCheckPoints].[Quantity],[DischargeDate]), tblCheckPoints.SortOrder;

If you want a copy of my sample database reply to the newsgroup and I'll send it to you.

--
HTH

Dale Fye


Ok You all have been a great help in the past. Challenge.
I have a query that calculates dates at 1 month, 3 months 6 months, 1 year and 2 years using the dateAdd function based on Discharge Date. Can I build a union query to join these fields into one column and if so how, and if not, why?
What I want is to be able to find Intervals of a specific date.
Example: Jan 3, 2004, generated on 1 month, 3 month, 6 month, 1 year and 2 year fields. I want to retrieve all Jan 3 2004, from each field as one group Interval.
Current query is based on Client table's discharge date:
Here is the query:
SELECT tblClient.DischargeDate, DateAdd("m",1,[DischargeDate]) AS [1month Interval], DateAdd("m",3,[DischargeDate]) AS [3month Interval], DateAdd("m",6,[DischargeDate]) AS [6month Interval], DateAdd("yyyy",1,[DischargeDate]) AS [1year Interval], DateAdd("yyyy",2,[DischargeDate]) AS [2year Interval]
FROM tblClient;
How would I retrieve these?
Discharge date:varies. Date to retrieve at 1,3,6 months and 1 and 2 years Current Date
Display as one group current date with those in 1 month column first, then 3 month column, then 6 month column, then 1 year, etc.
Can it be done?

 

[lyners]

Good Idea Dale, I was trying to do it in 1 big swoop, your way would be
more report friendly.



Lyners