Relationships (1 Viewer)

[waleedimtiaz]

Is this relationship correct? Tables are normalized now?

 

[plog]

Here's some context everyone:

How to have a report with more than 2 pages?

Hey everyone, I am a beginner in MS Access. I want to make a report with around 4-5 pages, but Access limits me to make a report within 2 pages only. I tried to make two different reports and then tried to combine them as subreports, in the REPORT VIEW I can see all 4 pages, but in the PRINT...

www.access-programmers.co.uk

Customized Checkboxes

Hi everyone, I am new to MS Access. I just wanted to whether I can make my own Data checkboxes under a specific field. In Access there is only Yes/No check boxes, I did use one approach by declaring Data as Fields and used Yes/No check box for each field. But I think this not the right way to...

www.access-programmers.co.uk

Initially posted about a report not fitting to 2 pages, then posted about having a ton of check boxes. Pat Hartman sniffed out a poor structure and now we are here.

Now to this post--no, it is not normalized. The big issue I see is that you have used values that should be in a field as names of fields. All those Yes/No fields should not exist. Let's take Incident Causes as an example. Instead of every possible cause as its own field you should only have one field and in that field you put the cause. If an accident has 4 causes you don't tick four of the fields in Incident Causes, you put 4 different records into Incident Causes--one for each cause.

This should be the structure of the Cause table:

Cause
causeID, autonumber, primary key
ID_Accident, number, foreign key back to Initials
causeType, text, this will hold what is now all those field names

That's it, those 3 fields are all you need to hold all the cause data. Like I said before, if you have 5 causes you put 5 records into that table.

Corrective Actions seems to have the same issue. Same with having 2 fields for Male and Female--you don't have 2 yes/no fields, you just need one field you stick a M or F in.

You have made other normalization mistakes as well. I suggest you read up on it:

Database normalization - Wikipedia

en.wikipedia.org

Then google a few tutorials and work thru them. Then apply what you learn to your own data.

 

[waleedimtiaz]

Here's some context everyone:

How to have a report with more than 2 pages?

Hey everyone, I am a beginner in MS Access. I want to make a report with around 4-5 pages, but Access limits me to make a report within 2 pages only. I tried to make two different reports and then tried to combine them as subreports, in the REPORT VIEW I can see all 4 pages, but in the PRINT...

www.access-programmers.co.uk

Customized Checkboxes

Hi everyone, I am new to MS Access. I just wanted to whether I can make my own Data checkboxes under a specific field. In Access there is only Yes/No check boxes, I did use one approach by declaring Data as Fields and used Yes/No check box for each field. But I think this not the right way to...

www.access-programmers.co.uk

Initially posted about a report not fitting to 2 pages, then posted about having a ton of check boxes. Pat Hartman sniffed out a poor structure and now we are here.

Now to this post--no, it is not normalized. The big issue I see is that you have used values that should be in a field as names of fields. All those Yes/No fields should not exist. Let's take Incident Causes as an example. Instead of every possible cause as its own field you should only have one field and in that field you put the cause. If an accident has 4 causes you don't tick four of the fields in Incident Causes, you put 4 different records into Incident Causes--one for each cause.

This should be the structure of the Cause table:

Cause
causeID, autonumber, primary key
ID_Accident, number, foreign key back to Initials
causeType, text, this will hold what is now all those field names

That's it, those 3 fields are all you need to hold all the cause data. Like I said before, if you have 5 causes you put 5 records into that table.

Corrective Actions seems to have the same issue. Same with having 2 fields for Male and Female--you don't have 2 yes/no fields, you just need one field you stick a M or F in.

You have made other normalization mistakes as well. I suggest you read up on it:

Database normalization - Wikipedia

en.wikipedia.org

Then google a few tutorials and work thru them. Then apply what you learn to your own data.

Thank you for your thorough response. I could do that easily, that's not a problem. The problem is I want to have these causes listed in the Form so the user can choose from these mentioned causes, rather than manually entering the causes by himself. The user should only select the cause from the list of causes. Or may be I can make a Combo Box or a List Box. The main application here is the Form where user could choose these causes by just selecting them.

 

[plog]

The main application of a database is data. Data dictates how you setup your tables, not anything downstream like forms or reports.

Get the foundation correct or you'll forever be hacking together fixes for everything else.

 

[Pat Hartman]

Thank you for your thorough response. I could do that easily, that's not a problem. The problem is I want to have these causes listed in the Form so the user can choose from these mentioned causes, rather than manually entering the causes by himself. The user should only select the cause from the list of causes. Or may be I can make a Combo Box or a List Box. The main application here is the Form where user could choose these causes by just selecting them.

DID YOU EVEN LOOK AT WHAT I WASTED MY TIME BUILDING FOR YOU? The user clicks a check box, EXACTLY as in your unnormalized version. YOU are the one who has to build the question table and i pretty much did that for you as well.

You have been told by SIX experts that your schema is wrong. There is no difference of opinion among us. Your schema is totally wrong. I took the time to show you what the schema should look like at a basic level. I even built forms to show you that you CAN get the visual you want using a properly normalized schema.

Have you given ANY thought to what happens to this data after you collect it? What is the point of collecting this type of data if you aren't going to have to analyze it? Just "try" to analyze anything given your current schema.

 

[Pat Hartman]

This should be the structure of the Cause table:

Looks like you didn't examine the model either. This is a version of the questionnaire model. The types don't need separate tables. They just need to be properly grouped so the related questions can be selected together. Appending all the questions to each incident is what allows the checkbox and the visual the OP wants WITHOUT hardcoding ANYTHING.

 

[Pat Hartman]

@waleedimtiaz I didn't mention this earlier because you have so much rework, I didn't want to add to the burden but I would also not use the attachment data type. It cannot be upsized and this database is a candidate for upsizing so fix it now or fix it later. Just use a many-side table and manage the attachments there. You can include a type which will allow the scanned objects to be grouped on forms. If the BE is SQL Server, you can store the scanned objects as blobs. But if the BE is ACE, it is better to just use a path and not store the graphic objects in the database.

 

[waleedimtiaz]

DID YOU EVEN LOOK AT WHAT I WASTED MY TIME BUILDING FOR YOU? The user clicks a check box, EXACTLY as in your unnormalized version. YOU are the one who has to build the question table and i pretty much did that for you as well.

You have been told by SIX experts that your schema is wrong. There is no difference of opinion among us. Your schema is totally wrong. I took the time to show you what the schema should look like at a basic level. I even built forms to show you that you CAN get the visual you want using a properly normalized schema.

Have you given ANY thought to what happens to this data after you collect it? What is the point of collecting this type of data if you aren't going to have to analyze it? Just "try" to analyze anything given your current schema.

Just thoroughly went through the database........Thanks a lot for your efforts. Now I could get a head start.

 

[Pat Hartman]

Let me know when you build that second subform