Need some help with a query... Please please!

[The Chairman]

I am trying to make a query that will help me acheive the following result:

I have 2 tables. One is my contact information, called CONTACTS, the other
is my sales info, called BOOKINGS. The common identifying field in both is
ContactNumber.

When someone buys a level, it gets marked as an individual sales line in
BOOKINGS. So, everyone who is "Level 2", also has a seperate "Level 1" line
in BOOKINGS.

What I am trying to do is create a query that will join the CustomerNo
field in both tables, then deliver the result of giving me all the Level 1
customers who do not have a Level 2 line in BOOKINGS. This has been tricky,
because like I said, everyone has a Level 1 line, so a simple query hasn't
worked for me as it returns everyone who has done any Level, regardless of
whether they have gone beyond 1.

Please go slow, I am new to this. Thanks so much!

Ryan

 

[John Vinson]

I am trying to make a query that will help me acheive the following result:

I have 2 tables. One is my contact information, called CONTACTS, the other
is my sales info, called BOOKINGS. The common identifying field in both is
ContactNumber.

When someone buys a level, it gets marked as an individual sales line in
BOOKINGS. So, everyone who is "Level 2", also has a seperate "Level 1" line
in BOOKINGS.

What I am trying to do is create a query that will join the CustomerNo
field in both tables, then deliver the result of giving me all the Level 1
customers who do not have a Level 2 line in BOOKINGS. This has been tricky,
because like I said, everyone has a Level 1 line, so a simple query hasn't
worked for me as it returns everyone who has done any Level, regardless of
whether they have gone beyond 1.

Please go slow, I am new to this. Thanks so much!

Ryan

A Subquery will be needed here. Put a criterion on CustomerNo of

NOT IN(SELECT CustomerNo FROM Bookings AS B WHERE B.LEVEL = "Level 2")

This will exclude all customers who have a Level 2 record.

Another approach - try them both - would be to use a calculated field:

