Newbie needs help with a simple(?) query expression

[The Chairman]

Hi, thanks for reading this, I apologize if I have the wrong group or
something. If so please point me in the right direction.

Anyway, I am trying to run a query that uses 2 tables - BOOKINGS and
CONTACTS.

They both contain customer information, identified and kept together by
a customer id number that both use. It is called CSSID in the CONTACTS
table, and CONTACTNO in the BOOKINGS table.

I would like to do 2 things:

1) Filter out any records whose CSSID in the CONTACTS table exists as
the CONTACTNO in the BOOKINGS table. In other words, if the contact has
purchased a service, it will appear in the BOOKINGS table. If the
contact hasn't, there will be no reference to them. I want to filter in
the contacts table people who have any listing in the bookings table, so
I am only able to see customers who have not bought anything.

2) Seperately, I would like to filter the CONTACTS table conversely, by
only showing contacts who have a listing in the BOOKINGS table. Then,
further filter that by the field COURSENAME ("Level One", "Level Two",
etc.)

Let me know if you need more data.

Thanks tremendously for your help with this!!

Ryan

 

[tina]

okay, it sounds like you want to distinct lists of customers: those who
have records in the CONTACTS table only, and those who have records in the
BOOKINGS table only. you don't want to see customers who have records in
*both* tables.

if you want to display these two distinct customer lists in one dataset, you
might try the following: use the Find Unmatched Query Wizard to create a
query that finds customers with no records in CONTACTS. use the wizard to
create a second query that finds customers with no records in BOOKINGS.
create a Union query based on the two "unmatched" queries, to pull the
records into a single dataset.

hth

 

[The Chairman]

okay, it sounds like you want to distinct lists of customers: those
who have records in the CONTACTS table only, and those who have
records in the BOOKINGS table only. you don't want to see customers
who have records in *both* tables.

if you want to display these two distinct customer lists in one
dataset, you might try the following: use the Find Unmatched Query
Wizard to create a query that finds customers with no records in
CONTACTS. use the wizard to create a second query that finds customers
with no records in BOOKINGS. create a Union query based on the two
"unmatched" queries, to pull the records into a single dataset.

hth

Hi hth,

Thanks for your rapid reply!

Pretty close, but not quite what I was trying to explain, sorry.

ALL customers are included in CONTACTS, only customers who have
purchased are also in BOOKINGS. BOOKINGS only contains the product
information and customer ID, not any other contact information. This is
how they correlate in the user-end shell software that uses the mdb
files.

So, basically, I want to query the CONTACTS table against the BOOKINGS
table, and if any occurence of the customer number occurs in the
BOOKINGS table, exclude that customer from the CONTACTS table (or the
new dataset we are pulling to.)

And, conversely, for a seperate mailing, do the opposite: ONLY include
CONTACTS whose customer number also appears in BOOKINGS, exclude
customers who don't, and then further filter it by another field in
BOOKINGS (service type).

Does that make more sense? Would you still use the same method?

Thanks a million!

 

[tina]

comments inline.

Hi hth,

"hth" is short for "hope this helps"; you can call me tina

Thanks for your rapid reply!

Pretty close, but not quite what I was trying to explain, sorry.

ALL customers are included in CONTACTS, only customers who have
purchased are also in BOOKINGS. BOOKINGS only contains the product
information and customer ID, not any other contact information. This is
how they correlate in the user-end shell software that uses the mdb
files.

So, basically, I want to query the CONTACTS table against the BOOKINGS
table, and if any occurence of the customer number occurs in the
BOOKINGS table, exclude that customer from the CONTACTS table (or the
new dataset we are pulling to.)

okay. in this case, create an Unmatched query using the wizard, as i said
before, to get a list of CONTACTS records with no matching customer ID in
BOOKINGS.

And, conversely, for a seperate mailing, do the opposite: ONLY include
CONTACTS whose customer number also appears in BOOKINGS, exclude
customers who don't, and then further filter it by another field in
BOOKINGS (service type).

this one is easy enough. add both tables to a new query in design view. link
the two tables on the customer ID fields, with an INNER JOIN. pull the
fields you want from either/both tables, and set criteria on the service
type field from BOOKINGS.

Does that make more sense? Would you still use the same method?

if you don't need to combine the two query datasets into one, then you don't
need a Union query.

Thanks a million!

hth

 

