1 Reply Latest reply on Mar 30, 2011 11:15 AM by philmodjunk

    Relating Customers to Advertisements

    KirinIchiban

      Title

      Relating Customers to Advertisements & Select from Value List or else Create New Record for Advertisement

      Post

      Hello, can someone give me advice on how to set this up, I don't even know if I have the tables and relationships correctly thought out:
      I have a CustomersTable with the field "SourcedFrom" where I want to keep track of how a customer may have come to know about my business, this could be from magazine ads, flyers, tv commercials, word of mouth, etc. Eventually I want to develop reports on how many customers a certain advertisement or marketing venue is generating, and then even further, how many of those customers eventually actually make an order and how much…

      But first things first, In another table, I have an Ads_n_MarketingTable that the records will have a UniqueItemID field and have another field something like ItemName where the contents would be something such as Time Magazine March 2011, or Television Commercial #1, or even word of mouth "Jane Doe recommended," etc, basically it could be anything….I will be adding new records to this table directly when I run a new advertisement, or such.

      Basically, ideally, let's say, 1 advertisement can have many customers related to it, but 1 customer should only be related to one advertisement venue….but what happens when there are multiple sources from which they've heard of my business?? argh…

      Anyway, let's pretend I'm heading in the right direction…. In the CustomersTable and SourcedFrom field I want to have a drop down list of the ItemName field in the Ads_n_MarketingTable. Once I select a value from the drop down menu, I suppose it should make a new record where the customer id & name would be related to that value's id and item name, for example, once I select Advertisement#1 from the SourcedFrom field for Customer Jane Doe in the CustomersTable, the Jane Doe & her CustomerID would be related to the record Advertisement#1 in the Ads_n_MarketingTable.

      Furthermore, I want it so that during data entry while in the CustomersTable, if the value is not listed in the drop down list, that you would be able to type in a new value, which would in turn add a new record to the Ads_n_MarketingTable, and be associated with the Customer that it was inputed for.

      Is this making any sense? Will it be something like having an orders table and then a line items table for the orders? I have fiddled around with different match fields, from my CustomersTable & Ads_n_MarketingTable but can't seem to get it to work out. Basically if I have a drop down value list from the ItemName field in the Ads_n_MarketingTable, but can't make it relate to the customerName & customerID, or create a new record on the Ads_n_MarketingTable or have it related to the Customer when I type in a new value.

      TIA for any guidance

        • 1. Re: Relating Customers to Advertisements & Select from Value List or else Create New Record for...
          philmodjunk

          First, you have to make a decision here:

          "let's say, 1 advertisement can have many customers related to it, but 1 customer should only be related to one advertisement venue….but what happens when there are multiple sources from which they've heard of my business??"

          You can restrict the connection to a single advertisement record. "What source was MOST influential in your decision to shop with us..."

          You can link a customer to multiple advertisement records. "List all the ways you've heard about us..."

          Either can be done, but the relationships needed will depend on which choice you make. The first option describes a "one to many" relationship where one advertisment links to many customers. The second option describes a "many to many" relationship where many advertisements can link to one customer and many customers can link to one advertisement.

          The choice is up to you.