Sorry for my subject vagueness, I just couldn't think of how to summarize my question.

I'm building a database that tracks several different types of incidents. Originally I was going to have one table that had all the fields required for each type of incident, but then I would have several fields only used for a particular type of incident, and this seemed improper to me. Then I decided to separate each incident into its own table, therefore every table would have the fields only required for that type of incident.

Each incident (and thus table) will have some similar fields, like Date, Store, Employee, and I would like to be able to query any of these. For example, I would like to query how many incidents an employee has across Incident Type 1 (a table), Incident Type 2 (a table), etc.

Which way is better, putting it all in one table, sharing the common fields, and then having fields that are unique to different incidents all in there - or separating them out (but then I need to know how to query across.

Thank you.

Use one table for the field common to all incidents and a related table for the others.

The related table has three fields:
The foreign key to the main incident table.
A foreign key to another table of incident attribute types.
A field to hold the value for the attribute.

Ah, if only there were a unique answer.

Using a set of split tables with one for common elements and one or more other tables for the uncommon stuff is one such solution. Here, you would do a JOIN of the common table to one or more of the uncommon tables.

Another is to have different tables but when you want to query them all, build a UNION query. Lots of unique tables, but then use the UNION query to bring the disjoint tables together.

A third view is that as long as the extra data that doesn't apply to every incident can be expressed in short strings or small integers, just include it but use some sort of type-code in the record so that you can do a filtering query to isolate the records into unique and different looks in other sections. This is the exact opposite of the UNION query. Here, you have one table and use queries to make it look like multiple tables.

As a pragmatist, I couldn't say which was right until I looked at a specific problem. There is no guaranteed best way because each of us has a different idea about what is best.

I'm having a problem with displaying adding data into my db, this is down to my level and usage with access but it is something I wish I put more thought into before hand. Having your table structure in a correct format is best way forward but something u need to think on how you are to input/query/output the data.

Ah, if only there were a unique answer.

Using a set of split tables with one for common elements and one or more other tables for the uncommon stuff is one such solution. Here, you would do a JOIN of the common table to one or more of the uncommon tables.

Another is to have different tables but when you want to query them all, build a UNION query. Lots of unique tables, but then use the UNION query to bring the disjoint tables together.

A third view is that as long as the extra data that doesn't apply to every incident can be expressed in short strings or small integers, just include it but use some sort of type-code in the record so that you can do a filtering query to isolate the records into unique and different looks in other sections. This is the exact opposite of the UNION query. Here, you have one table and use queries to make it look like multiple tables.

As a pragmatist, I couldn't say which was right until I looked at a specific problem. There is no guaranteed best way because each of us has a different idea about what is best.

I thought of UNION queries but then I thought all the tables had to have the same number of fields (in my case that wouldn't be true).

@GalaxiomAtHome: I thought of that after I posted my question, on my way home from work. My only problem with it is that I can not figure out how to make that work in a form (i.e. I understand the table structure and relationships, but I can't figure out how to add data through forms).

My only problem with it is that I can not figure out how to make that work in a form.

The attributes are shown in a Datasheet or Continuous Forms subform.

To add records, select the attribute type from a combo and then enter the value in the textbox on the same record.