too many fields - best way to create child table

[mark r.]

I have a customer table with primary key of TransactionID
and it has 255 fields. Each record represents a separate
sale, though customers have repeat sales. The
distinguishing characteristics between each sale is ID,
locationnumber,lastname, firstname, date-of-sale, date-of-
birth. I chose TransactionID to be the primary key to
keep things simple, because each transaction is unique.

Now there are many categories of fields which I could
place into another table if I understood how to make sure
that the other table had the exact transactionID as the
main customer table. Then I could join the to tables one-
to-one.

When the user initiates a new record in my main inpout
form, a tranasctionID is automatically generated. So If I
had a command button which opened a second form
to input data into this other category of fields:

1) how does that command button open a different table,
.............Docmd.Gotorecord-of-a-different-table

2) how can I make sure that transactionID in the second
table is automatically populated with the identical
transactionID from the main table, even if those fields
may be accessed on a different day than when the first
form was utilized by the user. I am imagining that i the
Afterupdate() event I could use the "set" command to make
table2.transactionID "get" table1.transactionID

 

[Hugh O'Neill]

I have a customer table with primary key of TransactionID
and it has 255 fields. Each record represents a separate
sale, though customers have repeat sales. The
distinguishing characteristics between each sale is ID,
locationnumber,lastname, firstname, date-of-sale, date-of-
birth. I chose TransactionID to be the primary key to
keep things simple, because each transaction is unique.

Now there are many categories of fields which I could
place into another table if I understood how to make sure
that the other table had the exact transactionID as the
main customer table. Then I could join the to tables one-
to-one.

When the user initiates a new record in my main inpout
form, a tranasctionID is automatically generated. So If I
had a command button which opened a second form
to input data into this other category of fields:

1) how does that command button open a different table,
............Docmd.Gotorecord-of-a-different-table

2) how can I make sure that transactionID in the second
table is automatically populated with the identical
transactionID from the main table, even if those fields
may be accessed on a different day than when the first
form was utilized by the user. I am imagining that i the
Afterupdate() event I could use the "set" command to make
table2.transactionID "get" table1.transactionID

Very briefly and somewhat simplistically, you need two Tables. One for
the Customer details including an indexed unique identifier Primary Key
Field. The other Table is for the Transactions. This Table will have
a Foreign key Field which you join to the Primary Key Field in the
Customer Table, using Referential Integrity in a one-to-many
relationship.

Next, create two Queries. One for the Customer Table and One for the
Transaction Table.

When you have those set up, and not before, you can design your Forms.
You will need a Main customer form using the customer Query as its
record source. In this form, you should create a Subform for the
transactions. Finally (well, sort of!), you need to set up the
Master/child link between the Subform's control on the main Form and
the Main Form itself. If you set everything up correctly, the link
tool will suggest the two Key Fields.

And away you go!

hth

Hugh

 

[TC]

You need to read:

http://support.microsoft.com/support/kb/articles/Q100139.ASP

HTH,
TC

I have a customer table with primary key of TransactionID
and it has 255 fields. Each record represents a separate
sale, though customers have repeat sales. The
distinguishing characteristics between each sale is ID,
locationnumber,lastname, firstname, date-of-sale, date-of-
birth. I chose TransactionID to be the primary key to
keep things simple, because each transaction is unique.

(snip)

 

[mark R.]

Hugh,

I appreciate your response on a labor day weekend, but I
think you presumed that my table was not normalized.

All my fields depend on my primary key, there are no
redundant groups or inconsistant dependencies (except for
one possibility which I will ask your opinion below as a
separate issue).

I simply have a humongous normalized table and I need a
second table to hold more fields.

The first table has an indexed primary key of AUTONUMBER
called ID and the second has a long integer indexed
primary key named ID2.

I have one form whereby the user inputs data to table1.
If I place fields from table2 onto that form:

1) how do I populate the ID2 field with the same AUTONUMBER
as table1?

2) how do I set the rowsource of the checkbox for a
table2 field to point to the other table, since the
underlying recordsource of the form is table1?

 

[mark R.]

Thanks........

All my fields depend on my primary key, there are no
redundant groups or inconsistant dependencies,except for
one possibility which I will ask your opinion,

Primary indexed key is AUTONUMBER (basically a transaction
ID)
Customer_address can be different on any given transaction
day.
BUT I ALWAYS WANT TO KNOW THE ADDRESS THAT THE CUSTOMER
WAS USING ON ANY GIVEN TRANSACTION DAY (I do not want to
overwrite address)
All other fields are not redundant or inconsistant.

