I'm working on a MS Access 2016 database that contains information about a collection of recipe books. I have a few tables for different types of data that are linked to each other using many-to-one and many-to-many relationships. Then, I want to export a "table of results" (in Excel format) for each book (via a query), containing a list of recipe names, page, ingredients, "companion" ingredients, utensils and notes. The output should be something like this:
I've tried to do this through the report tool in Access and setting the "Hide replicates" control to "Yes". The problem is that when I generate the report, the data keeps repeating itself, like this:
My question is if there's a way to do what I want, or something similar, without modifying each table one by one, because I have more than 70 books and 7000+ recipes. My query subschema is there:
And the generated SQL:
SELECT N_recetas.no_libro, N_recetas.Id, N_recetas.nombre_receta, N_recetas.pag, N_ingredientes.ingrediente, N_acompanamientos.nombre_acomp, N_utensilios.utensilio, N_recetas.notas
FROM N_utensilios
RIGHT JOIN ((((N_libros INNER JOIN N_recetas ON N_libros.no_libro = N_recetas.no_libro) LEFT JOIN (N_acompanamientos RIGHT JOIN rels_rec_acomp ON N_acompanamientos.Id = rels_rec_acomp.Id_acompanamiento_Fk) ON N_recetas.Id = rels_rec_acomp.Id_receta_FK) LEFT JOIN rels_rec_utensilios ON N_recetas.Id = rels_rec_utensilios.IdRecetaFK) INNER JOIN (N_ingredientes INNER JOIN rels_rec_ings ON N_ingredientes.id_ingrediente = rels_rec_ings.Id_ingrediente_FK) ON N_recetas.Id = rels_rec_ings.Id_receta_FK) ON N_utensilios.Id = rels_rec_utensilios.IdUtensilioFK
WHERE (((N_recetas.no_libro)=78));