Comparison Query

[maura]

My boss is looking for a report to be generated every week to show which
customers did NOT use our services the week before. We do transporting and we
have a drop date field, a billing field, among others, but I am not sure how
to structure a query to show or compare from week to week who give us
business and who doesn't --- kindof the way that Quickbooks does a comparison
and can show month to month how much a customer was billed. Any suggestions?
My brain just needs a little juice.

Thanks, maura.

 

[John Spencer]

First query - identify all customers that you DID do business with in the
previous week (qweekPrevious)

Second query - identify all customers that you DID do business with in this week.

Now use those two queries as the source for an unmatched query (query wizard).

So which date field(s) would you use to determine whether or not you did
business with a customer?

Depending on your table and field names this can all be done in one query. If
your field and table names consist of ONLY Letters, Numbers, and the
underscore character you can do this all in one complex query using two
subqueries - otherwise you will need to use the three query approach.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Ryan]

You would need a query showing all the companies table with right join to
your billing table. Put both tables in a query, and on the relationship say,
show all compaines and only billing records that match. This way all your
companies show, even if they dont have billing records. Then set up
grouping, a date range filter in the query and on the amount fields say =0 so
that you will only see companies with $0 amounts for the given date rage. If
you need help let me know.

 

[maura]

Okay, on the first query, I have a query that I have a criteria: Between
[Enter Start Date] And [Enter End Date] for the TransDropDate and this pulls
all the customers that I have "dropped and transported a car" for. This query
does show me all the customers during the dates I plug in that I have done
business with.

So, my next question is how do I do the next query and then compare the two?
Would I take this query and do this in code? Or can this all be done in the
query's. I generally only do simple queries, so this is kindof new to me. I
am sure I am not using the queries to their fullest.

And thanks for taking the time to reply to me! m.

 

[John Spencer]

Save the first query as qPriorWeek

Now, open the query in design view and save the query as qCurrentWeek. And
change the criteria to

Between DateAdd("d",7,[Enter Start Date]) and DateAdd("d",7,[Enter End Date])
and save that.

NOW click on the New query button and select Find Unmatched query wizard.
-- Select the two queries. qPriorWeek and then qCurrentWeek
-- Select the field to join on (customer id? customer name?)
-- Follow the wizard and you should end up with a query that will show you the
customers that were in qPriorWeek and are NOT in qCurrentWeek.

If you are still stuck, post the SQL of the existing query.
(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Okay, on the first query, I have a query that I have a criteria: Between
[Enter Start Date] And [Enter End Date] for the TransDropDate and this pulls
all the customers that I have "dropped and transported a car" for. This query
does show me all the customers during the dates I plug in that I have done
business with.

So, my next question is how do I do the next query and then compare the two?
Would I take this query and do this in code? Or can this all be done in the
query's. I generally only do simple queries, so this is kindof new to me. I
am sure I am not using the queries to their fullest.

And thanks for taking the time to reply to me! m.

First query - identify all customers that you DID do business with in the
previous week (qweekPrevious)

Second query - identify all customers that you DID do business with in this week.

Now use those two queries as the source for an unmatched query (query wizard).

So which date field(s) would you use to determine whether or not you did
business with a customer?

Depending on your table and field names this can all be done in one query. If
your field and table names consist of ONLY Letters, Numbers, and the
underscore character you can do this all in one complex query using two
subqueries - otherwise you will need to use the three query approach.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County