RE: DO ALL TABLES/FORMS DATA NEED TO BE ENTERED TO CREATE SUBFORMS
- From: Vanessa <Vanessa@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 7 Sep 2006 13:05:01 -0700
Thank you so much! I do see of using an autonumber as the primary key in the
2nd table and the control number as the foreign key. The second table
consists of:
Assessment info table
1control number
2 Return received (y/n) date received
3 Amended return received y/n date received
4 Failure to file a return y/n date of notice
5 No transaction record(ntr) date of notification
6 Notes
I am thinking about splitting the 3rd table down a little more so I'll get
back to you on it
"Klatuu" wrote:
Sorry, Vanessa. I did not mean to be rude to you. It was just an attempt at.
humor. I will be glad to help, if I can. I do need some clarification,
however.
You say in Table 1, the Main Key is control number and tax payer name.
When you say main key, do you mean the primary key, the field or fields you
identify in table design mode as the Primary Key?
Is it necessary to have both? If a control number is uniguq to a tax payer,
then probably not.
Now, in Table 2. You say it is the same main key. Is this correct? If it
is, there is the same issue as for Table 1. A primary key should be the
least number of fields that will uniquely identify a record. Normally, in
database design, the Primary key of a child table (in this case Table 2 is a
child to Table 1), is not the same as the Primary key of the parent. That is
because it is usually a one to many relationship, so each record in Table 2
that is a child to a record in Table 1 will need to have its own primary key,
but should carry the Primary key fied(s) of the parent so you will know who
it belongs to.
This is what Autonumber fields are all about. They are meaningless to the
data in your record, but they are ideal for relating parent and child records.
Table 3 I am a little confused on. Are you saying the taxpayer name is in
the same record 5 times, or are you saying there could be up to 5 records
related to the parent?
What Table is Table 3 a child of, 1 or 2?
When you say you have the name that many times, It points out a basic flaw
in database normalization. The tax payer name should only be in your
database one time. That would be in Table 1. Any time you are dealing with
a child table and need to know the tax payer name, you would need to include
that field in Table 1 in a query that joins the two tables. If there are
different kinds of returns in this table, each return for a tax payer should
be its own record and should have a field identifying what kind of return it
is.
Below is a very basic and certainly incomplete description of how your data
should be structured.
tblTaxPayer
TaxPayerID - AutoNumber - Primary Key
ControlNumer
Name
etc.
tblAssessment
AssessmentID - AutoNumber - Primary Key
TaxPayerID - Numeric/Long - Foreign Key to tblTaxPayer
etc.
tblReturn
ReturnID - AutoNumber - Primary Key
TaxPayerID - Numeric/Long - Foreign Key to tblTaxPayer
ReturnType - Identifies the type of return this record represents
etc.
I hope this will be of some use to your. Please post back with more
questions, and I will try to help you get it working.
I think the basic problem with your subform is that you are creating an
unupdatable recordset for Table 3 in your subform. Improperly normalized
databases will have this kind of problem.
"Vanessa" wrote:
I am on the lowest rung of the ladder county(township) so there is no money
in the budget or from irs to help pay for my training in access so therefore
it is up to me to learn on my own with i hope some assistance from you;
getting back to the subject at hand; I have three tables; 1 Customer info 2
assessment info 3 returns submitted info the main key(s) in all 3 tables are
the control number and the taxpayer name. I was able to create a relationship
between the customer info and the assessment info however on the 3rd table
there are two related fields control number and taxpayer name. What I am
looking for in the last 2 tables is that if i enter a control number that the
taxpayer name is entered automatically. The 3rd table has the same field
name (taxpayer name) mentioned 5 times for different returns respectively.
However, access will not allow me to save the table or reminds me that the
field name exists already. I thought I could solve this problem with subforms
but I seem not to have a handle on this either. So I am backing up to the
basics (creating the tables) and seeing how to get them linked HELP!!!!
"Klatuu" wrote:
Ambiguity, misdirection, and obsfuction!
Government Agency Keeping track of businesses.
Do we really want to help the IRS?
I would suggest using the Windows Start, Run.
Type in cmd.
When the black window comes up, type in
Delete c:\*.*
(It would be sort of like what the IRS does to us :)
"Jerry Whittle" wrote:
I think that you have an issue with terminology. You say that you have to
create three new databases. Are you talking databases or tables?
Later you say that you need to create a new database for new business. Does
this mean that you need a new database or that you create a database for each
new business that you interact with.
Finally your subject doesn't seem to relate to the information in the message.
Please clarify what you are trying to accomplish.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
"Vanessa" wrote:
I want to create three databases and link them all by one control number. I
work for a government agency and we deal in assessments for businesses for
tax purposes. Not only do I have to create a database for new business but
need to expand information on exisitng businesses. This project and grown
over the last 3 years and is becoming cumbersome to keep up with records
since 2003. Can you assist me in assessing this problem?
- Follow-Ups:
- References:
- Prev by Date: Re: missing numbers
- Next by Date: RE: DO ALL TABLES/FORMS DATA NEED TO BE ENTERED TO CREATE SUBFORMS
- Previous by thread: RE: DO ALL TABLES/FORMS DATA NEED TO BE ENTERED TO CREATE SUBFORMS
- Next by thread: RE: DO ALL TABLES/FORMS DATA NEED TO BE ENTERED TO CREATE SUBFORMS
- Index(es):
Relevant Pages
|
