Unique counts problem in a Crosstab query

[dgmdvm]

One portion of my database has a form the user can fill out by placing checkmarks in boxes to denote that specific types of furniture have been donated. The form is bound to a table "tblfurnitureIn". The check boxes are all yes/no fields. Perhaps this wasn't the best way to design the table but... From this data I have created a report to show, in database view, 12 months of what types of furniture has been donated. Thanks to the answers June7 provided to another forum user I was able to create my crosstab to show the donations by furniture type. My problem is that I also have to show the number of people that donated the furniture over the same time period. If I use the methodology I used to get the rest of the cross tab to work, I end up with a count of the number of times a donation was made rather than a distinct count of the people that made the donation. In my sample data, I have one donor that donated 1 item on 6/1, one item on 6/11, and 3 items on 6/12. So in my data, I get a count of 3 donors rather than a count of 1 donor that gave on 3 occasions. I tried distinct count among other things but didn't get any better results. Since I am only looking for one row of data, I questioned if it is correct to even use a cross tab.

My primary key for the donor is ID_number PK .

Here are my sql strings:

Query 1 (qryFurnitureInCountsPt1): this query converts the yes/no fields of the table to counts of "yes" for each furniture type. IT also returns a count of ID_numberpk but in my example the count is 3 even though there is only one person that donated (they donated 3 times)

SELECT Sum([FrnBed])*-1 AS CountofFrnBed, Sum([FrnDresser])*-1 AS CountofFrnDresser, Sum([FrnCouch])*-1 AS CountofFrnCouch, Sum([FrnEntertainment])*-1 AS CountofFrnEntertainment, Sum([FrnKitchentbl])*-1 AS CountofFrnKitchentbl, Sum([FrnRefrigerator])*-1 AS CountofFrnrefrigerator, Sum([FrnStove])*-1 AS CountofFrnStove, Sum([FrnMicrowave])*-1 AS CountofFrnMicrowave, Sum([FrnWasher])*-1 AS CountofFrnWasher, Sum([FrnDryer])*-1 AS CountofFrnDryer, Count(tblFurnitureIn.frnOther) AS CountofOther, Item_Donations.Date AS DonateDate, Count(Contact_Info.ID_numberPk) AS CountOfDonorFROM (Contact_Info INNER JOIN Item_Donations ON Contact_Info.ID_numberPK = Item_Donations.ID_number_FK) INNER JOIN tblFurnitureIn ON Item_Donations.Control_number = tblFurnitureIn.Control_Number
GROUP BY Item_Donations.Date;

Query 2 (qryFurnitureInCountsPt2): Assigns a string name to each count and puts it in a field named "furnituretype". Right now, my count of donors is in here but it returns the number 3 and it should be 1; it also puts the donor count into the "furnituretype" field-- another issue with my code I am not sure how to solve.

SELECT qryFurnitureInCountsPt1.DonateDate,  (CountofFrnCouch)  AS count, ("Couches") As FurnitureType FROM qryFurnitureInCountsPt1


UNION SELECT  qryFurnitureInCountsPt1.donatedate, CountofFrnBed, ("Beds") as FurnitureType from qryFurnitureInCountsPt1


UNION SELECT   qryFurnitureInCountsPt1.DonateDate, CountofFrnDresser, ("Dressers") AS FurnitureType FROM qryFurnitureInCountsPt1


UNION SELECT   qryFurnitureInCountsPt1.DonateDate, CountofFrnrefrigerator, ("Refridgerators") AS FurnitureType FROM qryFurnitureInCountsPt1


UNION SELECT   qryFurnitureInCountsPt1.DonateDate, CountofFrnStove, ("Stoves") AS FurnitureType FROM qryFurnitureInCountsPt1


UNION SELECT   qryFurnitureInCountsPt1.DonateDate, CountofFrnMicrowave, ("Microwaves") AS FurnitureType FROM qryFurnitureInCountsPt1


UNION SELECT   qryFurnitureInCountsPt1.DonateDate, CountofFrnWasher, ("Washers") AS FurnitureType FROM qryFurnitureInCountsPt1


UNION SELECT   qryFurnitureInCountsPt1.DonateDate, CountofFrnDryer, ("Dryers") AS FurnitureType FROM qryFurnitureInCountsPt1


UNION SELECT  DISTINCT qryFurnitureInCountsPt1.DonateDate, CountofDonor, ("Donors") AS Donor FROM qryFurnitureInCountsPt1


UNION SELECT   qryFurnitureInCountsPt1.DonateDate, CountofOther, ("Other furniture") AS FurnitureType FROM qryFurnitureInCountsPt1;

