Help in rewriting this Visual Fox Data Comparison Program to Access VBA or SQL T-SQL

[chieh]

Hi All:

I am working with Visual FoxPro, and I create this Program to compare data between two given month, where some account may or may not be available between the month, the end result will be a report with rows showing data in 2 month side-by-side, if not available, it wil show N/A on the side where the data is not available..., since the report is not a straight forward query and I have to write code to create this report, How would I replicate this code in Microsoft Access VBA and also in SQL Server T-SQL???

Simply copy the following program and save it and call it compTest and run it, all the test data are created on the fly, at the end, you can open C_Febdata, C_MarData cursor, and the final report C_Report cursor to see the before and after result.

Thank you.
Ch.

************************************************************************************************** CompTest.prg
* Compare data between two period, calculate total, etc. and then export the result to Excel
*
* The challange is to create a report THAT show SIDE-BY-SIDE month difference in Amount group by AccType+Client+Company,
* and at the same time show the details the amount breakdown by company and client.
* e.g.
* If the client is available in both the months, the row will show both month's info side-by-side,
* If the client is not available in Feb month, but in March, the row will show N/A in Feb and March info on March side
* If the client is available in Feb, but not in March, the row will show Feb data and N/A on the March side
*
******************************************************************************************************************************

*
* Create test table for February Month, and populate test data
*
set safety off

Create Cursor C_FebData ( Month C(3), company C(10), Client C(15), Account C(6), AccType C(4), Amount N(12,0) )

Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "ABCInc", "Sam", "ACC001", "Cash", 9000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "ABCInc", "Sam", "ACC002", "Cash", 5000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "ABCInc", "Sam", "ACC003", "GIC", 8000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "ABCInc", "Peter", "ACC004", "Cash", 2000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "ABCInc", "Peter", "ACC005", "Cash", 3000)

Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "DEFInc", "Jill", "ACC010", "Cash", 10000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "DEFInc", "Jill", "ACC011", "Cash", 5000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "DEFInc", "Jill", "ACC012", "GIC", 7000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "DEFInc", "Jill", "ACC013", "GIC", 6000)

Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "XYZInc", "Smith", "ACC020", "Cash", 7000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "XYZInc", "Smith", "ACC021", "GIC", 2000)
Insert into C_FebData (Month, company, Client, Account, AccType, Amount) Values ("Feb", "XYZInc", "John", "ACC022", "Cash", 5000)

Select C_FebData
Index on Acctype + Company + Client to Temp1


*
* Create test table for March Month, and populate test data
*
Create Cursor C_MarData ( Month C(3), company C(10), Client C(15), Account C(6), AccType C(4), Amount N(12,0) )

Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "ABCInc", "Sam", "ACC001", "Cash", 6000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "ABCInc", "Sam", "ACC002", "Cash", 7000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "ABCInc", "Sam", "ACC003", "GIC", 9000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "ABCInc", "Peter", "ACC006", "GIC", 2000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "ABCInc", "Peter", "ACC007", "GIC", 3000)

Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "DEFInc", "Jill", "ACC010", "Cash", 12000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "DEFInc", "Jill", "ACC011", "Cash", 8000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "DEFInc", "Jill", "ACC013", "GIC", 3000)

Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "XYZInc", "Smith", "ACC020", "Cash", 6000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "XYZInc", "Smith", "ACC022", "Cash", 10000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "XYZInc", "John", "ACC023", "GIC", 5000)
Insert into C_MarData (Month, company, Client, Account, AccType, Amount) Values ("Mar", "XYZInc", "John", "ACC024", "Loan", 7000)

Select C_MarData
Index on Acctype + Company + Client to Temp2


* Create temp cursor for storing the side-by-side comparison report.
Create Cursor C_Report (From_To C(8), Company1 C(10), Client1 C(15), Amount1 N(12,0), Filler C(2), Company2 C(10), Client2 C(15), Amount2 N(12,0), DiffAmount N(12,0))

* Create temp cursor same as C_Report for temporary storing and sorting the data before append them to C_Report
Create Cursor C_ReportTmp (From_To C(8), Company1 C(10), Client1 C(15), Amount1 N(12,0), Filler C(2), Company2 C(10), Client2 C(15), Amount2 N(12,0), DiffAmount N(12,0))


*
* Find the Unique Account Type across two comparing month's data,
* e.g. In this example, Feb data only have "Cash" and "GIC" account type,
* where as in March, it had "Cash", "GIC", and "Loan"
*
Select distinct(AccType) as AccType from C_FebData ;
Union ;
Select distinct(AccType) as AccType from C_MarData ;
Into Cursor C_UniqueAccType