I thought I would just leave the primary key as an
AUTONUMBER transactionID and let the user type the current
address into a combobox or select it from a query
supported combobox that lists all addresses the customer
ever used in the past ---- I have this already operational)

The article you had me read stated that sometimes one may
stray from typical normalization rules because in a
particular application, it may make sense to do so.

If you don't disagree with my choice, then it follows that
I simply have a humongous normalized table and I need a
second table to hold more fields.

The first table has that indexed primary key of AUTONUMBER
called ID and I made a second table with a long integer
indexed primary key named ID2.

I have one form whereby the user inputs data to table1.
If I place fields from table2 onto that form:

1) how do I populate the ID2 field with the same AUTONUMBER
as table1?

2) how do I set the rowsource of the checkbox for a
table2 field to point to the other table, since the
underlying recordsource of the form is table1?

 

[John W. Vinson]

I have a customer table with primary key of TransactionID
and it has 255 fields.

Then your table is quite certainly improperly normalized! I've needed
as many as 60 fields. TWICE, in many years of database design.

Each record represents a separate
sale, though customers have repeat sales. The
distinguishing characteristics between each sale is ID,
locationnumber,lastname, firstname, date-of-sale, date-of-
birth.

A sale doesn't have a last name or a date of birth. A *CUSTOMER* has
these attributes, but a sale doesn't! Even if you (unwisely IMO) want
to store this information repeatedly, you'ld still be better off
having a different table for each valid entity in your information
space.

I chose TransactionID to be the primary key to
keep things simple, because each transaction is unique.

Now there are many categories of fields which I could
place into another table if I understood how to make sure
that the other table had the exact transactionID as the
main customer table. Then I could join the to tables one-
to-one.

Incorrect: MANY TO MANY.

When the user initiates a new record in my main inpout
form, a tranasctionID is automatically generated. So If I
had a command button which opened a second form
to input data into this other category of fields:

Any reason not to use the tools that Access provides for this purpose
- Subforms? They maintain the link automatically with no code needed.

1) how does that command button open a different table,
............Docmd.Gotorecord-of-a-different-table

You're assuming that tables have record numbers. They don't.

2) how can I make sure that transactionID in the second
table is automatically populated with the identical
transactionID from the main table, even if those fields
may be accessed on a different day than when the first
form was utilized by the user. I am imagining that i the
Afterupdate() event I could use the "set" command to make
table2.transactionID "get" table1.transactionID

STOP, right now. Take a look at the Northwind sample database's table
structure. Take a look at any good reference book on Normalization.
You're making life much harder for yourself than it needs to be!

John W. Vinson [Access MVP]

 

[mark R.]

Consider a questionaire survey with 350 questions.
Consider that the table consists of the 350 questions,
the date/time-of-survey, lastname, firstname, date-of-
birth, address. A customer can take the survey multiple
times on multiple days, but most take the survey only
once, few twice, rarely three times, anlmost never on the
same day. Also, if a customer takes the survey on a
different day, their address may change and I don't want
to overwrite address info - I need to know what address
was used on which survey taken.

The only "normalization" to remove any fields would be to
have lastname, firstname birthdate and address in one
table and then the questions in another. So I save two
fields out of 355 and there are only few instances of
multiple addresses and I have the complication of
maintaining address to survey date. I didn't think it
worthwhile.

So, if you would reconsider your response, if I split off
150 questions into a second table, and use AUTONUMBER as
the primary indexed key in one table and a NUMBER foreign
key in the other with a one to one relationship, how do I
populate the NUMBER foreign key to match the first table
via my input form?

Look, I am no expert such as yourself, but I have a basic
understanding of parent and child table structure, so no
need to be condescending.

-----Original Message-----

I have a customer table with primary key of TransactionID
and it has 255 fields.

Then your table is quite certainly improperly normalized! I've needed
as many as 60 fields. TWICE, in many years of database design.

Each record represents a separate
sale, though customers have repeat sales. The
distinguishing characteristics between each sale is ID,
locationnumber,lastname, firstname, date-of-sale, date- of-
birth.

A sale doesn't have a last name or a date of birth. A *CUSTOMER* has
these attributes, but a sale doesn't! Even if you (unwisely IMO) want
to store this information repeatedly, you'ld still be better off
having a different table for each valid entity in your information

space.

I chose TransactionID to be the primary key to
keep things simple, because each transaction is unique.

Now there are many categories of fields which I could
place into another table if I understood how to make sure
that the other table had the exact transactionID as the
main customer table. Then I could join the to tables one-
to-one.

