Master table to track record in multiple tables

Posted on 2008-10-08
Last Modified: 2013-11-29
Hi Experts,

Thanks for your time and knowledge.

I have been asked to take a look at a database at work, I have a bit of experience with Access while no-one else at work does. The database stores Occupational Health and Safety Information; Injuries, Incidents, Machinery Maintenace etc from around the farm. When I opened it up, to my surprise it only has one table with with many colums. Each record only fills in part of the table, the section relating to that record. My natural instinct is to divide this table up; but having all the data in one table comes with one huge advantage; each entry is given an autonumber so has a unique identifier that can be noted on the associated paperwork.
As I said above I have a bit experience with Access, but I have never come accross this design before.

Is there a way of maintaining this Master table autonumber record and linking to a number of different tables?

I have started dividing up the main table using make table queries. The main table name is [OHS] with autonumber Primary key [Reg #]
So far, I have create three new tables [HR], [Inspections] and [Observations]. Each has an autonumber primary key ['table name' ID] eg [HR ID] for HR table. I have linked the [Reg #] from the [OHS] table with [Reg #] field in each of the tables and enforced referential integrity. I have also set the indexed property of the [Reg #] field in the child tables to Yes (no duplicates).

Once I have finished I will delete all field from [OHS] leaving only [Reg #] (maybe also a date).

How could I ensure that each time a new record is entered in any of the child tables; a new record is created in [OHS].[Reg #], and that each [Reg #] only relates to one record in one other table?

I am open to any ideas, you guys (and girls) know a lot more than me and any comments on the best deign for the database would be appreciated.

Thanks again.
Question by:dommac82
  • 5
  • 3
  • 2
LVL 10

Expert Comment

ID: 22674280
Are you sure you need to divide up your table? I can't immediately see any great disadvantage to the way it's currently set up. You could always add an IncidentType field (if there's not one already) which would allow you to use queries to pull out all the Injuries or Inspections etc.

Author Comment

ID: 22674376

Thank you for your opinion.

I probably don't need to divide up the table, it was just my first instinct. There is a field [Incident Class] that allows the user to put the record into a catagory, for use in queries etc.  After looking at one of my other databases the main user (our OHS Officer) complained that his table was too big.

Also the file size of this OHS database is significantly larger than others I have created even though mine have more records. Will this affect the performance?

I suppose my main concern is that this is not the type of design I am used to dealing with; I am more used to a relational database with multiple linked tables.

If anyone else looks at this question, your opinion would be valued.

Expert Comment

ID: 22674493
If you are committed to using Access for the foreseeable future there are features that support referential integrity. Tools, Relationships... will provide you with the ability to build the integrity rules.

The rules will most likely block the creation of stray child records but you can also build your forms such that the main table is the data source for the form and all the child data is entered via subforms with master/child links back to the parent. You can use a tab control to logically split up your data and to host the subforms, one per child table.

You may consider leaving the data in one record as long as it doesn't violate relation tables normal forms but still use the tab control to logically split up the data. While having a bunch of fields is a pain building joins all the time to put the data back together isn't a picnic either.

You might post a sample DB with a few records if you need more ideas.

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

LVL 10

Expert Comment

ID: 22674645
Just how big is the table (both in terms of file size and number of records)?

Author Comment

ID: 22674912

At the moment there is only one table in the database, with a bunch of queries and forms.

Unfortunately I cannot post a sample of the database, as this contains information that maybe considered confidential. Not that anyone on this site would use this information, just company policy.

I will do my best to describe the situation; the table contains records for a number of different Incident Class, eg Injuries, Risk Assessments, HR issues, Vehicle accidents, Plant Maintenance, etc. Field [Reg #] is an autonumber, and field [Incident Class] is a lookup from a value list.  Lets take for example an Injury - the record is given an autonumber and date, [Incident Class] is set to INJURY then for example columns A, B, C, D & E are filled in (the actual field names is not the issues here). However for Plant Maintenance the autonumber, Date and Incident Class are set, then columns  F, G, H & I are filled in.


Author Comment

ID: 22674914

The file size of the database is approx 100,000 KB with around 5000 records. The table has 72 columns.

There are also around 70 queries, 10 forms and 35 reports.

See because all the incident classes are in one table, there are a whole lot of queries to split the information out, then another layer of queries for calculations for the reports. This is not my doing, it is just how things are at the moment.
LVL 10

Expert Comment

ID: 22676621
Obviously I don't know how much data you've got in each record, but I'm a little surprised that the file size is so big, especially considering that many of the fields in each row will be blank. Have you compacted the database recently?

In terms of performance, are you actually experiencing slow response times?

And following on from what rheizman was saying, I understand you can't show us your real data, but could you create a new table with the same structure (just copy and paste the table and select 'Structure Only') and create a few fictitious records to post here?

Accepted Solution

rheitzman earned 250 total points
ID: 22679082
> there are a whole lot of queries to split the information out, then another layer of queries for calculations for the reports

This really isn't that bad an approach. In generic terms the subset of columns is called a 'view'.

I think I would just live with it and perhaps redesign the heavily used forms to make them more user friendly.

Author Closing Comment

ID: 31504481
I think I will just leave it the way it is.

Author Comment

ID: 22683724

I have not dealt with many of the forms and/or queries but I don't think that there are slow response times.

If both you and Rheitzman think I should leave it the way it is I will, as I said in my original post you know a lot more than I do.

Do you think I should just leave it?

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question