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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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.

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

635 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