Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Briceston
Yak Posting Veteran
54 Posts |
 Posted - 2013-08-16 : 16:09:40
|
| How would I write this If statement in Access?.I have fields with Patient ID and Code. I want to write if patient ID is the same then Code "PatientNew" else code = "PatientDischarge". Code "PatientNew" takes precedence if the the Patient ID is the same.Patient ID Code123456 PatientNew123456 PatinetOld789767 PatientDischarge |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-08-16 : 17:04:41
|
quote:
Originally posted by Briceston
How would I write this If statement in Access?.I have fields with Patient ID and Code. I want to write if patient ID is the same then Code "PatientNew" else code = "PatientDischarge". Code "PatientNew" takes precedence if the the Patient ID is the same.Patient ID Code123456 PatientNew123456 PatinetOld789767 PatientDischarge
IIF is usually used to compare data in two columns (or two scalar variables). If I am interpreting your explanation correctly, then your data is in multiple rows. I might be misinterpreting it, but when you say "if the patientID is the same", which patient id are you looking at and what are you comparing it against? |
 |
|
|
Briceston
Yak Posting Veteran
54 Posts |
Posted - 2013-08-16 : 20:10:43
|
Ok, so in my data set, I have multiple records with the same patient ID. They differ by code "patientnew", "patientold", and "patientDischarge". I want patientnew to take precedence over patientold. it would go like this: IIF the patientid field has the same record twice, then patient new.quote:
Originally posted by James K
quote:
Originally posted by Briceston
How would I write this If statement in Access?.I have fields with Patient ID and Code. I want to write if patient ID is the same then Code "PatientNew" else code = "PatientDischarge". Code "PatientNew" takes precedence if the the Patient ID is the same.Patient ID Code123456 PatientNew123456 PatinetOld789767 PatientDischarge
IIF is usually used to compare data in two columns (or two scalar variables). If I am interpreting your explanation correctly, then your data is in multiple rows. I might be misinterpreting it, but when you say "if the patientID is the same", which patient id are you looking at and what are you comparing it against?
|
|
|
|
jethrow
Starting Member
37 Posts |
Posted - 2013-08-17 : 05:09:51
|
Well, based on the given data & requirements, you may be able to just do this:SELECT [Patient ID], MIN([Table].Code) AS CodeFROM [Table]GROUP BY [Patient ID] However, if there are other codes, this might be the route to go:SELECT [Patient ID], IIF(MAX(n)=1,"PatientNew",MAX(s.Code)) AS CodeFROM ( SELECT [Patient ID], Code, IIF(Code="PatientNew",1,0) AS n FROM [Table]) AS sGROUP BY [Patient ID] Also, you are in the SQL Server section of the forum. I'm not a veteran here, but I'd assume Access questions should go in the Other Forums::MS Access forum.Microsoft SQL Server Noobie |
|
|
|
Briceston
Yak Posting Veteran
54 Posts |
Posted - 2013-08-17 : 10:26:28
|
Thanks, this was helpful. quote:
Originally posted by jethrow
Well, based on the given data & requirements, you may be able to just do this:SELECT [Patient ID], MIN([Table].Code) AS CodeFROM [Table]GROUP BY [Patient ID] However, if there are other codes, this might be the route to go:SELECT [Patient ID], IIF(MAX(n)=1,"PatientNew",MAX(s.Code)) AS CodeFROM ( SELECT [Patient ID], Code, IIF(Code="PatientNew",1,0) AS n FROM [Table]) AS sGROUP BY [Patient ID] Also, you are in the SQL Server section of the forum. I'm not a veteran here, but I'd assume Access questions should go in the Other Forums::MS Access forum.Microsoft SQL Server Noobie
|
|
|
|
|
|
|
|
|
IIF Statment in Access