having trouble making a quiery to the closest date to another date

[Freida]

I am trying to make a query in access based on 2 different dates.

Date 1= In Patient Date - member x admitted to the hospital on 2/3/2007

Date 2= PcP visit Date PCP visits on 12/20/2006, 1/14/2007,1/31/2007 I
need to no how to get the 1/31/2007.

So I have patients that stayed in the hospital, who may have been to see
their PCP 5 time in the last 6 months before they were admitted to the
hospital. I need to find out which PCP visitation date is the closest date
to the hospital admission date. I tried using the max function, but I am
getting a mismatch data type error. Can someone please explain to me how to
do this.

 

[Jason Lepack]

SELECT max([PcP Visit Date])
FROM [your table name]
WHERE [PcP Visit Date] < [Enter an In Patient Date:]
AND patient_id = [Enter a Patient Number:]

 

[Allen Browne]

You can use a subquery to get the nearest date from the other table.

This example assumes:
- Table1 is the table containing the [Date 1] field;
- [PCP Visit] is the table containing the [Date 2] field, and it has a
primary key named [PCP Visit ID].

SELECT *,
(SELECT TOP 1 [Date 2]
FROM [PCP Visit] AS B
WHERE ((B.[PatientID] = [Table1].[PatientID])
AND (B.[Date 2] Is Not Null))
ORDER BY Abs([Table1].[Date 1] - B.[Date 2]), B.[PCP Visit ID])
AS NearestDate2
FROM [Table1];

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

 

[Freida]

SELECT max([PcP Visit Date])
FROM [your table name]
WHERE [PcP Visit Date] < [Enter an In Patient Date:]
AND patient_id = [Enter a Patient Number:]

I am trying to make a query in access based on 2 different dates.

Date 1= In Patient Date - member x admitted to the hospital on 2/3/2007

Date 2= PcP visit Date PCP visits on 12/20/2006, 1/14/2007,1/31/2007 I
need to no how to get the 1/31/2007.

So I have patients that stayed in the hospital, who may have been to see
their PCP 5 time in the last 6 months before they were admitted to the
hospital. I need to find out which PCP visitation date is the closest date
to the hospital admission date. I tried using the max function, but I am
getting a mismatch data type error. Can someone please explain to me how to
do this.

I tried it but it did not work, I think I am doing something wrong, The last part was a paramater. I need to display a list of patients from the last 6 months, Showing the following fields in s queriy:

Member Name, Member Number, Hospital claim number, last pcp visit date, and
PCP. When I leave the last line out and add the rest of my fields I get and
error message saying the I tried to execute a query that does not include a
specified function......not part of the aggregate function. So I think there
is another step somewhere. Sorry that I wasn't clear on what I needed.
Please help again. Thx

 

[Dale Fye]

Allen,

Suppose your Admission date (Date1) were 2/3/07, and you have PCP dates of
1/20/07 and 2/5/07. Wouldn't your query return the one that is after the
admission date?

Why not just use MAX in the subquery?

SELECT *,
(SELECT MAX(B.[Date 2])
FROM [PCP Visit] AS B
WHERE B.[PatientID] = [Table1].[PatientID]
AND B.[Date 2] < [Table1].[Date1]) AS NearestDate2
FROM [Table1];

Dale
--
Email address is not valid.
Please reply to newsgroup only.

You can use a subquery to get the nearest date from the other table.

This example assumes:
- Table1 is the table containing the [Date 1] field;
- [PCP Visit] is the table containing the [Date 2] field, and it has a
primary key named [PCP Visit ID].

SELECT *,
(SELECT TOP 1 [Date 2]
FROM [PCP Visit] AS B
WHERE ((B.[PatientID] = [Table1].[PatientID])
AND (B.[Date 2] Is Not Null))
ORDER BY Abs([Table1].[Date 1] - B.[Date 2]), B.[PCP Visit ID])
AS NearestDate2
FROM [Table1];

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

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

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

I am trying to make a query in access based on 2 different dates.

Date 1= In Patient Date - member x admitted to the hospital on 2/3/2007

Date 2= PcP visit Date PCP visits on 12/20/2006, 1/14/2007,1/31/2007 I
need to no how to get the 1/31/2007.

So I have patients that stayed in the hospital, who may have been to see
their PCP 5 time in the last 6 months before they were admitted to the
hospital. I need to find out which PCP visitation date is the closest
date
to the hospital admission date. I tried using the max function, but I am
getting a mismatch data type error. Can someone please explain to me how
to
do this.

 

[Allen Browne]

Yes, it would. If the OP intended "closest prior date", it needs that
change.

The subquery should work.
The Total query (GROUP BY clause) might be more efficient.

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

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

Allen,

Suppose your Admission date (Date1) were 2/3/07, and you have PCP dates of
1/20/07 and 2/5/07. Wouldn't your query return the one that is after the
admission date?

Why not just use MAX in the subquery?

SELECT *,
(SELECT MAX(B.[Date 2])
FROM [PCP Visit] AS B
WHERE B.[PatientID] = [Table1].[PatientID]
AND B.[Date 2] < [Table1].[Date1]) AS NearestDate2
FROM [Table1];

Dale
--
Email address is not valid.
Please reply to newsgroup only.

You can use a subquery to get the nearest date from the other table.

This example assumes:
- Table1 is the table containing the [Date 1] field;
- [PCP Visit] is the table containing the [Date 2] field, and it has a
primary key named [PCP Visit ID].

SELECT *,
(SELECT TOP 1 [Date 2]
FROM [PCP Visit] AS B
WHERE ((B.[PatientID] = [Table1].[PatientID])
AND (B.[Date 2] Is Not Null))
ORDER BY Abs([Table1].[Date 1] - B.[Date 2]), B.[PCP Visit ID])
AS NearestDate2
FROM [Table1];

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

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

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

I am trying to make a query in access based on 2 different dates.

Date 1= In Patient Date - member x admitted to the hospital on
2/3/2007

Date 2= PcP visit Date PCP visits on 12/20/2006, 1/14/2007,1/31/2007
I
need to no how to get the 1/31/2007.

So I have patients that stayed in the hospital, who may have been to
see
their PCP 5 time in the last 6 months before they were admitted to the
hospital. I need to find out which PCP visitation date is the closest
date
to the hospital admission date. I tried using the max function, but I
am
getting a mismatch data type error. Can someone please explain to me
how
to
do this.