all #'s i could call, minus #'s i cant, add back #'s i can

[DawnTreader]

Hello All

i have a stupid problem. stupid because the Do Not Telemarket list here in
Canada is a sham and money grab. regardless i have to make a list of phone
numbers that i can call.

i am looking at getting a list with all the numbers that exist, taking away
the ones from the DNT list and then adding back the customers that may have
been taken out. we are allowed to call numbers that are currently customers
whether they are on the DNT or not.

basically i start with 3 tables, tblPurchasedList, tblDoNotTelemarket,
tblCustomers. each of these are imported and will be imported on a regular
basis. then i need the application i am creating to put the purchased list
into the tblFinalConsumersToCall. i have this part working the way i want it.
the tblFinalConsumersToCall looks like this:

ID PurchID DateAdded DNTID DateDeleted CustID DateAddCust PrintID DatePrinted
1 1 15/01/2009 1 15/01/2009
11 11 15/01/2009 14486 26/01/2009
16 16 15/01/2009 128281 15/01/2009
17 17 15/01/2009 136138 15/01/2009 323 26/01/2009
23 23 15/01/2009 4 26/01/2009
24 24 15/01/2009 190230 15/01/2009 5 26/01/2009

of course there is phone numbers in those records as well, but i am not
going to put them up here especially since they arent necessary because
everything can go by the ID fields.

the idea was to have this table store all records regardless of whether they
can be called or not, but to have the system figure out if they can be
called. so at this point i want to be able to say, if there is a DNTID then
dont show them unless there is also a CustID and if there is a PrintID then
dont show them no matter what.

like i said the stuff leading up to this table is fine, my problem is how to
make the system work through the logic and return only the records that
havent been printed, and are not DNTID'ed unless they are a customer.

any and all help appreciated

 

[John W. Vinson]

basically i start with 3 tables, tblPurchasedList, tblDoNotTelemarket,
tblCustomers. each of these are imported and will be imported on a regular
basis. then i need the application i am creating to put the purchased list
into the tblFinalConsumersToCall. i have this part working the way i want it.
the tblFinalConsumersToCall looks like this:

I'd approach this in two steps. First create an "unmatched" query between
tblPurchasedList and tblDoNotTelemarket. Join the two tables on the phone
number; click the join line and choose Option 2 (or maybe 3) - "Show all
records in tblPurchasedList and matching records in tblDoNotTelemarket". Put a
criterion of IS NULL on the phone number link field in tblDoNotTelemarket.

This will leave just those records in tblPurchasedList which are NOT in the
do-not-call list (btw: mine will be in the do not call list. At least it
should. <g>)

Now use the SQL window to create a UNION query stringing tblCustomers (who you
can call whether they're on either of the other two lists or not) with the
previous query. The UNION will have all phone numbers from tblCustomers, and
all the permitted numbers from the purchased list. You can then base an
Append query on this UNION query to populate tblFinalConsumersToCall.

 

[DawnTreader]

Hello John

Thanks! I will give this a try tonight. I have been swinging blindly at the
logic for a couple of weeks. this will be a great help.