multiple datasources for a datareport?

how can i create a report where in data are located in different recordsets or tables?

i tried this one but it did'nt gave me the result expected:
for 2 TABLES:
Dim Rst As New ADODB.Recordset
MyQuery = "select * from spdr1017d, totrefund"
Set Rst = con.Execute(MyQuery)
Set DR40KWSRC.DataSource = Rst
DR40KWSRC.Show vbmodal

rephrasing my problem, how can i create a report having multiple datasources using data report.. to all the gurus here.. pls help..

You cannot specify multiple datasource for the DataReport but you could combine tables using sql, what are you after?

Quote:

---

Originally Posted by dee-u

You cannot specify multiple datasource for the DataReport but you could combine tables using sql, what are you after?

---

i'm creating a refund system...
i would like to create a summary of the employees who needs to be refunded.. and i would like to get the total refund (per run).... and the total refund per month....

here's the layout i want to have at the Report Footer Section:
--------------------------------------
GRAND TOTAL:
EMPLOYEE COUNT: 154
AMOUNT AS INPUTTED: 324,789.05
AMOUNT (ex. Metering Charge): 320,000.25
TOTAL REFUND: 159,000.99

MONTH REFUND
January 820.05
February 1,230.25
March 3,355.74
---------------------------------------

the "total refund" will be accumulated through rptfunction
but the "total refund per month" is from another table

i used this query for datasource (for report), but did not get the right result.
(where 'spdr1017d' holds the detailed info and 'totrefund' holds the "total refund per month")

MyQuery = "select * from spdr1017d, totrefund"
Set Rst = con.Execute(MyQuery)

how?

You could set the value of those rptTextboxes at runtime if you want and not just rely on the rptFunction...

Quote:

---

Originally Posted by dee-u

You could set the value of those rptTextboxes at runtime if you want and not just rely on the rptFunction...

---

but i cannot place a rpttextbox in the report footer...
and if i place it in the details section, it will print repeatedly...

please help... i really need it as soon as possible...

Try RptLabel then change it's caption like this (before showing it)...

VB Code:

DataReport1.Sections("Section3").Controls("Label8").Caption = "dee-u"

got it... thanks so much! =) how about if i'll have 2 tables for my datasource.. how will i code it?
i used this code but it doesnt give me the result expected:

MyQuery = "select * from spdr1017d, totrefund"
Set Rst = con.Execute(MyQuery)
Set DR40KWSRC.DataSource = Rst

since it will only allow 1 datasource, i tried that code but in the details section, it prints repeatedly... it looks like this:

table A
Afield_1, Afield_2
jan 01
feb 02

table B
Bfield_1, Bfield_2
bill 0001
james 0001

in the details section, i only put Afield_1 and Afield_2 fields :

Afield_1 Afield_2
jan 01
feb 02
jan 01
feb 02
jan 01
feb 02

where in fact, i only this result:

Afield_1 Afield_2
jan 01
feb 02

help please...

Does those table have a relationship? You could try putting a DISTINCT or DISTINCTROW in your SQL statement...

VB Code:

MyQuery = "select DISTINCT * from spdr1017d, totrefund"

or

VB Code:

MyQuery = "select DISTINCTROW * from spdr1017d, totrefund"

Quote:

---

Originally Posted by dee-u

Does those table have a relationship? You could try putting a DISTINCT or DISTINCTROW in your SQL statement...

VB Code:

MyQuery = "select DISTINCT * from spdr1017d, totrefund"

or

VB Code:

MyQuery = "select DISTINCTROW * from spdr1017d, totrefund"

---

no.. it doesnt have a relationship...
the totrefund has 12 fields which represent the 12months of the year
this fields contains the 'total refund per month'

the spdr1017d, has a month_code field but it is not present in the
totrefund table..

is DISTINCTROW the same with GROUP BY?