Data Normalization

[R Tanner]

Since the whole purpose of data normalization is to ensure the
integrity of your data and eliminate duplications, if you have, say,
one column in a 10 column invoices table that tracks the name of the
client and that is the only aspect of the client that you track, you
would not want to make a separate table for it would you? since you
only track that one piece of information on the client, you could keep
it in the invoices table - there would be no benefit to an additional
table.

 

[Jeff Boyce]

If you are saying that the [ClientName] column contains a text string, then
you will be repeating that same string for each record involving the same
client.

Why would this matter?

Peoples' names change. Data entry is done with errors. Do you want to have
to review EVERY record in your table and update a changed (or incorrectly
entered) [ClientName], or would you rather go to the [tblClient] table and
change it one time?

The former approach increases the risk of having rows for:

John Doe
J.J. Doe
John J. Doe
Johnnie Doe
J. Dough

So what? So Access will NOT know that these are all the same person unless
YOU know they are all the same person and tell it. Much easier to have a
single record in a tblClient, with a ClientID, and store that ClientID in
each of the records you posited in your question. Less risk of data
integrity issues.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[R Tanner]

If you are saying that the [ClientName] column contains a text string, then
you will be repeating that same string for each record involving the same
client.

Why would this matter?

Peoples' names change.  Data entry is done with errors.  Do you want to have
to review EVERY record in your table and update a changed (or incorrectly
entered) [ClientName], or would you rather go to the [tblClient] table and
change it one time?

The former approach increases the risk of having rows for:

    John Doe
    J.J. Doe
    John J. Doe
    Johnnie Doe
    J. Dough

So what?  So Access will NOT know that these are all the same person unless
YOU know they are all the same person and tell it.  Much easier to havea
single record in a tblClient, with a ClientID, and store that ClientID in
each of the records you posited in your question.  Less risk of data
integrity issues.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Since the whole purpose of data normalization is to ensure the
integrity of your data and eliminate duplications, if you have, say,
one column in a 10 column invoices table that tracks the name of the
client and that is the only aspect of the client that you track, you
would not want to make a separate table for it would you?  since you
only track that one piece of information on the client, you could keep
it in the invoices table - there would be no benefit to an additional
table.- Hide quoted text -

- Show quoted text -

Okay great. That makes sense.

 

[R Tanner]

If you are saying that the [ClientName] column contains a text string, then
you will be repeating that same string for each record involving the same
client.

Why would this matter?

Peoples' names change.  Data entry is done with errors.  Do you want to have
to review EVERY record in your table and update a changed (or incorrectly
entered) [ClientName], or would you rather go to the [tblClient] table and
change it one time?

The former approach increases the risk of having rows for:

    John Doe
    J.J. Doe
    John J. Doe
    Johnnie Doe
    J. Dough

So what?  So Access will NOT know that these are all the same person unless
YOU know they are all the same person and tell it.  Much easier to havea
single record in a tblClient, with a ClientID, and store that ClientID in
each of the records you posited in your question.  Less risk of data
integrity issues.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Since the whole purpose of data normalization is to ensure the
integrity of your data and eliminate duplications, if you have, say,
one column in a 10 column invoices table that tracks the name of the
client and that is the only aspect of the client that you track, you
would not want to make a separate table for it would you?  since you
only track that one piece of information on the client, you could keep
it in the invoices table - there would be no benefit to an additional
table.- Hide quoted text -

- Show quoted text -

I was having problems appending data to a table that has lookup
columns with that given scenario and I was trying to figure out why I
can't just put it all in one table but you cleared up my question for
me.

 

[R Tanner]

If you are saying that the [ClientName] column contains a text string, then
you will be repeating that same string for each record involving the same
client.

Why would this matter?

Peoples' names change.  Data entry is done with errors.  Do you want to have
to review EVERY record in your table and update a changed (or incorrectly
entered) [ClientName], or would you rather go to the [tblClient] table and
change it one time?

The former approach increases the risk of having rows for:

    John Doe
    J.J. Doe
    John J. Doe
    Johnnie Doe
    J. Dough

