Date and Max

  • Thread starter Thread starter Bryan Hughes
  • Start date Start date
B

Bryan Hughes

Hello,

I am having trouble trying figure how to do this query.

I have a table that has [Case File ID], [Case Activity Date] and [Case
Active]. Case Active is a yes/no field (yes is active. no is inactive).


I need to make an active query and inactive query.

My current query looks like this:
SELECT DISTINCTROW tblMain_File.CFID,
[CFN] & " " & [CN] AS Name,
Max(tblCase_File_Activity.CAD) AS MaxOfCAD
FROM tblMain_File INNER JOIN tblCase_File_Activity
ON tblMain_File.CFID = tblCase_File_Activity.CFID
WHERE tblMain_File.GT<>"NA" Or tblMain_File.GT<>"NACF"
AND tblCase_File_Activity.CAS =True
GROUP BY tblMain_File.CFID,
[CFN] & " " & [CN]
ORDER BY tblMain_File.CFID;

On the active query I need to check the max active date where [CAS] is true
against the max active date [CAS] is false. If [CAS] true max date is
greater then [CAS] false max date, then case is active and will show in
query results.

Inactive query is the opposite.

How should I do this?

Please Help!

-Bryan
 
Bryan,

First thing I would do is create a query that gives you the most
recent CaseActivityDate for each case and gets you the status of that
case. Save this query as qryCaseCurrentStatus (assumes that you won't
have a record in tblCase_File_Activity with a date greater than todays
date).

SELECT CFA.[Case File ID] as CFID
, CFA.[Case Activity Date]
, CFA.[Case Active]
FROM tblCase_File_Activity CFA
INNER JOIN(SELECT [Case File ID] as CFID
, MAX([Case Activity Date]) as
CaseLastDate
FROM tblCase_File_Activity
GROUP BY [Case File ID]) as CFAMax
ON CFA.[Case File ID] = CFAMax.CFID

Now, create a second query that includes your tblMain_File and this
first query in the query grid. Join the two tables on the CFID field,
compute your Name field, and you are set to go. The query above will
only return one record for each file ID, and it will be the most
recent record.

--
HTH

Dale Fye


message Hello,

I am having trouble trying figure how to do this query.

I have a table that has [Case File ID], [Case Activity Date] and [Case
Active]. Case Active is a yes/no field (yes is active. no is
inactive).


I need to make an active query and inactive query.

My current query looks like this:
SELECT DISTINCTROW tblMain_File.CFID,
[CFN] & " " & [CN] AS Name,
Max(tblCase_File_Activity.CAD) AS MaxOfCAD
FROM tblMain_File INNER JOIN tblCase_File_Activity
ON tblMain_File.CFID = tblCase_File_Activity.CFID
WHERE tblMain_File.GT<>"NA" Or tblMain_File.GT<>"NACF"
AND tblCase_File_Activity.CAS =True
GROUP BY tblMain_File.CFID,
[CFN] & " " & [CN]
ORDER BY tblMain_File.CFID;

On the active query I need to check the max active date where [CAS] is
true
against the max active date [CAS] is false. If [CAS] true max date is
greater then [CAS] false max date, then case is active and will show
in
query results.

Inactive query is the opposite.

How should I do this?

Please Help!

-Bryan
 
Dale,

I created the query as you suggested but it is showing me all records in the
table. Here is what the query looks like:

SELECT CFA.[CFID], CFA.[CAD], CFA.[CAS]
FROM tblCase_File_Activity CFA
INNER JOIN(SELECT [CFID], MAX([CAD]) AS LastCaseDate
FROM tblCase_File_Activity
GROUP BY [CFID]) AS CFAMax
ON CFA.[CFID]=CFAMax.[CFID];

What could be the problem?

-Bryan
 
Sorry,

SELECT CFA.[CFID], CFA.[CAD], CFA.[CAS]
FROM tblCase_File_Activity CFA
INNER JOIN(SELECT [CFID], MAX([CAD]) AS LastCaseDate
FROM tblCase_File_Activity
GROUP BY [CFID]) AS CFAMax
ON CFA.[CFID]=CFAMax.[CFID]
AND CFA.[CAD] = CFAMax.LastCaseDate

--
HTH

Dale Fye


Dale,

I created the query as you suggested but it is showing me all records in the
table. Here is what the query looks like:

SELECT CFA.[CFID], CFA.[CAD], CFA.[CAS]
FROM tblCase_File_Activity CFA
INNER JOIN(SELECT [CFID], MAX([CAD]) AS LastCaseDate
FROM tblCase_File_Activity
GROUP BY [CFID]) AS CFAMax
ON CFA.[CFID]=CFAMax.[CFID];

What could be the problem?

-Bryan


Dale Fye said:
Bryan,

First thing I would do is create a query that gives you the most
recent CaseActivityDate for each case and gets you the status of that
case. Save this query as qryCaseCurrentStatus (assumes that you won't
have a record in tblCase_File_Activity with a date greater than todays
date).

