Understanding of multiple orders within one order

[MattCass87]

Good afternoon all,

I get the impression my understanding of the below is fundamentally flawed and i'd appreciate any guidance to help me see how this should work.

My ordering system needs to raise a job number for an entire project. During this project several orders may be made all with different products and at different times, so I need a order number and then orders within that order that contain a list of components.

Can anyone advise what relationships I need to complete the below:



1. Raise an order (e.g. order 1001)

That order is now raised however within that order over the next month I may make several orders within that order which would all be invoiced at the end. So I could have Order 1001 with part 1001-001, 1001-002 all with different products inside.

2. Create a part number for that order (e.g. Order 1001, part 001)

3. Add products to the part numbers e.g.

Order 1001, part 001, component 001,2,3,4,5,6,7,8,9
Order 1001, part 002, component 010,11,12,13,14,15,16
Order 1001, part 003, component 017,18,19,20,21,22,23

Am i looking at this completely wrong? when I've built the tables and am looking at the records I cant get my head around how access would know to raise part numbers within specified orders, same goes for products.

Thanks

Matt

[orange]

Describe your set up in simple, clear English.
It seems that you have Projects, and
each project can have 1 or more Jobs, and
each Job can have 1 or more Orders, and
each Order can be for 1 or more Products.

Is that correct? If not adjust as necessary.
Do you have other things involved?
People work on Projects, or people Order Products, or Projects are in different Locations,.....

[MattCass87]

Hi Orange,

Apologies, yes, thank you for clarifying what i'm trying to say

I have projects, each project can have 1 or more jobs and each job can have 1 or more orders and each order can be for 1 or more products.

Im designing a print house database, so a project is started, a job is raised, and then I start producing that job in various orders. the orders are print products that are a combination of materials. e.g. paper type 1 and laminate type 1 with quantities and dimensions.

[ArviLaanemets]

The simplest will be, that you differ clients purchase order (client orders from you some products), manufacturing orders used in your firm to organize the production, and sales invoices you send to client (depending your and clients trust level together with products, or before you deliver products). In last 2, you'll have a field where you estabilish the connection with client order (p.e. ClientOrderNo).

In our ERP system p.e. the production orders are created on workstation and operation base: every operation has separate production order, where article, workstation, operation on this article, the quantity of processed articles and production time are determined along with client order.

[khayward]

It sounds to me as though changing the names of things will help you here:

Projects = Sales Orders

Jobs = Work Orders

Products = Well, Products

Components = I would change this to parts, but then I'm in manufacturing

What I would have here, structurally, is a master table of Parts connected to a master table of Products via a junction table to enable a many to many relationship and then repeat this pattern up the line for Products to Work Orders and Work Orders to Sales orders would just be a direct one-to-many. I would personally simply have Work Order number be a reference rather than having an additional table but that depends on the business environment. You can directly connect Products to Sales Orders and have a Work Order Number or Job Number as a reference to get back to the paper form or an indexed field to search by, but once the data from the paper form, if you're even using one, has been entered the job number sorta becomes irrelevant.

This is just my opinion, I'm far from an expert, but I work in the job shop environment so hopefully what I've written makes some sense.

Good afternoon all,

I get the impression my understanding of the below is fundamentally flawed and i'd appreciate any guidance to help me see how this should work.

My ordering system needs to raise a job number for an entire project. During this project several orders may be made all with different products and at different times, so I need a order number and then orders within that order that contain a list of components.

Can anyone advise what relationships I need to complete the below:

1. Raise an order (e.g. order 1001)

That order is now raised however within that order over the next month I may make several orders within that order which would all be invoiced at the end. So I could have Order 1001 with part 1001-001, 1001-002 all with different products inside.

2. Create a part number for that order (e.g. Order 1001, part 001)

3. Add products to the part numbers e.g.

Order 1001, part 001, component 001,2,3,4,5,6,7,8,9
Order 1001, part 002, component 010,11,12,13,14,15,16
Order 1001, part 003, component 017,18,19,20,21,22,23

Am i looking at this completely wrong? when I've built the tables and am looking at the records I cant get my head around how access would know to raise part numbers within specified orders, same goes for products.

Thanks

Matt

[orange]

@khayward

Just wanted to highlight that you are responding to a post that is about 10 months old.
I just checked and Matt (the OP) has not been back since Sept 2017.

[khayward]

@khayward

Just wanted to highlight that you are responding to a post that is about 10 months old.
I just checked and Matt (the OP) has not been back since Sept 2017.

Thanks for pointing that out, I didn't even look at the OP date. Well, hopefully it's useful to someone.