Second Post: Last Date Query

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

Bryan Hughes

Hello,

I am trying to create a append query, but having trouble figuring it out.

I have two tables I am working with, one is a main and the other is a sub
table.

I need to get the last date ([Activity]![CAD]) and activity status
([Activity]![CAS]) As LastCAS entered for a ID Number ([Activity]![GTFID]).

Then if the ID Number [Main]![GTFID] is still open [Main]![Open] in the Main
table get the Date opened [Main]![IOD] ,
add Current month and year to [Main]![IOD] (CDate(Month(Date()) & "/" &
DatePart("d", [Main]![IOD]) & "/" & Year(Date())) As NewCAD)

then append the [GTFID], NewCAD, LastCAS to the Activity table.

-TFTH
Bryan
 
I do this with two queries. The first query finds the max date per
Activiity. The second query links the first query to the raw data via both
the ActivityID and the MaxDate.

You can probably make that second query an Append Query.
 
Thanks Steve,

I tried this the problem is that the activity table keeps a history of
activity, so an ID Number could have two max dates [CAD] one active [CAS] =
True, and inactive [CAS] = False.

I need to get the [CAS] status for the last date entered for the ID number.

I am trying to create a query for a batch transaction.

-TFTH
Bryan


[MVP] S.Clark said:
I do this with two queries. The first query finds the max date per
Activiity. The second query links the first query to the raw data via
both
the ActivityID and the MaxDate.

You can probably make that second query an Append Query.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

Bryan Hughes said:
Hello,

I am trying to create a append query, but having trouble figuring it out.

I have two tables I am working with, one is a main and the other is a sub
table.

I need to get the last date ([Activity]![CAD]) and activity status
([Activity]![CAS]) As LastCAS entered for a ID Number ([Activity]![GTFID]).

Then if the ID Number [Main]![GTFID] is still open [Main]![Open] in the Main
table get the Date opened [Main]![IOD] ,
add Current month and year to [Main]![IOD] (CDate(Month(Date()) & "/" &
DatePart("d", [Main]![IOD]) & "/" & Year(Date())) As NewCAD)

then append the [GTFID], NewCAD, LastCAS to the Activity table.

-TFTH
Bryan
Click to expand...
Click to expand...
 
Back
Top