How do I do this?

  • Thread starter Thread starter Wayne Robinson
  • Start date Start date
W

Wayne Robinson

OK all of you knowledgeable folks, I have got one for you (it should take
you all of 10 seconds, but I'm a tax guy).

Deal

Tax research database that stores tax info (rules, rate, exceptions etc.).
My users want to see a product (software) and topic (tax) combination when
they create a tax rule. A product may a distinct topic, but a topic can have
many products.

What I did was to create separate tables for the topics and products (and
created autonumber fields for each). I linked the 2 tables to create a
"Combined Topic" (product \ topic) field via a query.

All of the forms, macros etc work great, but as I was splitting the db to fe
and be I got an exception error (something out of range).

My real question is, given that the "Combined Topic" (product \ topic) won't
change much, should I just create a table of the "Combined Topic" and move
on. I was trying not to repeat info, but it seems to be easier to just store
the data I need for my users (they are used to ("Combined Topic") and that
would eliminate them from having to select a STATE, PRODUCT, TOPIC in order
to create rules

Kindest regards,

Wayne
 
Wayne

When you have two entities related many-to-many, Access can't resolve valid
combinations until you create a third table to hold those valid
combinations. This relation/junction/resolver table will hold one row for
each valid combination of your tblProduct and tblTopic. It could be as
simple as:

trelProductTopic
ProductID
TopicID

where the primary key is a multi-column key, based on the two foreign keys.
Or it may be further expanded to include its own Primary Key field (?an
Autonumber), and perhaps BeginDate and EndDate fields, to show the time
range during which the combination is valid.
 
Thanks Jeff.

I had a feeling that is what I needed to do but I was not sure that there is
not a way to define a global variable "combinedtopic" that would be
recognized as the unique.

I will change the select I created and have it make the table.

Thanks again.

Kindest regards
Wayne
 
Jeff, a quick follow-up on your suggestion. Can I store the "combinedtopic"
field in my topics table as the topicID and productID's are already
contained in the table? Is your suggestion based on the fact that it will be
more efficient for access to process the data?

Thanks,
 
Wayne

My suggestion was based on relational design considerations. If you are
using the topics table to hold topics, and to hold topic/product
combinations, I believe you'd be better off removing the valid combinations
to a third table.

What about other folks' ideas?

Jeff Boyce
<Access MVP>
 
Jeff, a quick follow-up on your suggestion. Can I store the "combinedtopic"
field in my topics table as the topicID and productID's are already
contained in the table? Is your suggestion based on the fact that it will be
more efficient for access to process the data?

Thanks,
Click to expand...

You're missing the point.

No. You cannot store "combinedtopic" in the Topics table. You need a
table of Topics, with a field for the TopicID and the text of the
topic; each topic will appear in this table *once, and only once*.
This table becomes your list of valid topics.

The "resolver" table Jeff describes links your Product table to this
Topics table. It should have fields for the ProductID (but *no other
product information*) and the TopicID (but *no other topic
information*).


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thank you Jeff and John. I and many people appreciate your constant and
consistent service.


Wayne
 
Back
Top