Incorrect: MANY TO MANY.

When the user initiates a new record in my main inpout
form, a tranasctionID is automatically generated. So If I
had a command button which opened a second form
to input data into this other category of fields:

Any reason not to use the tools that Access provides for this purpose
- Subforms? They maintain the link automatically with no code needed.

1) how does that command button open a different table,
............Docmd.Gotorecord-of-a-different-table

You're assuming that tables have record numbers. They don't.

2) how can I make sure that transactionID in the second
table is automatically populated with the identical
transactionID from the main table, even if those fields
may be accessed on a different day than when the first
form was utilized by the user. I am imagining that i the
Afterupdate() event I could use the "set" command to make
table2.transactionID "get" table1.transactionID

STOP, right now. Take a look at the Northwind sample database's table
structure. Take a look at any good reference book on Normalization.
You're making life much harder for yourself than it needs to be!

John W. Vinson [Access MVP]
.

 

[mark R.]

Consider a questionaire survey with 350 questions.
Consider that the table consists of the 350 questions,
the date/time-of-survey, lastname, firstname, date-of-
birth, address. A customer can take the survey multiple
times on multiple days, but most take the survey only
once, few twice, rarely three times, anlmost never on the
same day. Also, if a customer takes the survey on a
different day, their address may change and I don't want
to overwrite address info - I need to know what address
was used on which survey taken.

The only "normalization" to remove any fields would be to
have lastname, firstname birthdate and address in one
table and then the questions in another. So I save two
fields out of 355 and there are only few instances of
multiple addresses and I have the complication of
maintaining address to survey date. I didn't think it
worthwhile.

So, if you would reconsider your response, if I split off
150 questions into a second table, and use AUTONUMBER as
the primary indexed key in one table and a NUMBER foreign
key in the other with a one to one relationship, how do I
populate the NUMBER foreign key to match the first table
via my input form?

Look, I am no expert such as yourself, but I have a basic
understanding of parent and child table structure, so no
need to be condescending.

 

[Hugh O'Neill]

Hugh,

I appreciate your response on a labor day weekend, but I
think you presumed that my table was not normalized.

All my fields depend on my primary key, there are no
redundant groups or inconsistant dependencies (except for
one possibility which I will ask your opinion below as a
separate issue).

I simply have a humongous normalized table and I need a
second table to hold more fields.

The first table has an indexed primary key of AUTONUMBER
called ID and the second has a long integer indexed
primary key named ID2.

I have one form whereby the user inputs data to table1.
If I place fields from table2 onto that form:

1) how do I populate the ID2 field with the same AUTONUMBER
as table1?

2) how do I set the rowsource of the checkbox for a
table2 field to point to the other table, since the
underlying recordsource of the form is table1?

OK, well you obviously know what you are about. However, they are
quite rare and consequently I have never needed to make a one-to-one
relationship.

Having said that, this is possibly a way to do it (Access Help isn't
very specific about this). One Table has a unique index (Autonumber
should do) as the primary key. The other Table has a long integer
field also set to unique index values and also set to be the Primary
Key. In the Relationships screen drag one of these keys onto the other
and you get a one-to-one realationship shown.

As I say, I've not tried it for real, so others may have better ways of
doing this.

hth

Hugh

 

[John W. Vinson]

Consider a questionaire survey with 350 questions.

I have, several times; up to a thousand questions in fact. It's
perfectly possible to do this in a tall-thin manner. See Duane
Hookum's AtYourSurvey at

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

how do I populate the NUMBER foreign key to match the first table
via my input form?

By entering the many side tables' data using a Subform; the
Master/Child Link Fields will populate the child tables' foreign keys.
No code needed.

John W. Vinson [Access MVP]

 

[John Spencer (MVP)]

Ok, take your example. You should NOT have one field for each question in a
table. You should have a table of questions. So the very basic structure would
look something like:

tblQuestions
fldQuestionID
fldQuestionNumber
fldQuestionText

tblQuestionResponses
fldCustomerID
fldQuestionID
fldResponse

tblCustomers
fldCustomerID
fldLastName
fldFirstName
...

That would be the basic structure for a survey as you have proposed. You might
take a look at a more complex example of doing this.

Duane Hookom has a sample survey database at
http://rogersaccesslibrary.com/duanehookom/duanehookom.htm

This fully functional application uses a small collection of tables,
queries, forms, reports, and code to manage multiple surveys. Users can
create a survey, define questions, enter pre-defined answers, limit to list,
report results, create crosstabs, and other features without changing the
design of any objects.

