I am new to Access. I have a task now that needs me to disign a excel spreadsheet to collect data from my clients and then automatically (or using relatively easy) transfer these data to Access. My question is: how can I achieve that. Since I have no much knowledge on Access. I need your generous help.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Excel data - Access database
- Thread starter msand
- Start date
Corticus
Well-known Member
- Joined
- Apr 30, 2002
- Messages
- 1,579
Hi msand,
It sounds like this will be mostly for your use, which will make your life a lot easier. The biggest pain about Access to me (or any front-end for that matter) is that you have to make it impossible for even the most computer-illterate person to foul up.
I think most importantly, before you even get into the Access end of this project, well, hold up-
Do you have to use both programs? I see this scenario frequently and many times it is possible to achieve the desired end result using Access or Excel, not both. If you think this is an option, skip the rest of my post, and repost with which you want to use..
Yeah, before you get into the Access end of this, you want the Excel end of it set up correctly. In order for Access to pull data from Excel without going to great lengths, you need to organize your data in tables, with field names starting at A1 and going across left to right, one field per column, no blanks. You also need one row per record, no blanks (you can have blank fields, but you must at least have a primary key).
If your spreadsheet has users entering data in a format other than this, which is very likely, you can create a 'data' sheet, that consists entirely of reference to the other sheets, but formats the data in this manner.
Next, go into Access, and select File|Get External Data, select to import or link to your Excel tables, and use the file browser to go find your Excel file, make sure to set the fiel type to .xls If you use linked tables, you won't have to keep reimporting the data to keep it fresh, but the data will reside only in the Excel file, if you delete it, your data is gone.
Now you're in Access with your data in nice tables, and its time for relationships, querries, reports, forms, vba, SQL and all the glory that is Access.
I'm sure you'll need more, so please post back.
It sounds like this will be mostly for your use, which will make your life a lot easier. The biggest pain about Access to me (or any front-end for that matter) is that you have to make it impossible for even the most computer-illterate person to foul up.
I think most importantly, before you even get into the Access end of this project, well, hold up-
Do you have to use both programs? I see this scenario frequently and many times it is possible to achieve the desired end result using Access or Excel, not both. If you think this is an option, skip the rest of my post, and repost with which you want to use..
Yeah, before you get into the Access end of this, you want the Excel end of it set up correctly. In order for Access to pull data from Excel without going to great lengths, you need to organize your data in tables, with field names starting at A1 and going across left to right, one field per column, no blanks. You also need one row per record, no blanks (you can have blank fields, but you must at least have a primary key).
If your spreadsheet has users entering data in a format other than this, which is very likely, you can create a 'data' sheet, that consists entirely of reference to the other sheets, but formats the data in this manner.
Next, go into Access, and select File|Get External Data, select to import or link to your Excel tables, and use the file browser to go find your Excel file, make sure to set the fiel type to .xls If you use linked tables, you won't have to keep reimporting the data to keep it fresh, but the data will reside only in the Excel file, if you delete it, your data is gone.
Now you're in Access with your data in nice tables, and its time for relationships, querries, reports, forms, vba, SQL and all the glory that is Access.
I'm sure you'll need more, so please post back.
Upvote
0
Dear Corticus:
Thanks for your help. Actually the methodology is from my manager who has no experience with Access either. Last night, I had a quick browsing with Access and found actually I dont have to use both Excel and Access. So the question is: I gota find a handy data collection way (no matter in whatever way) for my clients and then save it in an Access database. Since data sheet from each client will come back to us from time to time, I am trying to find a way to streamline the updating process. So, if I want to use Access, how can I finish this task. BTW, i just joined my company and this is my first task. I dont want to screw it so please help. I should have learned Access earlier
Thanks for your help. Actually the methodology is from my manager who has no experience with Access either. Last night, I had a quick browsing with Access and found actually I dont have to use both Excel and Access. So the question is: I gota find a handy data collection way (no matter in whatever way) for my clients and then save it in an Access database. Since data sheet from each client will come back to us from time to time, I am trying to find a way to streamline the updating process. So, if I want to use Access, how can I finish this task. BTW, i just joined my company and this is my first task. I dont want to screw it so please help. I should have learned Access earlier
Upvote
0
Corticus
Well-known Member
- Joined
- Apr 30, 2002
- Messages
- 1,579
Okay,
Well what does this database have to track?
Are you familiar with tables and how to organize them?
If so, please post what you have in mind for your tables. If not, just post what kind of data you have, and what you want this db to do. It's hard to say if whether Excel of Access is a better route for you until I see what you're trying to do.
Well what does this database have to track?
Are you familiar with tables and how to organize them?
If so, please post what you have in mind for your tables. If not, just post what kind of data you have, and what you want this db to do. It's hard to say if whether Excel of Access is a better route for you until I see what you're trying to do.
Upvote
0
Dugantrain
Active Member
- Joined
- Feb 10, 2003
- Messages
- 354
Are you going to be designing their datasheets/spreadsheets that they'll be using? If so, you have a HUGE advantage in that you can design it the way you want and then just either import or copy/paste right into your table (there are also TransferSpreadsheet and Sendobject methods in Access and Excel VBA, but I'm assuming that your knowledge of VBA is minimal). Just keep a few beginner hang-ups in mind:
An Access table is not an Excel spreadsheet. So don't create a new table every time you get a new spreadsheet. Create one table, import all of your data into that table and then use queries/forms/reports to filter the criteria so that you see what you want. I'm sorry if this is elementary, but too many times I've seen database with, like, 20 different tables in it where there should have been one.
Keep us updated on your progress.
An Access table is not an Excel spreadsheet. So don't create a new table every time you get a new spreadsheet. Create one table, import all of your data into that table and then use queries/forms/reports to filter the criteria so that you see what you want. I'm sorry if this is elementary, but too many times I've seen database with, like, 20 different tables in it where there should have been one.
Keep us updated on your progress.
Upvote
0
here is what I want to do
Thanks for your help. Here is what I want to do.
I want to build an Access db which includes all the vendors' info. such as price, location, blablabla. However, to collect the info. on vendors, we need to send out our survey or Request For Information. Obviously, the survey (questionair) format is different from Access table. In access table, the info. of each vendor will be a record or in one row. So, my questions are: #1: how can I design the questionair so that it is easier to integrate to the Access table. #2: Is there any automation tool such as VBA or something else that can help me to finish the integration process? We often sent out our info. request for our vendors so when the info. come back, I need to integrate the info. to the Access db really quick. After putting all the info. in the access db, we can use query to analyze them.
Thanks for your help. Here is what I want to do.
I want to build an Access db which includes all the vendors' info. such as price, location, blablabla. However, to collect the info. on vendors, we need to send out our survey or Request For Information. Obviously, the survey (questionair) format is different from Access table. In access table, the info. of each vendor will be a record or in one row. So, my questions are: #1: how can I design the questionair so that it is easier to integrate to the Access table. #2: Is there any automation tool such as VBA or something else that can help me to finish the integration process? We often sent out our info. request for our vendors so when the info. come back, I need to integrate the info. to the Access db really quick. After putting all the info. in the access db, we can use query to analyze them.
Upvote
0
Corticus
Well-known Member
- Joined
- Apr 30, 2002
- Messages
- 1,579
Okay,
I think the design of your form for the user is kind of irrelavant, you can pretty much organize the data in the most logical manner to you on that. If you use a form for data entry in Access, you can model this form after the form from which you have to enter data, the tables are in the background.
Dugantrain raises a good point about lots of tables. When you start trying to figure out your tables, they're very different from spreadsheets, in that there will be no calculations and the data must be in a strict format.
I imagine you need some help with your tables to start, so it would help if you could post some sample data and maybe some ideas you have for your tables.
I think the design of your form for the user is kind of irrelavant, you can pretty much organize the data in the most logical manner to you on that. If you use a form for data entry in Access, you can model this form after the form from which you have to enter data, the tables are in the background.
Dugantrain raises a good point about lots of tables. When you start trying to figure out your tables, they're very different from spreadsheets, in that there will be no calculations and the data must be in a strict format.
I imagine you need some help with your tables to start, so it would help if you could post some sample data and maybe some ideas you have for your tables.
Upvote
0
Similar threads
