Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1063
  • Last Modified:

Master table to track record in multiple tables

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
dommac82
Asked:
dommac82
  • 5
  • 3
  • 2
1 Solution
 
calpurniaCommented:
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
 
dommac82Author Commented:
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
 
rheitzmanCommented:
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
 [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

 
calpurniaCommented:
Just how big is the table (both in terms of file size and number of records)?
0
 
dommac82Author Commented:
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
 
dommac82Author Commented:
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
 
calpurniaCommented:
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
 
rheitzmanCommented:
> 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
 
dommac82Author Commented:
Thanks.
I think I will just leave it the way it is.
0
 
dommac82Author Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now