Help in Data Report again...

[Encoder]

I have to start a new one... my questions have from my previous thread has already been answered and resolved.

So here it is:

I have a two different sets of data that comes from two different tables (in one database). One table, unitorder, contains the columns pono (P.O. No.), orderno, unitordered, suppliername, price, quantity, and totalunitprice. The other table is the supplier that contains suppliername and address.

Now the only thing in common between the two is obviously the suppiername. In my program you cannot have two unit orders with different supplier, so in one transaction (Purchase Order) there is only one supplier.

The problem is I need the information from both of the tables, suppliername and address where suppliername is equals to the suppliername of a certain Purchase Order, and the details from the unitorder table to be displayed in one datareport. But since they are from different tables, I just can't do that.

So how can I make that kind of datareport? Or is there a much easier way or it just can't be done?

[Encoder]

I also need these two commands in one datareport: SELECT * FROM transaction where client = ? and SELECT SUM(price) AS totalTransaction FROM transaction. How can I do that

[wes4dbt]

You can use an "inner join", like

select pono,orderno,price,supplier.name,supplier.address from unitorder inner join supplier on unitorder.client=supplier.client where supplier.client=  ?

If your only running this for one client you don't need grouping. You can put a "sum" function control in the footer section.

[Encoder]

I haven't tried that code, it might work but I think it will display the supplier name and address over and over again, as a list, as many times the ordered units are. I need the format to be like this:

Supplier Name:
Address:

Order No.: Unit Description: Price:
1 ...
2 ...
3 ...
..


And from your suggestion, can the footer section hold a different command from the details section? I'm sorry for these many questions, I'm really a newbie at VB.

[Encoder]

The probable solution that I have found is making a subreport in data report, but I haven't tried it yet since I don't know how to do it.

[wes4dbt]

There is no such thing as a subreport in a datareport. If all your doing a simple report like you describe above then you don't need the "inner join". You can assign the "supplier name" and "address" at runtime, then put a "sum" function in the report footer. I'll attach an example, it's not exactly the same but should get you started. Check the Datareport_Initialize code for how to assign values at runtime.