Assigning Parts to a Customers' "Workorder"

[Chrism2]

It's me again...

After getting (with a lot of help from ansentry) my database doing exactly what I wanted. I realised I wanted more... by my tiny mind can't see how to approach it - despite on the face of it looking like the simplest thing to do.

So here goes:

I have a table of customers. I also have a table of jobs, or workorders assigned to each customer. Each customer can have multiple jobs added at any time. All this works perfectly.

Now I want to assign parts (i.e. cables, tools, software) to each job. So each Job (sorted by WorkorderID) can have multiple parts added to it.

I'm using control tabs as well and would prefer it if I could use some kind of button on the Customers' Job summary view to "Add a Part".

Here's a link to my previous questions, complete with pictures....

http://www.access-programmers.co.uk/forums/showthread.php?t=108706

Ideally, it would be great if the Job Summary view I have made could also have a column saying how many parts have been used in the job. (This is very much a nice-to-have!)

I tried a search on the forums for clues, but I reckon I'm either not searching the right way or this is so easy no one else has asked it. Apologies in either case. Any help would be great...

 

[The_Doc_Man]

To assign parts to a job you need a table of assigned parts that includes the job number and the part number. This is an example of a junction table.

It has a foreign key to the Job table (Job Number) and a foreign key to the Parts table (Part number).

Once you have a table, you can open it on your form.

The only issue is whether you drive the main part of the form from the job table or the customer table. Of course, you realize that you have an issue with linking jobs to customers. Well, the same issue applies in concept when linking parts to jobs. It's like a parent, child, grandchild relationship.

 

[Chrism2]

Okay... I'm with you... kind of.

I think I've got the idea - but putting it into practice is the tough bit. If you could maybe push me a little more in the right direction!

I've created the Junction table... (I take it it is just a table and not some kind of query).

The fields are as follows:

