Need help with conceptual design... (1 Viewer)

[Sergeant]

Hello Everyone,
I have been eaves-dropping on your Q&A for a couple of weeks now. This place is just so full of information...I'd like to thank you all for just being here.
Now the question...
I have been tasked (I reluctantly raised my hand at a meeting) with building an Access database for my Squadron to track personnel data. I got off to a great start, but it is starting to get complicated now. Here are the requirements...

1) Track Performance Reports...They are required annually, there are deadlines, they must be circulated to several offices before being finalized, and we need visibility as to report location, date, and status in real time.

2) Track supervisor, date supervision began, duty title, etc...These can change, and when they do, I would like to create a suspense which will create a job list for our info mgr's to update in the BIG database (Not for the faint-hearted). Once these updates are made and verified in the BIG database, then a form would allow the data to be replaced in the main table.

3) There will be many users, spread over a LAN. Need different permissions for different people. Some of them are dangerous.
The data needs to be protected.

The list of requirements is long, but the above points are the heart of the system.

I managed to accomplish most of this, but I think I am going about it the hard way, so I'd appreciate any concept ideas that you might have. Need a sanity check.
Thanks,
Sarge
p.s. I do have specific questions, but I thought I'd throw out the concept question first, as I am sure I am making this too complicated.

 

[Rob.Mills]

Sounds like you're off to a good start. What is it you need help with?

 

[Sergeant]

Thanks for jumping in...

O.K. here is a good place to start...
When I want to make changes that have to be validated by my I.M.ers, could I have those proposed changes populate a different table, one that would be empty until there were changes pending?
i.e...
Working on a form from the main table, I want to change who someone's supervisor is, but I don't want those changes posted to the main table yet. I would like them to show in the suspense table until the I.M.ers load it into the BIG (unfriendly) database, and then they could validate the suspense, which would transfer the pending changes to the main table and replace the original data for certain fields in the record.

Here's what I tried...
I made a worksheet form with a combo box to select the individual from the "name" column. When the data comes up, I use another combo box to select the new supervisor from the "name" column. I enter the effective date in an unbound text box named "Effective Date", and press a button which takes me to another form, bringing the selected record data and proposed changes with it. This new form is linked to additional fields in the main table, such as "New Supervisor" , "Effective Date", and an automatic tick box that indicates a supervisor change is pending.
I have yet another form for the I.M.ers to get the data to update the BIG database. A button opens another form, which has a button that replaces the "Supervisor" field with the "New Supervisor" info and the "Date Sup'v Began" field with the "Effective Date" info. It also clears the pending data fields and resets the tick box to "0", removing the record from the I.M.ers query.

Is this a very awkward way to go about it, or is it just me? There is probably a function that I am not aware of that will make this easier.

I crashed into Access about two weeks ago, and the methods used above probably attest to that. I am, however, a very fast and willing learner.
Thanks a bunch,
Sarge