Go Premium for a chance to win a PS4. Enter to Win


Master table to track record in multiple tables

Posted on 2008-10-08
Medium Priority
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.


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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 750 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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

963 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