Solved Take repeating records (1 Viewer)

[zelarra821]

Hello.

Let's see if someone can help me.

@MajP made me an example to, for example, save multiple authors in case a book had more than one. I have tried to contact him, but there is no way, I suppose he will be busy.

It consists of having a union table (TLibrosAutores), where the Id of the book (from the TLibros table) and the ID of the author (from the TAautores table) are stored.

In the CLibrosAutores query it is the same as the TLibrosAutores table, but adding the fields of the Book and the Author.

InConsulta1 I show how it would work in a form. Basically, it is to use a function created for this specific case that what it does is concatenate the authors there are for a book and display it in a text field. I have two fields, one for the id and the other for the author.

What I want to know is if an author is repeated in more than one book.

What is the problem? That a book can have an Author 1 and an Author2, and only Author1 have written another different book. In this case, Author1 should come out as a repeated author, and author2 not.

I don't know if I have managed to explain myself.

Thanks

 

[zelarra821]

I have update the database so you can see how it works.

 

[MajP]

Not sure what display you want. But if you want Authors and there concatenated books then you have to do another query. I do not think there is a logical way to show that in one form. You could have possibly two subforms side by side

SELECT tautores.idautor,
       tautores.autor,
       Concatrelated("titulo", "clibrosautores", "idautor =" & [idautor]) AS
       Libros
FROM   tautores;

Query2 Query2

IDAutor	Autor	Libros
1​	Autor1	Libro122
2​	Autor2	Libro40 Libro103 Libro169
3​	Autor3	Libro39
4​	Autor4	Libro173
5​	Autor5	Libro232
6​	Autor6	Libro129 Libro153

 

[zelarra821]

what I want is to show books whose authors I have more books

 

[arnelgp]

you already have it in FLibros form?

 

[zelarra821]

you already have it in FLibros form?

I don't understand what you want to say

 

[arnelgp]

I don't understand what you want to say

Flibros will show you the books whose autor (post #4)?

post #3 will show you the autor of what book he autored/co-authored.

 

[zelarra821]

In FLibros I have the books and for the authors what it does is concatenate according to what is in TLibrosAutores, that is, they are not in the TLibros table

 

[MajP]

Not sure how you want to display this. Can you make a demo in word or Excel? How this is displayed. Is number of books next to the author good enough? Do you want to see the books? If so where?

 

[arnelgp]

TLibrosAutores, that is, they are not in the TLibros table

then why did you create the Junction table?
that is the purpose of the table.
to record multiple autor and multiple books.

if you will only use the autor and the book table,
there is no connection between them.

 

[zelarra821]

Well, I have achieved it by creating two queries.

A first, where I count the authors, and a second, where I relate the authors to the books. With that it is good for me to solve my problem.

 

[arnelgp]

What is the problem? That a book can have an Author 1 and an Author2, and only Author1 have written another different book. In this case, Author1 should come out as a repeated author, and author2 not.

good! i think you understand the problem.

 

[zelarra821]

You will allow me one last question.

Is it possible to embed Consulta1 in Consulta2? That is, have only one query, not two.

I have embedded when it was a simple FROM TLbooks, putting FROM () As Query1, but not when they are related.

 

[zelarra821]

I have solved it

SELECT CLibrosAutores.IDLibro, CQuery.CountOfId
FROM (SELECT IDAutor, Count(IDAutor) AS CountOfId FROM CLibrosAutores GROUP BY IDAutor)  AS CQuery INNER JOIN CLibrosAutores ON CQuery.IDAutor = CLibrosAutores.IDAutor
WHERE (((CQuery.CountOfId)>1));