VFP 7 Report Problem with 2 tables in the database

[wanderaround]

I have problem in creating a daily report with detail inventory information for specific client. The report have 2 tables. 1. Transaction table which store daily transaction for client. 2. Inventory tables which store inventory information for client. Both table have a key field of Client code. I need to print out summary transaction for a client from table 1 at the beginning of the report. And then print out all records of table 2 according to the client code. Anyone can help?

 

[ramani]

Hi

Method1:
SELECT "0" AS myHeader, a.myClient, a.whatever... , ;
SUM(b.myField1) AS myField1, ;
SUM(b.myField2) AS myField2 ;
FROM Table1 A, Table2 B ;
INTO CURSOR myCursor ;
WHERE a.myClient = b.myClient ;
AND.. other conditions ...if any ;
GROUP BY myClient ;
UNION ALL ;
SELECT "1" AS myHeader, a.myClient, a.whatever... , ;
b.myField1, b.myField2 ;
FROM Table1 A, Table2 B ;
WHERE a.myClient = b.myClient ;
AND.. other conditions ...if any ;
ORDER BY 2,1

Now you get a flat SQL cursor with the first record as your summary record and the rest of the records as your detailed record.

You have to output as a flat file.. i.e. sigle file report.

Take care that the report is grouped by client code and then by myHeader field in the cursor.. Also avoid the printing in detailed band if myHeader is "0".

Method2:
1. SELECT .. summery as above in Cursor1.
Index it on the Client code..
SET INDEX ORDER

2. Select the transactions from table 2.. as cursor2
Set the relation to cursor1
Output the report as master and child.. and summery info is available in cursor1.. so the solution..

ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[wanderaround]

Ramani,

Thanks a lot. Should I define it inside the form before calling the report?

Wanderaround

 

[ramani]

Yes you can do the SQL selects just before calling the report.. inside the form.. could be in the click event of the print button..

ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[wanderaround]

Ramani,

I didn't try the command you provided but not successful because of my weakness in VFP programming. To made the thing clear, I provide the following detail so that you may detail me on how to do:
database name: WS

Table1 name: TRANSACT
INDEX NAME: TR_CLIENT ( INDEX BY TR_CLIENT )
TABLE FUNCTION: STORE DAILY TRANSACTION RECORD.

Table2 name: INVENT
INDEX NAME: IN_C (INDEX BY IN_CLIENT )
TABLE FUNCTION: STORE INVENTORY INFORMATION FOR ALL CLIENT.

TRANSACT TABLE FIELDS: TR_CLIENT, TR_CN, TR_UPRICE, TR_QTY
INVENT TABLE FIELDS: IN_CLIENT, IN_STOCK, IN_QTY

KEY FIELDS : TR_CLIENT, IN_CLIENT

Now, I need to create a report which print out the detail of transaction record and group the transaction amount by TR_CN for client who have buy/sell the Stock on specific date (Client may have more than 1 transaction in a day). After printing the Transaction information, will then need to print out the detail information of Inventory record (may have more than 1 record) for the same client. If a client did not buy/sell the Stock, then record from both tables should be skip.

Once again, sorry for sparing you much time on this issue. Thank a lots.

Wanderaround