[MSAccessProfessionals] How to restrict results in a pivot query

Hello all

 

About 6 years ago I asked for help on writing a query to gather data from a variable number of records in various tables and put it all into csv file to populate a form. I did finally get it working and recently I needed to modify it to eliminate some information. I haven’t entirely succeeded so I hope you can help.

 

The system is for a boarding kennels or cattery. It’s a commercial system, developed in (I think) Visual Studio with an Access database, but I am able to develop custom queries for it.

 

The query is collect the information on up to six animals owned by one customer, when the customer books them in for boarding. It encompasses a number of pivot tables which collect each animal’s breed, vaccinations, medications, etc, and put the results into separate fields. I altered the pivot tables recently to eliminate animals which were deceased and I thought I’d done that successfully but actually the pivot tables still count the ‘blank’ record; when the form is populated the entries for the deceased animal are blank. When I tested it I must have selected bookings where the deceased pet was the last of the selected records so it appeared to be working.

 

One of the pivot queries is below; can anyone tell me a way of stopping the query adding the deceased animals to the count, please? I’m sure it’s pretty obvious but my mind is also blank!

 

TRANSFORM First(pets.breed_desc) AS FirstOfBreed

SELECT pets.pet_cust_no

FROM (SELECT tblpet.pet_cust_no, tblpet.pet_no, tblpet.pet_deceased, tblbreed.breed_desc AS breed_desc, Count(tblpet_1.pet_cust_no) AS ColHead FROM (tblbreed INNER JOIN tblpet ON tblbreed.breed_no=tblpet.pet_breed_no) INNER JOIN tblpet AS tblpet_1 ON tblpet.pet_cust_no=tblpet_1.pet_cust_no WHERE (((tblpet.pet_no)>=[tblpet_1.pet_no])) GROUP BY tblpet.pet_cust_no, tblpet.pet_no, tblpet.pet_deceased, [tblbreed].[breed_desc] ORDER BY tblpet.pet_no)  AS pets

WHERE (((pets.tblpet.pet_deceased)="N"))

GROUP BY pets.pet_cust_no

PIVOT "breed_desc" & [ColHead] In ("breed_desc1","breed_desc2","breed_desc3","breed_desc4","breed_desc5","breed_desc6");

Thanks in advance

 

Sue