Appending data from one table to multiple relational tables

Current flat table (COMPUINV) contains all fields about the computer
inventory including employees, handhelds and printers. the only unique
key in the flat table is the auto number field. this is the primary key.
Everything is in one row.I exported the table structure from the original table. Hopefully it will look correct in here.

ID Site Location UserName ComputerMakeModel SerialNoServiceTag Type SPEED RAM HD WindowsVersion OfficeVersion CD JackNumber PRINTER PCName IPAddress LastUpdate UPSInservice Date Registered HandheldModel HandheldPhoneNumber HandheldSerial Spybot

Well, you kind of see the current setup. It was a terrible design.

What I did was take that table, added some fields and made this:

ID PrinterID EmployeeID ComputerID HandHeldID UPSID SiteID Site Location UserName ComputerMakeModel SerialNoServiceTag Type SPEED RAM HD WindowsVersion OfficeVersion CD JackNumber PRINTER PCName IPAddress LastUpdate UPSInservice Date Registered HandheldModel HandheldPhoneNumber HandheldSerial Spybot



the next part I may not have needed.

This is where I took the current ID and copied it into the corresponding ID field that I made. Lets say that ID 4 had an employee, printer, and computer in that row. I took the ID 4 and put it into the fldempolyeeid, fldprinterid, and fldcomputerid. I hope that makes since. So, If I can run an append query that will take the data from the above ID fields and put them into the tblemphrd in the new table, then everything should be related back together.

Does this help?




"Ken Snell" wrote in message ...
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.

Here is a the append query that I am trying to get to work but it is coming
up with over 30,000 records. Even with the select distinct.

INSERT INTO tblemployeehardware ( EmployeeID, HardwareID, SiteID )
SELECT DISTINCT tblemployees.EmployeeID, tblhardware.ID, tblsites.SiteID
FROM tblemployees, tblhardware, tblsites
WHERE (((tblemployees.EmployeeID)=[tblhardware].[id]) AND ((tblhardware.ID)
Is Not Null))
ORDER BY tblhardware.ID;



"Ken Snell" wrote in message
...
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.

Ok, I have the append query showing the correct data, however, I can't run
it do to key violations.

INSERT INTO tblemployeehardware ( EmployeeID, HardwareID, SiteID )
SELECT tblemployees.EmployeeID, tblhardware.ID, tblsites.SiteID
FROM tblemployees, tblhardware, tblsites
WHERE (((tblemployees.EmployeeID)=[tblhardware].[id]) AND
([tblhardware].[id]=[tblsites].[siteid]))
ORDER BY tblhardware.ID;

I know I am really close.

"Ken Snell" wrote in message
...
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.

Hi!!

I believe I got it!!!

I created a select query to include the unique ID in the hardware table.
Then created the append query from that. Everything looks great!!!

Thank you so much for you help!!!

Jack

"Ken Snell" wrote in message
...
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.

Wow. Great news! I was going to reply to your posts this afternoon after
church, but I see it's unnecessary now. Good luck!

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
Hi!!

I believe I got it!!!

I created a select query to include the unique ID in the hardware table.
Then created the append query from that. Everything looks great!!!

Thank you so much for you help!!!

Jack

"Ken Snell" wrote in message
...
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.

Hi!!

I have another question for you.

I have several forms and that completed. Now, If I want to change the pc
assigned to an employee or site, do I need to first create an select query
with the unique ID's and then create an append query with just those ID's to
append the changes or new assignment to the tblemphrd? Form would be based
off of the append query?

"Ken Snell" wrote in message
...
Wow. Great news! I was going to reply to your posts this afternoon after
church, but I see it's unnecessary now. Good luck!

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
Hi!!

I believe I got it!!!

I created a select query to include the unique ID in the hardware table.
Then created the append query from that. Everything looks great!!!

Thank you so much for you help!!!

Jack

"Ken Snell" wrote in message
...
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.

Your form should be based on a select query that returns the tblEmplHard
records. You can filter on a specific hardware ID and then you can change
the employee to which that piece of hardware is assigned.

Append queries cannot be used as the basis for a form or report.

What you probably would want is a form that has a combo box in the form's
header and use that combo box to select the piece of hardware. Then have the
form be filtered (using the query noted above and by filtering as a WHERE
expression on the HardID field) to show the desired record. You then can
change the employee using a combo box that shows all employees.

The combo box wizard can get you started on this form.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
Hi!!

I have another question for you.

I have several forms and that completed. Now, If I want to change the pc
assigned to an employee or site, do I need to first create an select query
with the unique ID's and then create an append query with just those ID's
to
append the changes or new assignment to the tblemphrd? Form would be
based
off of the append query?

"Ken Snell" wrote in message
...
Wow. Great news! I was going to reply to your posts this afternoon after
church, but I see it's unnecessary now. Good luck!

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
Hi!!

I believe I got it!!!

I created a select query to include the unique ID in the hardware
table.
Then created the append query from that. Everything looks great!!!

Thank you so much for you help!!!

