Hit a wall with DB logic...

[Guest]

I am trying to create a contact management / lead fulfillment solution for a
magazine. Advertisers to the magazine can get leads two ways. The readers
can 1) Enter in the direct Reader Service number for the advertiser or
product, 2) Check a box to show interest in a category of products which may
span many advertisers. I have one table for advertiser information
(Advertisers), and two tables for leads information (Calls, Contacts). One
table keeps the leads contact info (just name and address, company name) and
the other keeps track of calls made to the leads, gathering demographic
information.
In the Calls table I have all of the possible spots ready for data entry
(Called "Response1" through "Response50" and a few for Direct responses) and
each response is selected from a Response table via a combo box on the Calls
form. This creates an "instance" in my calls table stores all the data
requested by the reader.
Here is the kicker.
At the end of the day I want to "run" leads based on responses in these
spots and match them up to the corresponding advertiser. I have a query that
runs out all the responses from the leads, but I am unsure how to go about
"sending" the leads' demographic information to the correct advertisers
(which could be many advertisers per lead).
For example, Schlage Locks runs an ad for their new door lock. We assign
reader service number "123" to the ad, and also put Schlage under two reader
service categories, say Access Control and Security. All of these things are
kept under the advertiser table. If a reader is interested in Access
Control, we want to send the lead to Schlage and anyone else who we selected
to be under Access Control. If the reader selects only the direct reader
service number, it should go only to the specific advertiser.
Without writing a hundred queries, what can I do? Am I making this too hard?
Should I take a walk in the parking lot?
Thanks for any help-
Frank

 

[John Vinson]

I am trying to create a contact management / lead fulfillment solution for a
magazine. Advertisers to the magazine can get leads two ways. The readers
can 1) Enter in the direct Reader Service number for the advertiser or
product, 2) Check a box to show interest in a category of products which may
span many advertisers. I have one table for advertiser information
(Advertisers), and two tables for leads information (Calls, Contacts). One
table keeps the leads contact info (just name and address, company name) and
the other keeps track of calls made to the leads, gathering demographic
information

In the Calls table I have all of the possible spots ready for data entry
(Called "Response1" through "Response50" and a few for Direct responses) and
each response is selected from a Response table via a combo box on the Calls
form. This creates an "instance" in my calls table stores all the data
requested by the reader.

Ok. You have committed the crime of "Committing Spreadsheet Upon a
Database". This is a misdemeanor punishible by being required to spend
at least one day studying the concepts of "Normalization".

If you have a one to many relationship, you should NOT - *NEVER* -
embed the many relationship into a single record. Instead, use Access
as it is designed: as a relational database. You need a Responses
table, related one to many to the Calls database. This would have a
field for the CallID, a ResponseNumber, and a Response; if a call gets
five responses there would be five records in this table with that
CallID; if it gets 84 responses, there would be 84 records.

For data entry and viewing, you could use a Form based on Calls with a
continuous Subform based on Responses; this subform would contain a
combo box listing the valid responses.

Here is the kicker.
At the end of the day I want to "run" leads based on responses in these
spots and match them up to the corresponding advertiser. I have a query that
runs out all the responses from the leads, but I am unsure how to go about
"sending" the leads' demographic information to the correct advertisers
(which could be many advertisers per lead).
...
Without writing a hundred queries, what can I do? Am I making this too hard?

Yes, by using an inappropriate table design. With the one to many
relationship, you have only one place to search - Responses.Response.

Should I take a walk in the parking lot?

Might help... reading up on Normalization would help a lot too!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps