asked on

using query to split a record into 2 records

hi guys,

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.

thanks guys!
TestDB.mdb

Rey Obrero (Capricorn1)

try this query

Select T.* From
(
SELECT Source.TF_Data1, Source.TF_Data2, Source.TF_Credit,0 As TF_Debit
FROM Source
WHERE Source.TF_Credit>0
Union All
SELECT Source.TF_Data1, Source.TF_Data2, 0 as TF_Credit, TF_Debit
FROM Source
WHERE Source.TF_Debit>0
) As T

Gustav Brock

You can use a Cartesian query:

SELECT
Voucher,
Amount,
IIf([Flag],[Amount],Null) AS Debet,
IIf(Not [Flag],[Amount],Null) AS Credit
FROM
tblVoucher,
(SELECT Distinct [Flags]=0 AS Flag FROM MsysObjects) AS tblFlag;

Replace, of course, table and field names with those of yours (except for MsysObjects).

/gustav

ASKER CERTIFIED SOLUTION

IrogSinta

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

developingprogrammer

ASKER

hi capricorn1, thanks for your help looking at this! = )

i was thinking along your trend of thought using a union all query - however, instead of including the outer select * portion, couldn't i just use the below? what is the value of adding the SELECT T.*? (unless of course i'm using it as a subquery)

SELECT Source.TF_Data1, Source.TF_Data2, Source.TF_Credit,0 As TF_Debit
FROM Source
WHERE Source.TF_Credit>0
Union All
SELECT Source.TF_Data1, Source.TF_Data2,  0 as TF_Credit,  TF_Debit
FROM Source
WHERE Source.TF_Debit>0

Open in new window

hi gustav and Ron! thanks your great solution! i'm quite intrigued by it actually - cause i don't know what flagged means ha. could you share what flags mean from msysobjects? i tried searching for what it means and so far i got it's a bitwise field but other than that i'm not too sure. once i understand what flags mean then i will be able to understand the cartesian query.

so guys the question(s) is:
1) what does flags mean
2) how did yall stumble upon this flag field even and learn about this?

msysobjects is a hidden table and i'm just very surprised that yall seem to know "everything" even about hidden objects! i'm trying to see if there is an avenue in terms of thinking that i'm missing that is causing me to miss out on coming across this technique that yall are using = )

thanks guys for everything!

developingprogrammer

ASKER

and guys, which is faster in terms of:
1) query performance
2) network performance

by network performance i mean that the BE is on the network and so in order to do the cartesian query or even search for where criteria the field (in the case of a where criteria) must be transferred from the network BE to the local FE. so i'm wondering if the cartesian query results in more data being transferred or the same as the union query using the where field.

thanks guys!

SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

developingprogrammer

ASKER

thanks so much guys, i really appreciate it