Linking DataBases

Hi!
I have 4 spreadsheets that are filled by 4 different users.
I am not an expert in Access but I created a Database and linked all of
these 4 spreadsheets into table section. (I Have: CA1, CA2, CA3 and CA4)
Now I am trying unsuccessfully to merge these 4 tables in order to create
only one form.
Can anybody help me with this?

Thanks a lot.
MaurÃ*cio

Mauricio

When you say "merge these 4 tables", do you mean actually put the data from
each of the four sources into a totally new table?

An alternative to this would be to use a query to collect all of the data in
the same place, and use that as the source for the form.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Maurício Paiva" wrote in message
...
Hi!
I have 4 spreadsheets that are filled by 4 different users.
I am not an expert in Access but I created a Database and linked all of
these 4 spreadsheets into table section. (I Have: CA1, CA2, CA3 and CA4)
Now I am trying unsuccessfully to merge these 4 tables in order to create
only one form.
Can anybody help me with this?

Thanks a lot.
Maurício

Yes Jeff.
Actually I meant these 4 linked tables into one.
Can you pls instruct me how to create a query?

I tried some other functions but I always receive a message saying that
tables are linked from excel and some actions could not be performed.

Tks,
MaurÃ*cio

"Jeff Boyce" escreveu:

Mauricio

When you say "merge these 4 tables", do you mean actually put the data from
each of the four sources into a totally new table?

An alternative to this would be to use a query to collect all of the data in
the same place, and use that as the source for the form.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP


"MaurÃ*cio Paiva" wrote in message
...
Hi!
I have 4 spreadsheets that are filled by 4 different users.
I am not an expert in Access but I created a Database and linked all of
these 4 spreadsheets into table section. (I Have: CA1, CA2, CA3 and CA4)
Now I am trying unsuccessfully to merge these 4 tables in order to create
only one form.
Can anybody help me with this?

Thanks a lot.
MaurÃ*cio

Mauricio

You have four linked 'tables' in Access.

Create a new table (empty) that is defined as needed.

Create a query that returns all the records from one of your four linked
tables. Open that query in design view and modify it to be an append query.
Append field (values) from your linked table to your permanent table.

Modify the query to "point" at a different linked table and append that
table's records.

Repeat until finished.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Maurício Paiva" wrote in message
...
Yes Jeff.
Actually I meant these 4 linked tables into one.
Can you pls instruct me how to create a query?

I tried some other functions but I always receive a message saying that
tables are linked from excel and some actions could not be performed.

Tks,
Maurício

"Jeff Boyce" escreveu:

Mauricio

When you say "merge these 4 tables", do you mean actually put the data
from
each of the four sources into a totally new table?

An alternative to this would be to use a query to collect all of the data
in
the same place, and use that as the source for the form.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Maurício Paiva" wrote in
message
...
Hi!
I have 4 spreadsheets that are filled by 4 different users.
I am not an expert in Access but I created a Database and linked all of
these 4 spreadsheets into table section. (I Have: CA1, CA2, CA3 and
CA4)
Now I am trying unsuccessfully to merge these 4 tables in order to
create
only one form.
Can anybody help me with this?

Thanks a lot.
Maurício

Do you really need to physically copy the data out of the 4 Excel tables into
one table? A key principle of relational databases is that each piece of data
exists once and once only - in your case, in the Excel tables.

My suggestion would be to use a Union query to combine the data out of the 4
tables, which would give you a dynamic view of the current data in all 4
tables. You could use this query just as you would a table - as the target of
a subsequent query, or a data source for a form or report.

Be aware that Access cannot change any data in linked Excel tables.
--
TedMi

"MaurÃ*cio Paiva" wrote:

Yes Jeff.
Actually I meant these 4 linked tables into one.
Can you pls instruct me how to create a query?

I tried some other functions but I always receive a message saying that
tables are linked from excel and some actions could not be performed.

Tks,
MaurÃ*cio

On Sep 24, 3:24*pm, tedmi wrote:
Do you really need to physically copy the data out of the 4 Excel tables into
one table? A key principle of relational databases is that each piece of data
exists once and once only - in your case, in the Excel tables.

My suggestion would be to use a Union query to combine the data out of the 4
tables, which would give you a dynamic view of the current data in all 4
tables. You could use this query just as you would a table - as the target of
a subsequent query, or a data source for a form or report.

Be aware that Access cannot change any data in linked Excel tables.
--
TedMi

but then the fact that it's a union query means that the data it
returns is not editable anyway... has nothing to do with Excel. Union
query results are not editable in Access either.

HI!
Thank you all for your replies.
In fact I don't to edit nothing in Access... I just want to make sure
whenever an user updates its individual spreadsheet that it will reflect to
DB in access.
Then the final user can make a deep search on the database.

Just to give you an idea... the pricing team will feed the spreadsheet
(several of them) and the main contact in sales team will do the search then
he can check for better quotes and give it when requested.

I am an infrastructure guy in the company and we hv no developers in here...
that's why I am pretty lost.

