Please help? Need plan of attack.

[GADTG]

I've deleted all my queries and forms and gone back to my tables on this workflow database. I was being a little bit of a maniac because I hit a wall, but now that I've calmed it down a bit I would like to as all of you lovely experts for your opinion on how to approach this.

I am teaching myself. Apologies in advance for butchered terms.

I have tblProjects. There can be only 1 customer per project, and there are certain fields that apply to the entire project (payment, etc). Within each project, there can be multiple tblStockArtProduction entries (within each StockArtProduction entry the fields are all dependent- I tried to break it up before but it was just a bunch of messy 1-1 relationships).

My original plan was to have a Projects Form with StockArtProduction as a subform. But the StockArtProduction subform MUST only show previous entries from, and add new entries to, the open Project for this to be an effective database.

I hit my wall in the last version with new subform entries. I could not get it to automatically add just to that Project (auto populate?). The employee would have to manually pick that Project... and I honestly just don't trust them to do that. (I still have this version if anyone has a thought to make it salvageable. It was looking pretty.)

So, now I'm open-minded. The Project and StockArtProduction relationship is the heart of this whole project. Employees will need to view both as a whole (we call it a workorder) throughout the life of the project. Other than that, I am willing to entertain any and all thoughts of how to structure.

I currently have a version they're using that just has a bunch of repeated blanks for StockArtProduction in the Projects form. But I want to do this the right way- build for longevity and flexibility so I won't have to do this from scratch again in another 2 years.

I have included my tables and relationships if it helps or inspires suggestions.
Please please please help. I can send you anything you need. Including money. But seriously, I'm outside my paygrade on this. Appreciate it.

 

[plog]

First, table relationships shouldn't look like an Arkansas family tree. There should only be one path between tables. I see 4 between tblProjects and tblStockArtProduction.

I really think you need to start on the relationship all over. Strip out every table except tblProjects and tblStockArtProduction because you say those are the main tables. Then determine how they need to be related. If it is directly that is fine, if not, add the tables necessary to make the connection, but make sure there is only one path between those 2 tables. After that is established add tables one at a time determining what other tables they need to have a relationship with, but be sure that doing so doesn't create duplicate paths.

At a table level, I'm not certain 2T, 4T, 6T and the size fields in tblStockArtProduction shuold be there--they might need to be another table. Of course, I can't say that definitelively without knowing more about the data they hold.

 

[GADTG]

