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!
Who is Participating?
IrogSintaConnect With a Mentor Commented:
gustav's idea of using a Cartesian query is an excellent one!  It just needs a little tweak to get to your desired result.  Here's a revision:

  IIf([Flag],[TF_Credit],IIf([TF_Credit]>0 And [TF_Debit]>0,0,[TF_Credit])) AS Credit,
  IIf(Not [Flag],[TF_Debit],IIf([TF_Credit]>0 And [TF_Debit]>0,0,[TF_Debit])) AS Debit
  (SELECT Distinct [Flags]=0 AS Flag FROM MsysObjects)  AS tblFlag;


Edit... Cap1's Union query works just as well of course.  The Cartesian query has the advantage in that you can edit the query via QBE (for those that prefer working with that).  That being said, both solutions are good to learn.
Rey Obrero (Capricorn1)Commented:
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 BrockCIOCommented:
You can use a Cartesian query:

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

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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

developingprogrammerAuthor Commented:
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!
developingprogrammerAuthor Commented:
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!
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
<what is the value of adding the SELECT T.*?>

i just included the SELECT T.* to the union query, so that if you need to create a new table you can simply convert the Select query into a make table query.

as for the performance, you can test both queries and time it using a simple vba codes

dim st as date, et as date

' run query here


debug.print datediff("s",st,et)
Gustav BrockConnect With a Mentor CIOCommented:
Thanks Ron. I didn't study the download, so my example was rather a skeleton than a ready solution.

> what does flags mean

Nothing. It's just a field where you have something that can be evaluated as true or false. It could be any table from which you can construct a recordset with two and only two rows. However, in case you have none, MsysObjects is always there.

developingprogrammerAuthor Commented:
thanks so much guys, i really appreciate it
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.