Normalising Database but unsure how in this case

griffo

Board Regular
Joined
Apr 19, 2004
Messages
140
Hi there

I'm trying to create a database that will ultimately serve as the source for mail merging Word documents.

So far I've got a table for Companies, Positions and Policies&Procedures, with appropriate Primary Keys. They look something like this (scaled down)

tblCompany (PK CoID) Fields - CoName, CoAddr1, CoAddr2, CoPhone etc
tblPositions (PK PosID) Fields - PosCoName, PosTitle
tblPolicies&Procedures (PK PPID) Fields - PPCode, PPName, PPDescription

What I was hoping to avoid was having to create a Table for each Policy&Procedure, as this number will grow continually over the next 6 months or so as we get different things finished. Other users of the system won't necessarily know how to create a table/form/query etc (unless there is an easy way to create a table and subsequent through a form that I haven't come across before).

To illustrate, lets say I have 2 forms: Annual Leave and Sick Leave.

On the annual leave form, there will be 3 mail merge fields - CompanyName, 1stAuthoriser (fed from PosID) and 2nd Authoriser (fed from PosID, but different to 1stAuth).

On the Sick leave form, there will be 2 mail merge fields - CompanyName and AuthorisedBy (fed from PosID).

Is there some way I can structure my database to capture these differences without having to create separate tables for each form - bearing in mind the ultimate outcome is to mail merge to a Word doc with the data source being a query that isolates one company in the recordset?

My first attempt at a table to capture this looks something like:
tblPPDetails (PK PPDetailID) Fields - PPID, PPMergeFieldName, PPMergeFieldValue

Am on the right track, or do I need to have a table for each form?

Thanks
Griffo
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

bdb04

Board Regular
Joined
May 22, 2006
Messages
58
There is a good chance I am misreading what you are saying, but you seem to have the idea right. You most certainly don't want your users creating their own forms/tables/queries. You can create a form for users to enter new Policies and Procedures as they are updated as well as remove outdated policies. All of that would be done in the P&P table you suggested...your design seems fine.

I guess where I am lost is why you are using the forms for the mail merge. Although I haven't done it personally (I generally do my merges from Word and call the query in Access), you could have one form for all of your mail merges with command buttons that start each merge that you want to do. I don't know how many you are talking about but if it is only a couple, this method should work fine.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,104
Messages
5,599,746
Members
414,333
Latest member
willfrederick

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top