User Flow:
- Order # , Order Item and Order Date is recorded by Operator 1
- QC is performed on the Ordered item by Operator 2.
—> If QC Check passes, select order number, record transaction and send to Pack and Ship.
—> If QC Check fails, select order number, record transaction and send order back to warehouse for repair.
—> After repair, perform QC check again on the ordered item, select order number. record transaction,
- Ship date is recorded for each Order number by Operator 3.
I have three tables and am following a traditional Relational Database logic:
- ORDERS , which basically just has an order number and an Order date (along with some other order details).
- QC CHECK, which is linked to ORDERS table, and where I store PASS or FAIL statuses as well as date of QC check linked to ORDER NUMBER field from ORDERS
- SHIPPING, which is linked to ORDERS, and where I store ship date for every order after it has gone through QC.
I have linked all tables correctly, all works well, etc.etc.
Where I am stuck completley, is that I want to be able to somehow automatically generate what is called a VIEW in Relational Database language (or virtual table) that JOINS the records in all three tables using ORDER number (which is correctly linked in every table).
I’ve spent 4 hours trying to figure it out, doing more lookups, rollups , etc., but the thing is, I want the VIEW (Virtual table) to update automatically so that I can then use that view to have a consolidated view of each order , when it was placed, how many QC checks it went through, and when it was shipped.
