Purchase Order Relationship (1 Viewer)

[silentwolf]

hmm ok I understand Pat.

But I guess in that case it is to much anyway.
I guess I just have the junktion table and then work from there as suggested above.

So you would work with a Structure like this below the SupIDRef ist red crossed out so I will delete it after.
Would you then Link ProductSupplier to PurchaseOrders ??

I really need to sort this out and turning always in the wrong direction...

Help would be much appreciated.

Cheers

 

[silentwolf]

Sorry to be a pain guys,

just updated the Relationship and was hoping to get some feetback.
At present I have Products and SupplierProducts nearly the same just for the sake of getting all Data into the Database.

Marked as red is what I believe could be deleted.

Would that be ok like this? And what should be added in order to be able to create Purchase Orders and Sel Price and PurchasePrice?

I did not quite understand how Pat ment it.

Sorry

 

[Pat Hartman]

The link to the manufacturer has to come from the supplier table NOT from the product table. I know when you're dealing with laptops, the manufacturer would be the same for the laptop regardless of who supplied it to you. However, if you are dealing with lower level items, CDs for example can be manufactured by any company. So, regardless of the type of product you are dealing with, the structure needs to be flexible. You don't want to have to modify the application after the fact to correct this type of error. Sometimes, you need to look outside the box when you are designing a schema. It never hurts to build in real world situations even if today, there are no low level items being sold. Think of this as defensive programming. It will save your bacon and make you look like a hero if you can say "no problem" to certain types of changes.

However, I don't understand what the red square is all about.

 

[silentwolf]

Hi again,

sorry for the late responds.. Had to sit back and think about the structure a bit more.

I changed Products to Parts which is more appropiate as we receive Prices from Suppliers for SpareParts.

Further more I have included ModelParts as a linke Table to Parts and Models

Each Model requiere Parts for Maintainence or Repair,
Parts could be used in different Models thats why the Linked Table ModelParts.

Also I did include PartGroup as a linked table to Group and Parts.
Again the Parts could belong to different groups.
For example a part could be a "Clamp for certain adapter"
So the Part would belong in Group "Clamps" and "Adapter"

Attached is the updated version of my Structure.

Maybe and it would be much appreciated if Pat or someone else take a look at it the naming convention should be also
match your liking

Purchase Orders are left out for now as I am wondering if the current Structure would be ok now or do I need to change something?

Cheers

 

[Pat Hartman]

The schema is still not correct. You have two separate paths to traverse to get to Parts and that won't work.

Separate what you purchase from what you use/sell. Is every part serialized? meaning when you use something, it is one specific instance of a "part"? Is it assigned up front or is it recorded when it is picked. I want to use/buy a 1T hard drive. I don't care who made it or what its serial number is. However, when it is picked, I want to record the serial number and that links back to the supplier and ultimately the manufacturer.

Try to write out some Use Cases to help with working out the logic and separating what you purchase from what you use/sell. Supplier is where price is assigned. You don't track price by manufacturer. You track price by who you buy from and unless you buy directly from the manufacturer (in which case, he is acting as a supplier) you may not even know their price. Sales price is also tracked at the supplier level. You either automatically calculate a markup or you enter the sale price specifically.

Discount amount would not be associated with a supplier unless you are talking about YOUR discount. Otherwise discounts related to what you sell would be associated with the customer or a quantity. If you buy 10, we charge you for 9 (which is associated with an order). Or, customers who are legal charities get a 5% discount.

We are only seeing some of the tables in the database so please tell us what part of the process we are looking at.

 

[silentwolf]

Hi,
thanks Pat but this drives me nuts this model..

Not sure how many times I tried in different ways of getting it right but still no luck.
Drives me nuts.

1. The Manufacturer is also the supplier!
2. Parts are for Maintainence Work, Parts come form Manufacturer/Supplier
3. Parts are needet to complete the Maintainence work.
4. Parts can be used on one or more Models
5. A model can have different Types "Model A" with Type of "Round", "Half Round", "6 Corners",....
6. Models have functions " "Model A" has "Whirl function", "Sound System", "Steam function",...
7. Models have issues .. "Steam function defect" need repair, or replacement or cleaning or new Parts in order to fix it.
8. We need to find easily the parts for each Order/Project so we can create an Offer for the Customer or to place an Invoice
9. Models could also be sold to a customer.
10. Find Models and prices so we can sell it with a markup
11. Find Parts or Models easily.. to place an Order or an Offer or a Delivery
12. Purchase Models or Parts
13. Parts can be used in more then just one Model!
14. Find all Parts according to what Model needs Maintainence

Discount amount would not be associated with a supplier unless you are talking about YOUR discount.

Yes it is our Discount to the Suppliers.
Each Supplier could have a different Discount rate.
Or change the Discount rates.

Sales price is also tracked at the supplier level. You either automatically calculate a markup or you enter the sale price specifically.

I created for that reason a Markup Table with Precentage Rates.
Table "MarkUp"

We are only seeing some of the tables in the database so please tell us what part of the process we are looking at.

Not exactly sure what you mean by that?
I thought it is clear with the table Names?
Project = Order we receive! I called it Project as it manly or mostly are Projects we are working on.
Project means we are working on Maintainance Work on Models at a particular Address.

Attached another try for the Structure.
it is not complete yet as I am very nervous of having it wrong again.

So is it so far ok and how would you tackle down the "Supplier Parts"?
And have that linked to the Purchase Orders or is that also wrong again?


Hope someone can help as I really need to get that finished.

