Solved

How to transpose records results in a Query??

Posted on 2002-05-22
15
189 Views
Last Modified: 2011-09-20
I am working with D6Ent, Paradox and the builtin QuickReport for the reports.
I got 2 tables, one with master records, an the seconds with details records. That is,

Table1            Table2
=====               =====            
A                   A1
                    A2
                    A3
B                   B1
                    B2
                    B3

I need to get this report.

A        A1           A2         A3
B        B1           B2         B3
====================================
SUM      A1+B1       A2+B2      A3+B3

Note: records on Table2 are fixed to 3 for each row in Table1, just for the example. Really they are dinamic, and you can set in a config table in which column of the report you list each value.

The only result that i get till now is a query which result is something like this:
A A1
A A2
A A3
B B1
B B2
B B3

So i think that if i can transpose each grouped record i can get something like I need (????)
Thanks you all...
0
Comment
Question by:spk2000ar
  • 6
  • 3
  • 3
  • +2
15 Comments
 
LVL 4

Expert Comment

by:YodaMage
Comment Utility
are you looking for something like:

select t1.field1, t2.field1, t2.field2, t2.field3
 from table1 t1, table2 t2
 where (t1.field1 = t2.{condition for relation})

*OR*

SELECT table1.field1, table2.field1, table2.field2,
  table2.field3
FROM table2
   INNER JOIN table1 ON (table2.field1{or condition for
   relation = table1.field1)
0
 
LVL 4

Author Comment

by:spk2000ar
Comment Utility
No, in your example field1..field3 are in the same record... but there are 3 RECORDs in table 2 that match the conditions in my example, i.e., A1, A2, and A3 are records on table2, not fields...
0
 
LVL 4

Expert Comment

by:YodaMage
Comment Utility
then

SELECT table1.field1, table2.field1
FROM table1
  INNER JOIN table2 ON (table1.field1 = table2.field1)
0
 
LVL 9

Expert Comment

by:ITugay
Comment Utility
Hi spk2000ar,

you are looking for technique named "CROSSTAB". You have to build crosstab query. It mean that every column in report is row from the table.
In this case, you have report with various numer of columns. QR doesn't allow to clone columns on demand. So, you have to build report dynamically. At first, you have to prepare your data in memory and then show it in QR. Easy way would be to use TQRMemo. In this case you do not need create QRLables in runtime.

--------
Igor.
0
 
LVL 14

Expert Comment

by:DragonSlayer
Comment Utility
Umm... perhaps the Decision Cube component might help? :)
0
 
LVL 4

Author Comment

by:spk2000ar
Comment Utility
YodaMage,
your second answer let me get the example i wrote on my initial question:
A A1
A A2
A A3
B B1
B B2
B B3
and you can group it, but then the QReport i get is something like this

GroupBand     A
DetailBand        A1
DetailBand        A2
DetailBand        A3
GroupBand     B
DetailBand        B1
DetailBand        B2
DetailBand        B3

and that not what i need... that is something like this
A        A1           A2         A3
B        B1           B2         B3
====================================
SUMMARY  A1+B1     A2+B2     A3+B3

Itugay,
perhaps thats the solution, but i don't get the idea. A quick example of a crosstab query may help.Think on fixed columns in the report, they are not dinamically created.

DragonSlayer,
i think on that, but i don´t understand the correct functionality of the DecisionCube. Can you explain it more???

thanks to you all!!!!!!!!!

0
 
LVL 4

Author Comment

by:spk2000ar
Comment Utility
Listening...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 14

Expert Comment

by:DragonSlayer
Comment Utility
Maybe you can check this out

http://www.delphizine.com/features/1997/11/di199711cj_f/di199711cj_f.asp

You have to register to log in, but I think you can try to sign up for their 1-month free trial.
0
 
LVL 9

Expert Comment

by:ITugay
Comment Utility


>>Think on fixed columns in the report, they are not dinamically created.

So it should be easy. Can you prepare result in matrix? If so, then place few TQRLabels on report and use OnPrint event to show prepared in matrix data. Let me know if you still need sample.

-------
Igor.
0
 
LVL 4

Author Comment

by:spk2000ar
Comment Utility
ITugay, i think in that matrix as a temporal table, which records are appended programmatically by the use of subsequent querys, thats because the number of record to be listed in the columns are not fixed, and can happened something like this:
A        A1           A2+A3
B        B1            B2          B3
C        C1            C2          
====================================
SUMMARY  A1+B1+C1    A2+A3+B2+C2   B3

But i get a great overhead on generating the data i needed.
If can be other solution, something more direct, it would be better...
0
 
LVL 4

Accepted Solution

by:
YodaMage earned 160 total points
Comment Utility
I don't use Quick Reports, so tough for me to say, but you need a cross tab report as ITugay said. He stated that there is no cros tab functionality to QR, so you should essentially use a memo field, add your data and arrange as needed using straight text manipulation, until you have the layout you want in the memo, then run your memo out to QR.

I have one similar report in an application, where I use a temp table which is created dynamically with needed columns, values are then calculated and populated so that I essentially have data in format required, then report (RB in my case), is printed, with pplabel.captions assigned at runtime. (And visible property adjusted as needed to remove access captions). The downside is you do have to have a 'Maximum' number of columns with this technique. You could go fully dynamic if you wanted to create and place labels dynamically.
0
 
LVL 4

Author Comment

by:spk2000ar
Comment Utility
I think thats all, the best way instead of the memo was the initial temp table.
Do you know if i can accept two comments as answer... or hoy can i slipt the points...
Because i believe that Itugay made gives the link, and YodaMage make the most effort.
Please, how can i do that???????
0
 
LVL 9

Expert Comment

by:ITugay
Comment Utility
hi,

you can ask in "community support" topic area to split points. Do not forget to provide link to this Q.

------
Igor.
0
 
LVL 1

Expert Comment

by:Computer101
Comment Utility
Points reduced for split.  Now you can accept an experts comment as an answer.  After that, make another question for the other expert in this topic area.

Computer101
E-E Moderator
0
 
LVL 4

Author Comment

by:spk2000ar
Comment Utility
ITugay, please comment the follow question to take your points!!

http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=delphi&qid=20304983

Thanks!!
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now