A Microsoft Office (Excel, Word) forum. OfficeFrustration

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

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

Appending data from one table to multiple relational tables



 
 
Thread Tools Display Modes
  #41  
Old August 18th, 2004, 01:54 AM
Ken Snell
external usenet poster
 
Posts: n/a
Default

If you're not able to update/change the employee info, then either
(1) the control isn't bound to the employee field in the form's
recordsource; or
(2) you're not using an appropriate query as the row source for the
combo box control that has the employee info (e.g., your control is trying
to put an employee name into the field instead of the employee ID).

Give info about what type of control you're using for the employee info,
what is its control source, what is its row source (if it's a combo box),
what is its bound column (if it's a combo box), etc.

--

Ken Snell
MS ACCESS MVP

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








































  #42  
Old August 18th, 2004, 01:56 AM
Ken Snell
external usenet poster
 
Posts: n/a
Default

It depends. Will any other table's info be wanting to use site / location
information besides the employee table? If not, then putting it into the
employee table would be fine -- it is denormalizing the table structure, but
in this case it's probably appropriate to do that.

--

Ken Snell
MS ACCESS MVP

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








































  #43  
Old August 18th, 2004, 02:55 AM
Stranger
external usenet poster
 
Posts: n/a
Default

Ok, I thought maybe you got tired of me.

As far as the locations, I don't want to denormalize. I want to try and
stay with appropriate standards.

any idea why the fields are not changing? I changed the form to the type
you suggested but that makes all records down one page. Kind of messy.


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










































 




Thread Tools
Display Modes

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

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

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


All times are GMT +1. The time now is 07:42 AM.


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