SELECT CFA.[Case File ID] as CFID
, CFA.[Case Activity Date]
, CFA.[Case Active]
FROM tblCase_File_Activity CFA
INNER JOIN(SELECT [Case File ID] as CFID
, MAX([Case Activity Date]) as
CaseLastDate
FROM tblCase_File_Activity
GROUP BY [Case File ID]) as CFAMax
ON CFA.[Case File ID] = CFAMax.CFID

Now, create a second query that includes your tblMain_File and this
first query in the query grid. Join the two tables on the CFID field,
compute your Name field, and you are set to go. The query above will
only return one record for each file ID, and it will be the most
recent record.

--
HTH

Dale Fye


message Hello,

I am having trouble trying figure how to do this query.

I have a table that has [Case File ID], [Case Activity Date] and [Case
Active]. Case Active is a yes/no field (yes is active. no is
inactive).


I need to make an active query and inactive query.

My current query looks like this:
SELECT DISTINCTROW tblMain_File.CFID,
[CFN] & " " & [CN] AS Name,
Max(tblCase_File_Activity.CAD) AS MaxOfCAD
FROM tblMain_File INNER JOIN tblCase_File_Activity
ON tblMain_File.CFID = tblCase_File_Activity.CFID
WHERE tblMain_File.GT<>"NA" Or tblMain_File.GT<>"NACF"
AND tblCase_File_Activity.CAS =True
GROUP BY tblMain_File.CFID,
[CFN] & " " & [CN]
ORDER BY tblMain_File.CFID;

On the active query I need to check the max active date where [CAS] is
true
against the max active date [CAS] is false. If [CAS] true max date is
greater then [CAS] false max date, then case is active and will show
in
query results.

Inactive query is the opposite.

How should I do this?

Please Help!

-Bryan
Click to expand...
 
Dale,

Thank you, that works exactly the way I needed!

No that I see the query, it makes sense.

Thank you again!

-Bryan
Sorry,

SELECT CFA.[CFID], CFA.[CAD], CFA.[CAS]
FROM tblCase_File_Activity CFA
INNER JOIN(SELECT [CFID], MAX([CAD]) AS LastCaseDate
FROM tblCase_File_Activity
GROUP BY [CFID]) AS CFAMax
ON CFA.[CFID]=CFAMax.[CFID]
AND CFA.[CAD] = CFAMax.LastCaseDate

--
HTH

Dale Fye


Dale,

I created the query as you suggested but it is showing me all records in the
table. Here is what the query looks like:

SELECT CFA.[CFID], CFA.[CAD], CFA.[CAS]
FROM tblCase_File_Activity CFA
INNER JOIN(SELECT [CFID], MAX([CAD]) AS LastCaseDate
FROM tblCase_File_Activity
GROUP BY [CFID]) AS CFAMax
ON CFA.[CFID]=CFAMax.[CFID];

What could be the problem?

-Bryan


Dale Fye said:
Bryan,

First thing I would do is create a query that gives you the most
recent CaseActivityDate for each case and gets you the status of that
case. Save this query as qryCaseCurrentStatus (assumes that you won't
have a record in tblCase_File_Activity with a date greater than todays
date).

SELECT CFA.[Case File ID] as CFID
, CFA.[Case Activity Date]
, CFA.[Case Active]
FROM tblCase_File_Activity CFA
INNER JOIN(SELECT [Case File ID] as CFID
, MAX([Case Activity Date]) as
CaseLastDate
FROM tblCase_File_Activity
GROUP BY [Case File ID]) as CFAMax
ON CFA.[Case File ID] = CFAMax.CFID

Now, create a second query that includes your tblMain_File and this
first query in the query grid. Join the two tables on the CFID field,
compute your Name field, and you are set to go. The query above will
only return one record for each file ID, and it will be the most
recent record.

--
HTH

Dale Fye


message Hello,

I am having trouble trying figure how to do this query.

I have a table that has [Case File ID], [Case Activity Date] and [Case
Active]. Case Active is a yes/no field (yes is active. no is
inactive).


I need to make an active query and inactive query.

My current query looks like this:
SELECT DISTINCTROW tblMain_File.CFID,
[CFN] & " " & [CN] AS Name,
Max(tblCase_File_Activity.CAD) AS MaxOfCAD
FROM tblMain_File INNER JOIN tblCase_File_Activity
ON tblMain_File.CFID = tblCase_File_Activity.CFID
WHERE tblMain_File.GT<>"NA" Or tblMain_File.GT<>"NACF"
AND tblCase_File_Activity.CAS =True
GROUP BY tblMain_File.CFID,
[CFN] & " " & [CN]
ORDER BY tblMain_File.CFID;

On the active query I need to check the max active date where [CAS] is
true
against the max active date [CAS] is false. If [CAS] true max date is
greater then [CAS] false max date, then case is active and will show
in
query results.

Inactive query is the opposite.

How should I do this?

Please Help!

-Bryan
Click to expand...
 
Back
Top