W
wonkydonkey
Guest
Hi, hope somebody can help with these, they are driving me round the bend!!
query 1
I work in a hospital where the system holds demographic details about every patient in a single table. One of the fields in this table is a unique case number. This should be in the format of a 9 digit number. However the field has never been validated and i'm attempting to clean the system up. I have created a query that finds anything within that field that isnt a number (e.g commas, letters etc), But, my next task is to find anything that isnt a 9 digit number. Anyone know how to do this? I dont assume its a big deal but I cant figure out how to approach it.
query 2
The same table I mentioned above stores not only a unique record for each patient but it also stores a log of any changes that are made to that record.
for example
2004847 john smith 28/05/1972 10 king road 12/05/2006
2004847 john smith 27/05/1972 10 king road 12/06/2006
that means the 2nd record was the last to be updated (last column) and is the current one in the system. Is there any way of extracting just the most recent records e.g the most recent for each unique number? some records wont have been updated and therefore will only have one record in the table. I have tried just doing a group by on every field and then set the "date changed" field to "last" will this work???
cheers for any help on either query
query 1
I work in a hospital where the system holds demographic details about every patient in a single table. One of the fields in this table is a unique case number. This should be in the format of a 9 digit number. However the field has never been validated and i'm attempting to clean the system up. I have created a query that finds anything within that field that isnt a number (e.g commas, letters etc), But, my next task is to find anything that isnt a 9 digit number. Anyone know how to do this? I dont assume its a big deal but I cant figure out how to approach it.
query 2
The same table I mentioned above stores not only a unique record for each patient but it also stores a log of any changes that are made to that record.
for example
2004847 john smith 28/05/1972 10 king road 12/05/2006
2004847 john smith 27/05/1972 10 king road 12/06/2006
that means the 2nd record was the last to be updated (last column) and is the current one in the system. Is there any way of extracting just the most recent records e.g the most recent for each unique number? some records wont have been updated and therefore will only have one record in the table. I have tried just doing a group by on every field and then set the "date changed" field to "last" will this work???
cheers for any help on either query
