Hi,
Just to give some background - I have 3 tables.
Each of these tables refer to a process of a serial number moving throughout a business.
Tables comprise of, "Out" which is an item going to a supplier. "Returned" which is coming back, and "final" which is to the customer.
Now, i link these all together using serial, and include all from first table and linking to the other two.
That means that every transaction has the 'going out' and when matching, what goes on next.
Now the problem is, if the same serial gets processed as 'going out' once again. This will start linking previous date orders and duplicating the results.
If it was a case of twice, it could just use an iif and display only where dates are greater than previous, but if a third happens i can't figure out a solution.
Someone has suggested to create two extra tables combining this information -
One 'archive' where items have 'finished' and not referencing to that agin
and another 'live' which will have items not yet returned.
Which makes sense, but I'm not sure how i would make that switch, as i assume i'll need to run 'first' iterations of everything with results, but then delete that same data from the source tables?
Either way, i'm a bit stumped.
If anyone is able to help at all, i'd be supremely grateful.
Thanks!