So what?  So Access will NOT know that these are all the same person unless
YOU know they are all the same person and tell it.  Much easier to havea
single record in a tblClient, with a ClientID, and store that ClientID in
each of the records you posited in your question.  Less risk of data
integrity issues.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Since the whole purpose of data normalization is to ensure the
integrity of your data and eliminate duplications, if you have, say,
one column in a 10 column invoices table that tracks the name of the
client and that is the only aspect of the client that you track, you
would not want to make a separate table for it would you?  since you
only track that one piece of information on the client, you could keep
it in the invoices table - there would be no benefit to an additional
table.- Hide quoted text -

- Show quoted text -

But just to clarify, John Doe is not the same on all records. 30% are
John Doe while another 30% are Jack Johnson and then the remaining 40%
are John Smith.

 

[Jeff Boyce]

But just to clarify, John Doe is not the same on all records. 30% are
John Doe while another 30% are Jack Johnson and then the remaining 40%
are John Smith.


Are you saying the entities known as "John Doe", "Jack Johnson" and "John
Smith" are the [ClientNames] in your table?

If so, EntityID = 1, =2, and =3 could serve as well, taken from a tblEntity
with those three IDs and those three 'names'.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[R Tanner]

But just to clarify, John Doe is not the same on all records.  30% are
John Doe while another 30% are Jack Johnson and then the remaining 40%
are John Smith.

Are you saying the entities known as "John Doe", "Jack Johnson" and "John
Smith" are the [ClientNames] in your table?

If so, EntityID = 1, =2, and =3 could serve as well, taken from a tblEntity
with those three IDs and those three 'names'.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay I understand that...I think I have down the concept finally...I'm
just having trouble running append queries to append new data to my
old data...It tells me the file is too large or I don't have enough
memory...any guesses?

 

[Douglas J. Steele]

Afraid that "having trouble" doesn't give us much to go by.

What trouble are you having? Are you getting any error messages? If so, what
are they?

What's the SQL of the queries you're trying to run?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

But just to clarify, John Doe is not the same on all records. 30% are
John Doe while another 30% are Jack Johnson and then the remaining 40%
are John Smith.

Are you saying the entities known as "John Doe", "Jack Johnson" and "John
Smith" are the [ClientNames] in your table?

If so, EntityID = 1, =2, and =3 could serve as well, taken from a
tblEntity
with those three IDs and those three 'names'.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay I understand that...I think I have down the concept finally...I'm
just having trouble running append queries to append new data to my
old data...It tells me the file is too large or I don't have enough
memory...any guesses?

 

[R Tanner]

Afraid that "having trouble" doesn't give us much to go by.

What trouble are you having? Are you getting any error messages? If so, what
are they?

What's the SQL of the queries you're trying to run?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

But just to clarify, John Doe is not the same on all records. 30% are
John Doe while another 30% are Jack Johnson and then the remaining 40%
are John Smith.

Are you saying the entities known as "John Doe", "Jack Johnson" and "John
Smith" are the [ClientNames] in your table?

If so, EntityID = 1, =2, and =3 could serve as well, taken from a
tblEntity
with those three IDs and those three 'names'.

Jeff Boyce
Microsoft Office/Access MVP

Okay I understand that...I think I have down the concept finally...I'm
just having trouble running append queries to append new data to my
old data...It tells me the file is too large or I don't have enough
memory...any guesses?

Well let me tell you what I have done first so maybe we can clear up a
major piece of the puzzle I am missing. I imported an additional
30,000 rows of data I need to store in my table into it's own
independent table in the hopes of running an append query and
appending it to my primary orders table. In my primary orders table,
I have 3 lookup columns to parent tables. Do I need to define a
relationship between these parent tables and my new data before I run
an append query? Before, I was trying to just append the new data to
the orders table (a child table) - - This included appending the
values being looked up from the parent table...

I can't paste records in here...I have tried that before and it looks
horrible when it is posted...So to explain, these are my categories in
my orders table:

Order
Contract Size
Price
Date
Time
Lookup To Banks
Lookup To Currencies
Lookup To Order Types

My Lookup columns are foreign keys representing the corresponding data
in their parent tables...

So what would be the process to mesh the new data with the child
'Orders Table'?

Thanks for your help.

 

[Douglas J. Steele]

"I have 3 lookup columns to parent tables."

Do you mean you're using that misfeature known as the lookup field? See
http://www.mvps.org/access/lookupfields.htm at "The Access Web" for some of
the reasons why most of us advise never to use lookup columns.

If you've got relationships set up to enforce referential integrity, then
the data must exist in the parent table before the related data can be
inserted into the child table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Afraid that "having trouble" doesn't give us much to go by.

