Self Join relationship to tally items ordered before a certain date

[nikmagnus]

Hi.

I have a database of orders, and I'm wanting to have a running tally of ordered items ordered before and including the date of the current order.

Eg. I insert the date onto the order form, then as I'm adding the items to the order portal (related to the order table by Order_ID) I get a tally of how many of those items have been ordered up to that date.

The reason: so that I can see which items are out of stock, as some orders are delayed by as much as a month, and mine is a first ordered - first sent service.

More details? I'll explain more if you need.

I'm trying a self join with a

 

THanks in advance

[AHunter3]

To be able to see in a portal all the ITEMS that have been ordered before the date of the current ORDER you would need the date ordered value to exist in the ITEMS table. If it currently only exists in the ORDERS table (i.e., Date Order Placed) you'll need to make a field in ITEMS that has an auto-enter option to enter the corresponding value from ORDERS at the time the line item rec is created.

 

Then you tie ORDERS to ITEMS in a new table occurrence, let's call it PREVIOUSLY ORDERED ITEMS, using ≥ to tie the current ORDERS::Date Order Placed record to PREVIOUSLY ORDERED ITEMS::DateOrderPlaced Lookup field.

[nikmagnus]

This is a great start. Thankyou so far for your input.

 

Let me more specific with the names of the tables and the relationships.

ORDER - contain contact, date of order received and a portal to LINO

LINO (line orders) - each record a tree variety, the number of trees and a lookup of price, calculates a line total. Related to ORDER by order_ID

FRUT - contains the fruittree variety, price, description and total stock. It also calculates total amount ordered by relating to LINO via the fruittree name and so generates a total ordered for that variety.

Other tables include a transaction table that operates similar to LINO, plus a main page, addresses, banking sheet - but I dont think they are relevant really.

 

From what I understand you are saying, I need to include the date of order in my LINO table.

Then you tie ORDERS to LINO in a new table occurrence, let's call it PREVIOUSLY ORDERED ITEMS, using ≥ to tie the current ORDER::order_received Order to PREVIOUSLY ORDERED ITEMS::order_received which is Lookup field.

Sorry I cant quite grasp this.

 

Currently my total ordered is generated from the FRUIT records by relating to LINO via tree_name (lino_FRUT__tree_name) using Sum ( LINO::tree_number). Where to after this? I was thinking a self-join with ≥ to the ORDER table using order_received would restrict to earlier orders, but I get confused when trying to tally up the total number of trees from LINO.

 

BTW, I really appreciate this

[nikmagnus]

The other thing that I do from the FRUT table is calculate the total stock of trees (from 3 different suppliers) resulting in total_stock. The total remaining is also calculated from here, ie: total_stock - total_trees_ordered. The latter is the sum(tree_name) from LINO.

Should I be doing these calculations from LINO?

 

Also, when I said:

Currently my total ordered is generated from the FRUIT records by relating to LINO via tree_name (lino_FRUT__tree_name) using Sum ( LINO::tree_number).

I actually meant (ord_lino_FRUT__tree_name) using Sum (ord_LINO_orderID::tree_number). I just checked this and I need the ord relationship as it gets displayed on the portal on the ORDER layout.

I'm kinda confusing myself a bit, now....

Thanks again! smiley-laughing