 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / January 2008Table field(s) autopopulating based on value from another field in same table
I'm working with a Insurance Sales Activity DB. One piece is the entry of the sales rep's daily sales by the type of contact (email, phone call, meeting) by their Contact and the products discussed, etc. They want to be able to check off each insurance product that was discussed. It goes against my grain to store each product in it's own field, however my users are unwilling to do it any other way. To keep them happy, I want to create a field for "All Products". When that field holds a yes value (or 1), I want all of the product fields to automatically populate with a "yes" value. I tried an IF statement in some of the product table field's default value property, but it didn't work. What am I doing wrong? =IIf([AllProducts]=Yes,Yes,"") Also, I could use a reference book on how to communicate to users. It can be very difficult and frustrating! Thanks Desilu >I'm working with a Insurance Sales Activity DB. One piece is the entry of >the sales rep's daily sales by the type of contact (email, phone call, >meeting) by their Contact and the products discussed, etc. They want to be >able to check off each insurance product that was discussed. It goes against >my grain to store each product in it's own field, however my users are >unwilling to do it any other way. I'd use a "junction" table with foreign keys pointing to the Contact entry and insurance product. What if they start selling a new product day after tomorrow? Which is rather likely. Now what you could do is use a cross product query and fill in that junction table upon opening that form. Then it comes real easy for the user to hit the check box beside each discussed product. Tony  SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ On Jan 13, 10:18 pm, "Tony Toews [MVP]" <tto...@telusplanet.net> wrote: > I'd use a "junction" table with foreign keys pointing to the Contact > entry and insurance product. I wasn't going to say anything but a minute later I saw this also from you: > I would do just about anything > to avoid fields in two tables pointing to each other. [http://groups.google.com/group/microsoft.public.access.tablesdbdesign/ msg/7da4d4747dcab625] Are you trying to convey specific meaning by using the word 'pointing'? If foreign keys do anything then it would be 'reference'. If you write out the SQL code 'by hand' it should become obvious e.g. (aircode): ALTER TABLE SalesPitches ADD FOREIGN KEY (product_name) REFERENCES InsuranceProducts Thus the two tables in a FK are the 'referencing table' and 'referenced table' respectively. Jamie. -- >> I'd use a "junction" table with foreign keys pointing to the Contact >> entry and insurance product. [quoted text clipped - 12 lines] > >If foreign keys do anything then it would be 'reference'. Pointing, referencing. Sure, whatever. To me just a different word meaning the same thing. >If you write >out the SQL code 'by hand' it should become obvious e.g. (aircode): I don't use SQL code that way. I use DAO code to update tables, indexes and relationships. >ALTER TABLE SalesPitches > ADD FOREIGN KEY (product_name) > REFERENCES InsuranceProducts > >Thus the two tables in a FK are the 'referencing table' and >'referenced table' respectively. Or they are parent, child, master, whatever. Who cares. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ > > If foreign keys do anything then it would be 'reference'. > > Pointing, referencing. Sure, whatever. To me just a different word > meaning the same thing. Emphasis on the "To me". I imagine that if I was in an interview situation where the candidate's cv/resumé read, "I'm an excellent, or so I like to think, programmer and systems analyst" and during the interview they used the word "pointing" (twice) to describe a foreign key, which I thought was odd, so I asked them if them if they meant "referencing", explaining my reasoning, to which the candidate replied, "Or do you mean parent, child, master, whatever. Who cares," then I imagine they would be classed a "don't hire". Jamie. -- >> > If foreign keys do anything then it would be 'reference'. >> [quoted text clipped - 11 lines] >child, master, whatever. Who cares," then I imagine they would be >classed a "don't hire". Fine by me. Tony SignatureTony Toews, Microsoft Access MVP Please respond only in the newsgroups so that others can read the entire thread of messages. Microsoft Access Links, Hints, Tips & Accounting Systems at http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ > >the candidate replied, "Or do you mean parent, > >child, master, whatever. Who cares," then I imagine they would be > >classed a "don't hire". > > Fine by me. A loner, eh <g>? Well, I'm here to remind you that when you interface with the computer literate public you are going to encounter geeks who care about technical correctness -- it comes with the territory. I'll attempt an Access example: consider the response a newbie gets when they mention they use "lookups" i.e. do they mean the database professionals' trade term "lookup table" or do they mean the dreaded- by-Access-professionals "lookup fields"? Here's hoping you can see that helps to be specific. Jamie. -- First, I would suggest you attempt to dissuade them from insisting on this method. As Mr. Toews said, it will likely be a common thing for new products to be added, and existing products to be removed. For each of those changes, a change to the table and form structures will be necessary. That said, to answer your question, I would simply set up an unbound combo box, called cboAllProducts, on the form that has an After_Update event that sets all of the other check boxes to -1 if the value in cboAllProducts is -1. Private Sub cboAllProducts_AfterUpdate() On Error Goto Err_Handler If cboAllProducts then cboProduct1 = -1 cboProduct2 = -1 cboProduct3 = -1 etc. End If Exit_Handler: Exit Sub Err_Handler: Msgbox Err.Number & " - " & Err.Description Resume Exit_Handler End Sub > I'm working with a Insurance Sales Activity DB. One piece is the entry of > the sales rep's daily sales by the type of contact (email, phone call, [quoted text clipped - 14 lines] > Thanks > Desilu |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.