DISTINCT Query Question

[andary]

Hi guys,

Basically I'm wanting a query to produce DISTINCT results for
"gallery.prod_title" only; obviously there are two tables involved.

With the DISTINCT results I'm aiming to print other data like:

contact_details.bus_name
contact_details.subdomain
gallery.prod_linkSm
gallery.prod_title
gallery.prod_desc

Question: WHERE / HOW does this come in to the statement??


The following query is roughly what I'm working with, and I know I've
mixed in too many columns which is 'round about the point where I
through I'd post this question.


--------------------------------------

SELECT DISTINCT gallery.prod_title, contact_details.subdomain,
gallery.prod_linkSm, contact_details.bus_name, gallery.prod_title,
gallery.prod_desc

FROM contact_details, gallery

WHERE gallery.id = contact_details.id

AND contact_details.anzsic = '4241002'
AND contact_details.list_show = 1
AND contact_details.show_gallery > 0

ORDER BY RAND()
LIMIT 5

 

[Allen Browne]

What is the relation between the 2 tables?
Presumably one Gallery can have many Contact_Details.

If so, you are wanting to select all the galleries, and for each one, any
*one* of the contacts (doesn't matter which one?)

1. Create a query using both tables.
Drag Gallery.ID onto Contact_Details.ID.
Access gives a Join line.

2. Double-click the join line.
Access pops up a dialog with 3 options.
Select the one that says:
All records from Gallery, and any matches from Contact_Details.
(This is called an outer join.)

3. In query design, depress Total button on the toolbar.
Access adds a Total row to the design grid.

4. Drag the fields you want from the Gallery table into the grid.
In the Total row under prod_title, accept Group By.
In the Total row under the other fields, you can leave Group By if you want,
or choose First to make the query more efficient. (You really need to use
First for memo fields especially.)

5. Drag the id field from contact_details into the grid.
In the Total row, choose First, or perhaps Min.

6. Save the query. Close.

7. Create another query using this one as an input table, and also your
Contact_Details table. Join the ID fields, and again change it to an outer
join.

8. Add any fields you want to the output grid.

If you want some alternative techniques to do the whole job in a single
query, see:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm