Jump to content

Basic relationship misunderstanding


Harry
 Share

This topic is 2867 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This topic is 2867 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.