Merging redundant columns

[sarge89]

Hi,



First I'd like to say, this is my first project using Access, and I'm learning as I go along. Decided to try something different instead of spreadsheeting

On to the question...I'm doing up a database of hazardous chemicals, based on publicly available information from various agencies. Each chemical has a name and a number, and has different hazards associated to it. The hazards are grouped into classes, and for each class the hazard is further broken down into categories. Hence for an explosive hazard (the class) I could have categories 1, 2 and 3 based on severity. For each iteration of the hazard class and category (for example explosive hazard category 1), there is a non-unique hazard statement. Another complication is that there is a qualitative element in the classification system, and hence agencies from different countries could classify the same chemical differently. This should be captured into the database as well. Thus for every chemical, I will have to enter the Japanese classification, the EU classification, etc.

I've managed to set up what I think is a correct relationship, based on my understanding of databases. However when I generate the report, fields such as the chemical name and ID are repeated for each hazard. I would like to merge these redundant entries, so the chemical name, I.D., and any other repeated fields appear only once. In essence, I'm trying to make it such that there is only one entry for each chemical+I.D/country, with multiple hazard subentries, rather than having multiple, seemingly duplicate entries.

I've attached screenshots of what I have currently, to illustrate what I'm trying to get across with this wall of text.



I'm wondering if what I want to achieve can be done simply by tweaking some of the report/query settings, or if there are inherent flaws in the relationship structure of my tables. Thanks in advance for any help

[Beetle]

Couldn't say for sure if there are inherent flaws in your table structure without a more detailed understanding of your particular model, but at first glance I don't see anything glaring. I do wonder about the purpose of the Overall MM table. Each instance of a Country/Chemical (from your table Chemical Country MM) can have multiple hazards, which is logical, but this would seem to indicate just a one-to-many with Hazards MM. Not sure of the purpose of the join table here.

As far as your report goes, it looks like you just need to add some sorting and grouping.

[sarge89]

Couldn't say for sure if there are inherent flaws in your table structure without a more detailed understanding of your particular model, but at first glance I don't see anything glaring. I do wonder about the purpose of the Overall MM table. Each instance of a Country/Chemical (from your table Chemical Country MM) can have multiple hazards, which is logical, but this would seem to indicate just a one-to-many with Hazards MM. Not sure of the purpose of the join table here.

As far as your report goes, it looks like you just need to add some sorting and grouping.

Thanks for the prompt reply. The reason for that join table is that any one hazard can be manifested in more than one chemical. I've attempted to do grouping, however when I do so, the I.D. group is nested within the chemical name group, which shouldn't be the case because name and I.D. are of the same "level", if you get what I mean by that.

[Beetle]

You can move fields around in design view (technically you're moving the Controls that are bound to those fields) to place them in a different group level.