Creating Database

[ThorstenG]

Hi its been a while since I've been on here..

I just have a couple of questions if someone is able to help me and without trawling through hundreds of links and how to's...



Am trying to create a Database where I have a number of records all with unique details.
From this I basically need an approval process.. So a record is created through a Form then entered into the main table tMainRec, from there on wards I dont know what to do...

Do i need a separate table with all the Departments that need to approve the new record, and how do I link this to the tMainRec? i.e. new columns for each department with a yes or no within it?

how would I see how each record is approved.. or if it is indeed approved? if anyone has a sample it would be even better.

Many thanks.

Thorsten

[ranman256]

Yes, I would have a tDept.

[Dept], [Manager]
Accting, Bob
Maint, Jerry
..etc

then in the form youd have a combo box with data from the tDept table.
when users enter data into a record, they would pick the dept from the list. (no join needed)

The record would have [APPROVED] field, (using the managers name?) this too could be a combo to pick from.

[ThorstenG]

Yes, I would have a tDept.

[Dept], [Manager]
Accting, Bob
Maint, Jerry
..etc

then in the form youd have a combo box with data from the tDept table.
when users enter data into a record, they would pick the dept from the list. (no join needed)

The record would have [APPROVED] field, (using the managers name?) this too could be a combo to pick from.

Hi ranman,

thanks for your reply - ive attached a picture of the database (all sensitive info has been taken out) thats as far as i've got but am unsure how to link the two?

Could you possibly have a look please?

Cheers

[ThorstenG]

Also... more than one department has to approve the record.. how would I work that in?

[LaughingBull]

Watch these videos on table relationships https://www.youtube.com/watch?v=ooblwJm16Gs and https://www.youtube.com/watch?v=WtEj_Fb6eaw

[rpeare]

If the departments that must sign off on a project are the same for every project you can set up a default structure for every new project. If each project requires a different set of departments to sign off you can do it through data entry, but either way you *should* use a junction table to connect tMainRec to tDepartment

i.e.

tblMainDeptJunction
MDJ_ID  MR_ID  D_ID  Pers_ID  Signoff_Date ---> other signoff related fields

PK = Primary key
FK = Foreign key

Where MDJ_ID is the PK of the table, MR_ID is the main record FK, D_ID is the department FK, Pers_ID is the personnel FK and the signoff_Date is self explanatory