SQL query to check one table and enter values into another

[kmfdm]

I have a very simple MS Access User Table (USER_TABLE) consisting of 3 fields: Customer_Number, User_Name, and Email_Address. I have another table (NEW_USERS) that consist of new requests for Users. It has a User_Status field that is blank by default, and also has the Customer_Number, User_Name, and Email_Address fields.

Half of the new requests that come through are users already existing, so I want to set up a query that will check the USER_TABLE to determine if a new request exists or not, using the Email_Address field checked vs. the Customer_Number field. Complicating this is the fact that 1) Customer_Number is not unique (many Users exists for a single Customer Number) and 2) Users can have multiple accounts for different Customer Numbers. This results in 4 scenarios in the NEW_USERS table when checking vs. the USER_TABLE:

Email_Address does not exist for Customer Number in USER_TABLE (New)


Email_Address exists for Customer Number in USER_TABLE (Existing)
Email_Address does not exist for Customer Number in USER_TABLE, but exists for other Customer Numbers (New-Multi)
Email_Address does exist for Customer Number in USER_TABLE, and also exists for other Customer Numbers (Existing-Multi)

What I would like to do is run these checks and enter the corresponding result (New, Existing, New-Multi or Existing-Multi) into the User_Status field.

This seems like it would be possible. Is there an easy way to write a SQL query to do this?

[June7]

Think you are going to need VBA code. Something like:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM User_Table WHERE Email_Address='" & Me.Email & "'")

Select Case rs.RecordCount
Case 0
Me!User_Status = "New"
Case 1
If rs!Customer_Number = Me.Customer Then
Me!User_Status = "Existing"
Else
Me!User_Status = "New-Multi"
End If
Case Else
Me!User_Status = "Existing-Multi"
End Select

[kmfdm]

I might be missing something in that VBA...I see where it queries the USER_TABLE table, but I do not see where is specifies that we are entering data into the NEW_USERS table.

Could you clarify?

edit: I see now that it's based on the current open Table, correct?

I was thinking it would be only SQL queries...I'm not as familiar with VBA. How do I actually run the VBA code in Access?

I tried your code in the 'Immediate Window' of the Access VBA Editor, but I got a compile error. I then your code to a new module, but when attempting to run it, it asks me to specify a Macro?

[June7]

That code would be behind a form that is bound to the New_Users table. It will populate the User_Status field of the current record. The real trick is figuring out what event to put the code in. Maybe the BeforeUpdate event of the email textbox.

A Data Macro might be able to accomplish this but I have never used Data Macros.

[kmfdm]

Can't I set it up to manually execute the VBA code at a time of my choosing? Instead of tying it to an event?

[June7]

Everything happens from an event. Could be data edit, button click, form close - all are events.

[kmfdm]

I tried to add it as a macro, but when running I get: "Compile Error: Invalid use of Me Keyword" at Ln 4 Col 87

[June7]

The Me alias is used only in VBA. The code would have to be behind the form that has the controls being referenced.

I don't use macros. Macro would have to reference the form by name or with ActiveObject (something like that).

[kmfdm]

Ok, this is all new to me, since I thought I could just have 4 SQL queries that would accomplish what I was trying to do.

So I build a form and then add this VBA to it?

[June7]

Yes. Build form. Code behind form in some event. Button click?

You can use queries but you still have to somehow get info from them. Suggested code opens a single dataset and evaluates.