Multiple Records By Date

[Vel.]

Hello,

I am trying to make a query which will dynamically show a different number
of records for each ClientID based on a date. I know that doesn't make much
sense, so let me try to explain more.

I have a query which outputs the following fields:

ClientID, RecallID, Timeframe, LastDate, StartDate, DateDue

ClientID is a uniquely identifying client ID (from tblClient)

There are 5 different RecallID values for each client; O, M, I, P, and F
(from a query which binds tblClient to tblRecallTypes)

TimeFrame is the length of time, in days, from when one recall is "due" to
the time the next will be due (from tblRecallTypes)

The LastDate field is a field which is populated with the most recent date
in tblRecall for which each ClientID has a RecallID based on a query, it is
null if a client has never had that type of recall
(For instance, someone might have three 'O's on three different dates,
LastDate is the most recent)

StartDate is the date the client entered treatment at our facility.

DateDue is the date the next item of Recall is due for that client, based
the value of LastDate+TimeFrame

Here's the thing. I want to only display those records where
LastDate+Timeframe<=Now() . This is something I am able to accomplish.
However, the more complicated part is, I want to continue to add the
timeframe to the LastDate over and over again for certain types of paperwork,
coming up with a new record and date due until I reach Now(). This report is
used to track outstanding paperwork and, on occasion, more than one of a
certain paperwork (RecallID) may be due. For instance

ClientID 12345, for RecallID "O", which has a timeframe of 30 days, has the
following record:

12345 ; O ; 30 ; 05/05/05 ; 01/19/05 ; 06/04/05

What I would prefer to have as an output would be

12345 ; O ; 30 ; 05/05/05 ; 01/19/05 ; 06/04/05
12345 ; O ; 30 ; 05/05/05 ; 01/19/05 ; 07/04/05
12345 ; O ; 30 ; 05/05/05 ; 01/19/05 ; 08/03/05

If I need to make a new query based on the previous one that is fine. I am
able to make multiple fields (IE DateDue1, DateDue2, DateDue3, etc.) by
multiplying the time frame, but that doesn't give me the functionality
necessary for making a report.

I hope this is clear, and if not, I will do my best to clarify if anyone has
any suggestions.

Thank You.

 

[peregenem]

the
following record:

12345 ; O ; 30 ; 05/05/05 ; 01/19/05 ; 06/04/05

What I would prefer to have as an output would be

12345 ; O ; 30 ; 05/05/05 ; 01/19/05 ; 06/04/05
12345 ; O ; 30 ; 05/05/05 ; 01/19/05 ; 07/04/05
12345 ; O ; 30 ; 05/05/05 ; 01/19/05 ; 08/03/05

If I need to make a new query based on the previous one that is fine.

Suggestion: create a Query object (VIEW) which returns the LastDate for
each (ClientID, RecallID) pair. You can then JOIN to this so that the
same LastDate appears for each (ClientID, RecallID) in your result set.

I had a few issues recreating your schema. You didn't say where
StartDate was modelled: you may have an 'episodes' table to enable
clients to have more than one StartDate but I've assumed one client one
StartDate aand modelled it in the 'clients' table accordingly. You
don't give a name for the DATETIME column in 'recalls' so I've used
recall_effective_date.

Also I had some confusion about dates: what you regard as the 'last'
date I see as being 'first'. Maybe the US date format is confusing me


CREATE TABLE tblRecallTypes (
RecallID CHAR(1) NOT NULL
PRIMARY KEY,
TimeFrame INTEGER NOT NULL,
CHECK (TimeFrame > 0)
)
;
INSERT INTO tblRecallTypes
VALUES ('O', 30)
;
INSERT INTO tblRecallTypes
VALUES ('M', 15)
;
INSERT INTO tblRecallTypes
VALUES ('I', 5)
;
INSERT INTO tblRecallTypes
VALUES ('P', 3)
;
INSERT INTO tblRecallTypes
VALUES ('F', 2)
;
CREATE TABLE tblClient (
ClientID CHAR(5) NOT NULL
PRIMARY KEY,
CHECK(ClientID LIKE '[0-9][0-9][0-9][0-9][0-9]'),
StartDate DATETIME
DEFAULT DATE() NOT NULL
)
;
CREATE TABLE tblRecall (
ClientID CHAR(5) NOT NULL
REFERENCES tblClient (ClientID)
ON DELETE CASCADE
ON UPDATE CASCADE,
RecallID CHAR(1) NOT NULL
REFERENCES tblRecallTypes (RecallID)
ON DELETE SET NULL
ON UPDATE CASCADE,
recall_effective_date DATETIME
DEFAULT DATE() NOT NULL,
PRIMARY KEY (ClientID, RecallID, recall_effective_date)
)
;
INSERT INTO tblClient
VALUES ('12345', #2005-01-19#)
;
INSERT INTO tblRecall
VALUES ('12345', 'O', #2005-05-05#)
;
INSERT INTO tblRecall
VALUES ('12345', 'O', #2005-06-04#)
;
INSERT INTO tblRecall
VALUES ('12345', 'O', #2005-07-04#)
;
CREATE VIEW RecallFirstEffectiveDates
AS
SELECT ClientID,
MIN(recall_effective_date) AS recall_effective_date,
RecallID
FROM tblRecall
GROUP BY ClientID, RecallID
;

And the solution query:

SELECT R1.ClientID, R1.RecallID, RT.TimeFrame,
RF.recall_effective_date AS LastDate,
C1.StartDate,
R1.recall_effective_date + RT.TimeFrame AS DueDate
FROM (
(
tblRecall AS R1 INNER JOIN tblClient AS C1
ON R1.ClientID = C1.ClientID
) INNER JOIN tblRecallTypes AS RT
ON R1.RecallID = RT.RecallID
) INNER JOIN RecallFirstEffectiveDates AS RF
ON R1.ClientID = RF.ClientID
AND R1.RecallID = RF.RecallID
WHERE RF.recall_effective_date + RT.TimeFrame <= NOW();

 

[Vel.]

Thanks for your help, but that isn't exactly what I was looking for.

To clarify a few questions you had;

RecallDate is the date used to determine the most recent effective recall.
StartDate is a field in tblClient, and each client has just one.

I am able to create a basic join query. The task I'm attempting, however,
is to generate mutliple records in a query based on a formula. Currently, I
can determine when the "next" item for tblRecall is due by using the
timeframe in tblRecallTypes combined with either the most recent date in
tblRecall (LastDate) or the date the client entered treatment (StartDate), if
they have no records in tblRecall yet. However, I want to know not only when
the next one is due, but also when the one after that and the one after that,
etc., is due, up until now.

For example, in my current query I get the following;

where DueOn =iif(Now()>iif(isnull([LastDate]), [StartDate],
[LastDate])+[TimeFrame], iif(isnull([LastDate]), [StartDate],
[LastDate])+[TimeFrame], Null)

[ClientID];[RecallType];[LastDate];[StartDate];[TimeFrame];[DueOn]

12345 ; O ; 30 ; 05/05/2005 ; 01/19/2005 ; 06/04/2005; 30; 07/04/2005

Instead, I would like to generate a new line based on DueOn until I reach
Now()... In code it would be something like:

DueOn = iif(Now()>iif(isnull([LastDate]), [StartDate], [LastDate])
X=1

While DueOn+TimeFrame <= Now()
DueOn = DueOn+TimeFrame*X
X = X + 1
Print [ClientID];[RecallType];[LastDate];[StartDate];[TimeFrame];[DueOn]
Loop


---

Now, I'm not exactly sure about the above code, as I don't due much
programming, but I hope that my multiple explanations will clear up what I'm
trying to accomplish with this query. I've found a workaround using multiple
fields and carriage return characters in my report, but that severely limits
the formatability of the report's detail section and is a far
less-than-elegant solution.

 

[peregenem]

RecallDate is the date used to determine the most recent effective recall.

I'm not sure I like that name: it sounds like the due date rather than
the effective date.

On a similar note, it is conventional for a table that models more than
one entity to be given a plural name, so 'Clients' would be more
appropriate. That prefixes describing physical implementation such as
'tbl' go against ISO data element naming standards is another story ...

I want to know not only when
the next one is due, but also when the one after that and the one after that,
etc., is due, up until now.

Your While...X=X+1..Loop shows you are thinking in terms of traditional
procedural programming. SQL requires a completely different approach
and a set-based mindset. So let's tweak the schema and create a new
auxiliary 'sequence' table (i.e. a table of counting numbers), a
standard SQL trick.

DROP VIEW RecallFirstEffectiveDates
;
DROP TABLE tblRecall
;
DROP TABLE tblClient
;
DROP TABLE tblRecallTypes
;
CREATE TABLE Sequence (seq INTEGER NOT NULL)
;
INSERT INTO Sequence VALUES (1)
;
INSERT INTO Sequence VALUES (2)
;
INSERT INTO Sequence VALUES (3)
;
INSERT INTO Sequence VALUES (4)
;
INSERT INTO Sequence VALUES (5)
;
INSERT INTO Sequence VALUES (6)
;
INSERT INTO Sequence VALUES (7)
;
INSERT INTO Sequence VALUES (8)
;
INSERT INTO Sequence VALUES (9)
;
INSERT INTO Sequence VALUES (10)
;
INSERT INTO Sequence VALUES (11)
;
INSERT INTO Sequence VALUES (12)
;
INSERT INTO Sequence VALUES (13)
;
INSERT INTO Sequence VALUES (14)
;
INSERT INTO Sequence VALUES (15)
;
INSERT INTO Sequence VALUES (16)
;
INSERT INTO Sequence VALUES (17)
;
INSERT INTO Sequence VALUES (18)
;
INSERT INTO Sequence VALUES (19)
;
INSERT INTO Sequence VALUES (20)
;
CREATE TABLE tblRecallTypes (
RecallID CHAR(1) NOT NULL
PRIMARY KEY,
TimeFrame INTEGER NOT NULL,
CHECK (TimeFrame > 0)
)
;
INSERT INTO tblRecallTypes
VALUES ('O', 30)
;
INSERT INTO tblRecallTypes
VALUES ('M', 15)
;
INSERT INTO tblRecallTypes
VALUES ('I', 5)
;
INSERT INTO tblRecallTypes
VALUES ('P', 3)
;
INSERT INTO tblRecallTypes
VALUES ('F', 2)
;
CREATE TABLE tblClient (
ClientID CHAR(5) NOT NULL
PRIMARY KEY,
CHECK(ClientID LIKE '[0-9][0-9][0-9][0-9][0-9]'),
StartDate DATETIME
DEFAULT DATE() NOT NULL
)
;
CREATE TABLE tblRecall (
ClientID CHAR(5) NOT NULL
REFERENCES tblClient (ClientID)
ON DELETE CASCADE
ON UPDATE CASCADE,
RecallID CHAR(1) NOT NULL
REFERENCES tblRecallTypes (RecallID)
ON DELETE SET NULL
ON UPDATE CASCADE,
RecallDate DATETIME
DEFAULT DATE() NOT NULL,
PRIMARY KEY (ClientID, RecallID, RecallDate)
)
;
INSERT INTO tblClient
VALUES ('12345', #2005-01-19#)
;
INSERT INTO tblRecall
VALUES ('12345', 'O', #2005-05-05#)
;
INSERT INTO tblRecall
VALUES ('12345', 'O', #2005-06-04#)
;
INSERT INTO tblRecall
VALUES ('12345', 'O', #2005-07-04#)
;

Because you are 'able to create a basic join query', I'll just give you
the bare bones:

SELECT tblRecall.ClientID, tblRecall.RecallID,
tblRecall.RecallDate AS recall_effective_date,
tblRecall.RecallDate + (tblRecallTypes.TimeFrame * [Sequence].seq) AS
recall_due_date
FROM tblRecall, tblRecallTypes, [Sequence]
WHERE tblRecall.RecallID = tblRecallTypes.RecallID
AND tblRecall.RecallDate + (tblRecallTypes.TimeFrame * [Sequence].seq)
<= NOW() ORDER BY tblRecall.RecallID, tblRecall.RecallDate;