Problem displaying data when DBF in 1st normal form

[suec032a]

I am using VFP9 SP2 and have 3 tables, consisting of customer information (table1), pet information (table2) and booking information (table3). Both table2 and 3 are linked to customer table. I am trying to rewrite an old 2.6 application and still pretty new to VFP9.

A customer can have multiple pets with multiple bookings. Past history has indicated that a maximum of 7 pets per customer.
My problem is that the user wants to see the pet information in the following format.
Pet 1 Pet 2 Pet 3 Pet 4.....
Name
Breed
Code
DOB..

I have 14 attributes for a pet, so I tried to struture the pet table in 1NF with one record per pet, but I am not sure how I can then get the information from the table into the above format? The original application was in 2.6, and was not normalised so all the pet information for a customer was held in one record.

Any advice would be gratefully accepted.

thanks

sue

 

[DSummZZZ]

One thing I have done in the past is create the report layout accommodating all the possible columns and rows - which in your case would be 7 X 14 - using array elements. You could then populate the array with either the real values or blanks, which wouldn't print, by scanning through the records or using a query.
It is a bit of a chore, but it works well.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Mike Lewis]

Sue,

Visual FoxPro has a tool called the PivotTable Wizard that looks like it might do what you want. I've never used it, and don't know how easy it is. But you might like to read the topic in the VFP Help file -- just search for PivotTable Wizard.

Welcome to the forum, and good luck with your conversion project.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[JRB-Bldr]

Generally I have accomplished what you are looking for by setting up a 'backwards' table relation.

SELECT Parent
SET ORDER TO CustID

SELECT Child2
SET ORDER TO CustID

SELECT Child1
SET RELATION TO CustID INTO Parent ADDITIVE
SET RELATION TO CustID INTO Child2 ADDITIVE

REPORT FORM MyReport NOCONSOLE TO PRINTER

In this way, only those records which have entries in Child1 will be 'reported' and for each of them, the Parent record and associated Child2 record will also be available for reporting.

Then you merely set your Report Form Groups to differentiate one CustID from another.

An alternative method would be to build an SQL Query which gathered the desired data into a single cursor for the purposes of reporting.

Good Luck,
JRB-Bldr

 

[markros]

Check

http://fox.wikis.com/wc.dll?Wiki~FastXTab

 

[suec032a]

Thank you everyone for your assistance. I tried the "backwards" relation for the report and this worked.

Sue