Help with a report issue

rbuser

I am trying to create what I originally thought was a simply report in
ReportBuilder 10.03

I have 2 tables - table 1 is a table of purchase orders, and their balances.

Table 2 is a table of units, and their values - and how much has been paid
on them, etc.

Now - table 1 is listed by vendor, then PO#, then POTotal, AmtPaid,
BalanceDue, and some others (not needed for report)

Table 2 simply has Unit#, PO#, wholesale Value, and current balance.

Now, what I'm looking for is a report that shows the Vendor, the sum of the
Balance of their PO's, and the sum of the balance of the units for those
PO's (a visual way to reconcile that the po balance matches the unit
balance).

Now, the first issue I had is if I use a single query, then the balance of
the current PO gets overstated because it calculates for every unit on the
PO (so if I had a PO with a $40K balance, and it had 20 units on the PO, the
query would come back with $800K as the PO balance) - so I broke them into 2
separate queries, and they come back fine (one query list the vendor and the
po balance, the other query list the vendor and the unit balance).

The issue I am now stuck at is how to get the report to show 1 line per
vendor, and just show the vendor, the balance amount from query1, and then
the balance amount from query2??

Thanks for any help!!!

- Chris

nicocizik

Hi Christopher,

You will need to link the two queries on the vendor so they traverse at the
same time in a one-to-one relationship. You can assign the main report to
the first query, and then place a subreport inside the detail band assigned
to the second query. If both queries are ordered by the linking field
(Vendor) then you should get the effect you are after.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com