Promotion Database

Question

Having trouble with some parts of my form design. I'm not sure which control to use in terms of&nbsp;promotion to allocation&nbsp;as this has a one-many relationship. How can I design it for the end-user to encode it efficiently?

daniel_pineault · Answer

I'm afraid your going to have to explains things to us as we have no clue what you are referring to when you say 'promotion to allocation'. Please explain your table structure and existing form design and what you are now trying to achieve. Also, an image can greatly help us understand quickly.

In general terms, if I need to display a one to many relationship in a form of be using a subform.

nate_alcantara · Answer

Daniel_Pineault&nbsp;I work for a canned goods manufacturing company and one of its marketing efforts is creating promotions/trade activities. Promotion to allocation means that one promotion has multiple accounts/customers to be allocated to. In our system, our customers have address codes so I have a table for this plus a table for the promotion details.My main problem is in terms of encoding for the end-user, how can I design the form efficiently enough that multiple accounts can be encoded per promotion form.&nbsp;

george_hepworth · Answer

Nate_Alcantara&nbsp;&nbsp;PMFJI:Data is stored in tables. What we need to see, therefore, is the table design. Thanks.

nate_alcantara · Answer

George_Hepworth&nbsp;These are my table designs so far and its relationship.&nbsp;I need help on how to design the form in terms of target area and allocation. One promo can be applicable to multiple areas and therefore have multiple accounts to be allocated to.

george_hepworth · Answer

Nate_Alcantara&nbsp;Only two tables?I see at least three or four others that could, and probably should, be included. I hope you have at least these, plus a couple others like "Client" or "Customer".Target ChannelTarget AreaDivisionPromo typeI list these because I think you target the same channels more than one. I would expect that campaigns are aimed at the same target areas repeatedly.Divisions within your organization should be listed in a table and should Promo Types.All of these would, I believe, be required for a relational database application. You may have them, of course, and only showed these two, but to be complete and accurate we need to sure they are there.A few notes on naming conventions and standard field types are in order, although tangential to your central question.Names should not have spaces in them, nor should other non-standard characters be part of names. E.g. # or ?These names are acceptable, but clumsy because anytime you have to reference one, you must enclose it in square brackets.E.g. [Address Code]Most long-term Access developers use of the more common naming conventions which avoid spaces and other non-standard characters.A second, much more problematic, choice is the use of a Multi-Value field for attachments. Again, experienced developers do not use this field type. One, embedded photos in an accdb causes it to bloat needlessly. Two, they are much harder to work with in the interface. Instead, you should create a separate table for PromoPhotos. It'll have three fields, i.e. those you have in the MVF now.Okay, back to the design. I'm going to use the naming convention that avoids spaces because it is so unpleasant to try to work with them. I hope you do make that change.You have a table called AddressCodes. I'd call it AddressCode (singular), but that's one of those things that attracts different decisions. Anyway, AddressCode implies that you are concerned with Addresses as the primary "fact" about your promomotions. I see three other fields in it, though, that really make me wonder. "ParentName", "RetailChain" and "Channel". Parent of what? Do you have a separate table of "Parents"? How about "RetailChain"? Is there a separate "RetailChain" table? If not, then both of them should be created and properly related via Primary and Foreign Key fields. Looking at "AddressName" I have to ask if you have a separate table with street, city, and state details? Or are you only interested in names of addresses, which I would imagine might be something like "The Main Downtown Mall"?Now to the TradeActivities table. The Primary Key in it is PromoID. Does that mean TradeActivity is a synonym for Promotion? If not, how are they related? If so, why two different names?One last question that I think you probably addressed, but which I'd like to clarify. What goes into the field called "Allocation"? Is that a number, a percentage, or perhaps something else?Thanks.

Forum Discussion

Promotion Database