Using an sql statement to return all titles given more than one author 1

[ChrisNome]

alright so here's a simpler problem that hopefully someone can answer:

I have a titles to authors many to many relationship and a qry that selects as such:

select ID, Title, MM.AuthorFK, Author ORDER BY title

so brings up a blocks of a title in the query with a line for each author in the block

If you wanted to return every title that had two distinct MM.AuthorFK's, how would you write that in the WHERE clause? The following won't return anything cause each author is on a different line:

SELECT DISTINCT ID, Title from QRY WHERE MM.AuthorFK = 1 AND MM.AuthorFK = 2 ORDER BY Title

???

 

[dhookom]

How about more accurate table and field names as well as relationships.
This can't work since there is no FROM clause:

select ID, Title, MM.AuthorFK, Author ORDER BY title

Duane
Hook'D on Access
MS Access MVP

 

[ChrisNome]

just trying to be brief but here ya go:

SELECT tblTitles.ID, tblTitles.Title, tblMM.AuthorIDFK, tblAuthors.Author FROM qryTitlesAuthors ORDER BY tblTitles.Titles

tblMM is a many-many table linking Titles and Authors

this produces a query with blocks of rows with the same title, each row showing a different author for that titles

I want to do a select statement on this query that will return every title given 2 or more different authors but can't seem to get how to write that in a WHERE clause since every author is on a different line.

 

[dhookom]

Your SQL doesn't seem to match field names since you have tblTitles.Title and tlbTitle.Title[red]s[/red].

SELECT tblTitles.ID, tblTitles.Title, tblMM.AuthorIDFK, tblAuthors.Author 
FROM qryTitlesAuthors 
WHERE tblTitles.ID IN 
(SELECT tblMM.TitleID
 FROM tblMM
 GROUP BY TitleID
 HAVING Count(*) >=2)
ORDER BY 2

Duane
Hook'D on Access
MS Access MVP

 

[ChrisNome]

Sorry for the typo i know how much of a difference that makes. Didn't copy and paste it, just typed it out so that's not the issue. I'll try your code though, there's some SQL language in there that I've never used, only seen in books, so hopefully that's a good sign!

Thx for your time!

CN

 

[ChrisNome]

so where would you designate the two authors you want to return titles based on in the SQL statement? I have them in comboboxes, cboAuth1, cboAuth2, and thought I needed a WHERE clause

 

[dhookom]

I thought you were looking for any two or more authors not two specific authors.

I just created a query in the Northwind sample database of the Orders and Order Details tables where an order contained 2 specific productIDs from combo boxes on a form.

The Orders table is like your tblTitles and Order Details like your tblMM.

SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Count(Orders.OrderID) AS CountOfOrderID
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE [Order Details].ProductID=[Forms]![frmProdCombos]![cboProd1] Or [Order Details].ProductID=[Forms]![frmProdCombos]![cboProd2]
GROUP BY Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate
HAVING Count(Orders.OrderID)=2;

Duane
Hook'D on Access
MS Access MVP

 

[ChrisNome]

i apologize if it was ambiguous. The idea is the user can select as many authors as they want from a group of comboboxes and have a list box show a list of all the titles in the db where their personally selected authors collaborated on a title by setting the rowsource of the listbox to an sql statement built from the comboboxes. I could do one author easily by selecting from the query with a simple WHERE clause (ie. "SELECT DISTINCT tblTitles.ID, tblTitles.Titles FROM qryTitlesAuthors WHERE [AuthorID]=" me!cboAuth1 & " ORDER BY tblTitles.Title;"). I'll try your strategy above though using the Count(Orders.OrderID) AS CountofOrderID, GROUP BY, and HAVING statements are all new to me

 

[dhookom]

select as many authors as they want from a group of comboboxes

Can you be a bit more explicit regarding the possible number of combo boxes and if the results should match all or just more than one or what?

When you are using 2 or 3 or 4 or ... combo boxes, it gets easier to build this with code.

Duane
Hook'D on Access
MS Access MVP

 

[ChrisNome]

there are 3 comboboxes and the user can use as many as them as they want. The resulting titles must have all authors in comboboxes that aren't null. so if the user chooses one author he'll get a list of all titles by that one. if the user chooses 3 authors he/she gets a list of all titles by ALL 3, and that's where I ran into my problem

 

[dhookom]

so here's a simpler problem

the Count(Orders.OrderID) AS CountofOrderID, GROUP BY, and HAVING statements are all new to me

have them in comboboxes, cboAuth1, cboAuth2

there are 3 comboboxes and the user can use as many as them as they want

I would solve this with code by building a SQL statement based on the combo boxes and then updating the SQL property of a saved query. Do you understand anything regarding vba code?


Duane
Hook'D on Access
MS Access MVP

 

[ChrisNome]

yea I'm pretty good with vba and am working on using the setwindowpos() api to try and keep an access pop-up form always on top of an ie if you want to check out my other threads .

I'm good with code and actually developed an algorithm using recordsets that will solve this problem. But when the database gets really big and many other paramters are added it can take way to long to run it.

I know there's a way to write an sql, unfortunately I haven't delved too deeply into advanced querying techniques so am confused on what a statement is that would return the results I want. I originally tried a recursive subquery thing to try and solve this problem and wreaked and crashed the db way too many times so i just tried to do it with code algorithms, but it can get really slow.

So I tried entering the sql code analogous to mine into a query on my db and it appears that there is a syntax error on the inner join. dunno if that's my error or not. but to get my understanding correct, the count() function will count the number of records in the query with the same fields, the WHERE clause will select only those records in the query that have one OR the other author, the GROUP BY clause will condense all the titles that are listed in two records (ie have BOTH authors together). What happens to the single instances where only one author is present? Are they ommitted by the GROUP BY clause or still included as single records with the grouped records?

 

[dhookom]

Pseudo code
- Count the number of combo boxes that aren't NULL
- Begin creating a SQL statement like:

SELECT tblTitles.ID, tblTitles.Title, tblMM.AuthorIDFK, tblAuthors.Author 
FROM qryTitlesAuthors 
WHERE

- Loop through the combo boxes to add the values into the where condition
- finish the SQL Statement with HAVING COunt(*) = " & count of non-null combo boxes
- set the SQL of a query to the SQL statement:

CurrentDb.QueryDefs("Your Query Name").SQL = strSQL

Duane
Hook'D on Access
MS Access MVP