R&R SQL

[StevenZog]

Hello;__I have a problem running one of my reports in R&R SQL. Initially, the report was created in R&R Xbase, since all of our data were in dbase database files. We are currently migrating to MS SQL 2005 Server. We^ve imported all of our data into the SQL Server. ____Now, the report I am having problems with, uses three different tables. My master table, is a table containing information about all of our clients and their account numbers. The second table contains information about purchases made by our clients, and the third table contains information about products which were returned. All three tables are linked using the client^s account number.____The report basically has three columns (AccountNumber, PurchaseAmount, and RefundAmount). When using the Xbase version, the report basically looks like this:____Account# Purchase$ Refund$__0001111 123.45 (blank)__ 234.56 (blank)__ 456.78 (blank)__ 111.22 (blank)__ 222.33 (blank)__ (blank) 234.56__ (blank) 22.33__ (blank) 89.65____Note: the (blank) doesn^t actually show up in the report. It is simplay a blank space.____As you see above, this client has made 5 purchases and 3 returns. For a total of 8 lines in the report. Now when I run the same report in the SQL version, I get this:____Account# Purchase$ Refund$__0001111 123.45 234.56__ 123.45 22.33__ 123.45 89.65__ 234.56 234.56__ 234.56 22.33__ 234.56 89.65__ 456.78 234.56__ 456.78 22.33__ 456.78 89.65__ 111.22 234.56__ 111.22 22.33__ 111.22 89.65__ 222.33 234.56__ 222.33 22.33__ 222.33 89.65____Now, obviously, if I create a total field down at the bottom of this report, the totals are all way out!!! I do I fix this?? Please help!!

[kfleming]

What you have is a multiple scan report where a single parent table has more than one scanned child table.____To accomplish this in a SQL report you need to create a User SQL report and use a UNION to combine two separate SELECT statements.____We have an article the describes how to do this: http://www.livewarepub.com/ftp/TechDocs/Multi-Scans_in_SQL.PDF____Kathleen__R&R Support