Query duplicating records

[grone2001]

Hi all,

I have a bit of a problem with my query. I'll try to explain this in a simple way. Imagine you have a table with authors' names and another one with the books they wrote.

When you put the tables together in a query it comes in like

Dan Brown Angels and Daemons
Dan Brown Da Vinci Code
Other Writer Other Brook1
Other Writer Other Book2

But when I do a report with the count of authors it counts as there are 4 writers when there is only 2. So the query should look like...

Dan Brown Angel and Daemons
Da Vinci Code
Other Writer Other Brook1
Other Book2

I know DISTINCT is the way but when I put DISTINCT in the query it still puts the name of the author for each of his boos, meaning that the pivot table report in Access does the count wrongly. I am sure there must be a simple way around this but I really do not know it. How you guys can help

Thanks in advance,

Best Regards,

Marcel Q Defize

 

[DCrake]

Have you created a join between the tow tables? Also are you using primary and foreign keys for referetial integrity?

 

[JamesMcS]

So you want the author's name to be displayed only once for each group of their books? I don't think you can do that. If you want to count the number of books per author, just create a query and group by author, then do a count of the title field. That will show you wach author once, and the number of books that they've written.

 

[grone2001]

Hey, thanks for the prompt reply! I've been busy like hell hiting my head against the wall with this.

As you know the issue is that when I do a count of authors in a pivor report, it counts as there are 6 authors. I was hoping that you can actually create a calculated field that will count only unique items? Cos that would be the solution to all my problems.

I know it is possible to do it in excel so it might be possible to do that in an access report just as well right?

Do you know what would be the formula for such thing?

Thanks again,