I battled with the StockArtProduction fields. Because purchasing uses that information straight off the workorder (workorder= Projects and StockArtProduction combo view I'm trying to achieve) I think (*hope*) they are in the right spot.

With relationships, I may be missing something fundamental about the concept. In every instance of a field referencing another table I made a relationship (the Employee who authorizes production, the Employee who completes the artwork, the Employee who orders the stock). Is this not the correct approach?

Stripped to just the main tables in question is attached. Thank you for your willingness to help. I had gotten a bit stressed.

 

[plog]

If different employees do different tasks on the project, that's fine and you've set it up correctly. My point was that your initial relationship screenshot had circular paths in them. Even with different employees doing different tasks, circular paths are incorrect.

Using just the recent screenshot: In EmployeeID in tblProjects and EmployeeIDAssignedTo in tblStockArtProduction. What differeniates these 2 fields? Is this one of those instances you mentioned?

Also, explain what we have so far and how it works. Don't use database jargon, just tell me what the data in these 3 tables represents. What type of business are you in? Give me an example of a Project and accompany Stock Art.

 

[GADTG]

I may need further explanation of circular relationships. I'll be happy to get rid of them.

Using just the recent screenshot: In EmployeeID in tblProjects and EmployeeIDAssignedTo in tblStockArtProduction. What differeniates these 2 fields? Is this one of those instances you mentioned?
>The usage of EmployeeID in those fields is exactly what I mentioned before. It's who took the project initially, and who is currently assigned to the artwork.

We do custom apparel! We have 10+ processes (screen-printing, embroidery, digital, decals, rhinestones,etc).

Here is where we are:
Customers place orders with us that typically are made up of multiple stock items or multiple processes.

For example, Adam Smith wants to make an order for "Team Mom Shirts". "Team Mom Shirts" is the Project- projects have some "blanket" info: customer, due date, payment info, etc. But within the project they may want 10 shirts with rhinestones, 25 screeenprinted and 40 embroidered. Each of those processes could be on different stock and will require different artwork to suit the process.

So this is an abbreviated version what it will look like (many fields missing):
PROJECT: Team Mom Shirts
[customer]: Adam Smith
due: 4/2/2014
payment status: Deposit on Account
[order taken by]: Lindsey (lookup from employee table)
-more blanket fields-
STOCKARTPRODUCTION:
1.
Purchasing Status: Need Inventory
[Stock]: Gildan Standard (lookup from products table)
Size Breakdown: (the 2T-XXXXL fields)
Process: Rhinestones (lookup from processes table)
Artwork Status: Needs Artwork
Art: Supplied logo large front
[Art Assigned to]: Lindsey (lookup from employee table)
Production Status: Not Ready
2.
Purchasing Status: Need Inventory
Stock: Gildan Tank (lookup from products table)
Size Breakdown: (the 2T-XXXXL fields)
Process: Screenprint (lookup from processes table)
Artwork Status: Waiting on Artwork
Art: Supplied logo front crest, full back with added frame
[Art Assigned to]: Josey (lookup from employee table)
Production Status: Not Ready
3.
Purchasing Status: Pulled
[Stock]: PA Polo (lookup from products table)
Size Breakdown: (the 2T-XXXXL fields)
Process: Embroidery (lookup from processes table)
Artwork Status: Needs Artwork
Art: Supplied digitized file, names added to right side in TNR
[Art Assigned to]: Emily (lookup from employee table)
Production Status: Not Ready
4.
Ready to add more to THIS project! (Customers are famous for adding more in the middle of the life of the project)

I hope this better explains it! Let me know if not and I will gladly try again!

 

[plog]

A circular relationship is a path that ends up where it starts. For example you can go from tblStockArtProduction to tblProducts to tblWastLog to tblProcesses back to tblStockArtProduction. You created a loop. That can both restrict the data you want to enter and cause incorrect data to be entered.

You also have multiple paths between tables. For example, using your first screenshot I said I see 4 paths between tblProjects and tblStockArtProduction:

1. Directly on the ProjectID field.
2. tblProjects - tblEmployees - tblStockArtProduction
3. tblProjects - tblEmployees - tblWastLog - tblProcesses - tblStockArtProduction
4. tblProjects - tblCustomers - tblCustomerContact - tblEmployees - tblStockArtProduction

Table relationships should look like branches of a tree not spiderwebs. Limbs can branch off from the trunk or other limbs, but they never attach to other limbs to form a circle.

Thanks for the explanation, that helps. Let's add tblCustomerContact to your most recent relationship screenshot. I assume this just logs all communciations between employees and customers, but at what level? Do you want to capture this at the Project level or the StockArtProduction level? What information are you capturing in this table?

 

[gemma-the-husky]

plog will give you some good guidance

but take this request

My original plan was to have a Projects Form with StockArtProduction as a subform. But the StockArtProduction subform MUST only show previous entries from, and add new entries to, the open Project for this to be an effective database.

the key to everything is to get the data designed correctly. once you get that right, you can be sort out the presentation. conversely, If you are struggling with presentation, it may well be because the data analysis needs attention

 

[GADTG]

For customer contact, I was planning on showing that as a subform as well (on the Customer form). Same issue as Projects and StockArtProduction... got it to show created contact. Couldn't get it to auto populate the new additions.

On the projects form, ideally you would click the customer name which would open their contact form with contact subform.

Futhermore, contacts can be flagged for "follow-ups" which consultants use as a running we-need-to-call-back list. I had this organized as a sorted continuous form with validation of RequiresFollowUp = True (and Resolved = Not True)

Contact does not have to be related to a project, just a customer. We have a lot calls for estimates on complex projects, check product availability, etc. and we have to "flag" them to get back to them before starting a project.

For these loops- should I go ahead and look at them now and try to resolve or just leave be for now? I don't want to do anything to disrupt progress.

 

[plog]

The loops don't exist any more, we are building your relationships from the ground up and are going to avoid them this time. We just got rid of one of them by you determining the relationship tblCustomerContact has in this database. Now let's get rid of the other one.

Describe what tblProcess and tblProducts hold. My hunch is that Products are part of the process and therefore should be related to that instead of directly to tblStockArtProduction. Can you give me a better screenshot of all the fields in those 2 tables as well as an explanation for what those 2 tables hold (again, describe in terms of your business not in database jargon).

 

[GADTG]

I'm so pumped!

The products table is all things related to our current products- what is is, who makes it and where to get it... this may be more appropriately called "Stock". We do a majority of apparel, but also do mugs, bags, phone covers, and all that jazz.

The processes are all of our available imprinting processes and services + graphic design only (what I USUALLY do lol). All of our processes are done between 2 locations (warehouse or showroom).

Most products are compatible with with most (if not all) of our imprint processes. Few have limitations because of material, etc. The process we use on each product is totally up to the customer, determined during consultation on many factors (price, turnaround time, etc). And sometimes multiple process will be used on one item.

 

[plog]

...And sometimes multiple process will be used on one item.

Your latest structure doesn't accomodate that. With what you have a product can have just 1 process. How would you put that in the current database structure?

Also what about this scenario:

In one project a customer wants a shirt and a blanket done with rhinestones and another shirt and blanket with screen printing. How many records does that take in tblStockArtProduction?

 

[GADTG]

Your latest structure doesn't accommodate that. With what you have a product can have just 1 process. How would you put that in the current database structure? Yep I had this "fixed" entries "multi-warehouse" and "multi-showroom" which would be clarified in notes. Not ideal I'm sure.

In one project a customer wants a shirt and a blanket done with rhinestones and another shirt and blanket with screen printing. How many records does that take in tblStockArtProduction? As I was envisioning it would be 4 entries under one project. Because the screen-printed items would be filtered to the warehouse upon production, and the embroidery would stay in showroom.

I realize there may even be a workflow issue. We've been working this way for 5 years (back when we had just 1 process). I'm VERY open minded.

 

[plog]

In my scenario your answer was the one I was hoping for. That's the right way to do it.

To accomodate multiple processes you would need another table in between tblStockArtProduction and tblProcesses. I don't know how many tblStockArtProduction have multiple processes, so you may just leave it like you have it.

We are almost there, I promise. The last thing we need to remove the circular paths is to resolve how tblWastelog fits into your structure. In your initial post you have it indirectly tied to tblStockArtProduction in two ways (via tblProcesses and via tblProducts). It needs to be directly tied to tblStockArtProduction. So instead of Process and Product fields you need a field to hold the ID field from tblStockArtProduction. Then to get product and process information you get it by going through tblStockArtProduction.

 

[GADTG]

To accomodate multiple processes you would need another table in between tblStockArtProduction and tblProcesses. I don't know how many tblStockArtProduction have multiple processes, so you may just leave it like you have it. It happens maybe once a month. Most people don't want to pay for it. So as long as you think it won't cause problems I think I may leave it with "multi".

We are almost there, I promise. The last thing we need to remove the circular paths is to resolve how tblWastelog fits into your structure. In your initial post you have it indirectly tied to tblStockArtProduction in two ways (via tblProcesses and via tblProducts). It needs to be directly tied to tblStockArtProduction. So instead of Process and Product fields you need a field to hold the ID field from tblStockArtProduction. Then to get product and process information you get it by going through tblStockArtProduction. I think I followed- attached!

 

[plog]

Perfect. Now the key to the rest of the tables is to add them as needed. So add tblAffiliations, tblVendors, tblSupplyRequest, tblMonogramFrames, tblFabrics and tblReferredBy just like you had initially.

The only tricky table is going to be tblEmployees which needs to be linked to multiple tables. For every table that links to tblEmployees, you bring another instance of tblEmployees in.

For example you have EmployeeID in tblCustomerContact, tblProjects and tblStockArtProduction. You will need 3 instances of tblEmployees in your relationships--one for each of these. Do not use the same tblEmployees to link to both tblCustomerContact and tblProjects.

I just noticed tblWastelog also has an EmployeeID--I don't think that is necessary. I believe since tblWastelog is linked to tblStockArtProduct that it should use that table's EmployeeID. Correct?

 

[GADTG]

Perfect. Now the key to the rest of the tables is to add them as needed. So add tblAffiliations, tblVendors, tblSupplyRequest, tblMonogramFrames, tblFabrics and tblReferredBy just like you had initially. Gang's all back!

The only tricky table is going to be tblEmployees which needs to be linked to multiple tables. For every table that links to tblEmployees, you bring another instance of tblEmployees in. EmployeesID is all over the place. Accountability is a huge issue because so many people touch each project. With the current system we waste so much time trying to figure out who handled what if a question arises. So I put it pretty much everywhere I thought there is potential for questions or mistakes (when they have to sign their name to it they seem to be a little more careful)

For example you have EmployeeID in tblCustomerContact, tblProjects and tblStockArtProduction. You will need 3 instances of tblEmployees in your relationships--one for each of these. Do not use the same tblEmployees to link to both tblCustomerContact and tblProjects. I'm not sure I 100% I'm following this bit. Should I create duplicate tables (Like I did one in the attachment)? Or something different?

I just noticed tblWastelog also has an EmployeeID--I don't think that is necessary. I believe since tblWastelog is linked to tblStockArtProduct that it should use that table's EmployeeID. Correct? Unfortunately that can't work because the tblStockArtProduction doesn't track which employee runs production, just where it was produced (showroom vs warehouse). I left it out because on production can be run by several people. EmployeeID is used in tblStockArtProduction to show who authorized the project to be updated to status "to print". Authorizing "to print" means they've checked to make sure all other statuses are clear (payment is at least a deposit, artwork is approved, etc). Ideally I would have it automatically check all relevant variables, but even I know I'm not ready for that yet.

However the waste log is truly a log, and if it's unlinked and they just write in their name into a text field it won't be detrimental. This feature is a bonus and not worth compromising the core. tblTimeClockErrors, tblTimeOffRequests, tblWasteLog and tblSupplyRequests are all bonus features I had added to test basic functionality... great to have but not crucial.

And again- thank you for this. I can't tell you how relieved I am to see a light at the end of the tunnel.

 

[GADTG]

Forgot to attach. It's apparently bed time.

 

[plog]

Nope, duplicate and circular paths again. You brought tblEmployees in twice: see where it says tblEmployees and tblEmployees_1? you need more of that. You need tblEmployees_2, tblEmployees_3, etc.

Right now tblEmployees has 7 tables linked to it. It should only have one. Every other link to it needs to have a new instance of tblEmployees (e.g. tblEmployees_2, tblEmployees_3). In the relationships you shouldn't have them all go to the same tblEmployees instance.

 

[GADTG]

I think it "clicked".
I want to make sure, if this is correct, that I did it the correct way...
To get the additional tables I just clicked add tables and added tbl Employees several times. From there I just deleted old relationships and added the new ones.
I did everything in Relationships- never went in to the actual tables.

 

[plog]

That's exactly what I was talking about. Looks good. Most importantly, do you understand it?

Looking at your initial relationships, you were very close--your tables were fine, just they way they were related need some tweaks. It was just a few small mental hurdles in seeing how your data ties together.