Updating Fields

[serino64]

Hello everyone,

I have a table called "Accounts" and a field called "Account Reps" and "Job Name". How do I create a query that will update the null fields for records 2 and 3 to reflect Susan and records 5,6,7 to reflect Daniel.

Account Reps Job Name
Record 1 Susan Porter Residence
Record 2 "Is Null" Porter Residence
Record 3 "Is Null" Porter Residence
Record 4 Daniel New Construction
Record 5 "Is Null" New Construction
Record 6 "Is Null" New Construction
Record 7 "Is Null" New Construction

Any help you can provide is well appreciated.

 

[lespaul]

is the account rep on each record tied to the Job Name? So on record 2 Account Rep needs to be Susan because Susan is the rep for the job Porter Residence? Or record 2 needs to be Susan because when you put them in this order the record "before" has Susan?

Leslie

Have you met Hardy Heron?

 

[serino64]

Thank you for the reply. Correct, The account rep for the Porter Residence is Susan.

 

[PHV]

UPDATE Accounts
SET [Account Reps]=DLookUp("[Account Reps]","Accounts","[Job Name]='" & [Job Name] & "' AND Not([Account Reps] Is Null)")
WHERE [Account Reps] Is Null

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[serino64]

Thanks PHV, that worked just fine!

 

[serino64]

First of thanks for the assistance. Now I have a one more problem similar issue different query. Here I need to populate the null values with the account rep. Records 2-6 belong to susan and 7-9 belong to Derek.

Account Rep StageChange
Record 1 Susan 03-1st. Meeting
Record 2 "IsNull" 02-Appointment
Record 3 "IsNull" 04-Design Phase
Record 4 "IsNull" 05-2-Est. (AJ)
Record 5 "IsNull" 06-Bid Review
Record 6 "IsNull" 07-Presentation
Record 7 Derek 01-New Lead
Record 8 "IsNull" 02-Appointment
Record 9 "IsNull" 04-Design Phase
Record 10 "IsNull" 05-2-Est. (AJ)

 

[lespaul]

How do you know that Record 2 with StageChange = '02-Appointment' is Susan's and Record 8 with the same StageChange value is Derek's?

Leslie

Have you met Hardy Heron?

 

[serino64]

Our company uses a Sales and Leads tracking software that allows us to export to a CSV. format in excel. I am using MS Access to create a linked table to that excel file, whereby allowing to create better reports.

The information it inports into MS Access is in the above aforementioned format. I just have to find someway to populate those null fieds with the account rep name.