Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to Update Master Database - via Spreadsheet - sent to sites
Hi there
Jest problem :- 1.We have a Supplier database using MS Access. 2.Need to populate data from 4 different sites - different applications used. E.G. One site may have MS Access, someone would update spreadsheet we sent, comes back, we update master database. 3.One off event hopefully. Data structure at Main Site:- A.3 Tables (1st table - Supplier details, 2nd table - QA details, 3rd table - Product details) Catch :- 1.Each company must use our generic list for products i.e. consists of Code + description. CODE NAME CH00 Chemicals - Undefined EL01 Electrical. - Cable LV/MV/HV EL02 Electrical. - Cathodic Prot. System Idea :- Use spreadsheet, with 3 sheets 1.1st sheet enter supplier deatils 2.2nd sheet - select supplier from say drop down (based on 1st sheet), select code from drop down (product code) 3.3rd sheet - select supplier from say drop down (based on 1st sheet), enter Quality Assurance details. N.B. Access \SQL Developer - not done Excel VBA within Excel. Key point :- 3 sheets will have common identifer, e.g. Supplier No, Reference. It means I can import data in - without any hassle , using scripting, etc.. into Access db. Do u follow? Qn:What do u think of my idea ? - is there a better way? - how do I do it ? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to Update Master Database - via Spreadsheet - sent to sites
-----Original Message----- Hi there Jest problem :- 1.We have a Supplier database using MS Access. 2.Need to populate data from 4 different sites - different applications used. E.G. One site may have MS Access, someone would update spreadsheet we sent, comes back, we update master database. 3.One off event hopefully. Data structure at Main Site:- A.3 Tables (1st table - Supplier details, 2nd table - QA details, 3rd table - Product details) Catch :- 1.Each company must use our generic list for products i.e. consists of Code + description. CODE NAME CH00 Chemicals - Undefined EL01 Electrical. - Cable LV/MV/HV EL02 Electrical. - Cathodic Prot. System Idea :- Use spreadsheet, with 3 sheets 1.1st sheet enter supplier deatils 2.2nd sheet - select supplier from say drop down (based on 1st sheet), select code from drop down (product code) 3.3rd sheet - select supplier from say drop down (based on 1st sheet), enter Quality Assurance details. N.B. Access \SQL Developer - not done Excel VBA within Excel. Key point :- 3 sheets will have common identifer, e.g. Supplier No, Reference. It means I can import data in - without any hassle , using scripting, etc.. into Access db. Do u follow? Qn:What do u think of my idea ? - is there a better way? - how do I do it ? Thanks in advance. . Idea will work - a little cumbersome but not bad if you do not have Access available at sites where data needs to be entered. Better than scripting: use a database query to duplicate the three Access tables on your Excel sheets (from menus, Data... Get External Data... New Database Query). Then have the spreadsheet either update the tables or create a new table (with input data) and use the Get External Data... Import option of Access to bring the tables back in (with new names) - then do update and/or append queries to get the info integrated into your main Access DB. Hope this brief summary makes sense - if you have questions you can try contacting me at kgdcc(NOSPAM) @westelcom.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Best way to Update Master Database - via Spreadsheet - sent to sites
Thanks for comments
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Master Form to Database | Excel Worksheet Functions | |||
Update Excel Database via Access Main Database with a script | New Users to Excel | |||
Update worksheet from Master workbook | Excel Discussion (Misc queries) | |||
How to update a "master" spreadsheet every time I create a newworkbook or a new sheet | Excel Worksheet Functions | |||
Mailmerge Master for use with Excel database | Excel Discussion (Misc queries) |