Jack

"Ken Snell" wrote in message
...
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.

Hi,

I'm a little confused again.

I should create a select query using the tblemplhrd and employee name from
tblemployees and say serial number from tblhard?



--
JAVIER NEGRON
"Ken Snell" wrote in message
...
Your form should be based on a select query that returns the tblEmplHard
records. You can filter on a specific hardware ID and then you can change
the employee to which that piece of hardware is assigned.

Append queries cannot be used as the basis for a form or report.

What you probably would want is a form that has a combo box in the form's
header and use that combo box to select the piece of hardware. Then have
the
form be filtered (using the query noted above and by filtering as a WHERE
expression on the HardID field) to show the desired record. You then can
change the employee using a combo box that shows all employees.

The combo box wizard can get you started on this form.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
Hi!!

I have another question for you.

I have several forms and that completed. Now, If I want to change the
pc
assigned to an employee or site, do I need to first create an select
query
with the unique ID's and then create an append query with just those
ID's
to
append the changes or new assignment to the tblemphrd? Form would be
based
off of the append query?

"Ken Snell" wrote in message
...
Wow. Great news! I was going to reply to your posts this afternoon
after
church, but I see it's unnecessary now. Good luck!

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
Hi!!

I believe I got it!!!

I created a select query to include the unique ID in the hardware
table.
Then created the append query from that. Everything looks great!!!

Thank you so much for you help!!!

Jack

"Ken Snell" wrote in message
...
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.

I'm using office 2003 if that helps.

"Ken Snell" wrote in message
...
Your form should be based on a select query that returns the tblEmplHard
records. You can filter on a specific hardware ID and then you can change
the employee to which that piece of hardware is assigned.

Append queries cannot be used as the basis for a form or report.

What you probably would want is a form that has a combo box in the form's
header and use that combo box to select the piece of hardware. Then have
the
form be filtered (using the query noted above and by filtering as a WHERE
expression on the HardID field) to show the desired record. You then can
change the employee using a combo box that shows all employees.

The combo box wizard can get you started on this form.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
Hi!!

I have another question for you.

I have several forms and that completed. Now, If I want to change the
pc
assigned to an employee or site, do I need to first create an select
query
with the unique ID's and then create an append query with just those
ID's
to
append the changes or new assignment to the tblemphrd? Form would be
based
off of the append query?

"Ken Snell" wrote in message
...
Wow. Great news! I was going to reply to your posts this afternoon
after
church, but I see it's unnecessary now. Good luck!

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
Hi!!

I believe I got it!!!

I created a select query to include the unique ID in the hardware
table.
Then created the append query from that. Everything looks great!!!

Thank you so much for you help!!!

Jack

"Ken Snell" wrote in message
...
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.

Assuming just one hardware item at a time will be displayed on the form, I'd
put two combo boxes on the form.

NOTE: Your actual table names and field names may be different from what I'm
showing here.

I would make the form's RecordSource be this query:

SELECT tblEmplHard.HardID, tblEmplHard.EmplID
FROM tblEmplHard;


I would bind one combo box to the HardID field, and the other to the EmplID
field.

I would make the RowSource for the hardware combo box be this:
SELECT tblHard.HardID, tblHard.HardName
FROM tblHard
ORDER BY tblHard.HardName;

I would make the RowSource for the employee combo box be this:
SELECT tblEmpl.EmplID, tblEmpl.EmplName
FROM tblEmpl
ORDER BY tblEmpl.EmplName;


Then, as you select a hardware item from the one combo box, the assigned
employee name will show up in the other combo box.

This may not be the most optimum result for you, but it should get you
started.
--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
Hi,

I'm a little confused again.

I should create a select query using the tblemplhrd and employee name from
tblemployees and say serial number from tblhard?



--
JAVIER NEGRON
"Ken Snell" wrote in message
...
Your form should be based on a select query that returns the tblEmplHard
records. You can filter on a specific hardware ID and then you can
change
the employee to which that piece of hardware is assigned.

Append queries cannot be used as the basis for a form or report.

What you probably would want is a form that has a combo box in the
form's
header and use that combo box to select the piece of hardware. Then have
the
form be filtered (using the query noted above and by filtering as a
WHERE
expression on the HardID field) to show the desired record. You then can
change the employee using a combo box that shows all employees.

The combo box wizard can get you started on this form.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
Hi!!

I have another question for you.

I have several forms and that completed. Now, If I want to change the
pc
assigned to an employee or site, do I need to first create an select
query
with the unique ID's and then create an append query with just those
ID's
to
append the changes or new assignment to the tblemphrd? Form would be
based
off of the append query?

"Ken Snell" wrote in message
...
Wow. Great news! I was going to reply to your posts this afternoon
after
church, but I see it's unnecessary now. Good luck!

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
Hi!!

I believe I got it!!!

I created a select query to include the unique ID in the hardware
table.
Then created the append query from that. Everything looks
great!!!

Thank you so much for you help!!!

Jack

"Ken Snell" wrote in message
...
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.