thanks for all your help and my apologies for my late replies - convalescing and will be getting back very soon.
got a quick question in the mean time - i know how to do this but i don't know if it's the most elegant way - i think there must be a better way.
i've attached my sample data base and basically i've got a row of data and amongst its many fields, there is a credit field and there is a debit field.
for one particular record, the credit and debit field has values - e.g. credit 50, debit 100.
i want to split this row into 2 rows - with exactly the same data, just that:
1) the credit row will have all the data and the credit row's value WITH the debit row as null,
2) the debit row will have all the data and the debit row's value WITH the credit row as null
the simplest way is to just use 2 queries with an Is Not Null criteria in the credit and the debit field and then use a union query to join the results.
however is there a way i can use a join or exists or something like that to achieve this result?
seems a bit kludgy to use 2 queries PLUS a union query - that's 3 queries in total.