[The Chairman]

this one is easy enough. add both tables to a new query in design
view. link the two tables on the customer ID fields, with an INNER
JOIN. pull the fields you want from either/both tables, and set
criteria on the service type field from BOOKINGS.

Thanks Tina!! (if that is, in fact, your real name. Not "hth" as you
tricked me with before ;-))

The first one worked perfectly, but I need further help with this second
one, if you can:

After running the filter I realized that there are multiple line items
in the BOOKINGS table. This means that if someone is "LEVEL 2", they
also have a seperate line item in BOOKINGS that contains "LEVEL 1".
However, what I would like to do is have the query pick up people who
ONLY have "LEVEL 1". The query as above picks up anyone Level 1 or
above. Can you help with this one?

Thanks again!

 

[tina]

is there a field in BOOKINGS that designates which Level each record belongs
to? if so, set criteria on that field to restrict the records to the level
you want to see.

hth

 

[The Chairman]

is there a field in BOOKINGS that designates which Level each record
belongs to? if so, set criteria on that field to restrict the records
to the level you want to see.

hth

Unfortunately no. The end-user uses the school history to figure out
what level the person is on. The school history in the User Interface
pulls from the BOOKINGS table.

Anyway, here is what should work in plain English, and maybe you can
help me translate:

1) Only filter records whose AccountNo appears in both CONTACTS and
BOOKINGS. (DONE! Linked with an INNER JOIN on ContactNo like you said,
and it works like a charm.)
2) Filter only the records who have a Level One or Level 1 line item in
CourseName in BOOKINGS. (DONE! from above.)
3) Then, we kind of need to do another conditional query, or something.
IF (field)CourseName in (table)BOOKINGS = "*1*" or "*One*", but the
ContactNo (Inner Joined above in tables BOOKINGS and CONTACTS) also has
another record in BOOKINGS that equals or is greater than "2" or "Two",
EXCLUDE this record from our results.

Thanks Tina, for putting in this time with me. It's so frustrating not
to know these commands... Almost like being a foreigner who knows what
he wants, but just can't get the shopkeeper to give it to him!

Ryan

 

[tina]

hmm, okay. i'm not all that great at subqueries and so on, so how about we
try to do this the "simple" way: create another query, which i think you
can base solely on BOOKINGS, where the CourseName criteria is

Not Like "*2*" And Not Like "*Two*"

now, substitute the above query in place of the BOOKINGS table, in the
"INNER JOIN" query we created previously.

hth

 

[The Chairman]

hmm, okay. i'm not all that great at subqueries and so on, so how
about we try to do this the "simple" way: create another query, which
i think you can base solely on BOOKINGS, where the CourseName criteria
is

Not Like "*2*" And Not Like "*Two*"

now, substitute the above query in place of the BOOKINGS table, in the
"INNER JOIN" query we created previously.

hth

I figured out what should be a great way to do it, but I have a
stumbling block...

Basically, I created a new field in the CONTACTS database called
LASTLEVELCOMP. Now I can filter the records backwards, starting with
Level 4 (which is the highest). Ideally, I should then be able to mass
fill in the LASTLEVELCOMP field in the filtered results to "4".

Then, I would filter results where COURSENAME is like "Three" and
LASTLEVELCOMP is blank. Then repeat that above.

But, I can't edit the LASTLEVELCOMP field when I am viewing the filtered
results. Do you know how I might be able to work around this?

I really can't tell you how much I appreciate you taking the time to
help me with this.

Ryan

 

[tina]

well, i have to admit that i've had a less-clear picture of your setup with
each successive post, and now i'm pretty well lost. i can tell you that
often multi-table queries are not updateable; can you do the "filtering"
using only the one table, to ensure that you can update the new field?

hth

 

[The Chairman]

well, i have to admit that i've had a less-clear picture of your setup
with each successive post, and now i'm pretty well lost. i can tell
you that often multi-table queries are not updateable; can you do the
"filtering" using only the one table, to ensure that you can update
the new field?

hth

Hey Tina,

Thanks for putting up with me this whole time. Anyway, I realized that
what I said last time isn't going to work after all.

Now that I have a more clear picture of what I need to do, let me post a
new topic outlining it. We might need some fresh eyes on it... let's
see!

Ryan

 

[tina]

good idea, Ryan, fresh eyes (or at least more eyes) are always good. good
luck with it!