Query 3 (qryFurnitureInCountsCrosstab): This is the cross tab query. It generates the furniture counts just fine and puts them into a datasheet that is 12 months long but with a user derived range of dates (user enters valid dates in text 2 and text4 text boxes when the report is created. I need to get the correct donor count so I can place it in a report.

PARAMETERS Forms!Print_client_reports_menu!text2 DateTime, Forms!Print_client_reports_menu!text4 DateTime;
TRANSFORM Sum(qryFurnitureInCountsPt2.Count) AS SumOfCount
SELECT qryFurnitureInCountsPt2.FurnitureType, Sum(qryFurnitureInCountsPt2.Count) AS [Total Of count]
FROM qryFurnitureInCountsPt2
WHERE (((qryFurnitureInCountsPt2.DonateDate)>=[Forms]![Print_client_reports_menu]![text2] And (qryFurnitureInCountsPt2.DonateDate)<=[Forms]![Print_Client_Reports_Menu]![Text4]))
GROUP BY qryFurnitureInCountsPt2.FurnitureType
ORDER BY qryFurnitureInCountsPt2.FurnitureType
PIVOT Year([donatedate])*12+Format([donatedate],"mm")-(Year([Forms]![Print_client_reports_menu]![text2])*12+Format([Forms]![Print_client_reports_menu]![text2],"mm"))+1 In (1,2,3,4,5,6,7,8,9,10,11,12);

Any comments would be appreciated. IF there is an easier way to get the correct donor count into a crosstab I would more than happy to try it!

Here is a screenshot of the report so you can see what I am after. You will note that line for donors shows a count of 3; the number should be 1 since the same person gave 3 times.





Thanks

[June7]

You had right idea to use DISTINCT, just not in correct sequence.

Have to first do a query that returns DISTINCT donors by year/month. Then use that query in another that does the count.

Might want to modify the UNION to extract month and year from the date value.

Advise not to use count as field name because it is a reserved word.

[dgmdvm]

[QUOTE=June7;401565]You had right idea to use DISTINCT, just not in correct sequence.

Have to first do a query that returns DISTINCT donors by year/month. Then use that query in another that does the count.

So I tried that with the following Query:

SELECT DISTINCT Contact_Info.ID_numberPKFROM (Contact_Info INNER JOIN Item_Donations ON Contact_Info.ID_numberPK = Item_Donations.ID_number_FK) INNER JOIN tblFurnitureIn ON Item_Donations.Control_number = tblFurnitureIn.Control_Number;

Results of first query:


When I run it, I do get a single ID_number PK. So far so good. But when I add that query to the query qryFurnitureInCountsPt1, I get a row of records for each date of contribution and the ID_number Pk is still listed 3 times.

SELECT Sum([FrnBed])*-1 AS CountofFrnBed, Sum([FrnDresser])*-1 AS CountofFrnDresser, Sum([FrnCouch])*-1 AS CountofFrnCouch, Sum([FrnEntertainment])*-1 AS CountofFrnEntertainment, Sum([FrnKitchentbl])*-1 AS CountofFrnKitchentbl, Sum([FrnRefrigerator])*-1 AS CountofFrnrefrigerator, Sum([FrnStove])*-1 AS CountofFrnStove, Sum([FrnMicrowave])*-1 AS CountofFrnMicrowave, Sum([FrnWasher])*-1 AS CountofFrnWasher, Sum([FrnDryer])*-1 AS CountofFrnDryer, Count(tblFurnitureIn.frnOther) AS CountofOther, Item_Donations.Date AS DonorDate, qryFurnitureInDonorsPt1.ID_numberPK AS DonorsFROM ((Contact_Info INNER JOIN qryFurnitureInDonorsPt1 ON Contact_Info.ID_numberPK = qryFurnitureInDonorsPt1.ID_numberPK) INNER JOIN Item_Donations ON Contact_Info.ID_numberPK = Item_Donations.ID_number_FK) INNER JOIN tblFurnitureIn ON Item_Donations.Control_number = tblFurnitureIn.Control_Number
GROUP BY Item_Donations.Date, qryFurnitureInDonorsPt1.ID_numberPK;

Results of the second query:




I think I am missing something still. I tried the cross tab from the second query but I can't get a row called "Donors" and I can't get the donors with the correct count.


Cross tab query:

PARAMETERS Forms!Print_client_reports_menu!text2 DateTime, Forms!Print_client_reports_menu!text4 DateTime;TRANSFORM Sum(qryFurnitureInCountsPt2.furnitureCounts) AS SumOfCount
SELECT qryFurnitureInCountsPt2.FurnitureType, qryFurnitureInDonorsPt1.ID_numberPK AS Donors, Sum(qryFurnitureInCountsPt2.furnitureCounts) AS [Total Of count]
FROM qryFurnitureInCountsPt2, qryFurnitureInDonorsPt1
WHERE (((qryFurnitureInCountsPt2.DonorDate)>=[Forms]![Print_client_reports_menu]![text2] And (qryFurnitureInCountsPt2.DonorDate)<=[Forms]![Print_Client_Reports_Menu]![Text4]))
GROUP BY qryFurnitureInCountsPt2.FurnitureType, qryFurnitureInDonorsPt1.ID_numberPK
ORDER BY qryFurnitureInCountsPt2.FurnitureType
PIVOT Year([donordate])*12+Format([donordate],"mm")-(Year([Forms]![Print_client_reports_menu]![text2])*12+Format([Forms]![Print_client_reports_menu]![text2],"mm"))+1 In (1,2,3,4,5,6,7,8,9,10,11,12);

Results of the crosstab:



The column labeled donors is the Id_numberPk. The row that corresponds to donors in Column one is the sum of the ID_numberpk 3 times. I think I am close but still missing one piece of code, just not sure what it is.

Thanks for taking a look at this for me!

[June7]

Can't join the donors in that second query. The donor count will have to be independent then feed it into the UNION. There is not year/month in the donor DISTINCT. How can you do monthly count without?

If you need more help, provide raw data for analysis and testing. If you want to provide db, follow instructions at bottom of my post.

[dgmdvm]

I think I can conceptualize this now. Two parallel sets of queries, one with donors, one with furniture. I have to bring the dates into each and then join them in a union query. I will work on that today and see if I can get the correct answers.

Thank you.

[dgmdvm]

I got this solved. The trick was to use only the month for the date field when counting donors but using the entire date when counting donations of furniture Thanks for all your help.