Taking a company off of a relied on spreadsheet

[TimSG]

This might have needed to be posted in a different forum. However...



I can't say I am new at Access, but I would still put myself in the beginner category. I've designed a database in the past - and I am aware it had poor practices involved, but it did suit it's purpose. I am now working with a new company that doesn't have much in a database and the items that are database driven are single stand-alone entities. Such as they use qb for their accounting side of things and they have a database called Wasp for inventory. At one time it was designed to link to qb, but that option no longer is possible and they never did it.

In any event, the company I work for is a job shop that keeps everything else on spreadsheets. Their Bills of materials are kept on spreadsheets and their orders and scheduling system is kept in a separate workbook. My focus is currently the workbook and I am having a little trouble deciding how this should be broken up. On one tab of the workbook, they have 38 fields - and one of those links to another sheet in the work book, where as that tab as about 7 that link back to the first one.

They have typical things like Job Code, Customer/Job, Order date, but then they go on to things like Approved Dollars, Unapproved Dollars, Deposit Amount, Deposit Date They have about 12 different fields for dates alone, approval dates, Date provided to the customer, Ready to ship date, Ship Date and the Actual Ship Date (some of them seem redundant). I could just throw this all in one table, but I would say this would be a bad practice. Just looking for pointers on how this should be broken up.

Thanks for your help.

[June7]

It is a balancing act between normalization and ease of data entry/output - "normalize until hurts, denormalize until it works". Those multiple date fields don't seem so bad to me.

It's the two-way link you describe that has me confounded.

[TimSG]

It is a balancing act between normalization and ease of data entry/output - "normalize until hurts, denormalize until it works". Those multiple date fields don't seem so bad to me.

It's the two-way link you describe that has me confounded.

Thank you for your response. Here's the way the spreadsheet links. On the first sheet called current job log, there is one column titled Actual Ship Date and that looks up what is listed on the second sheet in the workbook (called Prod Schedule) and looks in a column titled Actual Ship. Then, on that same sheet, it has lookups to the first sheet for Customer/Job, Customer Service Rep, Wanted Date, The Amount of the order (in dollars) and the amount of the deposit for the order. The spreadsheet titled Prod Schedule is only to be handled by production personnel, where the first one is supposed to be handled by customer service and that is why the reference each other. There are a few other people who look at this file - like are accountant for invoicing purposes and some other people to determine our forecast by the week and by the month.

The problem is the workbook is shared and we are starting to see the deficiencies of a shared workbook. The size of the file is quite large for a spreadsheet and I would like to convert it to a database without disrupting the apple cart. The only way that will happen is if I just transfer the file as it is to a table and let them manipulate the table just as if it was a spreadsheet - but of course through a datasheet form. Then I just need to figure out how to make a scheduling module that is similar to a gantt chart. It isn't exactly. What they did is put the days of the week in the columns and then the orders in the rows and filled in when people were starting on jobs in each day. I am sure it is something easy, but I am still a novice.

Then, I can add the BOM table information and try to convert inventory to it and it would be just like a real production system.

[June7]

Yes, get this in Access ASAP! The database should be split into frontend and backend files. Each user should run their own copy of the frontend.

Your 'Gantt' chart might be adequate for your situation. If you want to explore how to generate the chart from normalized data, examine attached db.

GanttChart.mdb

Another example in https://www.accessforums.net/showthread.php?t=18459

[TimSG]

Thanks, June7.
Yes, I agree it needs to get into access. I just have to figure out how to normalize the data - that is going to be my biggest issue. Thanks for the chart examples and I think I might be able to take the example and make it work for me. And it's kind of funny you came up with a calendar example because the boss also wants an online calendar with our time off - so that will be extremely helpful for reference as well.

I am sure I will need further help and what you have supplied me will help a lot.

[TimSG]

Hello All -

I took a break from trying to solve this issue to do another project. Let me explain what the schedule looks like and how we currently use it. The schedule is obviously a spreadsheet and it contains a lot of information, but I will shorten it down. The columns contain the job number, the due date Whether it was released or not, and from there each column is a date - basically the entire year is depicted. We shade the weekends to yellow and any holidays that come along. And as you guessed it the rows are the jobs filling in this information. So, to schedule, we go to that due date and put in SHP for the day it is supposed to ship and it has a formula that will put that date in the due date column. Then, we work backwards to fill in each location that will be working on the job and placing them into the dates we expect they will be working on it. We have a finishing department and they would be right before SHP, then we have a building department next to that and so on. When each area is on schedule for that date, we color it green. if they are not, its red. There are other various colors that we fill in.

So, I am trying to figure out how I can get this into a database where I can show the same basic principle. I want to be able to do pretty much the same thing, just in a database format. The closest thing I have seen to this is a gantt chart, but that isn't exactly what I am looking for. Any ideas are welcome.

Thanks,
TimSG

[Micron]

IMHO, you need to research normalization and some other basic, but very important, design principles and post back in this forum. Either start a new thread and link back to this one, or continue on with this one, the idea being to post your schema in some way. However, unless they're prepared to allow you a long learning and development curve, you are looking at a lengthy process to do it right. If you need to campaign for change, you'd better come up with a new and better wheel lest you damage your credibility and deeply entrench those who like the status quo. Your comment about simply transporting a spreadsheet to a table says to me that you don't grasp the difference between Excel (column based data) and Access (row based). If you do not grasp the entity/attribute relationship and forge forward, I believe you will fail (sorry). Also suggest you look for existing database templates/models and see if any are similar to what you need. With any luck, you might find one that's usable out of the box. If not, they can at least help to make sense of that relationship. Try some of these links, or search based on Access database design, etc. and then maybe post back with a schema. A very good approach to design is, believe it or not, pencil and large paper.

Data models
http://www.databaseanswers.org/data_models/

Normalization is paramount. Diagramming maybe not so much for some people.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/...cation-in.html

Important for success:
One source about how to name things - http://access.mvps.org/access/general/gen0012.htm
What not to use in names - http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html

[TimSG]

First, thank you for your response. I apologize because I didn't clarify what I was asking. Let me start out by saying I do know what normalization is and why it is important. You assumed rather wrongly that I just wanted to drop the excel file into a table - and that is not what I want to do. I was looking for help to DISPLAY the data that will be pulled from the tables to look similar to the excel sheet they are using now. Yes, I would like to manipulate it like I could on an excel sheet, but if that is not possible, that is fine. My main goal would be able to display data similar to the excel sheet I described. That is all.

Thanks for you help.

[Micron]

The only way that will happen is if I just transfer the file as it is to a table and let them manipulate the table just as if it was a spreadsheet

Well, that's where I got the notion from. Sorry for the ass-umption.
Beginning to look like you need a CMMS/work order type of design, even if it is manufacturing and not repair. If you do understand normalization, then maybe you ought to take a crack at it and post a pic of your schema (or relationships if you go that far) for comment.