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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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).

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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)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 BrockCIOCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.