Stuck

[skydiver]

Okay, I'm not sure what I want. Bear with me. I'm hoping you can point me in the right direction. Access 2003. I currently have two tables...one for Employees and one for Family. Both are related via a one-to-many. For every employee, there may be more than one family member hence the relationship. My dilemma is: when an employee leaves, transfers out, or is no longer employed with us how can I have that employee's data automatically populate to a "Former employee" table? I'm trying to avoid creating a separate table for "Former Employees" and having to type the data in manually. I'm just not sure how to go about doing this. Just a little guidance please...just a little guidance.

 

[MarkK]

Consider having a table for People, and then a field (or more) that define those people's roles. Most employees tables I've created determine ex-employees by the value of the LastDayWorked field. If it's Null they're still employed. If there's a date and it's < today, then they've worked their last day.
Cheers,

 

[skydiver]

So, how can I get them to now populate into a separate table? I don't want current/former employees on one form. I would like for them to get kicked out of the "Current" and dumped into a "Former." Make sense? Please excuse me.

 

[MarkK]

So, how can I get them to now populate into a separate table?

What I'm saying is don't put them in separate tables, but rather filter the result set based on criteria like IsNull(LastDayWorked). In each record store the type and status information that allows you to easily return a meaningful subset of record that you'd want to work with.
Then open the form with filter that only shows that subset.

 

[skydiver]

Hmm...sounds interesting. I like your idea instead. Do you have any step-by-step links similar to this?

 

[MarkK]

At it's simplest form, it'll work something like this ...
1) Create a table
2) Add fields you might use to provide a meaningful subset of records, like 'IsEmployee'
3) Create a form that uses the table in 1) as its recordsource
4) Put controls on the form that will display fields from the table
5) Open that form to only show employees using ...

DoCmd.OpenForm "Form3", , , "IsEmployee = True"

...or if you use macros set the "WhereCondition" parameter of the OpenForm action to "IsEmployee = True"
This allows you to have only one table, and only one form, yet enables you to be able to work with an infinite variety of subsets of data.
HTH

 

[skydiver]

I like it. I'll try it. Thanks lagbolt! I'll get back with you.

 

[MarkK]

Yeah, you bet, I'll be around.