EXISTS (SELECT CustomerNo FROM Bookings AS B WHERE B.Level = "Level
2")

and put a criterion of False on this field. Note that this might be
faster or slower depending on your data; try both to see if one or
other works better for you.

John W. Vinson[MVP]

 

[The Chairman]

A Subquery will be needed here. Put a criterion on CustomerNo of

NOT IN(SELECT CustomerNo FROM Bookings AS B WHERE B.LEVEL = "Level 2")

This will exclude all customers who have a Level 2 record.

Another approach - try them both - would be to use a calculated field:

EXISTS (SELECT CustomerNo FROM Bookings AS B WHERE B.Level = "Level
2")

and put a criterion of False on this field. Note that this might be
faster or slower depending on your data; try both to see if one or
other works better for you.

John W. Vinson[MVP]

Thanks so much. I hate to be a pest, but like I said I am new to this...
haven't had any experience in SQL before. If you don't mind, where would I
put that subquery in this query:

SELECT DISTINCT Bookings.CourseName, Contacts.ACCTNO, Contacts.TITLE,
Contacts.FNAME, Contacts.MI, Contacts.LNAME, Contacts.ADD1, Contacts.ADD2,
Contacts.CITY, Contacts.STATE, Contacts.ZIP, Contacts.PLUS4,
Contacts.DELPT, Contacts.CKDIGIT, Contacts.CRRT, Contacts.LOT,
Contacts.LOTORDER, Contacts.CASSCODE, Contacts.ADDTYPE
FROM Bookings INNER JOIN Contacts ON Bookings.ContactNo = Contacts.ACCTNO
WHERE (((Bookings.CourseName) Like "*One*" Or (Bookings.CourseName) Like "*
1*"))
ORDER BY Contacts.ACCTNO;

 

[The Chairman]

Also, note that the field names are slightly different; I altered them in
my OP for simplicity's sake.

 

[John Vinson]

haven't had any experience in SQL before. If you don't mind, where would I
put that subquery in this query:

Thanks for the SQL - it helps. I did overlook one criterion (to
correlate the subquery). Try

SELECT DISTINCT Bookings.CourseName, Contacts.ACCTNO, Contacts.TITLE,
Contacts.FNAME, Contacts.MI, Contacts.LNAME, Contacts.ADD1,
Contacts.ADD2,
Contacts.CITY, Contacts.STATE, Contacts.ZIP, Contacts.PLUS4,
Contacts.DELPT, Contacts.CKDIGIT, Contacts.CRRT, Contacts.LOT,
Contacts.LOTORDER, Contacts.CASSCODE, Contacts.ADDTYPE
FROM Bookings INNER JOIN Contacts ON Bookings.ContactNo =
Contacts.ACCTNO
WHERE (((Bookings.CourseName) Like "*One*" Or (Bookings.CourseName)
Like "*1*"))
AND NOT EXISTS
(SELECT CourseName FROM Bookings AS B
WHERE B.ContactNo = Contacts.ACCTNO AND
B.CourseName LIKE "*Two*" OR B.Coursename LIKE "*2*")
ORDER BY Contacts.ACCTNO;

I'd really suggest that embedding the level as a (free format yet!!)
string within the coursename is unwise: wouldn't it be better to have
a separate Level integer field, if the level is important to your
application?


John W. Vinson[MVP]

 

[The Chairman]

I'd really suggest that embedding the level as a (free format yet!!)
string within the coursename is unwise: wouldn't it be better to have
a separate Level integer field, if the level is important to your
application?

Yes, absolutely. I definetly agree.

All the work that I am doing right now is basically a quick fix
scramble, to get these names off to the mailhouse for a mailing that
needs to go out right away.

I didn't design this database, nor did I design the user-interface that
the end users are using. Basically, they took an off the shelf
application designed for their field, and I believe the company that
sells it slightly modifies it to their needs. I have no idea if the
interface could even handle a database change.

They are, however, in the market for a new custom database application
as they are quite unhappy with the software that they ended up with. It
does give a good start for them to know what they need and don't need,
however.

All of that info adds up to me having to get creative and really get to
know their mdb files to get this stupid mailing out!

Thanks a ton for your help!!

Ryan

 

[The Chairman]

Thanks for the SQL - it helps. I did overlook one criterion (to
correlate the subquery). Try

SELECT DISTINCT Bookings.CourseName, Contacts.ACCTNO, Contacts.TITLE,
Contacts.FNAME, Contacts.MI, Contacts.LNAME, Contacts.ADD1,
Contacts.ADD2,
Contacts.CITY, Contacts.STATE, Contacts.ZIP, Contacts.PLUS4,
Contacts.DELPT, Contacts.CKDIGIT, Contacts.CRRT, Contacts.LOT,
Contacts.LOTORDER, Contacts.CASSCODE, Contacts.ADDTYPE
FROM Bookings INNER JOIN Contacts ON Bookings.ContactNo =
Contacts.ACCTNO
WHERE (((Bookings.CourseName) Like "*One*" Or (Bookings.CourseName)
Like "*1*"))
AND NOT EXISTS
(SELECT CourseName FROM Bookings AS B
WHERE B.ContactNo = Contacts.ACCTNO AND
B.CourseName LIKE "*Two*" OR B.Coursename LIKE "*2*")
ORDER BY Contacts.ACCTNO;

I'd really suggest that embedding the level as a (free format yet!!)
string within the coursename is unwise: wouldn't it be better to have
a separate Level integer field, if the level is important to your
application?


John W. Vinson[MVP]

Hi John,

Thanks again for all your help.

Is there any way that you can think of that would speed up this query at
all? As is, it looks like it's going to take over an hour to complete.
In comparison, the query as it was before I added your subquery took
about 5 seconds or so.

Would trying it the other way you suggested in your first reply speed it
up in your estimation? If so, would you be so kind as to provide the
subquery integrated in to the SQL that I posted above?

It might sound like I am being picky, but really it's more for learning
purposes than anything else. Thanks again!

Ryan

 

[John Vinson]

Is there any way that you can think of that would speed up this query at
all? As is, it looks like it's going to take over an hour to complete.
In comparison, the query as it was before I added your subquery took
about 5 seconds or so.

The problem is that since you're using a wildcard search for *2*
anywhere within the field, it's impossible to take advantage of any
indexes - and that search is in the subquery so it must be run for
every row. Poky!

There are basically three ways to do this: the NOT EXISTS query; a NOT
IN query (similar and perhaps even slower); and a "frustrated outer
join" query. This final one might be the best bet but is the most
complex to set up.

First create a query selecting all the Level 2:

SELECT Distinct ContactNo FROM Bookings
WHERE Bookings.CourseName LIKE "*Two*"
OR Bookings.Coursename LIKE "*2*";

Save this query as qryLevelTwo. Then use an "unmatched" query:

SELECT DISTINCT Bookings.CourseName, Contacts.ACCTNO, Contacts.TITLE,
Contacts.FNAME, Contacts.MI, Contacts.LNAME, Contacts.ADD1,
Contacts.ADD2,
Contacts.CITY, Contacts.STATE, Contacts.ZIP, Contacts.PLUS4,
Contacts.DELPT, Contacts.CKDIGIT, Contacts.CRRT, Contacts.LOT,
Contacts.LOTORDER, Contacts.CASSCODE, Contacts.ADDTYPE
FROM (Bookings INNER JOIN Contacts ON Bookings.ContactNo =
Contacts.ACCTNO) LEFT JOIN qryLevelTwo ON qryLevelTwo.ContactNo =
Contacts.ACCTNO
WHERE qryLevelTwo.ContactNo IS NULL
ORDER BY Contacts.ACCTNO;


John W. Vinson[MVP]

 

[The Chairman]

The problem is that since you're using a wildcard search for *2*
anywhere within the field, it's impossible to take advantage of any
indexes - and that search is in the subquery so it must be run for
every row. Poky!

Thanks. Took out the wildcard expression, and the OR part. It ran massively
faster. Then, I copied and pasted the results in to a new table, modified
the query, and ran it again. Lather, rinse, repeat...

Thanks again. It's a wrap!

Ryan

 

[John Vinson]

Thanks. Took out the wildcard expression, and the OR part. It ran massively
faster. Then, I copied and pasted the results in to a new table, modified
the query, and ran it again. Lather, rinse, repeat...

<g> Yep. That's when temp tables come in handy. Glad you got it
working!

John W. Vinson[MVP]