I created multiple queries in the order listed below then came up with a funtioning Nested query "QryNESTED'
", "qryGroupData", "qryAlmostThere and "qryFinal" ...in that order
.with a view to gouping and pulling records based on simliar data charcteristics
and WHERE [CCon] = True) and..
........using this whereclause:
(((tblMain.GroupID) In ('STU','LTR','ASL')) And ((tblMain.SCTypeID) In ('MTH','GYM','EAT'))
----- (for all records of BNo, SCTypeID, SType, HConSize, SerialNo, and ([CCon] = True)).
I have added two functions: GetLnos and GetHNos (see the module)
When I launched the NESTED query or the "qryFinal", I found a problem (see the note in the attached zip filefor the explanation of the problem):
The office policy is to:
Agregate (count) records of similar charaxteristics into "1" if [CCon] is True
otherwise, list all records of similar or non-similar characteriistic separately where ([CCon] = False)
I am not too sure if my functions are the problem or whether I need to change my logic in the NESTED Query or qryFinal in order to get the desired result.
or....perhaps by changing my query from IIf([Grp]=-1,"TRUE","FALSE
") AS CCon
IIF(Ccon=-1,GetLNos(), LNo) as LNos and ....
IIF(Ccon=-1,GetHNos(), HNo) as HNos
What must I do in order to get the desired result?
I have attached here a working sample db with current problem and further explanation of the problem in a zip file
To test, simply open "qryNested" or "qryFinal" and type in 07/01/09 and 07/31/09 as Begin and End Dates.