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 |
#11
|
|||
|
|||
Linking DataBases
Mauricio
You may be running up against the difference between Excel (*a spreadsheet*) and Access (*a relational database*). In Excel you might just add another column to get another piece of data in the spreadsheet. In Access, doing the same thing (adding another field in a table) would NOT be the correct way to handle the situation. Among other reasons, you'd have to modify your queries, your forms, your reports, your code, ... every time! What a maintenance nightmare! Perhaps if you post a bit more information about just what it is that you're importing/linking, folks here could offer more specific suggestions. For example, if you are working with "monthly" values (and just adding another month in Excel), you'll need to rethink how you structure your data in Access if you wish to get full use of the relationally-oriented features/functions in Access. Regards Jeff Boyce Microsoft Office/Access MVP "Maurício Paiva" wrote in message ... 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 |
#12
|
|||
|
|||
Linking DataBases
Hi Jeff, good morning.
I apologize to give you this trouble. We have 2 teams (pricing and sales) and they want to make sure whenever an user updates its individual spreadsheet that it will reflect to DB in access or any other suggestion. There are specific fields in the spreadsheet that must not be seen by sales team. Just to give you an idea... the pricing team (4 people) will feed their individual spreadsheet and the sales team will check for prices anytime a customer asks for. I am an infrastructure guy in the company and we hv no developers in here... that's why I am lost with this case. Was I clear? Could I clarify the idea? Thank you, MaurÃ*cio "Jeff Boyce" escreveu: Mauricio You may be running up against the difference between Excel (*a spreadsheet*) and Access (*a relational database*). In Excel you might just add another column to get another piece of data in the spreadsheet. In Access, doing the same thing (adding another field in a table) would NOT be the correct way to handle the situation. Among other reasons, you'd have to modify your queries, your forms, your reports, your code, ... every time! What a maintenance nightmare! Perhaps if you post a bit more information about just what it is that you're importing/linking, folks here could offer more specific suggestions. For example, if you are working with "monthly" values (and just adding another month in Excel), you'll need to rethink how you structure your data in Access if you wish to get full use of the relationally-oriented features/functions in Access. Regards Jeff Boyce Microsoft Office/Access MVP "MaurÃ*cio Paiva" wrote in message ... 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 |
#13
|
|||
|
|||
Linking DataBases
Maurício
I usually advise folks just starting out with Access that there are three significant learning curves they'll need to be willing to take on if they are to be successful using Access to develop an application. 1. relational database design -- if "normalization" and "relational" aren't familiar terms, become familiar with them! 2. Access tricks and tips -- you'll need to learn the idiosynchracies of how Access does things. 3. Graphical User Interface design -- what kind of design(s) work well for users and what kinds work against users. Oh yes, and you need experience as a developer! If your situation doesn't allow the time or resources to handle all these learning curves, you may need to consider hiring someone who has already learned these. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "Maurício Paiva" wrote in message ... Hi Jeff, good morning. I apologize to give you this trouble. We have 2 teams (pricing and sales) and they want to make sure whenever an user updates its individual spreadsheet that it will reflect to DB in access or any other suggestion. There are specific fields in the spreadsheet that must not be seen by sales team. Just to give you an idea... the pricing team (4 people) will feed their individual spreadsheet and the sales team will check for prices anytime a customer asks for. I am an infrastructure guy in the company and we hv no developers in here... that's why I am lost with this case. Was I clear? Could I clarify the idea? Thank you, Maurício |
#14
|
|||
|
|||
Linking DataBases
Hey Jeff.
Thanks a lot for your support. We do need start thinking change our strategy... I will talk to staff and see other available options or to hire someone who could help us with development. B.Rgds, MaurÃ*cio "Jeff Boyce" escreveu: MaurÃ*cio I usually advise folks just starting out with Access that there are three significant learning curves they'll need to be willing to take on if they are to be successful using Access to develop an application. 1. relational database design -- if "normalization" and "relational" aren't familiar terms, become familiar with them! 2. Access tricks and tips -- you'll need to learn the idiosynchracies of how Access does things. 3. Graphical User Interface design -- what kind of design(s) work well for users and what kinds work against users. Oh yes, and you need experience as a developer! If your situation doesn't allow the time or resources to handle all these learning curves, you may need to consider hiring someone who has already learned these. Good luck! Regards Jeff Boyce Microsoft Office/Access MVP "MaurÃ*cio Paiva" wrote in message ... Hi Jeff, good morning. I apologize to give you this trouble. We have 2 teams (pricing and sales) and they want to make sure whenever an user updates its individual spreadsheet that it will reflect to DB in access or any other suggestion. There are specific fields in the spreadsheet that must not be seen by sales team. Just to give you an idea... the pricing team (4 people) will feed their individual spreadsheet and the sales team will check for prices anytime a customer asks for. I am an infrastructure guy in the company and we hv no developers in here... that's why I am lost with this case. Was I clear? Could I clarify the idea? Thank you, MaurÃ*cio |
|
Thread Tools | |
Display Modes | |
|
|