*
* Group the amount by AccType, Client and Company, e.g. If Peter in Company ABCInc has two cash Account Type,
* (ACC004 = $2000, & ACC005 = $3000), the group amount will be $5000 for Peter of ABCInc.
*
Select C_FebData
brow nowait
select Month, Company, Client, AccType, sum(amount) as clnAmount ;
from C_FebData group by AccType, Client, Company ;
order by AccType, Company, Client ;
Into Cursor C_FebGroup

Select C_FebGroup
Go Top

*
* Group the amount by AccType, Client and Company, e.g. If Peter in Company ABCInc has two cash Account Type,
* (ACC004 = $2000, & ACC005 = $3000), the group amount will be $5000 for Peter of ABCInc.
*
Select C_MarData
brow nowait
select Month, Company, Client, AccType, sum(amount) as clnAmount ;
from C_MarData group by AccType, Client, Company ;
order by AccType, Company, Client ;
Into Cursor C_MarGroup

Select C_MarGroup
Go Top


Select C_Report
Select C_ReportTmp
Index on Client1 + Company1 to Temp3

*
* Create the report
*
Select C_UniqueAccType
Scan All
Select C_ReportTmp
Zap

Select C_FebGroup
Scan All For AccType == C_UniqueAccType.AccType
Select C_ReportTmp
Append Blank
Replace Company1 with C_FebGroup.Company, ;
Client1 with C_FebGroup.Client, ;
Amount1 with C_FebGroup.ClnAmount, ;
Company2 with "N/A", ;
Client2 with "N/A", ;
Amount2 with 0
EndScan

Select C_MarGroup
Scan All For AccType == C_UniqueAccType.AccType
Select C_ReportTmp
seek (C_MarGroup.Client + C_MarGroup.Company)
If found()
Replace Company2 with C_MarGroup.Company, ;
Client2 with C_MarGroup.Client, ;
Amount2 with C_MarGroup.ClnAmount
Else
Select C_ReportTmp
Append Blank
Replace Company1 with "N/A", ;
Client1 with "N/A", ;
Amount1 with 0, ;
Company2 with C_MarGroup.Company, ;
Client2 with C_MarGroup.Client, ;
Amount2 with C_MarGroup.ClnAmount
Endif
EndScan

Select C_ReportTmp
Replace all DiffAmount with Amount1 - Amount2
Sum(Amount1) to lnSum1
Sum(Amount2) to lnSum2

Select * From C_ReportTmp order by DiffAmount Descending Into Cursor C_ReportOrd

Select C_Report
Append blank
Replace From_To with [Feb=>Mar], ;
Company1 with C_UniqueAccType.AccType, ;
Client1 with "", ;
Amount1 with lnSum1, ;
Company2 with "", ;
Client2 with "", ;
Amount2 with lnSum2


Append from DBF("C_ReportTmp")
Append Blank
EndScan

Select C_FebData
Brow nowait

Select C_MarData
Brow Nowait

Select C_Report
go top
brow nowait
copy to C_Report.txt type Xls

 

[TamarGranor]

Why do you think you can't do this with just a query? If I understand what you're trying to do, you just need a full join to make sure that if there's no match for a particular item, you don't drop it.

Something like this:

SELECT M1.Company, M1.Client, M1.AccType, ;
       NVL(M1.clnAmount, "N/A"), ;
       NVL(M2.clnAmount, "N/A") ;
  FROM C_FebGroup M1
    FULL JOIN C_MarGroup M2 ;
      ON M1.Company = M2.Company ;
       AND M1.Client = M2.Client ;
       AND M1.AccType = M2.AccType ;
  INTO CURSOR csrResult

I haven't worried about the order of records in the result; you'll need to add an ORDER BY clause. I also, for simplicity, worked with your intermediate cursors. You can actually do this in one query by make the queries that create the intermediate cursors derived tables in this query.

As for moving to Access or T-SQL, I can't speak to Access. I have no idea what its SQL capabilities are. T-SQL can definitely handle this query, except that NVL() is a VFP function; the T-SQL equivalent is IsNull().

Tamar

 

[chieh]

Thank you Tamar, I will give this a try in T-SQL to see if I can get the same result.

Regards,
Ch

 

[chieh]

Hi Tamar:

I tried the query, it combines the data into one table, if you run my program and see the C_report, it lays out the result of Feb vs Mar in a side-by-side manner, instead of combining them together, Is this still possible to do using SQL only? You need to run the fox program to see what I mean.

Thanks
Ch

 

[TamarGranor]

My code gives you what you want. I just didn't bother to repeat the descriptive data for each row. You can certainly put it in if you want.

Tamar