Mixed Summaries Report

[garyj]

I am not new to Access or VBA, but this one has me boggled :S
I have Access 2010, and I haven't done much VBA for creating reports. I think it would be better if there was a way to do this one without VBA.

I work for a nonprofit Summer Camp and we have a little shop in which parents prepay a credit to the shop and what is not used is returned to parents at the end of the week.

I have three tables that will be involved. Possibly only two are needed for this report.
CampKids is the table for fields: [KidsID], [Fname], [Lname], [CmpID], [CabinID]
Sales is the table for purchases: [SaleID], [KidsID], [ItemID], [Price], [SaleDate], etc
Orders is the table for orders purchased but cannot be received until the last day of camp: [OrderID], [KidsID], [SaleID], [ItemID], [Received]

I used to have a field in CampKids called ... and on each sale this amount would be adjusted. But errors tended to compound and an incorrect value is never corrected. So I removed that field, and opted for calculating the Balance each time the account is opened. It is much more accurate. I also removed fields [TshirtRecd], [DVDRecd], and [JournalRecd] and started up the Orders table making a subform with that information on the main account form.

[B]What I am trying to do now...
I had a report that worked off the old system, which summarized all the credits owed to campers in a particular camp - ie. Balance totals.
This report also summarized the total count of Tshirts, DVDs, and Journals that were still not [Received].

Additional problem: If a camper orders 2 DVDs, then two records are made in table Orders, each with [Received] = false.

I want a report that says: Camper Name / Balance Owed / Tshirts Count / DVDs Count / Journals Count (count being those not received)
The summary value would then give the Sum of Balance Owed, and the Total Count of the other 3 items.

I am thinking the only way to do this is write code for getting all the records during one of the events after the report is opened.
I would opt to add [Balance] back into CampKids, and to save the value calculated each time an account is closed. This would keep it accurate. I think the other values could still come from Orders... but I am not sure how.

Thanks for trying to understand my problem. Please let me know if I need to provide further info. Also, I don't need the whole code... just a start, if you follow my meaning. :)

Gary

[jonh]

Too much info but not enough detail. We need an example to work off.

If possible, you should always get data from a table/query rather than relying on code. It's up to date and pretty much garauteed to be acurate (based on input data).

[garyj]

Fair enough, but hard to start when I haven't a clue. I chanced upon the Aggregate Query, and it seems like it is what I want. It should be able to calculate the total false statements with a small adjustment and group them by KidsID. This is 75% of what I am after. But I still ran into some problems for which I need help. If seems to not like any value that has a blank in the 'Total' row of the Access Query in Design form. Here is a pic, and following is the SQL view.

12236

SELECT Orders.KidsID, [Fname] & " " & [Lname] AS Name, CampKids.CampID, Sum(Sum(IIf([ItemID]=5 And Not [Received],1,0))) AS [Need Shirts], Sum(Sum(IIf([ItemID]=12 And Not [Received],1,0))) AS [Need DVDs], Sum(Sum(IIf([ItemID]=17 And Not [Received],1,0))) AS [Need Journals]
FROM CampKids INNER JOIN Orders ON CampKids.KidsID = Orders.KidsID
GROUP BY Orders.KidsID
HAVING (((CampKids.CampID)=GetCmp()));

It says I can't have an aggregate function under expressions. I need the total of [Received] = False for each camper.
Before it was giving me trouble for having any column that didn't have a function.

Gary

[garyj]

Okay.. solved 100% of it - and posting it here - maybe it will be helpful to someone.
I chose to use two queries.

The first is a Select Query which selects the information I want and contains the expression functions which I can't do in the other query.
The second is the Aggregate Query, which uses the information first gained by the Select Query.
It works like a charm.

Select Query

SELECT Orders.KidsID, [Fname] & " " & [Lname] AS Name, _
CampKids.CampID, _
Cabins.Cabin, _
CampKids.Balance,_
IIf(([ItemID]=5 Or [ItemID]=58) And Not [Received],1) AS TShirt, _
IIf(([ItemID]=12 Or [ItemID]=53) And Not [Received],1) AS DVD, _
IIf(([ItemID]=17 Or [ItemID]=54) And Not [Received],1) AS Journal, _
Orders.Received FROM Cabins RIGHT _
JOIN (Orders INNER JOIN CampKids _
ON Orders.KidsID = CampKids.KidsID) _
ON Cabins.CbnID = CampKids.CabinID
WHERE (((CampKids.CampID)=GetCmp()) AND ((Orders.Received)=False));


Aggregate Query

SELECT [HasBalance-Select].KidsID, _
First([HasBalance-Select].Name) AS FirstOfName,_
First([HasBalance-Select].Cabin) AS FirstOfCabin, _
First([HasBalance-Select].Balance) AS FirstOfBalance, _
Sum([HasBalance-Select].TShirt) AS SumOfTShirt, _
Sum([HasBalance-Select].DVD) AS SumOfDVD, _
Sum([HasBalance-Select].Journal) AS SumOfJournal
FROM [HasBalance-Select]
GROUP BY [HasBalance-Select].KidsID;

I had changed some of the field names as well.