0

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:

Desired result

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:

Attained result

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:

DB query schema

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));

1 Answer 1

0

In a database, each row must contain every information of the column. Each row is independent of the all other rows. You cannot have excel like structure here.

enter image description here

Here the second row doesn't contain the information about ID, nombre_receta, pag, etc. that's why it is showing as independent row.

You should write the other column information also in the second row.

Your rows should look like this:

UniqueID,8154, Batidillo, 22, Sal, Tortillas

UniqueID,8154, Batidillo, 22, Sal, Guajes

And the unique ID will be the primary key and should be different.

Then you can query your database.

If you think that you are repeating the information in each row, then consider using a different table for repeated information and make a column containing the row ID of the table.

see the section 'Example data in non-normalized form' here to familiarize yourself. https://support.office.com/en-us/article/move-data-from-excel-to-access-90c35a40-bcc3-46d9-aa7f-4106f78850b4

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .