duplicate values in two tables - need to separate

  • Thread starter Thread starter SimonJester
  • Start date Start date
S

SimonJester

Hi everybody.
I sure hope someone can help.
I have two tables: book_search and books_purchased.
I want to be able to count the distinct number of customers
(customer_id) who have searched for a particular author by the author
name and I want a distinct count of the customers who have purchased
books by the same author as in the first table.
It is entirely possible that a customer in the search table could have
also purchased the book they searched for and thus be in the
books_purchased table. Design issues aside - I can't change what is -
is there a way that I can do a distinct count of book_search by author
customers (customer_id) then do a distinct count of books_purchased by
author customers (customer_id), but in books_purchased not count any
records where the customer also appears in the book_search table?
Any suggestions would be very gratefully appreciated.
Thanks everyone.
SJ
 
I see no one took a stab at it yet, so here goes a few nothings.
1) I'm wondering if you can create a filter query to produce a record
set for customers and searches from book_search, and use it as a data
source in a query using the books_purchased table. You'd probably need
to use the "Where" or "Expression" choice from the list of aggregate
functions in the second query (will be available since you'll be using
the "Count" aggregate function in the query design grid) and set the
criteria for the customer ID from one data source to be not equal (<>)
to the other customer ID field.
2) If that doesn't work, maybe two filter queries to gather the data
seperately, then a third to query those and set criteria to exclude
records where the customer ID is the same in both filter query results
(as previously stated).
3) Create a simpler query and export the results to Excel and filter
them there.
Hope that at least gets you started.
 
Back
Top