Access logic

[chipy]

OK, how about i put it this way:

say i have a table with several fields. the first field is an autonumber field (primary key). i want to be able to choose something from the second field (dropdown list). when that happens, i want the database program to automatically select the appropriate choices for the next two fields (fields 3 and 4).

fields 2, 3, and 4 are all pulling data from another table. how do i do this, if it is possible.

thanks
chipy

 

[imported_BigD]

I've been wanting to do something similar myself. Should be interesting.

 

[YoshiSato]

You just did not use the name of a Microsoft product and the term "logic" in the same sentance. :laugh:

 

[theknight571]

Trying to better understand the situation before I attempt a solution.

So you have two tables:

Table 1:
- Field 1A = Autonumber
- Field 1B = Data
- Field 1C
- Field 2D
- etc.

Table 2:
- Field 2A = Table 1.Field 1B
- Field 2B
- Field 2C
- Field 2D
- etc.

You have a form where you select Field 1B values from a drop down list, and based on that selection you'd like the other fields on the form to populate with data from fields 2B, 2C, 2D etc.

Right?

 

[chipy]

let me rephrase myself.

i have two tables. one table is called "employee" and contains the unique identifier, first name, last name etc. all persons in this table are employees, but some are also supervisors. to reduce data duplication, i have created one table to house normal employees and superviors (who are employees themselves and may have supervisors of their own).

i have another table called "meetings" which contains as records all meetings conducted. for each unique meeting, i want to be able to select the employee and their supervisor. i was originally thinking of linking this table to the "employee" table and getting a drop down box to be able to pick the employee and his/her supervisor.

1) am i going about it the wrong way?
2) any suggestions are helpful.

thanks!
chipy

 

[chipy]

there's gotta be an Access expert here somewhere...

 

[theknight571]

In your first table... is one of the fields a link to the unique ID of their supervisor?

If so, that would make it easy to lookup any employee's supervisor...from pee-on up to head honcho.

Then your drop down box would select the "employee" and you could use that "supervisor" field to lookup (link to) the supervisor's info.

So your "employee" table would contain:

Unique ID
Last Name
First Name
...
SupervisorID <--- contains the Unique ID of this employee's supervisor


You could also create a dummy record for those that have no supervisors.... if needed.

 

[chipy]

OK, so assuming i set up the tables according to your specifications:

EMPLOYEE MEETINGS
------------- -------------
Unique ID Meeting ID
Last Name Unique ID
First Name Supervisor ID
Supervisor ID

once i link the two tables with the Unique ID fields, i can do a drop down on the meetings table (Unique ID and Supervisor ID). but then how would i go about displaying each record with the employee first name, last name, supervisor first name, last name?

thanks,
chipy

 

[chipy]

it's currently 1:52AM local time in Texas. eureka! i think i've got it... took me days upon days to get this but i think i got it. thanks to all who participated, especially theknight571! thanks for your support.

i was able to get it working by first creating a relationship (enforcing refrential integrity) between employees table and meetings table. i linked the supervisor's id from the meetings table to the employee id of the employees table. then i made a query that said "give me everything from the meetings table" along with the first and last names from the employees table. then i created a second query joining the first query to the original employees table and getting the employees first name and last name from there.

in the end, i end up with a unique meeting id, supervisor id, supervisor first name, supervisor last name, employee id, employee first name, and employee last name. all while using only one table to hold both regular employees and supervisory employees.

wow!!! i would have never guess doing that using strictly SQL statements (for example in an Oracle/Linux platform).

 

[theknight571]

Congrats!

Glad I could be of assistance.... well at least I hope I was.