Consider a questionaire survey with 350 questions.
Consider that the table consists of the 350 questions,
the date/time-of-survey, lastname, firstname, date-of-
birth, address. A customer can take the survey multiple
times on multiple days, but most take the survey only
once, few twice, rarely three times, anlmost never on the
same day. Also, if a customer takes the survey on a
different day, their address may change and I don't want
to overwrite address info - I need to know what address
was used on which survey taken.

The only "normalization" to remove any fields would be to
have lastname, firstname birthdate and address in one
table and then the questions in another. So I save two
fields out of 355 and there are only few instances of
multiple addresses and I have the complication of
maintaining address to survey date. I didn't think it
worthwhile.

So, if you would reconsider your response, if I split off
150 questions into a second table, and use AUTONUMBER as
the primary indexed key in one table and a NUMBER foreign
key in the other with a one to one relationship, how do I
populate the NUMBER foreign key to match the first table
via my input form?

Look, I am no expert such as yourself, but I have a basic
understanding of parent and child table structure, so no
need to be condescending.

-----Original Message-----

I have a customer table with primary key of TransactionID
and it has 255 fields.

Then your table is quite certainly improperly normalized! I've needed
as many as 60 fields. TWICE, in many years of database design.

Each record represents a separate
sale, though customers have repeat sales. The
distinguishing characteristics between each sale is ID,
locationnumber,lastname, firstname, date-of-sale, date- of-
birth.

A sale doesn't have a last name or a date of birth. A *CUSTOMER* has
these attributes, but a sale doesn't! Even if you (unwisely IMO) want
to store this information repeatedly, you'ld still be better off
having a different table for each valid entity in your information

space.

I chose TransactionID to be the primary key to
keep things simple, because each transaction is unique.

Now there are many categories of fields which I could
place into another table if I understood how to make sure
that the other table had the exact transactionID as the
main customer table. Then I could join the to tables one-
to-one.

Incorrect: MANY TO MANY.

When the user initiates a new record in my main inpout
form, a tranasctionID is automatically generated. So If I
had a command button which opened a second form
to input data into this other category of fields:

Any reason not to use the tools that Access provides for this purpose
- Subforms? They maintain the link automatically with no code needed.

1) how does that command button open a different table,
............Docmd.Gotorecord-of-a-different-table

You're assuming that tables have record numbers. They don't.

2) how can I make sure that transactionID in the second
table is automatically populated with the identical
transactionID from the main table, even if those fields
may be accessed on a different day than when the first
form was utilized by the user. I am imagining that i the
Afterupdate() event I could use the "set" command to make
table2.transactionID "get" table1.transactionID

STOP, right now. Take a look at the Northwind sample database's table
structure. Take a look at any good reference book on Normalization.
You're making life much harder for yourself than it needs to be!

John W. Vinson [Access MVP]
.

 

[Mark R.]

Hugh,

You are right, I already have a one to one relationship
establisged.

What I don't get is how I should populate the "other long
integer" primary key field in the second table.

Do I put some =Table!fieldname reference in the
Default value of the table definition.

Do I put some code in the Formload() of my input form
Let Table2!integerfield = Table1AUTONUMBERfield
or something like that

I think it must be a simple answer

 

[Mark R.]

You seem to be the most knowledgeable and receptive of
responders so far..............thank you

Question: once I have a one-to-one relationship
established, with the first table using AUTONUMBER as the
indexed primary key andthe second table using LONGINTEGER
as its indexed primary key, HOW DO I populate the "second"
tables key field to match the "first" table's key field.







==========================================================
The following is a separate issue I would rather not
address in this discussion:

Pursuing your example,

tblQuestionResponses
fldCustomerID
fldQuestionID
fldResponse

tblCustomers
fldCustomerID
fldLastName
fldFirstName
...

I would need another field in tblQuestionResponses
called fldSurveydate because a customer may have
a different response on a different day, having taken the
survey three times on three different days.

Then, I guess I have 350 tables that look like
tblQuestionResponse because there are 350 question IDs

I guess somehow you use AppendQueries to link all 350
tables to tblCustomer as a rowsource to a form for input,
and that's where I get lost.

So since I understand a flat table using AUTONUMBER as a
unique key, and 90% of customers do only one survey, it
seems more efficient for me, right now, to stick to a one-
to-one relationship to a second table to handle my extra
110 questions.

 

[John W. Vinson]

You seem to be the most knowledgeable and receptive of
responders so far..............thank you

I'm willing to go along... WITH WHAT I CONSIDER AN EXTREMELY BAD
DESIGN, one that *WILL* cause you no end of headaches down the road.
I'd *REALLY* recommend that you investigate AtYourSurvey. It's a very
flexible app.

