3 tables with up to 2 fields the same for each

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 tables created the common fields are contnum and tpname1 with most
important listed first. Third table has tpname repeated for different
circumstances. Access would not let me save or close this table. Tpname is
one of the links to the original table. What can I do?
 
I have 3 tables created the common fields are contnum and tpname1 with most
important listed first. Third table has tpname repeated for different
circumstances. Access would not let me save or close this table. Tpname is
one of the links to the original table. What can I do?
Click to expand...

STOP. You're on the wrong track!

I see in your other thread that you're a) using Taxpayer Name as a
primary key, and b) trying to store the name multiple times in a
table. NEITHER is a wise idea.

For one thing, names are not unique. Suppose you have two taxpayers,
Bill Smith and his father Bill Smith - different addresses, different
properties, different tax bills. And there's another unrealated Bill
Smith down the street, who should get his own <ahem> bill.

You *must* use a unique person ID. Most tax rolls have a numeric
taxpayerID; Access can certainly help you create and maintain this ID.

Secondly, you seem to be trying to let the appearance of the final
report drive your table structure. This is pretty much guaranteed to
give you a BAD database design! Data storage and data presentation are
two different tasks, with different requirements.

Your table structure should be "normalized" - among other things, this
means that you should store each piece of information ONCE, and once
only (taxpayer name for example). You would use Queries to link to it
(in order to show it five places on a report for example).

See the following resources - especially check out the "Database
Design 101" link on Jeff's site.

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson[MVP]
 
The control number is the primary key which would be the same as a tax id for
this department's purposes. and the reason why we would want the name to pop
up is to eliminate any double reporting which would result in double
assessment and double taxing which is of course illegal. At this I have
spreadsheets dating back to 2004 for businesses, individuals with rv's. I
get my information for new businesses from the department of revenue,
phonebook, newspapers and other miscellaneous threads. The new businesses
are entered into their own spreadsheet and when i get a response by either
return mail and a tax return, I bold and highlight this row and a trigger to
me that some action has taken place and at the end of the mailing information
i put in the same row as the rest of the data. This is becoming cumbersome
because companies change names, locations etc without notifying thier local
government officials and it results in duplication. This is one of the tasks
i am trying to eliminate. Also this year the state has come up with new
tasks for us to do so i am trying to figure out how to implement the tracking
of those documents as well. I sould appreciate as much hlp as possible on
this task
 
Back
Top