Solved Work order System with a few extra bits thrown in (1 Viewer)

GeordieH

New member
Local time
Today, 01:12
Joined
Mar 31, 2020
Messages
22
Hi, I'm currently working on a Work Order System and have become STUCK! Is anyone available to go through the Relationship structure with me?
 

Minty

AWF VIP
Local time
Today, 01:12
Joined
Jul 26, 2013
Messages
7,174
I'm sure someone can assist - it would help if you could give a 10,000 ft overview of the business process, and maybe a picture of your current tables from the relationship window?
 

GeordieH

New member
Local time
Today, 01:12
Joined
Mar 31, 2020
Messages
22
Just sorting that out as we speak... Hold on.. ;)
 

GeordieH

New member
Local time
Today, 01:12
Joined
Mar 31, 2020
Messages
22
This is a Garage and it needs a job card which shows the Vehicle, WO No, WO Items (I have a straight forward structure for this and it works ok). But I need to be able to include and itemise the RA, PPE and Equip required to do the job on the same job card. It would be handy if I could select 'Many' RA's/PPE/Equip for each job, but it just wont link for some reason. I've attached a picture, I hope it comes through ok. Any ideas where i'm going wrong???
 

Attachments

jdraw

Super Moderator
Staff member
Local time
Yesterday, 20:12
Joined
Jan 23, 2006
Messages
13,269
Here is link to a generic Workflow model from Barry Williams' site.
The model shows the typical set up and is intended as starting point that you can add to, ignore or adapt to meet your needs.
It would be helpful to you and readers if you could provide a description of the business, the processes and the things involved in those processes. Sometimes communication is improved if you walk us through "a day at the office" type approach. Another is to use the tried and true- who, what, when, where, why, how much and how often - outline as a guide. There are several related articles in the Database Planning and Design link in my signature.

Welcome to AWF and good luck with your project.
 

Minty

AWF VIP
Local time
Today, 01:12
Joined
Jul 26, 2013
Messages
7,174
I'm guessing that you are effectively trying to build a "Bill of Materials" automatically for a particular type of work order?
Something like
Code:
Work Order = Oil Change
 ¬  RA = Oil contamination of skin
 ¬ PPE = Gloves
 ¬ PPE = Overalls
If not perhaps you could give us some sample data?
 

GeordieH

New member
Local time
Today, 01:12
Joined
Mar 31, 2020
Messages
22
OK, good feedback, here goes.

The garage will have many vehicles in the garage.
Each Vehicle can have many WOs (so imagine the vehicle is being MOT'd - that is one WO, the vehicle will be Serviced - that's another WO, the vehicle will be Cleaned - thats another WO) Each WO throughout the garage and will have a unique number (WO1001, etc) Start & End date and a comments section
Each WO will have many jobs to complete (so MOT will need - Oil change, Filter Change, etc)
Each Job will have specific RA (Use of Ventilation, Hand Arm Vibrations, etc), PPE (Boots, Gloves, Overalls, Eye Protection), EQ (Spanner!, Hammer, Wrench, etc) to complete the job

I've normalised the Data into the tables you can see in the attachment.

I want to be able to put this information on to a form (Tab Controlled) and subsequently on to a report (through Subforms) but to have a drop down menu which links to the RA,PPE,EQ tables. At the moment the subform will only allow me to link to the Vehicle Form but it wont provide the Link to the RA tables, I think its because they're too far away.

Does this help, see att.

I know its going to be a simple solution, but ive been battling with this for a while and I cant see the wood for the trees :)

Any offers !
 

Attachments

GeordieH

New member
Local time
Today, 01:12
Joined
Mar 31, 2020
Messages
22
Here is link to a generic Workflow model from Barry Williams' site.
The model shows the typical set up and is intended as starting point that you can add to, ignore or adapt to meet your needs.
It would be helpful to you and readers if you could provide a description of the business, the processes and the things involved in those processes. Sometimes communication is improved if you walk us through "a day at the office" type approach. Another is to use the tried and true- who, what, when, where, why, how much and how often - outline as a guide. There are several related articles in the Database Planning and Design link in my signature.

Welcome to AWF and good luck with your project. (Appreciated :))
 

Minty