Question: once I have a one-to-one relationship
established, with the first table using AUTONUMBER as the
indexed primary key andthe second table using LONGINTEGER
as its indexed primary key, HOW DO I populate the "second"
tables key field to match the "first" table's key field.

As I've said twice... I'll say a third time:

By entering the data for the second table on a Subform, on a form
bound to the first table, using the linking field as the Master/Child
Linking Field property of the Subform control.

The following is a separate issue I would rather not
address in this discussion:

Pursuing your example,


I would need another field in tblQuestionResponses
called fldSurveydate because a customer may have
a different response on a different day, having taken the
survey three times on three different days.
Fine.

Then, I guess I have 350 tables that look like
tblQuestionResponse because there are 350 question IDs

No. YOU HAVE ONE tblQuestionResponses. Store the question ID *IN THE
TABLE!* You're using a relational database, after all!

It has fields:

fldCustomerID
fldQuestionID
fldSurveyDate <<< three-field composite Primary Key
Answer

If you have 350 questions - or 895 questions - you have 350 *ROWS* (or
895 rows) in this table every time someone takes the questionnaire.
It's a tall skinny table, not a wide flat one, but it contains all the
information that your wide-flat table contains. It has the added
advantage that if a question changes, or is added, or is deleted, you
don't need to go in and change your table structures, rewrite all your
queries, and redesign all your forms and reports.

I guess somehow you use AppendQueries to link all 350
tables to tblCustomer as a rowsource to a form for input,
and that's where I get lost.

So since I understand a flat table using AUTONUMBER as a
unique key, and 90% of customers do only one survey, it
seems more efficient for me, right now, to stick to a one-
to-one relationship to a second table to handle my extra
110 questions.

It seems that way. For today, it may be better.

For any longterm use, *this design is a major disaster waiting to
happen*.

 

[Hugh O'Neill]

Hugh,

You are right, I already have a one to one relationship
establisged.

What I don't get is how I should populate the "other long
integer" primary key field in the second table.

Do I put some =Table!fieldname reference in the
Default value of the table definition.

Do I put some code in the Formload() of my input form
Let Table2!integerfield = Table1AUTONUMBERfield
or something like that

I think it must be a simple answer

There's no coding to do! The referential integrity on the Table join
sees to that. If you use a main form containing a Subform, rmember to
set the Master/Child link in the Subform control on the main Form. It
will automatically offer the two joined Fields, anyway, so just confirm
it. Then, whenever you enter anything in the Subform, the foreign key
Field in the underlying Table will automatically be filled in with the
Primary Key Field's value.

hth

Hugh

 

[Hugh O'Neill]

Hugh,

You are right, I already have a one to one relationship
establisged.

What I don't get is how I should populate the "other long
integer" primary key field in the second table.

Do I put some =Table!fieldname reference in the
Default value of the table definition.

Do I put some code in the Formload() of my input form
Let Table2!integerfield = Table1AUTONUMBERfield
or something like that

I think it must be a simple answer

Mark, now I know what the application is, I have to say that John and
John are both quite correct in their advice about your structure. I
agree with them wholeheartedly in suggesting that you really should
consider revisiting the Table structure.

Hugh

 

[TC]

Hi Mark

In thinking about normalization, it is best to completely forget about
"physical" things like autonumbers, combo boxes, queries, long integers,
populating fields, rowsources, & the various other things that you said in
your post.

It is better to focus on the following questions:
- What tables do I need?
- What is the primary key of each table (ignoring its data type), and
- What are the relationships between those tables?

THEN you can bother about data types (autonumbers? long integers?), combo
boxes, queries, populating fields, rowsources, & so on.

Believe me - this isn't a quibble. It is one of the secrets to understanding
how to design a proper relational database. You need to get the table
structures, primary keys & relationships right - in your head - before you
worry about combo boxes, form rowsources, & so on.

HTH,
TC

 

[Bob]

I stand by my previous post.

Survey Information Table
Survey ID
Customer Name
Customer Address
etc.

Response Table
Survey ID
Question Number
Answer

In your scenario, how do you summarize the results. It seems you would have
to write a summary calc of some sort for each field. In my scenario, you
would write one summary query, grouping by question number. If the
questions require different types of answers (number, letter, phrase), you
could have another table with question number, question, type of response,
etc. Then your response would have a few more fields, Numerical Answer,
Text Answer, etc. and use just the correct field for each answer. Then you
would group by answer type, also.