What trouble are you having? Are you getting any error messages? If so,
what
are they?

What's the SQL of the queries you're trying to run?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

But just to clarify, John Doe is not the same on all records. 30% are
John Doe while another 30% are Jack Johnson and then the remaining 40%
are John Smith.

Are you saying the entities known as "John Doe", "Jack Johnson" and
"John
Smith" are the [ClientNames] in your table?

If so, EntityID = 1, =2, and =3 could serve as well, taken from a
tblEntity
with those three IDs and those three 'names'.

Jeff Boyce
Microsoft Office/Access MVP

Okay I understand that...I think I have down the concept finally...I'm
just having trouble running append queries to append new data to my
old data...It tells me the file is too large or I don't have enough
memory...any guesses?

Well let me tell you what I have done first so maybe we can clear up a
major piece of the puzzle I am missing. I imported an additional
30,000 rows of data I need to store in my table into it's own
independent table in the hopes of running an append query and
appending it to my primary orders table. In my primary orders table,
I have 3 lookup columns to parent tables. Do I need to define a
relationship between these parent tables and my new data before I run
an append query? Before, I was trying to just append the new data to
the orders table (a child table) - - This included appending the
values being looked up from the parent table...

I can't paste records in here...I have tried that before and it looks
horrible when it is posted...So to explain, these are my categories in
my orders table:

Order
Contract Size
Price
Date
Time
Lookup To Banks
Lookup To Currencies
Lookup To Order Types

My Lookup columns are foreign keys representing the corresponding data
in their parent tables...

So what would be the process to mesh the new data with the child
'Orders Table'?

Thanks for your help.

 

[R Tanner]

"I have 3 lookup columns to parent tables."

Do you mean you're using that misfeature known as the lookup field? Seehttp://www.mvps.org/access/lookupfields.htmat "The Access Web" for some of
the reasons why most of us advise never to use lookup columns.

If you've got relationships set up to enforce referential integrity, then
the data must exist in the parent table before the related data can be
inserted into the child table.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

Afraid that "having trouble" doesn't give us much to go by.

What trouble are you having? Are you getting any error messages? If so,
what
are they?

What's the SQL of the queries you're trying to run?

"R Tanner" <[email protected]> wrote in message

But just to clarify, John Doe is not the same on all records. 30% are
John Doe while another 30% are Jack Johnson and then the remaining 40%
are John Smith.
Are you saying the entities known as "John Doe", "Jack Johnson" and
"John
Smith" are the [ClientNames] in your table?
If so, EntityID = 1, =2, and =3 could serve as well, taken froma
tblEntity
with those three IDs and those three 'names'.
Regards
Jeff Boyce
Microsoft Office/Access MVP

Okay I understand that...I think I have down the concept finally...I'm
just having trouble running append queries to append new data to my
old data...It tells me the file is too large or I don't have enough
memory...any guesses?

Well let me tell you what I have done first so maybe we can clear up a
major piece of the puzzle I am missing.  I imported an additional
30,000 rows of data I need to store in my table into it's own
independent table in the hopes of running an append query and
appending it to my primary orders table.  In my primary orders table,
I have 3 lookup columns to parent tables.  Do I need to define a
relationship between these parent tables and my new data before I run
an append query?  Before, I was trying to just append the new data to
the orders table (a child table) - - This included appending the
values being looked up from the parent table...

I can't paste records in here...I have tried that before and it looks
horrible when it is posted...So to explain, these are my categories in
my orders table:

Order
Contract Size
Price
Date
Time
Lookup To Banks
Lookup To Currencies
Lookup To Order Types

My Lookup columns are foreign keys representing the corresponding data
in their parent tables...

So what would be the process to mesh the new data with the child
'Orders Table'?

Thanks for your help.- Hide quoted text -

- Show quoted text -

Okay to explain...I have solved this problem and this is the SQL
statement I used...Orders is the table I was inserting the new data
into, Currencies, Banks, and Order Types are my parent tables, and Jan
17 2008 is my table I am appending...

One of my problems is I am learning SQL at the same time I am learning
Access...I didn't realize I would need to know SQL to really be able
to do stuff with Access...but anyways...

INSERT INTO Orders ( [Order], [Order Types_ID], Contract_Size,
Currencies_ID, Price, Banks_ID, [Date], [Time] )
SELECT [Jan 17 2008].Order, [Order Types].ID, [Jan 17 2008].Contacts,
Currencies.ID, [Jan 17 2008].Price, Banks.ID, [Jan 17 2008].Date, [Jan
17 2008].Time
FROM (([Jan 17 2008] INNER JOIN Currencies ON [Jan 17
2008].Currency=Currencies.Currency) INNER JOIN Banks ON [Jan 17
2008].Bank=Banks.Bank) INNER JOIN [Order Types] ON [Jan 17
2008].Order_Type=[Order Types].Order_Type;


Onto your reference to the link about the lookup fields...I understand
I should not use them - but I don't understand how else to display a
foreign key to the parent table other than through a lookup
field...For example, let me propose what I have done so far with my
current database I am working on. It consists of about 30
spreadsheets with 17 fields I have imported from excel - all of the
data is in one table - which once it is normalized will be about 7
tables. I tried to split it with the table analyzer but it didn't
work. So. Focusing on one column, I ran a totals query to pull the
data out and then I ran a make table query to make it a table. I then
named this table Trade_Specialists. My child table now has 16 fields,
labeled Tickets. I added an additional field to Tickets called
TradeSpecialists_ID and created a relationship between this field and
the ID field in Trade_Specialists. My world is at a halt right now
cause I can't figure out how to get the freakin foreign ID in this
foreign ID field. If you go to my parent table, Trade_Specialists, I
have about 15 rows, with the names of each trade specialist and the ID
for each. There is a subdatasheet for each trade specialist showing
the tickets he/she has done.

Also of note is that in my Tickets table, because of how I created my
parent table (totals query --> make table query), I still have the
original Trade Specialist field. Of course I can't relate that field
to the parent field because it is a text data type, so I'm not sure
what to do with it.

After I figure out how to do this, I think I will be fine for figuring
out the rest. I have come a long ways in a short period of time as
far as my understanding of database design, normalization, etc.

 

[Douglas J. Steele]

Hmm. Given that SQL, the necessary data should exist in Currencies, Banks
and OrderTypes before you try to insert them into Orders. What's the error
message you're getting again?

As to your comment "I don't understand how else to display a foreign key to
the parent table other than through a lookup field", you should never be
working directly with the tables. You should always use a form. Putting a
combo box on the form is appropriate, putting a combo box into a table
isn't.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Okay to explain...I have solved this problem and this is the SQL
statement I used...Orders is the table I was inserting the new data
into, Currencies, Banks, and Order Types are my parent tables, and Jan
17 2008 is my table I am appending...

One of my problems is I am learning SQL at the same time I am learning
Access...I didn't realize I would need to know SQL to really be able
to do stuff with Access...but anyways...

INSERT INTO Orders ( [Order], [Order Types_ID], Contract_Size,
Currencies_ID, Price, Banks_ID, [Date], [Time] )
SELECT [Jan 17 2008].Order, [Order Types].ID, [Jan 17 2008].Contacts,
Currencies.ID, [Jan 17 2008].Price, Banks.ID, [Jan 17 2008].Date, [Jan
17 2008].Time
FROM (([Jan 17 2008] INNER JOIN Currencies ON [Jan 17
2008].Currency=Currencies.Currency) INNER JOIN Banks ON [Jan 17
2008].Bank=Banks.Bank) INNER JOIN [Order Types] ON [Jan 17
2008].Order_Type=[Order Types].Order_Type;


Onto your reference to the link about the lookup fields...I understand
I should not use them - but I don't understand how else to display a
foreign key to the parent table other than through a lookup
field...For example, let me propose what I have done so far with my
current database I am working on. It consists of about 30
spreadsheets with 17 fields I have imported from excel - all of the
data is in one table - which once it is normalized will be about 7
tables. I tried to split it with the table analyzer but it didn't
work. So. Focusing on one column, I ran a totals query to pull the
data out and then I ran a make table query to make it a table. I then
named this table Trade_Specialists. My child table now has 16 fields,
labeled Tickets. I added an additional field to Tickets called
TradeSpecialists_ID and created a relationship between this field and
the ID field in Trade_Specialists. My world is at a halt right now
cause I can't figure out how to get the freakin foreign ID in this
foreign ID field. If you go to my parent table, Trade_Specialists, I
have about 15 rows, with the names of each trade specialist and the ID
for each. There is a subdatasheet for each trade specialist showing
the tickets he/she has done.

Also of note is that in my Tickets table, because of how I created my
parent table (totals query --> make table query), I still have the
original Trade Specialist field. Of course I can't relate that field
to the parent field because it is a text data type, so I'm not sure
what to do with it.

After I figure out how to do this, I think I will be fine for figuring
out the rest. I have come a long ways in a short period of time as
far as my understanding of database design, normalization, etc.

 

[R Tanner]

Hmm. Given that SQL, the necessary data should exist in Currencies, Banks
and OrderTypes before you try to insert them into Orders. What's the error
message you're getting again?

As to your comment "I don't understand how else to display a foreign key to
the parent table other than through a lookup field", you should never be
working directly with the tables. You should always use a form. Putting a
combo box on the form is appropriate, putting a combo box into a table
isn't.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)



Okay to explain...I have solved this problem and this is the SQL
statement I used...Orders is the table I was inserting the new data
into, Currencies, Banks, and Order Types are my parent tables, and Jan
17 2008 is my table I am appending...

One of my problems is I am learning SQL at the same time I am learning
Access...I didn't realize I would need to know SQL to really be able
to do stuff with Access...but anyways...

INSERT INTO Orders ( [Order], [Order Types_ID], Contract_Size,
Currencies_ID, Price, Banks_ID, [Date], [Time] )
SELECT [Jan 17 2008].Order, [Order Types].ID, [Jan 17 2008].Contacts,
Currencies.ID, [Jan 17 2008].Price, Banks.ID, [Jan 17 2008].Date, [Jan
17 2008].Time
FROM (([Jan 17 2008] INNER JOIN Currencies ON [Jan 17
2008].Currency=Currencies.Currency) INNER JOIN Banks ON [Jan 17
2008].Bank=Banks.Bank) INNER JOIN [Order Types] ON [Jan 17
2008].Order_Type=[Order Types].Order_Type;

Onto your reference to the link about the lookup fields...I understand
I should not use them - but I don't understand how else to display a
foreign key to the parent table other than through a lookup
field...For example, let me propose what I have done so far with my
current database I am working on.  It consists of about 30
spreadsheets with 17 fields I have imported from excel - all of the
data is in one table - which once it is normalized will be about 7
tables.  I tried to split it with the table analyzer but it didn't
work.  So.  Focusing on one column, I ran a totals query to pull the
data out and then I ran a make table query to make it a table.  I then
named this table Trade_Specialists.  My child table now has 16 fields,
labeled Tickets.  I added an additional field to Tickets called
TradeSpecialists_ID and created a relationship between this field and
the ID field in Trade_Specialists.  My world is at a halt right now
cause I can't figure out how to get the freakin foreign ID in this
foreign ID field.  If you go to my parent table, Trade_Specialists, I
have about 15 rows, with the names of each trade specialist and the ID
for each.  There is a subdatasheet for each trade specialist showing
the tickets he/she has done.

Also of note is that in my Tickets table, because of how I created my
parent table (totals query --> make table query), I still have the
original Trade Specialist field.  Of course I can't relate that field
to the parent field because it is a text data type, so I'm not sure
what to do with it.

After I figure out how to do this, I think I will be fine for figuring
out the rest.  I have come a long ways in a short period of time as
far as my understanding of database design, normalization, etc.

Yes sorry the SQL statement I just posted works. The Currencies,
Banks, and Order Types tables were created using the table analyzer.
By the way, since I used the table analyzer, they have lookup columns
as foreign ID's.

The database I posted as currently working on is a different one.
I should have clarified that better rather than inferring it. through
[currently]. I don't know how I can update 4,000 records in my child
table, Tickets.TradeSpecialists_ID, to contain the foreign ID from
Trade_Specialists - and I never would have guessed I should do it
through a form. I am going to have to change my approach
completely.

Do you have any direction as to where I can learn how to do this?

 

[R Tanner]

Hmm. Given that SQL, the necessary data should exist in Currencies, Banks
and OrderTypes before you try to insert them into Orders. What's the error
message you're getting again?

As to your comment "I don't understand how else to display a foreign key to
the parent table other than through a lookup field", you should never be
working directly with the tables. You should always use a form. Puttinga
combo box on the form is appropriate, putting a combo box into a table
isn't.

Okay to explain...I have solved this problem and this is the SQL
statement I used...Orders is the table I was inserting the new data
into, Currencies, Banks, and Order Types are my parent tables, and Jan
17 2008 is my table I am appending...

