How to link items accross tables with duplicates/potentially more complicated

[Rjack]

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!

[Bulzie]

You can't use serial as the key to the records if it is not unique in the table. Do you have to keep a record history on when it changed from Out to Returned to Final? Sounds like maybe you need:

tblSerial (SerialNbr(PK), other fields)
tblSerialProcess(ProcessID(autonumber), SerialNbr(FK combobox from tblSerial) Status(Out, Returned, Final), StatusChangeDT(datetime)

You can then filter out records that have Final as status in your query. Also if you have to send out the item again, use a new record (select same Serial).

[NTC]

well you can't rely on just the serial# - you need a unique ID for the overall transaction (Out, Return, Final) that ties the 3 together uniquely.

having said that - I'll warn you that you're design is problematic in my opinion. Those 3 tables should be 1 table (the transaction table) and there should be Status field for: Out, Return, Final;

then you need look up tables for your products/serials, customers, suppliers.