Table field(s) autopopulating based on value from another field in same table

[Desilu via AccessMonster.com]

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

 

[Tony Toews [MVP]]

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
--
Tony 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/

 

[Jamie Collins]

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.

--

 

[Curis]

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

 

[Tony Toews [MVP]]

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'.

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
--
Tony 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/

 

[Jamie Collins]

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.

--

 

[Tony Toews [MVP]]

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".

Fine by me.

Tony
--
Tony 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/

 

[Jamie Collins]

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.

--