I have a report where I get data from multiple tables. The query generates from 1 – 12 rows which I then process one at a time. Each row creates a page in the report. What I am trying to do is generate just one page with all of the data. Instead, what I am getting is one page for each row in dataTable and every page is identical. The simplest solution is to just print the first page but ideally I would like to limit it to just 1 page of output.
Here is my query:
Code: Select all
SELECT
parms.parms_unit,
people.people_id,
people.peo_name,
people.peo_building_unit,
people.peo_type,
people.peo_company,
people.peo_cell_phone,
people.peo_home_phone,
people.peo_work_phone,
people.peo_address_1,
people.peo_address_2,
people.peo_city,
people.peo_state,
people.peo_zip,
people.peo_email,
people.peo_lease_start_date,
people.peo_lease_end_date,
unit.unit_id,
unit.unit_building_unit,
unit.unit_address_1,
unit.unit_city,
unit.unit_state,
unit.unit_zip,
unit.unit_park_1,
unit.unit_park_2,
unit.unit_garage,
pets.pet_id,
pets.pet_building_unit,
pets.pet_dog,
pets.pet_cat,
pets.pet_license_num,
pets.pet_breed,
vehicle.vehicle_id,
vehicle.veh_building_unit,
vehicle.veh_make,
vehicle.veh_model,
vehicle.veh_year,
vehicle.veh_color,
vehicle.veh_license_num,
vehicle.veh_exp_date,
DATE_FORMAT(CURDATE(),'%M %d, %Y') AS today_date
from parms, people
LEFT JOIN unit ON people.peo_building_unit = unit.unit_building_unit
LEFT JOIN pets ON people.peo_building_unit = pets.pet_building_unit
LEFT JOIN vehicle ON people.peo_building_unit = vehicle.veh_building_unit
WHERE people.peo_building_unit = parms.parms_unit
ORDER By peo_type desc
- Run above query
- Alter dataTable to add a column for each field in report
- Define a variable for each field in the report and initialize them with underscores
- Read each row in dataTable and move data to the appropriate variable
- Update the fields in dataTable that were created by the alter statement above with values from the variables
I was hoping that by only updating the dataTable once at the end that it would only create one page but that is not the case. It still generates one page for each row in dataTable.
Do you have any suggestions?
Let me know if you want to see the entire report script.
Thanks,
John Klassen