date change

[M.McDoom]

Hello,

I am designing a query to give me information on the latest date entry
of an item. But when it runs it gives me the last date of the
previous item which is not from the same PID.

Here is how I set it up. (Change of risk level =CRL) (PID= person
identification)

Field: PID Previous risk level New risk level
Description Date Date
Table: Event Change of Risk level CRL CRL
Event CRL

The previous risk level cannot equal the new risk level. The date from
the CRL table cannot equal the date of the event date. The CRL date
needs to be after the event date, but I don't know what criteria will
make it show up in the query. Basically I just need the query to show
me the date that the risk level changed, but since each entry into the
event table can be multiple entries for a person the query doesn't
run....thanks for your help

 

[Allen Browne]

In a relational database, you don't store information about the previous
entry again in each record.

Can I suggest you use a table with fields like this:
- ID AutoNumber primary key
- PID foreign key to your Person table's primary key.
- RiskDate Date/Time When this risk level was assigned.
- RiskLevelID foreign key to a table of risk levels.

If you need help comparing that to the previous risk level, see:
Referring to a Field in the Previous Record or Next Record
at:
http://support.microsoft.com/kb/210504/en-us