Re: Hit a wall with DB logic...

From: John Vinson (jvinson_at_STOP_SPAM.WysardOfInfo.com)
Date: 11/09/04 

Date: Tue, 09 Nov 2004 13:20:31 -0700

On Tue, 9 Nov 2004 11:04:01 -0800, "Cheech73"
<Cheech73@discussions.microsoft.com> wrote:

>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 Spread*** 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