Appending data from one table to multiple relational tables

That make since. I am also using the location table. Should I include that
or only if the location would change? Or do you think I should just add the
location to the hardware table?

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

I did what you stated below but all I see are the ID's and not the names.
Any ideas? Instead of having the additional sites table, what if I added
that to the employee table?

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

You're right... I didn't finish my "setup" for the combo boxes.

The bound column for each combo box should be set to 1. Set the column count
to 2 for each. Set the column widths to 0";2" for each.

That should do it.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I did what you stated below but all I see are the ID's and not the names.
Any ideas? Instead of having the additional sites table, what if I added
that to the employee table?

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

Hello,

I have this part done and it is showing the assignments initially, however,
this part is not working: Then, as you select a hardware item from the one
combo box, the assigned employee name will show up in the other combo box.
If I pick a different hardware name, it changes it for that employee. It is
not showing the current employee assigned. Any idea?



"Ken Snell" wrote in message
...
You're right... I didn't finish my "setup" for the combo boxes.

The bound column for each combo box should be set to 1. Set the column
count
to 2 for each. Set the column widths to 0";2" for each.

That should do it.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I did what you stated below but all I see are the ID's and not the
names.
Any ideas? Instead of having the additional sites table, what if I
added
that to the employee table?

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

I found one other item. If I do change the hardware name, and try to exit
it says that changes are not successful because it would create a duplicate
in the index or primary key. Why would that be? I would think it would be
acting like changing the employees assigned PC.

-
"Ken Snell" wrote in message
...
You're right... I didn't finish my "setup" for the combo boxes.

The bound column for each combo box should be set to 1. Set the column
count
to 2 for each. Set the column widths to 0";2" for each.

That should do it.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I did what you stated below but all I see are the ID's and not the
names.
Any ideas? Instead of having the additional sites table, what if I
added
that to the employee table?

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

Change the form to a continuous forms view. That will allow you to see the
separate records in the tblEmplHard table and you can change them
independently.

What is happening on your current form (because I didn't provide enough
details, sorry) is that only one record is seen and any changes are made to
that record alone. When you change the hardware item for an employee, and if
that hardware item already exists on another record, you're getting a
duplicate use of the same hardware, and the table says "NO WAY" because of
how we set up the table.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I found one other item. If I do change the hardware name, and try to exit
it says that changes are not successful because it would create a
duplicate
in the index or primary key. Why would that be? I would think it would
be
acting like changing the employees assigned PC.

-
"Ken Snell" wrote in message
...
You're right... I didn't finish my "setup" for the combo boxes.

The bound column for each combo box should be set to 1. Set the column
count
to 2 for each. Set the column widths to 0";2" for each.

That should do it.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I did what you stated below but all I see are the ID's and not the
names.
Any ideas? Instead of having the additional sites table, what if I
added
that to the employee table?

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

I changed it to continuous. It has every record going down the page.

With the form I am working on now, I would use that to assign an employee to
new hardware? Correct?

I tried picking a different name but it still is not changing the employee.


"Ken Snell" wrote in message
...
Change the form to a continuous forms view. That will allow you to see the
separate records in the tblEmplHard table and you can change them
independently.

What is happening on your current form (because I didn't provide enough
details, sorry) is that only one record is seen and any changes are made
to
that record alone. When you change the hardware item for an employee, and
if
that hardware item already exists on another record, you're getting a
duplicate use of the same hardware, and the table says "NO WAY" because of
how we set up the table.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I found one other item. If I do change the hardware name, and try to
exit
it says that changes are not successful because it would create a
duplicate
in the index or primary key. Why would that be? I would think it would
be
acting like changing the employees assigned PC.

-
"Ken Snell" wrote in message
...
You're right... I didn't finish my "setup" for the combo boxes.

The bound column for each combo box should be set to 1. Set the column
count
to 2 for each. Set the column widths to 0";2" for each.

That should do it.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I did what you stated below but all I see are the ID's and not the
names.
Any ideas? Instead of having the additional sites table, what if I
added
that to the employee table?

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

Do you think I should add the site, location to the employee field and
eliminate the site table? Or would that be denormalizing the table?

I really appreciate all your help,

"Ken Snell" wrote in message
...
Change the form to a continuous forms view. That will allow you to see the
separate records in the tblEmplHard table and you can change them
independently.

What is happening on your current form (because I didn't provide enough
details, sorry) is that only one record is seen and any changes are made
to
that record alone. When you change the hardware item for an employee, and
if
that hardware item already exists on another record, you're getting a
duplicate use of the same hardware, and the table says "NO WAY" because of
how we set up the table.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I found one other item. If I do change the hardware name, and try to
exit
it says that changes are not successful because it would create a
duplicate
in the index or primary key. Why would that be? I would think it would
be
acting like changing the employees assigned PC.

-
"Ken Snell" wrote in message
...
You're right... I didn't finish my "setup" for the combo boxes.

The bound column for each combo box should be set to 1. Set the column
count
to 2 for each. Set the column widths to 0";2" for each.

That should do it.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I did what you stated below but all I see are the ID's and not the
names.
Any ideas? Instead of having the additional sites table, what if I
added
that to the employee table?

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

Hi, are you still here?

-- "Ken Snell" wrote in message
...
Change the form to a continuous forms view. That will allow you to see the
separate records in the tblEmplHard table and you can change them
independently.

What is happening on your current form (because I didn't provide enough
details, sorry) is that only one record is seen and any changes are made
to
that record alone. When you change the hardware item for an employee, and
if
that hardware item already exists on another record, you're getting a
duplicate use of the same hardware, and the table says "NO WAY" because of
how we set up the table.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I found one other item. If I do change the hardware name, and try to
exit
it says that changes are not successful because it would create a
duplicate
in the index or primary key. Why would that be? I would think it would
be
acting like changing the employees assigned PC.

-
"Ken Snell" wrote in message
...
You're right... I didn't finish my "setup" for the combo boxes.

The bound column for each combo box should be set to 1. Set the column
count
to 2 for each. Set the column widths to 0";2" for each.

That should do it.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I did what you stated below but all I see are the ID's and not the
names.
Any ideas? Instead of having the additional sites table, what if I
added
that to the employee table?

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

Yes. I was out of town for just under a week. Haven't had chance to look at
newsgroups since coming back, but I will.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
Hi, are you still here?

-- "Ken Snell" wrote in message
...
Change the form to a continuous forms view. That will allow you to see
the
separate records in the tblEmplHard table and you can change them
independently.

What is happening on your current form (because I didn't provide enough
details, sorry) is that only one record is seen and any changes are made
to
that record alone. When you change the hardware item for an employee,
and
if
that hardware item already exists on another record, you're getting a
duplicate use of the same hardware, and the table says "NO WAY" because
of
how we set up the table.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I found one other item. If I do change the hardware name, and try to
exit
it says that changes are not successful because it would create a
duplicate
in the index or primary key. Why would that be? I would think it
would
be
acting like changing the employees assigned PC.

-
"Ken Snell" wrote in message
...
You're right... I didn't finish my "setup" for the combo boxes.

The bound column for each combo box should be set to 1. Set the
column
count
to 2 for each. Set the column widths to 0";2" for each.

That should do it.

--

Ken Snell
MS ACCESS MVP

"Stranger" wrote in message
...
I did what you stated below but all I see are the ID's and not the
names.
Any ideas? Instead of having the additional sites table, what if
I
added
that to the employee table?

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