Jeff,
the idea with the queries is great... however when I try to append I can see
only the other excel lilnks... it doesn't show me that new table a create
with the fields only.
Is there any trick?

MaurÃ*cio

"Jeff Boyce" escreveu:

Mauricio

You have four linked 'tables' in Access.

Create a new table (empty) that is defined as needed.

Create a query that returns all the records from one of your four linked
tables. Open that query in design view and modify it to be an append query.
Append field (values) from your linked table to your permanent table.

Modify the query to "point" at a different linked table and append that
table's records.

Repeat until finished.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"MaurÃ*cio Paiva" wrote in message
...
Yes Jeff.
Actually I meant these 4 linked tables into one.
Can you pls instruct me how to create a query?

I tried some other functions but I always receive a message saying that
tables are linked from excel and some actions could not be performed.

Tks,
MaurÃ*cio

"Jeff Boyce" escreveu:

Mauricio

When you say "merge these 4 tables", do you mean actually put the data
from
each of the four sources into a totally new table?

An alternative to this would be to use a query to collect all of the data
in
the same place, and use that as the source for the form.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP


"MaurÃ*cio Paiva" wrote in
message
...
Hi!
I have 4 spreadsheets that are filled by 4 different users.
I am not an expert in Access but I created a Database and linked all of
these 4 spreadsheets into table section. (I Have: CA1, CA2, CA3 and
CA4)
Now I am trying unsuccessfully to merge these 4 tables in order to
create
only one form.
Can anybody help me with this?

Thanks a lot.
MaurÃ*cio

Did you first create the new table?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Maurício Paiva" wrote in message
...
Jeff,
the idea with the queries is great... however when I try to append I can
see
only the other excel lilnks... it doesn't show me that new table a create
with the fields only.
Is there any trick?

Maurício

"Jeff Boyce" escreveu:

Mauricio

You have four linked 'tables' in Access.

Create a new table (empty) that is defined as needed.

Create a query that returns all the records from one of your four linked
tables. Open that query in design view and modify it to be an append
query.
Append field (values) from your linked table to your permanent table.

Modify the query to "point" at a different linked table and append that
table's records.

Repeat until finished.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Maurício Paiva" wrote in
message
...
Yes Jeff.
Actually I meant these 4 linked tables into one.
Can you pls instruct me how to create a query?

I tried some other functions but I always receive a message saying that
tables are linked from excel and some actions could not be performed.

Tks,
Maurício

"Jeff Boyce" escreveu:

Mauricio

When you say "merge these 4 tables", do you mean actually put the data
from
each of the four sources into a totally new table?

An alternative to this would be to use a query to collect all of the
data
in
the same place, and use that as the source for the form.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Maurício Paiva" wrote in
message
...
Hi!
I have 4 spreadsheets that are filled by 4 different users.
I am not an expert in Access but I created a Database and linked all
of
these 4 spreadsheets into table section. (I Have: CA1, CA2, CA3 and
CA4)
Now I am trying unsuccessfully to merge these 4 tables in order to
create
only one form.
Can anybody help me with this?

Thanks a lot.
Maurício

Hi Jeff.
in fact I had to create the table before creating the excel links.
and You are right... it worked fine.

The problem now is query doesn't allow the table to be updated... I have to
run it everytime to reflect to table.

For example:
I added new fields to the spreadsheet and the link into excel was updated
but not the new table with all info.
How can I do this real-time?

Thanks a lot!

"Jeff Boyce" escreveu:

Did you first create the new table?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"MaurÃ*cio Paiva" wrote in message
...
Jeff,
the idea with the queries is great... however when I try to append I can
see
only the other excel lilnks... it doesn't show me that new table a create
with the fields only.
Is there any trick?

MaurÃ*cio

"Jeff Boyce" escreveu:

Mauricio

You have four linked 'tables' in Access.

Create a new table (empty) that is defined as needed.

Create a query that returns all the records from one of your four linked
tables. Open that query in design view and modify it to be an append
query.
Append field (values) from your linked table to your permanent table.

Modify the query to "point" at a different linked table and append that
table's records.

Repeat until finished.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"MaurÃ*cio Paiva" wrote in
message
...
Yes Jeff.
Actually I meant these 4 linked tables into one.
Can you pls instruct me how to create a query?

I tried some other functions but I always receive a message saying that
tables are linked from excel and some actions could not be performed.

Tks,
MaurÃ*cio

"Jeff Boyce" escreveu:

Mauricio

When you say "merge these 4 tables", do you mean actually put the data
from
each of the four sources into a totally new table?

An alternative to this would be to use a query to collect all of the
data
in
the same place, and use that as the source for the form.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP


"MaurÃ*cio Paiva" wrote in
message
...
Hi!
I have 4 spreadsheets that are filled by 4 different users.
I am not an expert in Access but I created a Database and linked all
of
these 4 spreadsheets into table section. (I Have: CA1, CA2, CA3 and
CA4)
Now I am trying unsuccessfully to merge these 4 tables in order to
create
only one form.
Can anybody help me with this?

Thanks a lot.
MaurÃ*cio