Solved

Master table to track record in multiple tables

Posted on 2008-10-08
10
1,050 Views
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.
0
Comment
Question by:dommac82
  • 5
  • 3
  • 2
10 Comments
 
LVL 10

Expert Comment

by:calpurnia
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.
0
 

Author Comment

by:dommac82
ID: 22674376
Calpirnia,

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.
0
 
LVL 7

Expert Comment

by:rheitzman
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.


0
 
LVL 10

Expert Comment

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

Author Comment

by:dommac82
ID: 22674912
Rheitzman,

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.

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:dommac82
ID: 22674914
Calpurnia,

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.
0
 
LVL 10

Expert Comment

by:calpurnia
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?
0
 
LVL 7

Accepted Solution

by:
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.
0
 

Author Closing Comment

by:dommac82
ID: 31504481
Thanks.
I think I will just leave it the way it is.
0
 

Author Comment

by:dommac82
ID: 22683724
Calpurnia,

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?
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now