Adding value in table based on values in two other fields

[duthiev]

Hello: Is there (simple) way that I can populate a field based on the values of two other fields? For example, in a list of new employees, if a new employee starts on Nov 30 and works in the Administration Department, his human resources contact is Mary. If a new employee starts on Nov 30 and works in Sales, his contact would be Fred. I hope this is clear! Many thanks!

 

[pbaldy]

Sure, though if the contact can be determined from those fields, you don't really need to save it. If you still need to, you can either use a DLookup or open a recordset to find the value and then assign it to your field.

 

[duthiev]

Thanks for the speedy response! I'm not sure if I understand you; however, I want to avoid having to select the contact for each new employee. I might get 300 employees each day. Is there anyway we can get Access to evaluate the first two fields (date, department) and automatically select the contact name? Thanks again.

 

[pbaldy]

What I'm saying is that if the contact can be derived based on date and department (I assume you have some sort of table?), then you can simply perform this lookup when you need the contact rather than save it. It the same as not saving "total" when you already have "price" and "quantity".

However, if you need to save it for whatever reason, you can certainly have Access evaluate the 2 fields. That would obviously require a table or something that held the data necessary for it to make that evaluation. The tool to use would either be a DLookup function or a recordset.

 

[duthiev]

Thanks for the feedback. I'm not sure how to use a recordset or Dlookup, but I'll explore.

 

[pbaldy]

Look in VBA Help, which should get you in the right direction, and post back if you get stuck. Generally, the DLookup will be probably be easier to implement but the recordset will be more efficient. Good reference if you go the DLookup route:

http://www.mvps.org/access/general/gen0018.htm

 

[duthiev]

Hi again: I looked at Dlookup and I'm not sure this would work. Each day, the contact for a department changes. For example, on November 30, new employees in Sales would be assigned Mary. On December 1, new employees in Sales are assigned Fred. So, there's no connection between department and contact from one day to the other. Once again, your help is really appreciated!

 

[Rabbie]

Hi again: I looked at Dlookup and I'm not sure this would work. Each day, the contact for a department changes. For example, on November 30, new employees in Sales would be assigned Mary. On December 1, new employees in Sales are assigned Fred. So, there's no connection between department and contact from one day to the other. Once again, your help is really appreciated!

There is a a pretty good rule that if you can't do it on paper you can't do it in Access and if you can do it on paper then you can do it in Access once you understand how it works on paper.
If you can tell us how you decide who is the contact for a department for starters on a given date then we will be able to help you better.

 

[duthiev]

It's a lottery (i.e. random) in order to ensure the employees in different departments are equally distributed amongst contacts. Thanks!

 

[Rabbie]

It's a lottery (i.e. random) in order to ensure the employees in different departments are equally distributed amongst contacts. Thanks!

Access can only tell you what you have told Access. or at least you have to tell Access how to to work it out.

In your case you will need to decide how you allocate new starters to contacts and then program that into Access.

 

[duthiev]

Thanks! I'll see if that can be done.

 

[SRAB]

Ohh you need a random name generator.

 

[duthiev]

HI: Sounds intriguing! Would you be able to elaborate? Thanks!