Pulling data from one table to another under conditions?

[Nimrod]

I am hoping an Access guru can help here. I have two tables: Employee and
another as IDCode. The IDCode consists of two letters each, and goes from
AA, AB, AC..AZ. I want only one code to be assigned to one employee.
Furthermore, once a code is used, it needs to be marked as such. If an
employee leaves the department, I want the assigned IDCode to become
available once I remove the employee.

Question is, what is the strategy in getting this done? If this isn't the
proper group, could someone maybe suggest a more appropriate group?

Thanks in advance,
Scott

 

[Guest]

hi,
what else in the IDCode table besides the ID code.

 

[DL]

Just a thought, if the IDCode is the employee specific is it wise to reuse
it if an emplyee has left/moved?

 

[Nimrod]

Just a thought, if the IDCode is the employee specific is it wise to reuse
it if an emplyee has left/moved?

That is a good point, and I thank you for making it. Okay, so here's what I
have:

tblEmployee
EmployeeID Autonumber
EmployeeName Text

tblCode
CodeID Autonumber
Code Text

The "Code Text" is a list of two-letter ID's, AA, AB..AZ, etc. Would I use
a query to determine if a code is assigned? Say I make a list of 100 ID's
and in an employee form, I create a new employee. A combo box should be
used to pull up only the available IDs. As soon as I assign a code to this
employee and the record is saved, the tbleCode should reflect this code is
no longer available.

Is this difficult to do?

Also, I've purchased a couple of how-to books for Access 2003, but is there
any particular book that is considered to be "THE" book on Access?

Thanks again in advance,
Scott

 

[Nimrod]

Just a thought, if the IDCode is the employee specific is it wise to
reuse

That is a good point, and I thank you for making it. Okay, so here's what I
have:

tblEmployee
EmployeeID Autonumber
EmployeeName Text

tblCode
CodeID Autonumber
Code Text

One more thought. I know there's a way to put values into a combo box (ie.
when creating one option is to look up and another is to "type what I
want".). I imagine this would be best, so long as I never remove an
employee (thus re-using an ID code).

Thanks again,
Scott

 

[tina]

if you're never going to re-use employee IDs (probably a good decision), are
you sure you want to limit yourself to a two-letter ID? the table will be
limited to a total of 676 IDs. that may sound like a lot, but even in a
small business, normal turnover could use up those IDs fairly quickly.
unless you're absolutely sure that your database will have a limited
lifespan, it's a good idea (as much as reasonably possible) to identify and
avoid "built-in limitations" that will shorten its' useful life before
significant design adjustments are required.

hth

 

[Nimrod]

if you're never going to re-use employee IDs (probably a good decision), are
you sure you want to limit yourself to a two-letter ID? the table will be
limited to a total of 676 IDs.

Okay, so here is my dilema. How do I create a combo box to display only
those IDs that are available? And the method would have to allow me to
append to it. So for now if I have a list of 676 codes, as soon as I assign
one code, it needs to be flagged as used (thus preventing that code from
appearing when I am creating a new employee record). We've started out with
AA-AZ and BA-BZ.

If I use a table for just ID Codes, I could easily append that. Otherwies,
if i use the "Type what I want" option I have to edit the combo box and add
more (and I don't know if there's a string length limitation for that).

In any regard, any help on this would be appreciated. I've already learned
so much about relationships in these groups that I've made major
improvements over what I had to start with.

Thanks in advance,
Scott

 

[tina]

how about just creating a table to hold all your IDs that you want to use.
you (as the db admin, not as a user) can add to that table whenever you
want. don't bother marking the IDs as used or unused. just create a query
(the query wizard can do it for you) for *unmatched* IDs, based on a
comparison between the IDs table and the employees table: the query should
return only the IDs from the IDs table that *do not* have matching IDs in
the employees table. then on the data entry form, base your ID combo box on
that query. set a macro or code on the control's OnEnter property to requery
the control. that way you'll always be working with a "current" list of
"unmatched" - and therefore available - IDs.

hth