Query question, etc.

[lemieux66c]

Hello guys,

I was just wondering if you could help me out with something. I actually have several questions, but I believe that this is the right section to post this particular question – if not, please let me know. Now, for a little background on what I have done/trying to do….

I imported an excel spreadsheet that contained a list of books and corresponding information (such as ISBN #, title, author, etc.) that are being used in all the public schools in my state. I created a separate form that has all the contact information for the publishers. What I’m trying to do is to create a query that only selects books that appear in the list a certain number of times – such as 4 or more. The reason behind this is that my supervisor would like to contact the publishers to see if they are willing to donate books for the library (which is for education students).

My query as currently designed pulls up the ISBN number of the book, the title of the book and contact information for the publisher (while only pulling up the ones listed 4 or more times). My problem occurs because the titles listed do not always match. Thus, a book may be listed 4 times but it does show up in my query because the title is spelled three different ways as the data I received was manually entered from each school district and they entered the information how they wanted to. I need the title to be included but including it ruins my query.

Does this make any sense? If so, is there a way to solve the problem without changing all of the titles of the books? I wouldn't mind if the titles were listed twice as long as the accurate count of the ISBN number occurred. I don’t know too much about Access, but several forum members helped me a few years back when I last used the program, and I was thinking you might be able to help me again.


Thanks.

 

[DCrake]

I think the first thing to do is to normalise the book names. I take it that the ISBN is the same for each variation.

Step 1:

Function ResetBookTitles()

Dim Rs1 AS DAO.Recordset
Dim Rs2 As DAO.Recordset
Dim StrISBN As String
Dim StrBookTitle As String

Set Rs1 = CurrentDb.OpenRecordset("Select ISBN From TblBooks Group By ISBN Order By ISBN;")

Step 2

If Not Rs1.EOF And Not Rs1.BOF Then
   Do Until Rs1.EOF
        StrISBN = Rs1("ISBN")
        Set Rs2 = CurrentDb.OpenRecordset("Select * From TblBooks Where ISBN ='" & StrISBN & "'")
        StrBookTitle = Rs2("Title")
        'Make all the titles for this book the same based on the title of the first one found

        Do Until Rs2.EOF
             Rs2.Edit
             Rs2("Title") = StrBookTitle
             Rs2.Update
             Rs2.MoveNext
        Loop
        Rs2.Close
        'Move to the next ISBN in the table
        Debug.Print StrISBN & vbTab & StrBookTitle
        Rs1.MoveNext
    Loop
    Rs1.Close
End If
Set Rs1 = Nothing
Set Rs2 = Nothing
Debug.Print "Finished"

End Function

Cut and paste this code into a new module in your mdb. Remember to change the field and table names to match yours.

I would at this point make a back up copy of your book table in case something goes wrong.

Press Ctrl+G to open up the immediate window then type in

?ResetBookTitles()

This should now go to your master book table and reset all the book titles to read the same for each ISBN in the table.

So now if you create a query grouped by ISBN and Book title and count the ISBN and add a criteria where count > 3

SELECT ISBN, BookTitle, Count(1) AS iCnt
FROM TblBooks
GROUP BY ISBN, BookTitle
HAVING (((Count(1))>3))
ORDER BY Count(1) DESC;

This will give you a list of all books most to least that you have 4 or more copies of.

Hope this helps

David

 

[lemieux66c]

Thanks for the reply. I'll give it a try when I get home from work.

 

[lemieux66c]

Hi,

I'm still having trouble with this. I believe I renamed all of the fields/tables to match the ones I've had. I also tried naming my fields/tables to match what was posted. Any ideas of what might be going wrong?

 

[jal]

Have you run DCrake's code and confirmed the results? That is to say, is it now evident that each repeating title has a consistent spelling?