WorkorderID (The autonumber of a specific job given to a specific customer. Taken from the "Jobs" table)
DateFinished (The date the job was completed. Also taken from the "Jobs" table. This Should automatically complete itself IF there is an entry in the relevant field in the "Jobs" table).
PartID (The autonumber assigned to a specific part. Many parts can be used in any job. This value is taken from the "Parts" table)
PartName: (A short description that can be chosen by slecting a part from an existing drop down combo box, taken from the "Parts" table. This is the ONLY field I want people to be able to enter unless I can put in a "Quantity" field).
PartCost and PartPrice: (From the parts "table", once the Part has been chosen from the drop down box, these need to fill themselves in.

and ideally, PartsTotalPrice (A sum of the PartsPrice field multiplied by the Quantity).

Where to now?

I'm understanding relationships better; but I don't know how you get these fields (except the two that require user input) to fill themselves in.

Your help as always is VERY much appreciated!

 

[The_Doc_Man]

I'm going to put on the "theory" hat for a moment.

Normalization is a way to "purify" your data, minimizing its space and isolating in on conceptual boundaries. (If you know this already, bear with me anyway, I'm going somewhere with it.) This isolation protects data from interactions that might otherwise destroy it. It is the whole point of the "all fields depend only on the prime key" rule of normalization, also called the "don't mix apples and oranges" rule.

However, data interactions are part of the real world. Case in point, customers buy things. If you wish to track this fact, you need an "impure" table that references customers and the things they can buy, clearly an apples and oranges mix. An invoice table shows that a customer made a purchase, and an invoice details (child) table shows the parts that the customer bought on that invoice. Here the relationship is

Parts <== Invoice Details ==> Invoice ==> Customer

Invoice Details is the junction between an invoice (which implies a customer) and the parts bought by the customer. It is a list of parts (and perhaps quantities of those parts) that were obtained through that invoice, which leads you back to the customer. Now in your case, you might refer to a WORK ORDER instead of an invoice, but the concept isn't that far off.

The only issue you would have to consider is that your work orders might contain labor hours. But if you think about it, you have a resource called an Employee for which the hours in the day can be allocated to a work order just like any other part could be allocated to a work order. After all, they have definite quantity and a definite cost. Overhead (profit, expenses) applies to labor similarly to the way it applies to physical parts. So you would have ANOTHER relationship on your work order or invoice that looks like

Employee <== Hours Billed ==> Invoice ==> Customer.

Again, if more than one person works the work order, you have multiple entries in the Hourse Billed table.

Now how do you combine this sort of thing? Use a UNION table where you have the fields in the UNION's parts "cast" the disparate entities into things you can use for billing computations.

Now, about pricing: Again, you have Parts. If (when) prices change, you need to track part prices on a given date because an invoice from January and another from June very well could have different prices even the list of purchased parts was identical. Note that the PartPrice table is just this:

Parts ==> Part Price <== Calendar

So the price of an item depends on the date and the part number. This lets you have prices that change independently. You can then look for the price by finding the price with the maximum date less than the date of the invoice. This concept works for billable hours, too, since your employee's bill rates can be treated as a price-oriented item. Again, calendar-dependent.

What you will find when trying to build one of these apps is that before you know it, you have so many relationships and threads and JOIN queries that you lose track of the forest because of the number of trees. This is where organization and keeping good records becomes not merely a good idea but an essential part of your design process. Patience and methodical, consistent work are the keys to such projects.

On the practical side, sometimes you find yourself going nuts with all the elements you have to make come together. Just remember a couple of simple rules. You build a new table when what you want isn't in the database yet. That is because a database cannot tell you what you didn't tell it first. You build a new query when what you want is there and you just have to find it. But both tables and SELECT queries (plus their variants such as UNION queries) have only one purpose: To provide a stream of records. So anywhere you could have used a table, a query works pretty well. Including a JOIN of a table to a query so that in effect you have multiple JOINS in a single query forming a multi-table recordset. This will let you build pretty reports and such.

OK, having said all this, how do you make it work?

The only person who can tell you with any certainty how to make your application work is YOU. Because you know the ins and outs of what you wanted do to. We cannot. So think about the relationships and entities of your business. Go through a design analysis to verify purity according to the rules of normalization. For Access/any version - you want 3rd normal form as a minimum. Higher normalization (4th, 5th, and special) is possible,

 

[ansentry]

Chrism2,

Have a look the attached sample it may give you some ideas;

This sample "saves" the price of the part so that if you have a price change at a later date it does not effect the old invoice.

I does not have any labour content.

 

[Chrism2]

I Tried!

I tried... and tried... and tried some more.

I can see how it works from your example; have drawn out everything in a plan; but when it comes to the code, It just doesn't go.

All I want to do is have a form that allows you to add parts to a job. It sounds so damn easy to do! Ansenty, your example makes it look simple, and the "InvoiceDetails" subform is more or less exactly what I need. But I'm just getting nowhere.

I know it's not the most conventional database ever made (I've tried my best) but if someone could possibly take a peek at this and help me out; you'd be saving me so much sweat and tears.

Thankyou so much for your time and efforts to help.

 

[ansentry]

I can't view your db as I don't have WinRar on my computer (only winzip)

 

[workmad3]

Hmm... I quite like your view of normailisation, The_Doc_Man I always saw normalisation as the process of ensuring that your data has as little redundancy as possible, and that updates will be correct, no matter what (kind of goes with minimum redundancy)

Also, with any relational DB, you should have a minimum of 3rd normal form, unless you have VERY convincing reasons as to why it should be lower (sometimes in very high performance apps 2NF, or occasionally 1NF are more suited because they are faster). Once you get beyond 3NF, things can go a bit silly with tables (for example, Boyce Codd Normal Form says that you need to have to be in 3NF, and you can't have any possible candidate keys in the table.... it generally ends up with each table having 1 or 2 fields after the PK)

 

[Chrism2]

I can't view your db as I don't have WinRar on my computer (only winzip)

Apologies... I didn't have WinZip. I do now!

 

[ansentry]

Sorry still can't open it, make sure that you make new winzip file and don't try and winzip the winrar file.

 

[Chrism2]

Sorry - I've been having trouble getting the filesize right...

Then I found the "Compact and Repair Database tool".

D'oh.

 

[The_Doc_Man]

workmad3, you aren't wrong either. Normalization eliminates redundancy. It also enables you to isolate your data. Which is the horse and which is the cart? Damfino! (Pronounce it...)

In either case, the idea is that with "purified" tables, you use the junction table or child tables as the real workhorses of your DB. It becomes incredibly rare to change a "pure" table unless you are adding something/someone new. Like, hiring a new employee, adding a new product to sell, gaining a customer, finding a new supplier. Everything else occurs in the child or junction tables. And of course you never remove anything from a pure table because that way you never lose track of whatever was there. On the other hand, archiving & removing old entries in child/junction tables... no biggie.

 

[ansentry]

Here is your DB Back after Changes.

I have made a few manly I have changed your relationships and have take "lookup" out of your tables.

I have put the subform in for your partsUsed (or you could change the name to partsissued) I think this is what you wanted.

I have also put in a requery behind the save button and have changed the code that will save the data. (Job Entry).

I have used FK at the end of the name for foreign key so that you can identify them.

Good Luck.

 

[Chrism2]

Thankyou!

That's exactly it. Although I'm not sure if it is me, but there is an error when I try to enter a new job using the job entry sheet.

(Something about it not containing an automation object and then something required in Customers)

 

[ansentry]

Sorry about the problem, some idiot (that would be me) changed the key from CustomerID to CustomerFK for foreign keys...........but..........I forgot to change it in the Link Master and Child in the Job Entry subform.

 

[Chrism2]

That is brilliant. Thankyou so, so much.

One more question. :-S Sorry!

In my previous misguided efforts, I removed the report "Jobs" (I see you put one in). When I "re-attached" my one, the database prompts for several Parameter values. These were namely the Engineer ID, the Job Type ID and Customer ID. I found and corrected the first two, but can't seem to see where either my "Print Selected Job command" or "Jobs" report makes reference to "Customer ID". So, it keeps prompting for it.

Not a big problem, more of a niggle. Any ideas where I can look?

 

[ansentry]

I removed from your database the Customer Name from the Jobs table. If you have the CustomerFK in the (which you should) then why would you need the customer name.

In you data base change the name of the query PrintJobReportQuery to PrintJobReportQuery_old and then import my query called PrintJobReportQuery into you database. When you have done that base you report on my query.

Are you now using the one I posted or are you importing mine into your original.

Open my and have a look at the relationships you will see that I have changed them quite a lot. Note the "FK: on the foreign keys.

I have attached a very small sample to explain why you do not need data in 2 tables.

If you can fix the problem let me know and I will give you an email address (via PM) that you can send it to me and I will have a look.