One of my problems is I am learning SQL at the same time I am learning
Access...I didn't realize I would need to know SQL to really be able
to do stuff with Access...but anyways...

INSERT INTO Orders ( [Order], [Order Types_ID], Contract_Size,
Currencies_ID, Price, Banks_ID, [Date], [Time] )
SELECT [Jan 17 2008].Order, [Order Types].ID, [Jan 17 2008].Contacts,
Currencies.ID, [Jan 17 2008].Price, Banks.ID, [Jan 17 2008].Date, [Jan
17 2008].Time
FROM (([Jan 17 2008] INNER JOIN Currencies ON [Jan 17
2008].Currency=Currencies.Currency) INNER JOIN Banks ON [Jan 17
2008].Bank=Banks.Bank) INNER JOIN [Order Types] ON [Jan 17
2008].Order_Type=[Order Types].Order_Type;

Onto your reference to the link about the lookup fields...I understand
I should not use them - but I don't understand how else to display a
foreign key to the parent table other than through a lookup
field...For example, let me propose what I have done so far with my
current database I am working on.  It consists of about 30
spreadsheets with 17 fields I have imported from excel - all of the
data is in one table - which once it is normalized will be about 7
tables.  I tried to split it with the table analyzer but it didn't
work.  So.  Focusing on one column, I ran a totals query to pull the
data out and then I ran a make table query to make it a table.  I then
named this table Trade_Specialists.  My child table now has 16 fields,
labeled Tickets.  I added an additional field to Tickets called
TradeSpecialists_ID and created a relationship between this field and
the ID field in Trade_Specialists.  My world is at a halt right now
cause I can't figure out how to get the freakin foreign ID in this
foreign ID field.  If you go to my parent table, Trade_Specialists, I
have about 15 rows, with the names of each trade specialist and the ID
for each.  There is a subdatasheet for each trade specialist showing
the tickets he/she has done.

Also of note is that in my Tickets table, because of how I created my
parent table (totals query --> make table query), I still have the
original Trade Specialist field.  Of course I can't relate that field
to the parent field because it is a text data type, so I'm not sure
what to do with it.

After I figure out how to do this, I think I will be fine for figuring
out the rest.  I have come a long ways in a short period of time as
far as my understanding of database design, normalization, etc.

Yes sorry the SQL statement I just posted works.  The Currencies,
Banks, and Order Types tables were created using the table analyzer.
By the way, since I used the table analyzer, they have lookup columns
as foreign ID's.

    The database I posted as currently working on is a different one.
I should have clarified that better rather than inferring it. through
[currently].  I don't know how I can update 4,000 records in my child
table, Tickets.TradeSpecialists_ID, to contain the foreign ID from
Trade_Specialists - and I never would have guessed I should do it
through a form.  I am going to have to change my approach
completely.

Do you have any direction as to where I can learn how to do this?- Hide quoted text -

- Show quoted text -

Or when I say they have lookup columns as foreign ID's, the child
table, which is Orders, has lookup columns referencing them as foreign
ID's...

 

[R Tanner]

