Relating Customers to Advertisements & Select from Value List or else Create New Record for Advertisement
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