Many thanks

Albert

 

[Pat Hartman]

Alright, your business repairs stuff using parts you can obtain ONLY from the manufacturer?? So, I take my Honda to the dealership and he fixes something, he only uses authorized parts but I don't think he is required to buy them from Honda. I could be wrong about that though.

You haven't actually defined what business process you are trying to automate. There are multiple options and maybe you need to do them all. Projects (which sounds like a service order) is probably the heart of the application. Forget about Customers, Invoices, PO's, and Inventory for the time being. We have a Service Order/Project and to fill it, we need parts and labor and ultimately tax. Everything else revolves around this section.

Think about Projects, Models, ModelParts, Parts, PartsGroups, and Groups. The Project has the ID of the model that is being repaired. That model links to a parts table that lists all the parts for a particular model. Models links to Manufacturer but that is tangential and has nothing to do with the Project. Parts links to Supplier which may or may not also be a manufacturer but also isn't relevant at this point. To facilitate searching for the part you need, parts links to a group or category table and parts can belong to multiple groups hence the PartsGroups junction table but remember, the search is also qualified by model so for any given group, you only see what is connected to the particular model you are servicing. Any given Part can be used on multiple models hence the ModelParts junction table.

When you get the request for service, you don't know what is wrong so you don't know what parts or labor is required for the repair. Now you need another path off of Projects that you can use to identify parts/labor needed to complete the repair. This will ultimately become the basis for the invoice. You shouldn't need a second table.

How is labor calculated? Is it a flat hourly rate regardless of the trade or do you charge different rates depending on whether you need a plumber or an electrician. Is the hours to repair associated with a particular part the way it is at the car dealer? Or is it however long it takes Joe to do it today?

Is there an existing application or several applications that you are combining? And based on #9, you also sell stuff??? Do you have a storefront or is this mail order? We don't have anywhere near enough information to help you to zero in on a rational data model.

 

[silentwolf]

Alright, your business repairs stuff using parts you can obtain ONLY from the manufacturer??

Yes more or less. Generic parts like nuts and Bolts or washers could be bought somewhere else to in a Warehouse or something like that. But not really relevant in that case.

You haven't actually defined what business process you are trying to automate.

Using your example of your Honda : You bought the Hoda from a dealer, after a while that poor Lady does not start anymore. You are call the Dealer and tell him about the Problem.

The dealer is to bussy to look at this or does not repair it. So he calles us to fix the poor Lady on the weekend.
The Manufacturer sends me an "Service Order" about what brand it is "Honda", what Type it is "CBR 900rr" the issue "Starter defect", tells me who the Customer is and where the "Customer Address is"

Also is a billing code to tell me who needs to pay for the costs of this repair.
If the "CBR 900rr" is still under waranty or factory waranty then I can send the bill directly to "Honda" if it is the dealers fault as he stuffed something up then the bill goes to "Honda Dealer Vienna", or if it is just something the customer had done then the bill goes to the "Customer"

Therefor I have a BillingCode table to let me register those type of "BillingCodes".

Also there are just private Customers calling and wanting a Service for their "Honda" .. so we go and do also that.

Models have different Types . Like a Car which has 5 doors or 2 doors or Caprio.. Still the same "Make" but not the same "Type".
So the parts are mostly the same but there wont be 5 seats in the 2 Door car.

This system needs to be able to fine "Already" repaird or maintaince work done in the past so we can make an Offer accordingly.
So sometimes we need to give the customer an Offer frirst before we can do the work.

Finding parts is essential to give the correct Offer. As there could be steam functions or or soundsystems or whirl functions and those parts can be expensive and nowadays those prices can change quickly.

Regarding the Prices of our work it is depanding on distance mostly "flat rate" for travel.
Work can be both in hourly rates or in flat rates depanding on what needs to be done.

Maintaince work is done once a year so we need to record when the next duration is when we need to contact the Customer for the yearly Maintainace.
Perhabs give him a new price as parts went up. Or the petrol prices are 4 Euros a Liter. So the prices need to be updated for a new Offer.

On site we like to give the Customer work what has been done. Like a delivery slip kind of.

Work has been done "Fixing bath tap as it was calcified or parts replaced or Parts fixed.

Once that is recordet I like to send a Invoice of what has been done.

Models as such is mainly not sold but if we record the prices it would not be a problem do do so in a Customer is require an Offer.

Models could be bought from other Suppliers not the Manufacturer it self.

Hope that is a bit more clear?

Attached another try of the Schema

Is there an existing application or several applications that you are combining?

No there is no existing system well a bought one but from that system I just got the customers out nothing else.
The system must handle all kind of business intelegent to work with models parts, Customers, billing, quotes and so on.

 

[Pat Hartman]

Hide all the lookup tables. They are not relevant to the discussion. And repost. Try to avoid crossing lines when possible.

At some point, you might appreciate this mini-app I add to all my projects. It manages simple lookups. Since Access limits relationships to 32 per table, I don't enforce RI to the lookups. But I always make queries and log them in the table for easy reference. That way, if you always use the correct query as the rowsource of the combo, the RowSource enforces RI sufficiently well.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

 

[silentwolf]

Hi thanks Pat,

I will look into it and also post without relevant tables.
Just in the middle of something but I will get back to you!
Cheers for your input and help"

 

[silentwolf]

Hi again,

sorry been away for work the past days.

Attached you can find the current relationships.

Can someone take a look at it for me please and tell me if that is ok so far or what needs to be changed?

Many thanks!