Calculating in a report between seperate records

[Grant Meredith]

Hi all a friend has approached me with this problem and a
table. The first problem is that the table/database is
built wrong but that cannot be changed since the database
is a national one

TABLE
------------
Record UR Name Start_Date End_Date
-------- ---- -------- ------------ ------------
1 234 Jo 1/3/2002 14/3/2002
2 234 Jo 20/3/2002 23/3/2002
3 234 Jo 3/5/2003 7/3/2003
4 234 Jo 16/6/2003 23/6/2003

so all records here concern the same person, the problem
is on a report you must have a field that calculates the
difference in days between the end of one treatment to
the start of the next. Between say end of record 1 to the
start date of record 2. Are there any ACCESS gurus there
who can help?
I know how to easily calculate the no of days between the
start & end dates of a single record
=DateDiff("d", [Start_Date], [End_Date])
but not on a report between records. ACCESS is not my
point of focus

 

[Allen Browne]

Base the report on a query.

In query design view, type something like this into a fresh column in the
Field row. It returns the most recent end date for the same person (assuming
UR identifies the person), prior to the Start_Date. Because we are using a
2nd copy of the same table, we have to use a different name, so I've
referred to it as "Dupe":

( SELECT Max([End_Date]) As LastEndDate
FROM MyTable AS Dupe
WHERE (Dupe.UR = MyTable.UR)
AND (Dupe.End_Date < MyTable.Start_Date) )

That's called a subquery - a complete query statement within another query.
Once you have that working, you can do your DateDiff(). For example, you may
end up with something like this as the calculated field in your query:

Days: DateDiff("d", ( SELECT Max([End_Date]) As LastEndDate FROM MyTable AS
Dupe WHERE (Dupe.UR = MyTable.UR)
AND (Dupe.End_Date < MyTable.Start_Date) ) , [Start_Date)

 

[Guest]

Thanks Allen, Still have a prob though even when i change the references to suit the table in this database (in this case Table1) using the first expression i get no output at all? I have included all the other table fields in the query first. Is this correct?

Base the report on a query.

In query design view, type something like this into a fresh column in the
Field row. It returns the most recent end date for the same person (assuming
UR identifies the person), prior to the Start_Date. Because we are using a
2nd copy of the same table, we have to use a different name, so I've
referred to it as "Dupe":

( SELECT Max([End_Date]) As LastEndDate
FROM MyTable AS Dupe
WHERE (Dupe.UR = MyTable.UR)
AND (Dupe.End_Date < MyTable.Start_Date) )

That's called a subquery - a complete query statement within another query.
Once you have that working, you can do your DateDiff(). For example, you may
end up with something like this as the calculated field in your query:

Days: DateDiff("d", ( SELECT Max([End_Date]) As LastEndDate FROM MyTable AS
Dupe WHERE (Dupe.UR = MyTable.UR)
AND (Dupe.End_Date < MyTable.Start_Date) ) , [Start_Date)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi all a friend has approached me with this problem and a
table. The first problem is that the table/database is
built wrong but that cannot be changed since the database
is a national one

TABLE
------------
Record UR Name Start_Date End_Date
-------- ---- -------- ------------ ------------
1 234 Jo 1/3/2002 14/3/2002
2 234 Jo 20/3/2002 23/3/2002
3 234 Jo 3/5/2003 7/3/2003
4 234 Jo 16/6/2003 23/6/2003

so all records here concern the same person, the problem
is on a report you must have a field that calculates the
difference in days between the end of one treatment to
the start of the next. Between say end of record 1 to the
start date of record 2. Are there any ACCESS gurus there
who can help?
I know how to easily calculate the no of days between the
start & end dates of a single record
=DateDiff("d", [Start_Date], [End_Date])
but not on a report between records. ACCESS is not my
point of focus

 

[Allen Browne]

The first query can have as many fields as you want.

Switch your query to SQL View (View menu), copy the SQL statement, and post
here.

Presumably Start_Date and End_Date are Date/Time type fields?

UR is the correct field to match on?

And the query works, but this expression yields a blank column?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen, Still have a prob though even when i change the references

to suit the table in this database (in this case Table1) using the first
expression i get no output at all? I have included all the other table
fields in the query first. Is this correct?

Base the report on a query.

In query design view, type something like this into a fresh column in the
Field row. It returns the most recent end date for the same person (assuming
UR identifies the person), prior to the Start_Date. Because we are using a
2nd copy of the same table, we have to use a different name, so I've
referred to it as "Dupe":

( SELECT Max([End_Date]) As LastEndDate
FROM MyTable AS Dupe
WHERE (Dupe.UR = MyTable.UR)
AND (Dupe.End_Date < MyTable.Start_Date) )

That's called a subquery - a complete query statement within another query.
Once you have that working, you can do your DateDiff(). For example, you may
end up with something like this as the calculated field in your query:

Days: DateDiff("d", ( SELECT Max([End_Date]) As LastEndDate FROM MyTable AS
Dupe WHERE (Dupe.UR = MyTable.UR)
AND (Dupe.End_Date < MyTable.Start_Date) ) , [Start_Date)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi all a friend has approached me with this problem and a
table. The first problem is that the table/database is
built wrong but that cannot be changed since the database
is a national one

TABLE
------------
Record UR Name Start_Date End_Date
-------- ---- -------- ------------ ------------
1 234 Jo 1/3/2002 14/3/2002
2 234 Jo 20/3/2002 23/3/2002
3 234 Jo 3/5/2003 7/3/2003
4 234 Jo 16/6/2003 23/6/2003

so all records here concern the same person, the problem
is on a report you must have a field that calculates the
difference in days between the end of one treatment to
the start of the next. Between say end of record 1 to the
start date of record 2. Are there any ACCESS gurus there
who can help?
I know how to easily calculate the no of days between the
start & end dates of a single record
=DateDiff("d", [Start_Date], [End_Date])
but not on a report between records. ACCESS is not my
point of focus