multiple datasources for a datareport?

[jeanette_db]

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

[jeanette_db]

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

[dee-u]

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

[jeanette_db]

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?

[dee-u]

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

[jeanette_db]

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

[dee-u]

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

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

[jeanette_db]

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

[dee-u]

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

MyQuery = "select DISTINCT * from spdr1017d, totrefund"

or

MyQuery = "select DISTINCTROW * from spdr1017d, totrefund"

[jeanette_db]

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

MyQuery = "select DISTINCT * from spdr1017d, totrefund"

or

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?