A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Appending data from one table to multiple relational tables



 
 
Thread Tools Display Modes
  #11  
Old August 6th, 2004, 02:59 AM
Stranger
external usenet poster
 
Posts: n/a
Default Appending data from one table to multiple relational tables

I tried to do that but access will not let me have more than one auto
number field in the table.


"Ken Snell" wrote in
:

If you made the "fldHardID" field in tblEmpHard an autonumber field, then
change it to Long Integer. Same for the "fldEmplID" field -- it should be
Long Integer.


  #12  
Old August 6th, 2004, 03:24 AM
Ken Snell
external usenet poster
 
Posts: n/a
Default Appending data from one table to multiple relational tables

You should not have *any* autonumber fields in tblEmplHard table. They both
should be Long Integer.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
. ..
I tried to do that but access will not let me have more than one auto
number field in the table.


"Ken Snell" wrote in
:

If you made the "fldHardID" field in tblEmpHard an autonumber field,

then
change it to Long Integer. Same for the "fldEmplID" field -- it should

be
Long Integer.




  #13  
Old August 6th, 2004, 09:50 PM
Stranger
external usenet poster
 
Posts: n/a
Default Appending data from one table to multiple relational tables

I have made that change. However, since I have the hardware in separate
tables, computer table, printer table, etc. It is requiring in the
tblemphard to have a value in every field.

1. Should I not separate the hardware into separate tables?
2. If it is ok, then why can I not assign a computer without assigning a
printer, etc?

Thanks.



"Ken Snell" wrote in message
...
You should not have *any* autonumber fields in tblEmplHard table. They

both
should be Long Integer.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
. ..
I tried to do that but access will not let me have more than one auto
number field in the table.


"Ken Snell" wrote in
:

If you made the "fldHardID" field in tblEmpHard an autonumber field,

then
change it to Long Integer. Same for the "fldEmplID" field -- it should

be
Long Integer.






  #14  
Old August 6th, 2004, 11:22 PM
Ken Snell
external usenet poster
 
Posts: n/a
Default Appending data from one table to multiple relational tables

The structure that I gave you assumed that all hardware data are in one
table. Splitting them into printers, computers, etc. tables shouldn't be
necessary. You can use a field in tblHard to identify the type of hardware.
For the table structure I gave you, each piece of hardware, *regardless of
what it is*, must have a unique ID that is not shared with any other
hardware piece.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I have made that change. However, since I have the hardware in separate
tables, computer table, printer table, etc. It is requiring in the
tblemphard to have a value in every field.

1. Should I not separate the hardware into separate tables?
2. If it is ok, then why can I not assign a computer without assigning a
printer, etc?

Thanks.



"Ken Snell" wrote in message
...
You should not have *any* autonumber fields in tblEmplHard table. They

both
should be Long Integer.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
. ..
I tried to do that but access will not let me have more than one auto
number field in the table.


"Ken Snell" wrote in
:

If you made the "fldHardID" field in tblEmpHard an autonumber field,

then
change it to Long Integer. Same for the "fldEmplID" field -- it

should
be
Long Integer.








  #15  
Old August 7th, 2004, 04:10 AM
Stranger
external usenet poster
 
Posts: n/a
Default Appending data from one table to multiple relational tables

Hi,

I really appreciate all of your help.

Let me make sure I understand what you are saying.
I can put all hardware into one table. Have an additional field specifying
whether it is a printer, desktop, server, handheld, etc.

For the statement, "each piece of hardware, *regardless of what it is*, must
have a unique ID that is not shared with any other hardware piece."

Does this mean that the autonumber field which is the primary key in that
table would be the unique ID??

Or

Do you mean that I need to somehow assign a unique ID something like
desktops number 1, servers number 2m etc?

--
JAVIER NEGRON
"Ken Snell" wrote in message
...
The structure that I gave you assumed that all hardware data are in one
table. Splitting them into printers, computers, etc. tables shouldn't be
necessary. You can use a field in tblHard to identify the type of

hardware.
For the table structure I gave you, each piece of hardware, *regardless of
what it is*, must have a unique ID that is not shared with any other
hardware piece.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I have made that change. However, since I have the hardware in separate
tables, computer table, printer table, etc. It is requiring in the
tblemphard to have a value in every field.

1. Should I not separate the hardware into separate tables?
2. If it is ok, then why can I not assign a computer without assigning

a
printer, etc?

Thanks.



"Ken Snell" wrote in message
...
You should not have *any* autonumber fields in tblEmplHard table. They

both
should be Long Integer.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
. ..
I tried to do that but access will not let me have more than one

auto
number field in the table.


"Ken Snell" wrote in
:

If you made the "fldHardID" field in tblEmpHard an autonumber

field,
then
change it to Long Integer. Same for the "fldEmplID" field -- it

should
be
Long Integer.










  #16  
Old August 7th, 2004, 04:10 AM
Stranger
external usenet poster
 
Posts: n/a
Default Appending data from one table to multiple relational tables

I think I have answered the question I just sent. Each piece of hardware
would have it's own unique ID because each one is a separate record in the
table therefore it would have it's own unique auto number? Right?

--

"Ken Snell" wrote in message
...
The structure that I gave you assumed that all hardware data are in one
table. Splitting them into printers, computers, etc. tables shouldn't be
necessary. You can use a field in tblHard to identify the type of

hardware.
For the table structure I gave you, each piece of hardware, *regardless of
what it is*, must have a unique ID that is not shared with any other
hardware piece.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I have made that change. However, since I have the hardware in separate
tables, computer table, printer table, etc. It is requiring in the
tblemphard to have a value in every field.

1. Should I not separate the hardware into separate tables?
2. If it is ok, then why can I not assign a computer without assigning

a
printer, etc?

Thanks.



"Ken Snell" wrote in message
...
You should not have *any* autonumber fields in tblEmplHard table. They

both
should be Long Integer.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
. ..
I tried to do that but access will not let me have more than one

auto
number field in the table.


"Ken Snell" wrote in
:

If you made the "fldHardID" field in tblEmpHard an autonumber

field,
then
change it to Long Integer. Same for the "fldEmplID" field -- it

should
be
Long Integer.










  #17  
Old August 7th, 2004, 06:23 PM
Ken Snell
external usenet poster
 
Posts: n/a
Default Appending data from one table to multiple relational tables

Right!

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I think I have answered the question I just sent. Each piece of hardware
would have it's own unique ID because each one is a separate record in

the
table therefore it would have it's own unique auto number? Right?

--

"Ken Snell" wrote in message
...
The structure that I gave you assumed that all hardware data are in one
table. Splitting them into printers, computers, etc. tables shouldn't be
necessary. You can use a field in tblHard to identify the type of

hardware.
For the table structure I gave you, each piece of hardware, *regardless

of
what it is*, must have a unique ID that is not shared with any other
hardware piece.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I have made that change. However, since I have the hardware in

separate
tables, computer table, printer table, etc. It is requiring in the
tblemphard to have a value in every field.

1. Should I not separate the hardware into separate tables?
2. If it is ok, then why can I not assign a computer without

assigning
a
printer, etc?

Thanks.



"Ken Snell" wrote in message
...
You should not have *any* autonumber fields in tblEmplHard table.

They
both
should be Long Integer.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
. ..
I tried to do that but access will not let me have more than one

auto
number field in the table.


"Ken Snell" wrote in
:

If you made the "fldHardID" field in tblEmpHard an autonumber

field,
then
change it to Long Integer. Same for the "fldEmplID" field -- it

should
be
Long Integer.












  #18  
Old August 8th, 2004, 01:47 AM
Stranger
external usenet poster
 
Posts: n/a
Default Appending data from one table to multiple relational tables

Thanks for you help with that one. Now, I am back to the original issue of
getting all of the data from the current flat table into the new tables.
Can I somehow create one append query that will update the data into all the
tables?

--
JAVIER NEGRON
"Ken Snell" wrote in message
...
Right!

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I think I have answered the question I just sent. Each piece of

hardware
would have it's own unique ID because each one is a separate record in

the
table therefore it would have it's own unique auto number? Right?

--

"Ken Snell" wrote in message
...
The structure that I gave you assumed that all hardware data are in

one
table. Splitting them into printers, computers, etc. tables shouldn't

be
necessary. You can use a field in tblHard to identify the type of

hardware.
For the table structure I gave you, each piece of hardware,

*regardless
of
what it is*, must have a unique ID that is not shared with any other
hardware piece.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I have made that change. However, since I have the hardware in

separate
tables, computer table, printer table, etc. It is requiring in the
tblemphard to have a value in every field.

1. Should I not separate the hardware into separate tables?
2. If it is ok, then why can I not assign a computer without

assigning
a
printer, etc?

Thanks.



"Ken Snell" wrote in message
...
You should not have *any* autonumber fields in tblEmplHard table.

They
both
should be Long Integer.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
. ..
I tried to do that but access will not let me have more than one

auto
number field in the table.


"Ken Snell" wrote in
:

If you made the "fldHardID" field in tblEmpHard an autonumber

field,
then
change it to Long Integer. Same for the "fldEmplID" field --

it
should
be
Long Integer.














  #19  
Old August 8th, 2004, 03:08 AM
Ken Snell
external usenet poster
 
Posts: n/a
Default Appending data from one table to multiple relational tables

I'm not sure that just one append query will do the job.. you may need to
run sequential ones. However, I cannot see your data in the flat file, and I
don't believe you've described it during this thread. So I'll need more info
from you first regarding the structure of the current data and in what
format it is (ACCESS table, EXCEL spreadsheet, text file, etc.).

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
Thanks for you help with that one. Now, I am back to the original issue

of
getting all of the data from the current flat table into the new tables.
Can I somehow create one append query that will update the data into all

the
tables?

--
JAVIER NEGRON
"Ken Snell" wrote in message
...
Right!

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I think I have answered the question I just sent. Each piece of

hardware
would have it's own unique ID because each one is a separate record

in
the
table therefore it would have it's own unique auto number? Right?

--

"Ken Snell" wrote in message
...
The structure that I gave you assumed that all hardware data are in

one
table. Splitting them into printers, computers, etc. tables

shouldn't
be
necessary. You can use a field in tblHard to identify the type of
hardware.
For the table structure I gave you, each piece of hardware,

*regardless
of
what it is*, must have a unique ID that is not shared with any other
hardware piece.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I have made that change. However, since I have the hardware in

separate
tables, computer table, printer table, etc. It is requiring in

the
tblemphard to have a value in every field.

1. Should I not separate the hardware into separate tables?
2. If it is ok, then why can I not assign a computer without

assigning
a
printer, etc?

Thanks.



"Ken Snell" wrote in message
...
You should not have *any* autonumber fields in tblEmplHard

table.
They
both
should be Long Integer.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
. ..
I tried to do that but access will not let me have more than

one
auto
number field in the table.


"Ken Snell" wrote in
:

If you made the "fldHardID" field in tblEmpHard an

autonumber
field,
then
change it to Long Integer. Same for the "fldEmplID" field --

it
should
be
Long Integer.
















  #20  
Old August 8th, 2004, 04:05 AM
Stranger
external usenet poster
 
Posts: n/a
Default Appending data from one table to multiple relational tables

I don't know if this is going to help but in the original flat table I added
additional ID columns. Like employeeid, printerid, upsid, etc. I copied
the unique ID from the computer ID (primary key) into those additional
fields so that the unique id will be in each of those fields if the employee
has additional hardware. In other words, if an employee had a computer and
printer, I copied the primary key ID (lets say 4) into the employeeid and
printerid fields that I created in the original table. I'm hoping this will
help in bringing the data into the new tables.

I kept the tblsites. Maybe this isn't needed but since it is not hardware,
I figure it should be separate. ??

I can append the data into the parent tables with no problem but I just
can't get the tblemlhrd to tie everything together. I can manually go in
and put each number in but that would take a while.

"Ken Snell" wrote in message
...
Right!

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I think I have answered the question I just sent. Each piece of

hardware
would have it's own unique ID because each one is a separate record in

the
table therefore it would have it's own unique auto number? Right?

--

"Ken Snell" wrote in message
...
The structure that I gave you assumed that all hardware data are in

one
table. Splitting them into printers, computers, etc. tables shouldn't

be
necessary. You can use a field in tblHard to identify the type of

hardware.
For the table structure I gave you, each piece of hardware,

*regardless
of
what it is*, must have a unique ID that is not shared with any other
hardware piece.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I have made that change. However, since I have the hardware in

separate
tables, computer table, printer table, etc. It is requiring in the
tblemphard to have a value in every field.

1. Should I not separate the hardware into separate tables?
2. If it is ok, then why can I not assign a computer without

assigning
a
printer, etc?

Thanks.



"Ken Snell" wrote in message
...
You should not have *any* autonumber fields in tblEmplHard table.

They
both
should be Long Integer.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
. ..
I tried to do that but access will not let me have more than one

auto
number field in the table.


"Ken Snell" wrote in
:

If you made the "fldHardID" field in tblEmpHard an autonumber

field,
then
change it to Long Integer. Same for the "fldEmplID" field --

it
should
be
Long Integer.














 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Appending date from one table to multiple tables Stranger Running & Setting Up Queries 7 July 30th, 2004 12:30 AM
Append one table to multiple tables? Stranger Running & Setting Up Queries 2 July 28th, 2004 01:00 PM
Combining data from multiple fields into Pivot table Os Worksheet Functions 5 June 1st, 2004 05:04 PM
Need help with Tables Design and Relationships Tom Database Design 24 May 19th, 2004 06:51 PM
Data Table Max Worksheet Functions 1 April 12th, 2004 04:12 AM


All times are GMT +1. The time now is 03:57 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.