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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Linking DataBases
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 |
#3
|
|||
|
|||
Linking DataBases
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 |
#4
|
|||
|
|||
Linking DataBases
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 |
#5
|
|||
|
|||
Linking DataBases
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 |
#6
|
|||
|
|||
Linking DataBases
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. |
#7
|
|||
|
|||
Linking DataBases
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. |
#8
|
|||
|
|||
Linking DataBases
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 |
#9
|
|||
|
|||
Linking DataBases
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 |
#10
|
|||
|
|||
Linking DataBases
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 |
|
Thread Tools | |
Display Modes | |
|
|