data report in vb 1

[ishikha]

hi,

i want to get fields from two tables in one report.i am using data environment as a data source

can u suggest me a solution?????????

regards
ishikha

 

[CasperTFG]

You need to be a lot more specific than that, sorry....

 

[stnkyminky]

Yes.
Each report requires a data member or data source. In your data environment, define a command object and specify your SQL statement to pull the fields you need. When you create a datareport, assign the command object to the datareport and you will be able to insert the fields into the datareport. Scott
Programmer Analyst

 

[ishikha]

hi stnkyminky,

this i know.my problem is that i want to get data from two tables in one data report.the two tables doesnot have any relation.they are totally seperate.i am using ms access as a database.since i have norelation in my tables so i have to apply two seperate sql query for both tables.

now i don't know how to write two seperate sql statements under one command so that i will able to drag the results of both sql statements in one data report

regards
ishikha

 

[webspy]

hi
I hope know about the join sql query.
As i understand ur problem here i this query to write
dataenvironment->command->properties->sql


-----
select a.*,b.* from table1 a,table2 b
----

the above query is for getting all the fields from the two table thru one sql command

If u want some particular fields from those two tables
-----
select a.name,a.age,a.sex,b.salary,b.department from table1 a,table2 b

----

i hope this code will solve ur problem
bye
happy programming
webspy

 

[ishikha]

hi webspy,

this is close to my problem.i want to write sql query for calculating the total no of records in one table and then i have to write the sql query for calculating the total no of records in second table.how i will write it?????????

bye
ishikha

 

[webspy]

ok
try this code

select count(a.field1),count(b.field2) from table1 a,table2 b

replace ur fields and table names in corresponding areas

bye
webspy

 

[ishikha]

hi

i have tried this query but it gives the wrong result

select count(mdialogic.srno),count(mcomputer.machineno) from mdialogic,mcomputer

it does not count the correct no of records
for both tables it gives the ans 24

bye

 

[Guest_imported]

Hej,

It is correct that it doesn't count the right number of records.

U have to use count(*) if u want to count the total number of records in a table.

count(<fieldname>) counts the number of VALUES in a table...
(The number of records where <fieldname> has a value)

Succes!

Mark.

 

[ishikha]

hi

i have also tried this but it again give me the ans 24

select count(*),count(*) from mdialogic, mcomputer

please suggest me some sol.

ishikha

 

[stnkyminky]

What you're wanting to do is possible using a Parent and Child command objects. Although, (you knew this was coming) I am 99.99% positive a relation must exist between the two tables. Below is an example that contains a relation.

http://www.vb-world.net/articles/dbtutorial6/index5.html

Other possible solutions for you. Can you generate the report in HTML or Word? These formats would give you better flexibility......just a suggestion. Scott
Programmer Analyst

 

[webspy]

ok we will do it in another method

like

put a 2 label objects in page header(section 2) in datareport

and connect the any one of ur database table thru command1 in dataconnection and put it as datareport's data member.

then put the 2 label's names as count1 and count2

in the form put a button and write this code
________________________________________
Private Sub Command2_Click()
Set db = OpenDatabase(App.Path & &quot;\sample.mdb&quot
Set rs = db.OpenRecordset(&quot;select count(*) from xooline&quot
Set rs2 = db.OpenRecordset(&quot;select count(*) from second&quot
DataReport1.Sections(&quot;section2&quot.Controls.Item(&quot;count1&quot.Caption = rs(0)
DataReport1.Sections(&quot;section2&quot.Controls.Item(&quot;count2&quot.Caption = rs2(0)
DataReport1.Show
End Sub
_____________________________

this is the another way to assing the values for datareport labels from forms.

i tried this and works fine.

i hope this will help u to solve ur problem.

happy programming
webspy

 

[itlee]

ishikha,

If you are returning an aggregate you need to wrap each aggregate statement with brackets() and inclose both statements in another select statement.

E.g.

select(select count(*) from table1) as col1, (select count(*) from table2) as col2

this will return a two column result set:

col1 col2
---- ----
1234 2536


Good Luck,
ITLee, Programmer

 

[ishikha]

hi webspy,

thank u very much my problem is solved.

there is one more problem that how i will write the sql query if i want the sql query on two fields belonging to one tables.
for one field &quot;name&quot; i will write this(given below) query now i want this similar query for another field &quot;slot&quot;
i want to write only one query for both this fields
i.e under one command so that i will be able to include the results of both query in one data report

select name,count(*)from mdialogic group by name order by 2 desc

thankyou

regards
ishikha

 

[ishikha]

hi webspy,

there is one more problem.i am having one table &quot;object&quot; .in this table there is one field called &quot;name&quot;.In this field i am having diffrent entries like modem,telephone,stablizer etc.now i want that when i write the code as u told me in the form it will automatically generate the labels in the data report for all the items i am having in the field &quot;name&quot;

like

dialogic 5
computer 7
modem 2
telephone 10
stablizer 1


for dialogic and computer i know this will be the entry but for modem,telephone etc i don't know how many items are there in the name field what are there labels so i cant just include labels in the report i want they automatically generate the no of labels in the data report by counting the no of diffrent no of items in &quot;name&quot; and also label.caption =the name of the item

in order to get this type of output i have used &quot;select count(distinct name)from object&quot; in the form but it will gave the error in distinct name

can u suggest me some sol.

regards
ishikha

 

[webspy]

ok put two labels on the datareport and change their properties -Cangrow = T

Set db = OpenDatabase(App.Path & &quot;\sample.mdb&quot
Set rs = db.OpenRecordset(&quot;select count(*) from xooline&quot
Set rs2 = db.OpenRecordset(&quot;select count(*) from second&quot
' add your field1 values from database thru while loop here i put some dummy names
a = &quot;name1&quot; + Chr(13) + Chr(13) + Chr(13) + &quot;name2&quot; + Chr(13) + Chr(13) + &quot;name3&quot;
'similarly second field
b = &quot;2&quot; + Chr(13) + Chr(13) + &quot;12&quot; + Chr(13) + Chr(13) + &quot;15&quot;
' assign the variables to labels
DataReport1.Sections(&quot;section2&quot.Controls.Item(&quot;count1&quot.Caption = a
DataReport1.Sections(&quot;section2&quot.Controls.Item(&quot;count2&quot.Caption = b
DataReport1.Show

i think this is ok for u
webspy

 

[ishikha]

hi webspy,

i could not able to understand ur code can u give me ur mail id so i mail my report and form to u can write the code in them.in that i also explain exactly what i want

regards
ishikha

 

[webspy]

please send a database and forms
or cut and paste ur form sourece code where u goint to call the datareport
my mail id raghuram23@hotmail.com

webspy

 

[ishikha]

hi webspy,

i am sending my code in 10 min actually there is a problem in my account

ishikha