Hmm. Given that SQL, the necessary data should exist in Currencies, Banks
and OrderTypes before you try to insert them into Orders. What's the error
message you're getting again?
As to your comment "I don't understand how else to display a foreign key to
the parent table other than through a lookup field", you should neverbe
working directly with the tables. You should always use a form. Putting a
combo box on the form is appropriate, putting a combo box into a table
isn't.
--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Okay to explain...I have solved this problem and this is the SQL
statement I used...Orders is the table I was inserting the new data
into, Currencies, Banks, and Order Types are my parent tables, and Jan
17 2008 is my table I am appending...
One of my problems is I am learning SQL at the same time I am learning
Access...I didn't realize I would need to know SQL to really be able
to do stuff with Access...but anyways...
INSERT INTO Orders ( [Order], [Order Types_ID], Contract_Size,
Currencies_ID, Price, Banks_ID, [Date], [Time] )
SELECT [Jan 17 2008].Order, [Order Types].ID, [Jan 17 2008].Contacts,
Currencies.ID, [Jan 17 2008].Price, Banks.ID, [Jan 17 2008].Date, [Jan
17 2008].Time
FROM (([Jan 17 2008] INNER JOIN Currencies ON [Jan 17
2008].Currency=Currencies.Currency) INNER JOIN Banks ON [Jan 17
2008].Bank=Banks.Bank) INNER JOIN [Order Types] ON [Jan 17
2008].Order_Type=[Order Types].Order_Type;
Onto your reference to the link about the lookup fields...I understand
I should not use them - but I don't understand how else to display a
foreign key to the parent table other than through a lookup
field...For example, let me propose what I have done so far with my
current database I am working on.  It consists of about 30
spreadsheets with 17 fields I have imported from excel - all of the
data is in one table - which once it is normalized will be about 7
tables.  I tried to split it with the table analyzer but it didn't
work.  So.  Focusing on one column, I ran a totals query to pull the
data out and then I ran a make table query to make it a table.  I then
named this table Trade_Specialists.  My child table now has 16 fields,
labeled Tickets.  I added an additional field to Tickets called
TradeSpecialists_ID and created a relationship between this field and
the ID field in Trade_Specialists.  My world is at a halt right now
cause I can't figure out how to get the freakin foreign ID in this
foreign ID field.  If you go to my parent table, Trade_Specialists,I
have about 15 rows, with the names of each trade specialist and the ID
for each.  There is a subdatasheet for each trade specialist showing
the tickets he/she has done.
Also of note is that in my Tickets table, because of how I created my
parent table (totals query --> make table query), I still have the
original Trade Specialist field.  Of course I can't relate that field
to the parent field because it is a text data type, so I'm not sure
what to do with it.
After I figure out how to do this, I think I will be fine for figuring
out the rest.  I have come a long ways in a short period of time as
far as my understanding of database design, normalization, etc.

Yes sorry the SQL statement I just posted works.  The Currencies,
Banks, and Order Types tables were created using the table analyzer.
By the way, since I used the table analyzer, they have lookup columns
as foreign ID's.

    The database I posted as currently working on is a different one.
I should have clarified that better rather than inferring it. through
[currently].  I don't know how I can update 4,000 records in my child
table, Tickets.TradeSpecialists_ID, to contain the foreign ID from
Trade_Specialists - and I never would have guessed I should do it
through a form.  I am going to have to change my approach
completely.

Do you have any direction as to where I can learn how to do this?- Hidequoted text -

- Show quoted text -

Or when I say they have lookup columns as foreign ID's, the child
table, which is Orders, has lookup columns referencing them as foreign
ID's...- Hide quoted text -

- Show quoted text -

Once I have my data completely normalized and everything is set up, I
plan to only have records entered via a form...

 

[Douglas J. Steele]

You can use queries to populate the related tables.

Going back to your previous example, you'd run the following three queries
before you'd run the one populate the Orders table:

INSERT INTO Banks (Bank)
SELECT Bank
FROM [Jan 17 2008]
WHERE Bank NOT IN (SELECT DISTINCT Bank FROM Banks)

INSERT INTO Currencies (Currency)
SELECT Currency
FROM [Jan 17 2008]
WHERE Currency NOT IN (SELECT DISTINCT Currency FROM Currencies)

INSERT INTO [Order Types] (Order_Type)
SELECT Order_Type
FROM [Jan 17 2008]
WHERE Order_Type NOT IN (SELECT DISTINCT Order_Type FROM [Order Types])

This assumes, of course, that [Order Types_ID], Currencies_ID and Banks_ID
are AutoNumber fields.

BTW, it's generally recommended that you not use special characters such as
spaces in your table and field names.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hmm. Given that SQL, the necessary data should exist in Currencies, Banks
and OrderTypes before you try to insert them into Orders. What's the error
message you're getting again?

As to your comment "I don't understand how else to display a foreign key
to
the parent table other than through a lookup field", you should never be
working directly with the tables. You should always use a form. Putting a
combo box on the form is appropriate, putting a combo box into a table
isn't.

Yes sorry the SQL statement I just posted works. The Currencies,
Banks, and Order Types tables were created using the table analyzer.
By the way, since I used the table analyzer, they have lookup columns
as foreign ID's.

The database I posted as currently working on is a different one.
I should have clarified that better rather than inferring it. through
[currently]. I don't know how I can update 4,000 records in my child
table, Tickets.TradeSpecialists_ID, to contain the foreign ID from
Trade_Specialists - and I never would have guessed I should do it
through a form. I am going to have to change my approach
completely.

Do you have any direction as to where I can learn how to do this?