Aggregate Function Problem

[chris_f]

Hi All,

Can you please help me with an access sql query I am writing in ASP. I am trying to return data on a product, it's last review and the total number of reviews. The problem is something to do with the nesting but I think I'm going about it the wrong way.

Tables
Product
ProductID
Name
Description
Manufacturer
StartedOn
......

Reviews
ReviewID
ReviewBy
ReviewDate
ReviewTime
ReviewContent
......

I want to return the following:
ProductId, Name, Description, Manufacturer, StartedOn, TotalReviews (A count of the number of reviews), LastReviewBy, LastReviewDate, LastViewTime

Any Ideas? I keep getting errors about aggregate queries and I can't find a solution anywhere.

Chris

[edited by: chris_f at 12:31 pm (utc) on June 13, 2003]

[WebJoe]

Hi

Try this:

FROM Prod
WHERE Prod.CategoryID = " & Request.QueryString("id") & " 

no need to have the table posts in your outer statement.

[chris_f]

Hi WebJoe,

I have re-written the above to make more sense. What you are proposing doesn't bring back all the information I need. It only brings back the product info.

Chris

[korkus2000]

Which join are you using? I am assuming you need both the product and reviews tables.

What table are these coming from?
TotalReviews (A count of the number of reviews), LastReviewBy, LastReviewDate, LastViewTime

Are you using a count query for TotalReviews?

[chris_f]

Thanks for the responses. I've just resolved the problem. I started at the begging and built each query piece by piece.

Thanks for you assistance
Chris