1 / 12

Tony Bayliss FCCA Senator System Consultants Limited

Explore a robust database system with continuous form feature akin to spreadsheets but more efficient. Developed by Tony Bayliss of Senator System Consultants Ltd, the system integrates Sage 50, Access, and SQL for seamless operational management. Enjoy unique features like schedule automation and priority-based item management. Learn more at senatorsys.co.uk.

rwilburn
Download Presentation

Tony Bayliss FCCA Senator System Consultants Limited

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Case Study with Interesting features including:Continuous Form allowing User to Move Rows Around (Like A Spreadsheet but Better) Tony Bayliss FCCA Senator System Consultants Limited www.senatorsys.co.uk tbayliss@senatorsys.co.uk

  2. My First Little Database. Dataease For DOS – Still going!

  3. Previous Access User Group Talks • Linking Access to accounts systems, particularly Sage 50 with Developer VB toolkit • Controlling user access using VBA behind menu forms. • School Admission System allowing online applications into a MySQL Database with PHP web form and Access front-end. • Downloads on the above at www.senatorsys.co.uk plus: • Name and address upper and lower case fix. VBA utilities allowing for most unusual capitalisations, – Mc, O’, etc and UK post codes. • Convert amounts in numbers to words up to 1000 pounds. • Tony Bayliss, Senator System Consultants Limited tbayliss@senatorsys.co.uk

  4. Overview of Whole System:Client powder coats aluminium sections & sheetsPrevious system Unix-based needed replacing.My system in use since 2013 comprises:1. Sage 50 for accounts 2. Custom Access/SQL applications: a. Job/Sales/Works Order Processing b. Despatch and Invoicing c. Production and Transport Scheduling (added 2018)Over a million order lines processed so far Tony Bayliss FCCA Senator System Consultants Limited www.senatorsys.co.uk tbayliss@senatorsys.co.uk

  5. Interesting Features • Some customer parts trigger several internal sub jobs • Some customer parts have several “additional operations” • So, one line on an order may result in extra chargeable invoice lines • How do you show the additionals – subform within a subform? • I decided on a button to view/edit additionals in a dialog box but showing number of additionals and summary on main screen.

  6. Data Entry With Sub Items and Sub-SubItems

  7. Production Manager’s Daily Chore. 5 of These Spreadsheets. Jobs to Move, Delete, etc

  8. Production Scheduling • About 1000 jobs outstanding at any time. • Need scheduling into different production lines • Some jobs involve operations on more than one line • Previously done by manually entering job summaries into spreadsheets (one per line) and moving them around using insert rows, cut and paste. • Job details already in Access/SQL database so big time saving if we can replicate the spreadsheets in Access. • Also schedules of jobs for one customer are sent to them by email – again done on spreadsheets by copying and pasting from full schedules.

  9. Solution • Asked advice on Access User Group Listserver first (big response), then…. • New table to store Production Schedule Items. 65,000 records created in one year. (SQL view to extract only items not complete) • Button within job entry to create and view/edit items in production schedule table (may be several schedule operations on different lines) • When items are despatched from job system, they are complete and corresponding schedule items are completed automatically so disappear from the schedules. • Screens to create, view/edit all production schedule items for each line. • Two fields needed in schedule table. Priority number and Select (Yes/No). • Change sequence using priority numbers and move several at a time using select field. Several items can have same priority numbers and there will be gaps in priority numbers (happens automatically when jobs despatched and completed). • So much quicker than insert, cut and paste on a spreadsheet.

  10. Schedule edit screen • One screen for each production line • Continuous form with each scheduled production item • Button to print the schedule. Red highlight facility • Each line has a priority number and a select box • Can just change priority no. of one or more lines then click Re-Sort • To move an item or items: • a. Select with tick box (or button to select all with same priority) • b. Move button asks what is new priority number for selected item(s) • c. It asks: if existing item(s) with that number, move those down? (i.e. add 1 to priority number for that and following numbers) • Renumber all to remove gaps (e.g. where items have been completed/despatched)

  11. In summary:A simple template for a continuous form where rows can be moved around singly or in blocks – almost like a spreadsheet but better and quicker. Tony Bayliss FCCA Senator System Consultants Limited www.senatorsys.co.uk tbayliss@senatorsys.co.uk

More Related