Trying to create a If and Statement

[wheddingsjr]

Hi all

I am new to the forum and have a problem I was hoping someone could help me with. I have a table that I am trying to write an if statement for. Please see sample below. The idea is to populate the "Y/N" column by comparing the "MEMNO" and the "BEGDATE" and determine whether or not there is both a "SICK" and "PREVENTIVE" type for the same MEMNO on the same BEGDATE. Any suggestions would be appreciated. Thanks in advance.

MEMNO	BEGDATE	SERVCODN	TYPE	Y/N
ABC	01/11/16	99395	SICK
ABC	01/11/16	99213	PREVENT
ABC	01/11/16	99213	PREVENT
ABC	01/11/16	99213	PREVENT
DEF	05/31/16	99397	SICK
DEF	05/31/16	99213	PREVENT
GHI	02/16/16	99397	SICK
GHI	02/16/16	99213	PREVENT
MNOP	02/29/16	99214	PREVENT
MNOP	02/29/16	99396	SICK
QRS	04/04/16	99396	SICK
QRS	04/04/16	99213	PREVENT
TUV	01/29/16	99396	SICK
TUV	01/29/16	99214	PREVENT
XYZ	04/29/16	99397	SICK
XYZ	04/29/16	99213	PREVENT
ABC1	02/03/16	99396	SICK
ABC1	02/03/16	99213	PREVENT

[aytee111]

Query1 - all "SICK"
Query2 - all PREVENTATIVE
Query3 - join 1 and 2 on MemNo and BegDate - those are the ones that have both.

[wheddingsjr]

Thanks aytee111

I created both queries and attempted to create the third but I am not sure which one to use. Sorry but I am rather new to access and I can do simple queries but this is a bit complex for me. So I join the two queries and to pull the data I need to do what?

Thanks

wheddingsjr

[aytee111]

In the third query, bring in query1 and query2. "Join" means to link the fields, i.e. drag MemNo in query1 to MemNo in query2, that is a join. Do the same for BegDate. Now you have two sets of data, in the query fields add the fields that you want to show in the results.

[wheddingsjr]

Thanks again... I know about the join...but are you saying that the third query should be an update query, with the Memno and the Begdate joined....bring the Y/N field in query 1 to and update that dataset and then do it again using the query 2 fields?

[aytee111]

Forgot about the updating! Bring in the table as well and join it in the same way. Then update the Y/N on the table.

[wheddingsjr]

ahhh..ok...I thought that was what I should do but wanted to double check...thanks a bunch

[aytee111]

Make a copy of the table before you run it, then make sure that the correct records are being updated.

[wheddingsjr]

wow...thanks aytee111..it worked PERFECTLY!!!!

[aytee111]

Glad to be of service.