billycl
asked on
2 Tables in Report (Report Builder, Rave, QReport or ARWordReport)
I have 2 tables
TABLE1
ID1 Name City
1 John London
2 Billy Atlanta
TABLE2
ID2 Product Quantity
1 Delphi 45
2 C++ 15
1 C# 11
1 Java 34
2 VS.Net 14
I want to view all data about all people. First personal data,
then detail data about sale (where ID1=ID2).
---------------
Name City
John London
Product Quantity
Delphi 45
C# 11
Java 34
---------------
Name City
John London
Product Quantity
C++ 15
VS.Net 14
---------------
...
i use Report Builder, Rave, QReport or ARWordReport.
TABLE1
ID1 Name City
1 John London
2 Billy Atlanta
TABLE2
ID2 Product Quantity
1 Delphi 45
2 C++ 15
1 C# 11
1 Java 34
2 VS.Net 14
I want to view all data about all people. First personal data,
then detail data about sale (where ID1=ID2).
---------------
Name City
John London
Product Quantity
Delphi 45
C# 11
Java 34
---------------
Name City
John London
Product Quantity
C++ 15
VS.Net 14
---------------
...
i use Report Builder, Rave, QReport or ARWordReport.
Something like this:
object Form1: TForm1
Left = 192
Top = 107
Width = 870
Height = 640
Caption = 'Form1'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
Scaled = False
PixelsPerInch = 96
TextHeight = 13
object QuickRep1: TQuickRep
Left = 48
Top = 24
Width = 816
Height = 1056
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
Functions.Strings = (
'PAGENUMBER'
'COLUMNNUMBER'
'REPORTTITLE')
Functions.DATA = (
'0'
'0'
'''''')
Options = [FirstPageHeader, LastPageFooter]
Page.Columns = 1
Page.Orientation = poPortrait
Page.PaperSize = Letter
Page.Values = (
127.000000000000000000
2794.000000000000000000
127.000000000000000000
2159.000000000000000000
127.000000000000000000
127.000000000000000000
0.000000000000000000)
PrinterSettings.Copies = 1
PrinterSettings.Duplex = False
PrinterSettings.FirstPage = 0
PrinterSettings.LastPage = 0
PrinterSettings.OutputBin = Auto
PrintIfEmpty = True
SnapToGrid = True
Units = MM
Zoom = 100
object TitleBand1: TQRBand
Left = 48
Top = 48
Width = 720
Height = 40
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
AlignToBottom = False
Color = clWhite
ForceNewColumn = False
ForceNewPage = False
Size.Values = (
105.833333333333300000
1905.000000000000000000)
BandType = rbTitle
end
object ColumnHeaderBand1: TQRBand
Left = 48
Top = 88
Width = 720
Height = 40
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
AlignToBottom = False
Color = clWhite
ForceNewColumn = False
ForceNewPage = False
Size.Values = (
105.833333333333300000
1905.000000000000000000)
BandType = rbColumnHeader
object QRLabel1: TQRLabel
Left = 80
Top = 24
Width = 33
Height = 17
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Size.Values = (
44.979166666666670000
211.666666666666700000
63.500000000000000000
87.312500000000000000)
Alignment = taLeftJustify
AlignToBand = False
AutoSize = True
AutoStretch = False
Caption = 'Name'
Color = clWhite
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = [fsBold]
ParentFont = False
Transparent = False
WordWrap = True
FontSize = 8
end
object QRLabel2: TQRLabel
Left = 180
Top = 24
Width = 23
Height = 17
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Size.Values = (
44.979166666666670000
476.250000000000000000
63.500000000000000000
60.854166666666670000)
Alignment = taLeftJustify
AlignToBand = False
AutoSize = True
AutoStretch = False
Caption = 'City'
Color = clWhite
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = [fsBold]
ParentFont = False
Transparent = False
WordWrap = True
FontSize = 8
end
object QRLabel3: TQRLabel
Left = 268
Top = 24
Width = 45
Height = 17
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Size.Values = (
44.979166666666670000
709.083333333333300000
63.500000000000000000
119.062500000000000000)
Alignment = taLeftJustify
AlignToBand = False
AutoSize = True
AutoStretch = False
Caption = 'Product'
Color = clWhite
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = [fsBold]
ParentFont = False
Transparent = False
WordWrap = True
FontSize = 8
end
object QRLabel4: TQRLabel
Left = 344
Top = 24
Width = 50
Height = 14
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Size.Values = (
37.041666666666670000
910.166666666666700000
63.500000000000000000
132.291666666666700000)
Alignment = taLeftJustify
AlignToBand = False
AutoSize = True
AutoStretch = False
Caption = 'Quantity'
Color = clWhite
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = [fsBold]
ParentFont = False
Transparent = False
WordWrap = True
FontSize = 8
end
end
object DetailBand1: TQRBand
Left = 48
Top = 128
Width = 720
Height = 17
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
AlignToBottom = False
Color = clWhite
ForceNewColumn = False
ForceNewPage = False
Size.Values = (
44.979166666666670000
1905.000000000000000000)
BandType = rbDetail
object QRDBText1: TQRDBText
Left = 80
Top = 0
Width = 57
Height = 17
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Size.Values = (
44.979166666666670000
211.666666666666700000
0.000000000000000000
150.812500000000000000)
Alignment = taLeftJustify
AlignToBand = False
AutoSize = True
AutoStretch = False
Color = clWhite
Transparent = False
WordWrap = True
FontSize = 8
end
object QRDBText2: TQRDBText
Left = 180
Top = 0
Width = 57
Height = 17
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Size.Values = (
44.979166666666670000
476.250000000000000000
0.000000000000000000
150.812500000000000000)
Alignment = taLeftJustify
AlignToBand = False
AutoSize = True
AutoStretch = False
Color = clWhite
Transparent = False
WordWrap = True
FontSize = 8
end
object QRDBText3: TQRDBText
Left = 268
Top = 0
Width = 57
Height = 17
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Size.Values = (
44.979166666666670000
709.083333333333300000
0.000000000000000000
150.812500000000000000)
Alignment = taLeftJustify
AlignToBand = False
AutoSize = True
AutoStretch = False
Color = clWhite
Transparent = False
WordWrap = True
FontSize = 8
end
object QRDBText4: TQRDBText
Left = 344
Top = 0
Width = 57
Height = 14
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Size.Values = (
37.041666666666670000
910.166666666666700000
0.000000000000000000
150.812500000000000000)
Alignment = taLeftJustify
AlignToBand = False
AutoSize = True
AutoStretch = False
Color = clWhite
Transparent = False
WordWrap = True
FontSize = 8
end
end
object SummaryBand1: TQRBand
Left = 48
Top = 145
Width = 720
Height = 40
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
AlignToBottom = False
Color = clWhite
ForceNewColumn = False
ForceNewPage = False
Size.Values = (
105.833333333333300000
1905.000000000000000000)
BandType = rbSummary
end
end
end
object Form1: TForm1
Left = 192
Top = 107
Width = 870
Height = 640
Caption = 'Form1'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
Scaled = False
PixelsPerInch = 96
TextHeight = 13
object QuickRep1: TQuickRep
Left = 48
Top = 24
Width = 816
Height = 1056
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = []
Functions.Strings = (
'PAGENUMBER'
'COLUMNNUMBER'
'REPORTTITLE')
Functions.DATA = (
'0'
'0'
'''''')
Options = [FirstPageHeader, LastPageFooter]
Page.Columns = 1
Page.Orientation = poPortrait
Page.PaperSize = Letter
Page.Values = (
127.000000000000000000
2794.000000000000000000
127.000000000000000000
2159.000000000000000000
127.000000000000000000
127.000000000000000000
0.000000000000000000)
PrinterSettings.Copies = 1
PrinterSettings.Duplex = False
PrinterSettings.FirstPage = 0
PrinterSettings.LastPage = 0
PrinterSettings.OutputBin = Auto
PrintIfEmpty = True
SnapToGrid = True
Units = MM
Zoom = 100
object TitleBand1: TQRBand
Left = 48
Top = 48
Width = 720
Height = 40
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
AlignToBottom = False
Color = clWhite
ForceNewColumn = False
ForceNewPage = False
Size.Values = (
105.833333333333300000
1905.000000000000000000)
BandType = rbTitle
end
object ColumnHeaderBand1: TQRBand
Left = 48
Top = 88
Width = 720
Height = 40
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
AlignToBottom = False
Color = clWhite
ForceNewColumn = False
ForceNewPage = False
Size.Values = (
105.833333333333300000
1905.000000000000000000)
BandType = rbColumnHeader
object QRLabel1: TQRLabel
Left = 80
Top = 24
Width = 33
Height = 17
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Size.Values = (
44.979166666666670000
211.666666666666700000
63.500000000000000000
87.312500000000000000)
Alignment = taLeftJustify
AlignToBand = False
AutoSize = True
AutoStretch = False
Caption = 'Name'
Color = clWhite
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = [fsBold]
ParentFont = False
Transparent = False
WordWrap = True
FontSize = 8
end
object QRLabel2: TQRLabel
Left = 180
Top = 24
Width = 23
Height = 17
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Size.Values = (
44.979166666666670000
476.250000000000000000
63.500000000000000000
60.854166666666670000)
Alignment = taLeftJustify
AlignToBand = False
AutoSize = True
AutoStretch = False
Caption = 'City'
Color = clWhite
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = [fsBold]
ParentFont = False
Transparent = False
WordWrap = True
FontSize = 8
end
object QRLabel3: TQRLabel
Left = 268
Top = 24
Width = 45
Height = 17
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Size.Values = (
44.979166666666670000
709.083333333333300000
63.500000000000000000
119.062500000000000000)
Alignment = taLeftJustify
AlignToBand = False
AutoSize = True
AutoStretch = False
Caption = 'Product'
Color = clWhite
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = [fsBold]
ParentFont = False
Transparent = False
WordWrap = True
FontSize = 8
end
object QRLabel4: TQRLabel
Left = 344
Top = 24
Width = 50
Height = 14
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Size.Values = (
37.041666666666670000
910.166666666666700000
63.500000000000000000
132.291666666666700000)
Alignment = taLeftJustify
AlignToBand = False
AutoSize = True
AutoStretch = False
Caption = 'Quantity'
Color = clWhite
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'Tahoma'
Font.Style = [fsBold]
ParentFont = False
Transparent = False
WordWrap = True
FontSize = 8
end
end
object DetailBand1: TQRBand
Left = 48
Top = 128
Width = 720
Height = 17
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
AlignToBottom = False
Color = clWhite
ForceNewColumn = False
ForceNewPage = False
Size.Values = (
44.979166666666670000
1905.000000000000000000)
BandType = rbDetail
object QRDBText1: TQRDBText
Left = 80
Top = 0
Width = 57
Height = 17
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Size.Values = (
44.979166666666670000
211.666666666666700000
0.000000000000000000
150.812500000000000000)
Alignment = taLeftJustify
AlignToBand = False
AutoSize = True
AutoStretch = False
Color = clWhite
Transparent = False
WordWrap = True
FontSize = 8
end
object QRDBText2: TQRDBText
Left = 180
Top = 0
Width = 57
Height = 17
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Size.Values = (
44.979166666666670000
476.250000000000000000
0.000000000000000000
150.812500000000000000)
Alignment = taLeftJustify
AlignToBand = False
AutoSize = True
AutoStretch = False
Color = clWhite
Transparent = False
WordWrap = True
FontSize = 8
end
object QRDBText3: TQRDBText
Left = 268
Top = 0
Width = 57
Height = 17
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Size.Values = (
44.979166666666670000
709.083333333333300000
0.000000000000000000
150.812500000000000000)
Alignment = taLeftJustify
AlignToBand = False
AutoSize = True
AutoStretch = False
Color = clWhite
Transparent = False
WordWrap = True
FontSize = 8
end
object QRDBText4: TQRDBText
Left = 344
Top = 0
Width = 57
Height = 14
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
Size.Values = (
37.041666666666670000
910.166666666666700000
0.000000000000000000
150.812500000000000000)
Alignment = taLeftJustify
AlignToBand = False
AutoSize = True
AutoStretch = False
Color = clWhite
Transparent = False
WordWrap = True
FontSize = 8
end
end
object SummaryBand1: TQRBand
Left = 48
Top = 145
Width = 720
Height = 40
Frame.Color = clBlack
Frame.DrawTop = False
Frame.DrawBottom = False
Frame.DrawLeft = False
Frame.DrawRight = False
AlignToBottom = False
Color = clWhite
ForceNewColumn = False
ForceNewPage = False
Size.Values = (
105.833333333333300000
1905.000000000000000000)
BandType = rbSummary
end
end
end
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
As far aas I see above you have 2 tables: first tbale contains customers, and second table contains products...
Now... if you want to see each customers buy list then you probabely will need a third table that would contain the sales....
If you link the tables through ID1 and ID2 (weird) then
it's simple:
The code will look like this
Query1.Active:=false;
Query1.Sql.Clear;
Query1.Sql.Add('select * from Table1,Table2 where Table1.ID1=Table2.Id2 order by Id1,Name');
Query1.Active:=true;
// Now you have the data that you need to place on the Report.
For QuickReport it's like this:
On your form (or on the DataModule) pl;ace a Query Component (ADO or BDE) and a DataSource and connect them
Create a new form put a QReport on it and add to the uses list of the form the DataModule (or the main form where you keep the DataComponents (Query, DataSource))...
On the DataSet Property of the QuickReport enter the DataSource
Then just place some DataAware components ( QRDBText ) on the Detail band and connect it's properties to the same DataSource