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
|
|||
|
|||
Access and Excel
Hello there,
My friends company are looking for ways to speed up so she asked me..... They have lots of sales and target data in fairly large Excel spreadsheets. With formulas, charts, etc. They have a number of sites that each month send them their figures for the month either via email, fax or spreadsheet. My friend is then copying all this info into her spreadsheet.....please bare with me.......... She then has to do a Word document with information about each month, which she is at the moment typing in again. They also want to stop the various branchs from updating the data afterwards and not allow the branches to see each other. What could they do? I've said I would help. I immediately thought of Access with forms, security, etc. Her boss has now said that he will only allow her to have Access, the other branches will only have Excel. Has anyone got any tips for having the branches always use Excel and for that to update a database, which can then be queried for mail merges?? I have to say I'm still wanting to create some sort of Access database for her as she is reentering 3 times at the moment. Your help is much appreciated Judith |
#2
|
|||
|
|||
Judith,
It is certainly doable, but be warned: there's more to it than meets the eye! Apart from a robust data structure design, it will take a simple, foolproof, intuitive user interface for input / output. The downside here is, the simpler and esier it is for the user, the more complex it is for the developer. My point is, even if you know your stuff inside out, it will still take quite some time to put it together, it is not something you can expect to set up overnight... which is why some people/cpmanies make a living on such projects. So, are you prepared to do this for free? Assuming that you are, here are some thoughts (for what they are worth): Updating: * Sites need to send in their data in standardized spreadsheets, with either a constant name, e.g. Site1.xls, or a name with a constant and a changing part like Site1-Jan05.xls. * Spreadsheets are saved in a particular folder, and linked into the Access database. If the ssheet name is constant each month the file just overwrites the previous one (you lose history, at least in that folder), or if there is a time indication in the filename, some simple VBA code can be employed to change the link each month. * Data from the linked ssheets is imported in native Access table(s) through append queries, with a month indication added. It's a good idea to run a delete query for the specific ste and month prior to each import, so you cope with revisions. All automated in VBA code. Reporting: * Table with siteID, site name, mail address per site; * Universal report for all sites, based on query including siteID field. Site filtering on criterion read off a form. * VBA process to open above table as a recordset, browse through the records, and for each record (a) put the siteID in the control in the above form for the report to be filtered on, and send the report to the mail address in the record. Surely there may be a number of different approaches; this is how I envisage it at first glance. If you decide to go ahead with it, post any specific questions you may have. In any case, make sure you spend enough time on the data structure; a robust design is only half the job, but a bad one is a recipe for disaster, and the later down the development process it is found out the worse! HTH, Nikos JudithJubilee wrote: Hello there, My friends company are looking for ways to speed up so she asked me..... They have lots of sales and target data in fairly large Excel spreadsheets. With formulas, charts, etc. They have a number of sites that each month send them their figures for the month either via email, fax or spreadsheet. My friend is then copying all this info into her spreadsheet.....please bare with me.......... She then has to do a Word document with information about each month, which she is at the moment typing in again. They also want to stop the various branchs from updating the data afterwards and not allow the branches to see each other. What could they do? I've said I would help. I immediately thought of Access with forms, security, etc. Her boss has now said that he will only allow her to have Access, the other branches will only have Excel. Has anyone got any tips for having the branches always use Excel and for that to update a database, which can then be queried for mail merges?? I have to say I'm still wanting to create some sort of Access database for her as she is reentering 3 times at the moment. Your help is much appreciated Judith |
#3
|
|||
|
|||
Judith,
It seems likely that a properly designed and constructed Access application would help your friend's work to be more efficient. As long as the data in the Excel files is in a suitable structure, it will be possible to import this data into the database, and for this process to be reasonably automated. Possibly this would involve the use of the TransferSpreadsheet method, followed by some data manipulation in Access via append queries and update queries. And similarly, she will probably be able to export the required data from her Access database to a suitable format in Word, or PDF, or some such. -- Steve Schapel, Microsoft Access MVP JudithJubilee wrote: Hello there, My friends company are looking for ways to speed up so she asked me..... They have lots of sales and target data in fairly large Excel spreadsheets. With formulas, charts, etc. They have a number of sites that each month send them their figures for the month either via email, fax or spreadsheet. My friend is then copying all this info into her spreadsheet.....please bare with me.......... She then has to do a Word document with information about each month, which she is at the moment typing in again. They also want to stop the various branchs from updating the data afterwards and not allow the branches to see each other. What could they do? I've said I would help. I immediately thought of Access with forms, security, etc. Her boss has now said that he will only allow her to have Access, the other branches will only have Excel. Has anyone got any tips for having the branches always use Excel and for that to update a database, which can then be queried for mail merges?? I have to say I'm still wanting to create some sort of Access database for her as she is reentering 3 times at the moment. Your help is much appreciated Judith |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
WHY | General Discussion | 9 | December 16th, 2004 12:49 AM | |
How to use a Access Query that as a parameter into Excel database query | Karen Middleton | Running & Setting Up Queries | 1 | December 13th, 2004 07:54 PM |
Data from Excel to Access | Database Design | 2 | August 20th, 2004 12:53 PM | |
Linking Excel data through Access to use a Form as an Interface | Laura | Links and Linking | 0 | March 23rd, 2004 03:59 PM |