Note down if it changes

[naungsai]

Dear friends

I want to note down the record if it changes.
Supposed I have a patient 101 and his category is as follow in respective date.

[U]PatientID[/U]  [U]Pdate[/U]  [U]Pcategory[/U]
101	1/1/2010	C
101	1/25/2010	C
101	2/10/2010	B
101	2/22/2010	C
101	3/2/2010	C
101	3/5/2010	A

I want to retrieve records when the Pcategory changes. So that the result would be as followed.

[U]PatientID[/U]  [U]Pdate[/U]  [U]Pcategory[/U]
101	1/1/2010	C
101	2/10/2010	B
101	2/22/2010	C
101	3/5/2010	A

Can somebody help me please?

Naungsai

 

[John Big Booty]

You could us a Group By query. You would group by PatientID and Category and return Max of Date.

 

[naungsai]

Hi John
thanks for your quick reply.
If i group the category, i lost the incidence of first category "C".
In this case I don't want to lose it.
how should I solve it.

Best
NS

 

[vbaInet]

If your data looked like this:

ID    PatientID     Pdate       Pcategory
1       101         1/1/2010        C
2       101         1/25/2010       C
3       101         2/10/2010       B
4       101         2/22/2010       C
5       101         3/2/2010        C
6       101         3/5/2010        A

Then it can be done.

OR

I noticed that each group of Cs are of different months. If this is the case then it probably is doable.

 

[naungsai]

Dear vbaInet

My data is like your first statement. The patient category may change within a week or sometimes remaining the same for months. I want to retrieve the first date of change of patient category.

Hope you could solve it.

Best

 

[vbaInet]

So is it sorted by ID?

 

[naungsai]

So is it sorted by ID?

yes. it may need to be sorted, but not necessary.

 

[naungsai]

Dear vbaInet

You are correct. The table need to be sorted by ID or PDate.

 

[vbaInet]

Which one are you going to use to sort?

Using the format above (i.e. with the ID field and the sort applied), show us the current set of records and what you would like it to become - just like you did in your first post.