DO ALL TABLES/FORMS DATA NEED TO BE ENTERED TO CREATE SUBFORMS

[Vanessa]

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?

 

[Jerry Whittle]

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.

 

[Klatuu]

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

 

[Vanessa]

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]

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]

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]

Okay. One thing you might think about for the Assessment table is whether
you send out multiple notices. If you do, you current table will only record
either the first notice date or the most recent. If you send out multiple
notices and want to track all notices sent, you may need a child to
Assessments to have a record to record the sending of each notice.

 

[Vanessa]

Good Morning. On the first table I have up to 17 fields for basic taxpayer
information. The usual Contorl Number, name address etc. Also If a taxpayer
mailing information is different from the location of the personal property I
also have that included as well. So the basic layout is as such. Just want
to know if its too much for one table

1. Contnum 2. tpname1 3. tpname2 4. tpaddr1 5. tpaddr2 6. tpcity
7 tpstat 8. tpzip 9 tpphn 10 tpfax 11 tpemail 12 ppaddr 13
ppcity 14 TID
15. inital date 16 Source 17 Notes

 

[Klatuu]

What happens if a taxpayer owns more than one property?

 

[Vanessa]

Good morning Klatuu. I thought I would be able to put this off till later.
Anyhoo the answer to your question lies in several scenarios. First is the
simplest, they don't; second we have two forms that I cross reference. One
form the taxpayer says i have possession of this property but I don't own it
the second scenario is that I own this property at that location and am
paying the taxes on it. I was hoping to cover this later when it came to the
other types of forms that the taxpayer(s) submit; but if it has to be covered
now I'd rather have it cleaned up now than biting later down the road Thanks

 

[Klatuu]

Then I don't see a problem with the table design. So what do we want to do
next?

 

[Vanessa]

Okay now we have discussed 2 of the tables and here is the layout of table #3
which has the other forms attached:
1. Contnum
2. 103N y/n
3. tpname
4. 103O y/n
5. tpname
6. ppid y/n
7. tpname
8. ez2 y/n
9 tpname
10. abatemen y/n
11.tpname

And this is where my nightmare started!!! So any suggestions on how to make
these tables flow as smoothly as possible. I have been gathering test data;
returns that entail the information that is represented in each table. But
I'll wait to see if you have anymore suggestions. You're a great help! Can I
keep you in my file cabinet?

 

[Klatuu]

I need to go back and look at the other tables. I'm really wrapped up with a
report that has to be done ASAP and it is tricky because there are 7
different group levels that have to be in different orders and
visible/invisible depending on user selection, so I may not get to it until
tomorrow.

If you keep me in your file cabinent, can you drill a couple of air holes?

 

[John Vinson]

Okay now we have discussed 2 of the tables and here is the layout of table #3
which has the other forms attached:
1. Contnum
2. 103N y/n
3. tpname
4. 103O y/n
5. tpname
6. ppid y/n
7. tpname
8. ez2 y/n
9 tpname
10. abatemen y/n
11.tpname

Why on Earth do you have the same field (tpname) in the table five
times!? (You can't have, and don't need, multiple instances of the
same field).

It looks like a better design would be to model this one to many
relationship as a one to many relationship:

Contnum
Type <e.g. "103N", "1030", "ppid">
tpname

with five RECORDS rather than five sets of fields.

John W. Vinson[MVP]

 

[Vanessa]

Because they can have more than one. They can have a 103N and/or O as well
as a PPID etc., but I do see your point however. What I would like to see
happen is the tpname to pop up automatically when the contnum is put in on an
existing account instead of me retyping and researching

 

[John Vinson]

Because they can have more than one. They can have a 103N and/or O as well
as a PPID etc., but I do see your point however.

Of course. That's why I suggest the normalized structure. You can have
TENS OF MILLIONS of records in a table; you can certainly have five
records, one for a 103N, another for a 1030, another for a PPID.
That's how relational databases WORK.

What I would like to see
happen is the tpname to pop up automatically when the contnum is put in on an
existing account instead of me retyping and researching

In a normalized table design you store the tpname once, and once only;
you can DISPLAY IT ON THE SCREEN as many times as you wish.

Don't confuse data STORAGE with data DISPLAY. They are *two separate
operations* with different requirements. Your table structure should
be driven by the logical relationships between the data; once the
table structure is correct, *then* you can design your Forms to
display data from multiple tables, as needed. Letting your form design
dictate your table structure is almost guaranteed to give you a VERY
BAD table structure.

To get back to the subject line of this thread:

No. It is NOT necessary to enter ANY data in order to create subforms.
It's quite the opposite: you *first* design your Tables, using the
logical structure of the data; you *second* create your Forms, based
on those (empty) tables; only *then* do you start entering data into
the tables, using the Forms and Subforms to do so.

John W. Vinson[MVP]

 

[Vanessa]

Klatuu have you had a chance to look at my stuff yet

 

[Vanessa]

I'm ready to try with some test data. What do you suggest?

 

[Vanessa]

Okay then as of this morning I have created two of the tables. What I want to
do in the second table is when I enter the control number that the tpname
would pop up automatically. How do I do this?

 

[John Vinson]

Okay then as of this morning I have created two of the tables. What I want to
do in the second table is when I enter the control number that the tpname
would pop up automatically. How do I do this?

Use a Form with a Combo Box and/or a Subform.

I have NO idea what your current tables are, nor how they are related,
nor any clear idea about the meaning of the data. If you are using
Tables to enter data... *don't*. Tables are NOT designed for data
entry, but for data *storage*.

If you have one tpname to many records of data about that tpname, a
convenient way to see both is to use a Form based on the table
containing tpname, with a continuous Subform based on the related
table. That way you don't need to have the tpname "pop up" at all -
it's just there in plain view.


John W. Vinson[MVP]