Group repeating records in a query - Database about chemical products

[david.cortes]

Hi everyone! I am creating a database to manage information about chemical products and I hope you can help me with this problem.






As you can see in the first table (tblChemicalProducts), all the products have a primary hazard, but not all of them have a secondary hazard, so I created another table to store the information about secondary hazards (see Figure 2) and made a relationship with the main table through the SubstanceID field (see Figure 3). This is because a product may have a single secondary hazard, several secondary hazards, or no secondary hazard.






For example, as you can see in the second table (tblSecondaryHazards), Sulfuric Acid (SubstanceID=1001) doesn’t have a record associated with it because it doesn’t have a secondary hazard. Methanol (SubstanceID=1002) has one record associated with it because it has only one secondary hazard. Finally, Chlorine (SubstanceID=1003) has two records associated with it because it has two secondary hazards (it's an oxidizing substance and also corrosive).

My problem is that I don’t know how to create a query that returns a table like the one shown in Figure 4:




As you can see, I need to group all the secondary hazards of a product in one single record of the query, but right now I am obtaining a different result (like the one shown in Figure 5). I don’t want that the products with two or more secondary hazards appear repeated in the query. Does someone know if that is possible? Do I have to use SQL code to do that? Thanks in advance.

[June7]

Could use VBA code to build a comma separated string of SecondaryHazard data and display in a single field. Review http://allenbrowne.com/func-concat.html

Forcing each secondary hazard into its own field would utilize a complicated CROSSTAB query. How many secondary hazards could be possible?

[ArviLaanemets]

Change database structure to something like:
tblChemicalProducts: SubstanceID, Formula, Description;
tblHazards: HazardID, Hazard, [HazardType];
tblChemicalHazards: ChemicalHazardID, SubctanceID, HazardID, [HazardType]

Depending on hazards having always same type (i.e. primary or secondary), or having the type depending on chemical, you'll have field for HazardType either in hazards table or in chemical hazards table.

[david.cortes]

Thank you very much! I used the VBA code from the link that you gave me and was able to do exactly what I wanted to achieve.