Help with looking data up. Possible use of "Select Distinct" function?

[Spyder757]

I’ve recently acquired a database from another officer and the manager would like me to upgrade it to include the ability to prepopulate the supervisors of employees when they are entered into he form. This is what I currently have:

Main Table (the data will be entered here)
-Employee Name
-Login ID
-Supervisor
-Call Out Date
-Call Out Time

Employee Table (source of known data)
-Employee Name
-Login ID
-Supervisor

Enter Call Out Form (used to enter data into the Main Table)

Now what the manager would like to be able to do is select the agent’s name from a combo box on the form, and have the Login ID and Supervisor fields automatically populate. Currently the Login ID will auto populate as the last owner of this database configured it to do so by using a “Select Distinct” statement in the Lookup tab of the Main Table.

I attempted to copy this format to use it with the new supervisor column but I just can’t get it to work. Any suggestions?

I don’t really understand the use of the “Select Distinct” function nor what it actually does.

Any help is always appreciated.

Spyder757

 

[randysmid]

Hi,
Here is part of your solution. I wrote an FAQ on the difference between "distinct" and "distinctrow". Once you read this, you will understand how they can be used in your particular case.
In the other case, I am not sure if I understand the problem, but perhaps this technique is what you are looking for:
Sometimes, we find it necessary to refer back to our table in a cross reference situation. For example, each employee can have a supervisor, and each supervisor can have a supervisor. Do you see the problem? So, in our query, simply add the same table twice, such as the Employee table. So, the first instance of this table will be called "Employee" but the 2nd instance will be called "Employee_1". If you need to another instance, that would become "Employee_2".

Does this help?

HTH,
Randy Smith
California Teachers Association

 

[Spyder757]

Not really, to be honest that is just confusing let me explain it a bit more.

The call center I work in constantly changes the supervisors of employees. For this reason it is important to know the supervisor of the employee at the time they called out because by next week it could change.

What I would like to do is this:

Have a forum where I select the agent's name and the ID and Supervisor name will automaticly populate on the form and get saved into the mail table.

Spyder757

 

[Spyder757]

And that should read "form" not "forum".

Spyder757

 

[randysmid]

Hi,
I am confused. Here is what you wrote:
Have a form where I select the agent's name and the ID and Supervisor name will automaticly populate on the form and get saved into the mail table.

But, you say that the supervisor can change weekly. I am trying to visualize how you will make a change to the supervisor if the Mail table is automatically updated.

I think you want the user to optionally change the supervisor id, then have then click a command button to write the record to the Mail table. Is this what you mean?


HTH,
Randy Smith
California Teachers Association

 

[Spyder757]

Let me start over. The supervisors change fairly frequently, our Workforce department maintains a table named “Employees” that they update as these changes occur.

Because of these frequent changes I need to be able to write to the main table the supervisor they had at the time of the call out.

I.E. if an agent calls out on 03/01/02 I need to know who the supervisor was at that time, not their current supervisor.

I know this can be done by simply selecting the name as I’ve got an example of this pulling their ID and a skill out of the employee table. I just can’t figure out how it’s done.

Quite simply what I would like is this:

Go to my data entry form, select the agent’s name from a combo box, and automatically populate the ID, Skill, and Team Leader fields.

This above-mentioned data would be looked up from the Employees table, which is maintained by another department.

Is this a better explanation?

Spyder757

 

[randysmid]

Hi,
It seems like you need another table, perhaps called "SupervisorOnDuty". For any given date range, only one supervisor would be listed.
The records in the table might look like this:
SupervisorID Start End
Smith07 04/01/2003 04/15/2003
Jones02 04/16/2003 04/18/2003
Smith07 04/19/2003 04/22/2003
Davis03 04/23/2003 04/26/2003

If this sounds reasonable, then a query can be created to look at the call out date, and compare itself with the various start and end dates. Of course, you cannot have any overlap in the dates. As an example, if someone had a call out date of 4/15, then Smith07 would be returned. An error would occur if someone had a call out date of 3/31.

HTH,
Randy Smith
California Teachers Association

 

[Spyder757]

I ended up doing almost exactly what you mentioned using 2 tables, one table just writes to the other and then clears itself out afterwards.

Thank you for your help.

Spyder757