I'm hoping someone can help. I have an application with many tables which I've built my own relationship table to handle all the table joins.
I've now got an issue where there is an unintended relationship between two tables which is giving me an incorrect result.
I've attached a small simple test application that should describe the issue (I've also attached the Excel doc with the raw data if that helps on changing the load script).
The issue is as follows..
I have a table called 'XRef' which has a cross reference of Items to a customer item In the example attached, ItemID = 1 is currently referenced to customer item 'xa'. Now in the past, customer item 'xa' was referenced to ItemID = 2 and you'll see in the sales table it has a sale of item xa (XrefID = 1) to ItemID = 2, I do want to show this in sales s it was a sale of 'xa', so in my straight table the total sales quantity sold of 30 of Customer Item 'xa' is correct as I do want to view the entire history of sales of 'xa'.
The stock information should only point to the stock of the ItemID which is currently mapped to item 'xa' in the XRef table, hence as it's currently mapped to ItemID = 1, stock is 0 so it should show 0 stock, however it's picking up the stock onItemID = 2 too which is not what I want.
I've intentionally used a relationship table in order to force the joins to the data I require but there's still an unintentional join to ItemID = 2 via the Sales relationship.
My goal is a table showing the following:
I thought of using two seperate identical Item tables, however I use other fields in the Items table in my app as selection boxes e.g. Manufacturer, Barcode, Colour etc, if I have two item tables then a selection of Manufacturer from the original Item table will not filter by Manufcaturer in this example as it would be working off an independent Item table.
Ideally I want it to work off the current Item Table. If anyone has any ideas of how I can make it look at all sales, but only item data from it's current cross reference to an item I would be most grateful.
My thanks in advance