Basic relationship misunderstanding

[Harry]

Hi Board!

 

Here's a problem I have, that i just don't understand from a Schema point of view, on a basic level.

 

I've tried to do this sort of thing before and failed, but now i really need to do it, so I've got to learn. Can you help me understand? What sort of relationship is this, that I need? How do i do it?

 

I have four tables:

 

A 'Months' table - summarises data by the month for display on an automatic Dashboard

A 'Shipping' table - logs products going into boxes being packed.

An 'Orders' table - order headers

An 'Orders_Item' table - the order lines for above.

 

The last three are giving me the aches.... (I'm only listing fields that are concerned with this issue)

 

<Months Table>

Year_Month

 

<Shipping Table>

Order_ID

Stock_Code

Time_Stamp

Year_Month_c

 

<Orders Table>

Order_ID

 

<Orders_Item Table>

Order_ID

Unit_Price

Stock_Code

 

 

Relationships:

 

Months:Year_Month (e.g. 201501) = Shipping:Year_Month_c

Shipping:Order_ID = Orders:Order_ID

Orders:Order_ID = Orders_Item:Order_ID

 

In Months table, I'd like a 'Total_Shipped_Price' field that gave me a Sum of all the 'Unit_Price's of each product that has been put into a box.

 

....This then shows on a graph on a dashboard that updates minute by minute, showing how much has been packed. (I have the dashboard showing forecast sales and actual entered sales per month. Now I want to show how much has been shipped out.)

 

Of course, because there is no 'Stock_Code' in 'Orders' If I relate as per above then I lose connection to the line by line, stock code by stock code details. I have my 'Shipping' table and I cannot get a 'Unit_Price' per record. That's what i'm after.

 

I can't figure it out and I know this is basic DB stuff.

 

Can you point me in the right direction? What should i be reading about?

 

Thanks!

[Harry]

It's like i need to take the Shipping:Stock_Code field with me through the 'join' table of Orders....

 

Don't get it!

[eos]

Please describe this a bit plainer:

 

On your Dashboard layout (based on the Months table) you want to see all line items (or summarized values for these line items; or both?) that match … what exactly, besides Year_Month?

 

And how/where does a Stock_Code come into play?

[Harry]

Yea, OK! Sorry...

 

Months is my SUM table.

 

I have a 'Works_Orders' table.

I have a 'Products' table.

I have a 'Jobs' table.

 

Joined like this:

 

Works_Orders::Stock_Code = Products:Stock_Code.

Products:Job_Group_ID = Jobs:Job_Group_ID

 

Products can share Job_Group_ID's so multiple Stock_Codes are made using the same set of Jobs (different colours, etc.)

 

Job Groups have multiple Departments in them, with records formatted as:

 

Job_Group_ID / Job_Number / Department / Time

 

What I want to do, is in my Summary table (Months) Is show Count for the number of Works_Orders and the Sum of Time of those Works_Orders, *Per Department*

 

I have the Count, which was easy. But I don't udnerstand how to specify the '...Per Department.' bit.

 

I have Globals in my Summary table that i use to establish relationships to these other tables, such as 'Start_Date_Of_Month' and 'End_Date_Of_Month' and also department names 'CUTTING' and 'INSPECTION'.

 

I can't link from my Summary  (Months) table through Works_Orders, through Products and into the Jobs table, how do i 'carry' the Department through to the Summary table?

 

I just don't get it....

 

Sorry for not being clear. I'm sure this is very simple if I can understand it....

Should I set Global fields of those Departments into the Products and Works_Order tables, so I can link each one to each one and then into the Summary table?