AWF VIP
Local time
Today, 01:12
Joined
Jul 26, 2013
Messages
7,174
You think you are missing a table which would be what items are needed.

So As your items are all effectively simply lookups why not have them in one table with an Item type identifier?
ItemID
ItemType (RA, PPE, EQ etc)
ItemDescription


Then you need a WOItems junction table and/or a WOTypes that you have pre-stored items per work type?
 

GeordieH

New member
Local time
Today, 01:12
Joined
Mar 31, 2020
Messages
22
Hi Minty,

I'm sure a junction table is needed, but no matter which variation i put in, it fails. Rather than use a lookup, because there are numerous (100+) RAs and Equipment to chose from, I would rather use a table which we can look up but make multiple selections from that table and attach it to the WO. See the att and see if you can see where i'm coming from with relation to the Subforms. The att is what i'm aiming for in both the Form to input the info and then reproduce that in a report for printing.
 

Attachments

Minty

AWF VIP
Local time
Today, 01:12
Joined
Jul 26, 2013
Messages
7,174
I'm not convinced that's the best route to go, however, if you are absolutely sure there will never be a fourth "item"/thing/widget for a job then...
You need three junction tables;

WO_RAs , WO_PPEs, WO_Equips

And three subforms. You can maybe see why I am suggesting putting these all into one list.
If you add another "thing" you'll have to reorganise and redevelop everything to add the next table and subform.
 

GeordieH

New member
Local time
Today, 01:12
Joined
Mar 31, 2020
Messages
22
Hi Minty, I've added the extra tables and its working to a degree. I think its going to be a combination of getting the Qry right and then turning them into a form. Let me work on it and i'll come back to you tomorrow if that's ok, don't know if you're about!? Thanks for all the help today. I wouldn't mind showing you what I come up with later. Keep smiling :)
 

Attachments

Minty

AWF VIP
Local time
Today, 01:12
Joined
Jul 26, 2013
Messages
7,174
That looks about right. I should be about tomorrow.

I still think I'd have just had one table ;)
 

GeordieH

New member
Local time
Today, 01:12
Joined
Mar 31, 2020
Messages
22
Worked on this project last night. I created a query to incorporate the Vehicle to the WO Task, that worked fine and created an input form to present it. Its a standard layout I use to gain a hierarchy.
Then I created a query to link Vehicle to RA and the query worked, that is it populated the fields, great, But, it presented all the records in the RA Table!!?? I followed this up by creating a subform on the Vehicle form, but it wont acknowledge that Each WO Task will have many RAs, because the Form Wizard seems to only let you have 3 tables. Stumped. I'm trying a series of different querys now and see what it brings up. Its as if the hierarchy is one step behind?

I listened to your suggestion of using one table, that would work, but I have a list/table to hold a couple hundred RAs, same for Equipment, and the field will have to look that up, so even though that might work for a few items, i'm not sure it would be ideal in this one, (no offence :)) Still battling on... :) Challenge, just love a challenge :) :)
 

Minty

AWF VIP
Local time
Today, 01:12
Joined
Jul 26, 2013
Messages
7,174
You shouldn't need a query to drive the subforms, simply use the WO ID as the master/child subform links, Unless I'm missing something?
Perhaps upload a sample database - (Dummy data and enough records to demonstrate the problem / make it work)

You could easily use one table, and simply filter the required item combo box with a pre-filter (RA for instance).
 

GeordieH

New member
Local time
Today, 01:12
Joined
Mar 31, 2020
Messages
22
Have a look at this, I hope ive explained it ok. open the FrmJC, theres a note on there to read to try and explain where I want this to go.
 

Attachments

GeordieH

New member
Local time
Today, 01:12
Joined
Mar 31, 2020
Messages
22
Hi Minty,

Remember that each WO Task will have many RAs, PPE & Equipment :) :) That's the bit a cant pull through :)
 
Last edited:

Minty

AWF VIP
Local time
Today, 01:12
Joined
Jul 26, 2013
Messages
7,174
Have a look - very rough and ready, you would need to change the text box controls to combo's with the appropriate fields to display the descriptions. But this should get you going. Look carefully at the subform child master links and it's current event
 

Attachments

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom