I have an Access database with four tables: Products, Chapters,
Competences and Brochures. Actually I got more tables, but I start
with this to simplify my problem. To each product there is a number of
brochures in different langauges, but I only want to show one single
line per product. So, despite that my first product have five
brochures, I still only want to show one line with data for that
product. My code below mentioned will show me five lines with data.
That's my problem.

--- start code ---
SELECT DISTINCT Products.Pro_Name, Products.Pro_Number,
Products.Pro_ID, Chapters.Chapter_ID, Chapters.Chapter,
Competences.Competence_ID, Brochures.Bro_ID
FROM (Competences INNER JOIN (Chapters INNER JOIN Products ON
(Chapters.Chapter_ID = Products.Chapter_ID) AND (Chapters.Chapter_ID =
Products.Chapter_ID)) ON Competences.Competence_ID =
Products.Competence_ID) INNER JOIN Brochures ON Products.Pro_ID =
Brochures.Pro_ID
GROUP BY Products.Pro_Name, Products.Pro_Number, Products.Pro_ID,
Chapters.Chapter_ID, Chapters.Chapter, Competences.Competence_ID,
Brochures.Bro_ID, Products.Pro_Number
HAVING (((Competences.Competence_ID)=2))
ORDER BY Chapters.Chapter_ID, Products.Pro_Number;
---end code---

I don't undertstand though, if you have 5 brochures for a given
product, which brochure.id etc do you expect to be returned if you
only show one line?  If you don't care and just want the very first
one, just turn "select" into "select top 1". I think top works in
access.

Otherwise I think you need clarify what you really want in the results.

Thanks for your comment, frankcorrao-ga!

I have not tried your proposal with "select top 1" yet. I need to
check whether there is a brochure or not for each product. If there is
one or more brochures, I create a link to another page, where I put
all brochures for the product in question. I want to make the link
with this additional peace of code:

If isnull(objRs("Bro_ID")) Then
  strBroPic = ""
Else
  strBroPic = "<img src=""../../images/icons/brochure.jpg"" alt=""Brochure"" />"
End If
 
My goal is to have a product page where the icon is showen only if
there is a brochure or not - and still only one line per product. Is
my problem/task more clear now?

I will try top 1, but please reply if you got additional comments.
Thanks in advance.

Dear frankcorrao-ga,

I have tried your proposal with "select top 1", but this only returns
the first product line.

Instead I have done som testing with this code below, but this one
only takes products where there is one or more brochures related. This
is a problem, because some of my products don't have brochures
related, but could have video instead. And I need to show my whole
product list.

Maybe you can help me with modifying the code, so it will take all my products.

--- code start ---
SELECT DISTINCTROW Products.Pro_Name, Count(Brochures.Bro_ID) AS AntalOfBro_ID
FROM Products INNER JOIN Brochures ON Products.Pro_ID = Brochures.Pro_ID
GROUP BY Products.Pro_Name
ORDER BY Products.Pro_Name DESC;
--- code end ---

Hi

i am not much into access, i m a man of sql server and in sql server i
may have solved this problem in following way -

1 - create a stored procedure
2 - SP should take an argument - PrevBroID. It may be null
2(a) if PrevbroID is null then fetch first BroID (if available)
2(b) if PrevbroID is not null then return next avlible BroID to passed
BroID i.e. prev BroID

each n every time ur link is clicked, it should call this SP passing
as argument the BroID it is representing.

Hope i am clear.

utsav_verma-ga!

Thanks for your participation in solving my problem. I'm not that good
at SQL, so could you maybe write a bit of the code for SP - or maybe
show me an example of a similary task - if you got one. Thanks in
advance.

It sounds like you're using access for web which is probably not the
best idea.  You can download a free copy of sql server 2005 express
edition from microsofts website http://www.microsoft.com/sqlserver. 
see query below.

select Products.Pro_Name, Products.Pro_Number, Products.Pro_id,
Chapters.Chapter,Competencies.Competence_ID,  (select top 1
Brochures.Bro_ID from Brochures where Brochures.Pro_ID =
Products.Pro_ID) as Bro_ID from Products left outer join Competencies
on Competence.Competence_ID = Products.Pro_ID
left outer join Chapters on Chapters.Chapter_ID = Products.Chapter_ID
where Competence.Competence_ID = 2
order by CHapters.Chapter_ID, Products.Pro_Number