bobby6055
asked on
Grouping problem
I created multiple queries in the order listed below then came up with a funtioning Nested query "QryNESTED'
"qryAllMainDataInDateRange
.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)
PROBLEM:
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
to...
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.
GetH-LNos-V1.zip
"qryAllMainDataInDateRange
.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)
PROBLEM:
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
to...
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.
GetH-LNos-V1.zip
ASKER
Mark:
I wish you the same for the new year.
This new question is a followup of the other one you assisted with and I will appreciate your further assistance on the topic.
I know you like challenges and I am sure it's going to be fun.
Regards
Bobby
I wish you the same for the new year.
This new question is a followup of the other one you assisted with and I will appreciate your further assistance on the topic.
I know you like challenges and I am sure it's going to be fun.
Regards
Bobby
ASKER
Mark:
I am concerned that the grouping may prevent me from getting what I want.
If that is the case, I will have to split the conditions (CCon=True versus CCon=False) with separate queries and then join the final results with a UNION query.
What do you think?
Additionally, please add three fields (Field1,Field2 and Field3) to "tblMain" with data or no data in the newly created fields.
When you finally evolve new queries please add those newly created fields as part of your suggested Query(s) . The reason is that I have other fields I intend to add to your new suggested - placing the three fields in your final query(s) will let me know where to add my own fields when I finally got everything working.
The three newly created fields should be textypes.
I am concerned that the grouping may prevent me from getting what I want.
If that is the case, I will have to split the conditions (CCon=True versus CCon=False) with separate queries and then join the final results with a UNION query.
What do you think?
Additionally, please add three fields (Field1,Field2 and Field3) to "tblMain" with data or no data in the newly created fields.
When you finally evolve new queries please add those newly created fields as part of your suggested Query(s) . The reason is that I have other fields I intend to add to your new suggested - placing the three fields in your final query(s) will let me know where to add my own fields when I finally got everything working.
The three newly created fields should be textypes.
ASKER
Mark:
I am off to Church with my family (1st Sunday of the new year), I will respond to any of your questions later.
I am off to Church with my family (1st Sunday of the new year), I will respond to any of your questions later.
OK, will look at it, and get together some questions for your return. Have fun.
ASKER
ok
ASKER
I am back
Try this,
1 main query straight from details...
PARAMETERS [Forms]![ReportDateRange]!
SELECT tblGroupS.GType, Q3.BNo, Q3.LNo, tblSCType.SCType, Q3.HouseNo, Q3.StreetName, Q3.QFlow, Q3.QFlow/4 as QFlowA, iif(Q3.CCon=-1,"TRUE","FAL
From ((((
(
SELECT DISTINCT tblMain.GroupID, tblMain.BNo, tblMain.LNo, tblMain.SCTypeID, tblMain.HouseNo, tblMain.StreetName, tblMain.STypeID, tblMain.QFlow, tblMain.CCon, tblMain.SerialNo, tblMain.LocalID
FROM tblMain
WHERE (((tblMain.GroupID) In ('STU','LTR','ASL')) AND ((tblMain.SCTypeID) In ('MTH','GYM','EAT')) AND ((tblMain.Appdate) Between [Forms]![ReportDateRange]!
union
SELECT tblMain.GroupID, tblMain.BNo, GetLnos([bno],[sctypeID],[
tblMain.SCTypeID,GetHnos([
FROM tblMain
WHERE (((tblMain.GroupID) In ('STU','LTR','ASL')) AND ((tblMain.SCTypeID) In ('MTH','GYM','EAT')) AND ((tblMain.Appdate) Between [Forms]![ReportDateRange]!
group by tblMain.GroupID, tblMain.BNo, GetLnos([bno],[sctypeID],[
tblMain.SCTypeID,GetHnos([
) Q3
INNER JOIN tblGroupS ON Q3.GroupID = tblGroupS.GroupID )
INNER JOIN tblLocality ON Q3.LocalID = tblLocality.LocalID )
INNER JOIN tblSCType ON Q3.SCTypeID = tblSCType.SCTypeID)
INNER JOIN tblSType ON Q3.STypeID = tblSType.STypeID)
1 main query straight from details...
PARAMETERS [Forms]![ReportDateRange]!
SELECT tblGroupS.GType, Q3.BNo, Q3.LNo, tblSCType.SCType, Q3.HouseNo, Q3.StreetName, Q3.QFlow, Q3.QFlow/4 as QFlowA, iif(Q3.CCon=-1,"TRUE","FAL
From ((((
(
SELECT DISTINCT tblMain.GroupID, tblMain.BNo, tblMain.LNo, tblMain.SCTypeID, tblMain.HouseNo, tblMain.StreetName, tblMain.STypeID, tblMain.QFlow, tblMain.CCon, tblMain.SerialNo, tblMain.LocalID
FROM tblMain
WHERE (((tblMain.GroupID) In ('STU','LTR','ASL')) AND ((tblMain.SCTypeID) In ('MTH','GYM','EAT')) AND ((tblMain.Appdate) Between [Forms]![ReportDateRange]!
union
SELECT tblMain.GroupID, tblMain.BNo, GetLnos([bno],[sctypeID],[
tblMain.SCTypeID,GetHnos([
FROM tblMain
WHERE (((tblMain.GroupID) In ('STU','LTR','ASL')) AND ((tblMain.SCTypeID) In ('MTH','GYM','EAT')) AND ((tblMain.Appdate) Between [Forms]![ReportDateRange]!
group by tblMain.GroupID, tblMain.BNo, GetLnos([bno],[sctypeID],[
tblMain.SCTypeID,GetHnos([
) Q3
INNER JOIN tblGroupS ON Q3.GroupID = tblGroupS.GroupID )
INNER JOIN tblLocality ON Q3.LocalID = tblLocality.LocalID )
INNER JOIN tblSCType ON Q3.SCTypeID = tblSCType.SCTypeID)
INNER JOIN tblSType ON Q3.STypeID = tblSType.STypeID)
ASKER
Mark:
I have examined this query thoroughly and found a minor problem.
I modified tblMain and set [CCon] for tall records for BNo 2444 to True.
I added "TTR" to your queries WHERECLAUSE as shown here.
WHERE (((tblMain.GroupID) In ('TTR','STU','LTR','ASL'))
Upon running your query there was a problem which I have described in the attached excel file. I also placed my suggested correct result in Sheet1 and Sheet2
I am also attaching a new sample db here to reflect the modifications I have just made with your suggested query in it.
GetH-LNos-V2.mdb
Grouping-Problem-Notes.xls
I have examined this query thoroughly and found a minor problem.
I modified tblMain and set [CCon] for tall records for BNo 2444 to True.
I added "TTR" to your queries WHERECLAUSE as shown here.
WHERE (((tblMain.GroupID) In ('TTR','STU','LTR','ASL'))
Upon running your query there was a problem which I have described in the attached excel file. I also placed my suggested correct result in Sheet1 and Sheet2
I am also attaching a new sample db here to reflect the modifications I have just made with your suggested query in it.
GetH-LNos-V2.mdb
Grouping-Problem-Notes.xls
Sorry about that, GROUPID was not part of the function....
So, added it in to the query where it calls the functions , and also into the functions....
So, the function params now has "groupid as string" as the first argument, and the where clause becomes :
mySQL = mySQL & " WHERE Groupid = '" & Groupid & "' AND BNo=" & myBNo & " AND SCTypeID='" & mySC & "' AND StypeID='" & myS & "' AND [CCon]=" & myGrp & " and SerialNo='" & mySNo & "' AND"
See attached...
GetH-LNos-V3.mdb
So, added it in to the query where it calls the functions , and also into the functions....
So, the function params now has "groupid as string" as the first argument, and the where clause becomes :
mySQL = mySQL & " WHERE Groupid = '" & Groupid & "' AND BNo=" & myBNo & " AND SCTypeID='" & mySC & "' AND StypeID='" & myS & "' AND [CCon]=" & myGrp & " and SerialNo='" & mySNo & "' AND"
See attached...
GetH-LNos-V3.mdb
ASKER
Mark:
I subjected the query to another test by adding HConNo and HConSize (as per note under ID: 26165642 ---> for Filed1, Field 2, Field3 etc.
I modified Record ID 462 by changing the HConSize from 6in to 10in
similarly, I changed the STypeId from CM to SA
I also amended my function to include HConSize such that it becomes.....
mySQL = mySQL & " WHERE Groupid = '" & Groupid & "' AND BNo=" & myBNo & " AND SCTypeID='" & mySC & "' AND StypeID='" & myS & "' AND [CCon]=" & myGrp & " AND [HConSize]=" & myHCS & " and SerialNo='" & mySNo & "' AND"
I have attached a summary of the result expected in the attached Excel file document.
Is it possible to further isolate and exclude a record from being grouped into [LNos] and [Hnos] if the fields characteristics changed?....this is my question in mind for the new modification?
Grouping-Problem-Notes2.xls
New-Query.doc
I subjected the query to another test by adding HConNo and HConSize (as per note under ID: 26165642 ---> for Filed1, Field 2, Field3 etc.
I modified Record ID 462 by changing the HConSize from 6in to 10in
similarly, I changed the STypeId from CM to SA
I also amended my function to include HConSize such that it becomes.....
mySQL = mySQL & " WHERE Groupid = '" & Groupid & "' AND BNo=" & myBNo & " AND SCTypeID='" & mySC & "' AND StypeID='" & myS & "' AND [CCon]=" & myGrp & " AND [HConSize]=" & myHCS & " and SerialNo='" & mySNo & "' AND"
I have attached a summary of the result expected in the attached Excel file document.
Is it possible to further isolate and exclude a record from being grouped into [LNos] and [Hnos] if the fields characteristics changed?....this is my question in mind for the new modification?
Grouping-Problem-Notes2.xls
New-Query.doc
ASKER
correction.
It should be....
....similarly, I changed record Id 651 with respect to the "STypeId" from "CM" to "SA"
It should be....
....similarly, I changed record Id 651 with respect to the "STypeId" from "CM" to "SA"
Rats, forgot that you wanted Field1, Field2, Field3 as added fields to tblmain...
The way that it was originally written was to list each column (for the function), and those columns basically corresponded to the group by...
Having a hard coded list of columns is needed for the group by and that is where the problems occur. Modify the group by and it means that the concatenation functions also have to change...
While it might be possible to parse the system tables (MSysObjects and MSysQueries) really dont hink we want to go there...
Might be best to have one additional function like "Concat_tblMain" where gicen a row id, it will concatenate various columns into a delimitted string and then we use that function in place of a list of column names...
It still means that it is possible to get the Group By clause out of synch, but means there is only two places to change them (ie the group by list of columns and the function), not four or five (ie the arguments and the where clause in both functions and then both the group be and calling those functions)...
Experience will say that these types of constructs (functions calling functions trying dynamic group bys) take an eternity to return the results...
Let me think more on it - might be a few new constructs, but might be worthwhile if all we have to do is to chhange the one function for determining which columns determine how to concatenate...
Does that sound like the type of thing ? Or, did you have something else in mind ?
The way that it was originally written was to list each column (for the function), and those columns basically corresponded to the group by...
Having a hard coded list of columns is needed for the group by and that is where the problems occur. Modify the group by and it means that the concatenation functions also have to change...
While it might be possible to parse the system tables (MSysObjects and MSysQueries) really dont hink we want to go there...
Might be best to have one additional function like "Concat_tblMain" where gicen a row id, it will concatenate various columns into a delimitted string and then we use that function in place of a list of column names...
It still means that it is possible to get the Group By clause out of synch, but means there is only two places to change them (ie the group by list of columns and the function), not four or five (ie the arguments and the where clause in both functions and then both the group be and calling those functions)...
Experience will say that these types of constructs (functions calling functions trying dynamic group bys) take an eternity to return the results...
Let me think more on it - might be a few new constructs, but might be worthwhile if all we have to do is to chhange the one function for determining which columns determine how to concatenate...
Does that sound like the type of thing ? Or, did you have something else in mind ?
ASKER
Mark:
I am open to new ideas. Let's give it a try.
In your last posted query, I need to have your query display "tblSType.Stype instead of tbLMain.STypeID, Could you jst repost your lat query with SType listed in the Column instead of STypeID?...I tried it but did not succeed.
I am open to new ideas. Let's give it a try.
In your last posted query, I need to have your query display "tblSType.Stype instead of tbLMain.STypeID, Could you jst repost your lat query with SType listed in the Column instead of STypeID?...I tried it but did not succeed.
ASKER
Mark:
I discover a snag.
I am unable to pull any records for December 2009. Please type in 12/01/09 and 12/10/09 for example, there was no record.
tblMain contain all the records for December but cnnot populate them with your query. I dont know why?
I discover a snag.
I am unable to pull any records for December 2009. Please type in 12/01/09 and 12/10/09 for example, there was no record.
tblMain contain all the records for December but cnnot populate them with your query. I dont know why?
ASKER
Open tblMain and sort records of tblMain.AppDate in descending order. You will find records dated up till 12/14/09.
I removed the filter "WHERE BNo = 2444" from your assisted query. I still could not populate the records with your query.
I removed the filter "WHERE BNo = 2444" from your assisted query. I still could not populate the records with your query.
OK, those date things are probably due to NULL in the serial number - it is not checked (for null) within the function, but it is when calling the function - means it wont match.
Putting in Stype is no problems.
Have spent quite some time looking and playing with ways to make it easier with groups and group by...
Think the best I came up with was creating a new query, concatenating the columns used to determine the "treat as if it were one" and then using that view in both the select and in the function. Also, pass in the column name to the function so there is only one function to maintain.
Works reasonably well but not real fast. So if it is for user interaction, then it will be a bit slow - say a few seconds / maybe more depending on real table sizes. If it is for reporting purposes, then a pregnant pause is a little more tolerable...
Using the above means is that the function will unlikely change - just the view (meaning query) with the concatenated columns, and the group by within the select. Trouble is (and this is where the time is lost) the function also uses that query to get the distinct LNo or HouseNo.
The other way is to make it faster by having just the one function, but changing that and the select statement.
I will play a little bit more and try to come up with a fast and yet minimise the number of places you have to change to define the "grouping and the Group by"
Putting in Stype is no problems.
Have spent quite some time looking and playing with ways to make it easier with groups and group by...
Think the best I came up with was creating a new query, concatenating the columns used to determine the "treat as if it were one" and then using that view in both the select and in the function. Also, pass in the column name to the function so there is only one function to maintain.
Works reasonably well but not real fast. So if it is for user interaction, then it will be a bit slow - say a few seconds / maybe more depending on real table sizes. If it is for reporting purposes, then a pregnant pause is a little more tolerable...
Using the above means is that the function will unlikely change - just the view (meaning query) with the concatenated columns, and the group by within the select. Trouble is (and this is where the time is lost) the function also uses that query to get the distinct LNo or HouseNo.
The other way is to make it faster by having just the one function, but changing that and the select statement.
I will play a little bit more and try to come up with a fast and yet minimise the number of places you have to change to define the "grouping and the Group by"
ASKER
Mark:
Thanks for trying
Thanks for trying
ASKER
I placed this (Nz(SerialNo,"") in the function, I received several errors.
ASKER
The errors shows after I ran the query so I removed the Nz(SerialNo,"") from the function.
I also tried the Nz(SerialNo)
I also tried the Nz(SerialNo)
ASKER
Mark:
Hi,
Is it possible to correct the Query you posted under ID: 26170593 such that it can populate records for 12 /01/09 - 12/14/09 ?
Hi,
Is it possible to correct the Query you posted under ID: 26170593 such that it can populate records for 12 /01/09 - 12/14/09 ?
Yes, I dont have that problem...
the nz(serialno,"") if inside the quoted string needs to be nz(serialno,"""") ie you have to encaspsulate the double quotes so they are "escaped" and understood within that string...
Sorry I havent been able to get back to you - been tied up with other matters.
Will be looking into this tonight - have pretty much exhausted the different methods to minimise maintenance and still give some performance.
the nz(serialno,"") if inside the quoted string needs to be nz(serialno,"""") ie you have to encaspsulate the double quotes so they are "escaped" and understood within that string...
Sorry I havent been able to get back to you - been tied up with other matters.
Will be looking into this tonight - have pretty much exhausted the different methods to minimise maintenance and still give some performance.
ASKER
Mark:
Problem:
(1). I dont see how the serialno field could be the problem because the [serialNo] field is
not part of the query.
(2). From your query.......this part of your query is giving me some problem while trying to convert to my office db.
SELECT qry.Course, qry.Grp, sum(qry.countofcourseid) AS CountOfCourseID
and.....
AS qry
GROUP BY qry.Course, qry.Grp;
Error:
After converting your query for office use, I am getting an error that "qry" is not found.
QUESTION:
Would you mind posting the work around solution of your query under ID: 26170593
with a view to avoiding the use of "qry"
Problem:
(1). I dont see how the serialno field could be the problem because the [serialNo] field is
not part of the query.
(2). From your query.......this part of your query is giving me some problem while trying to convert to my office db.
SELECT qry.Course, qry.Grp, sum(qry.countofcourseid) AS CountOfCourseID
and.....
AS qry
GROUP BY qry.Course, qry.Grp;
Error:
After converting your query for office use, I am getting an error that "qry" is not found.
QUESTION:
Would you mind posting the work around solution of your query under ID: 26170593
with a view to avoiding the use of "qry"
Sure, one better, updated the database following that post with the Style name (but excludes hconsize). It used Q3 in place of QRY
Checked the date range and got BNo 3487 and a couple of others...
Used 01 Dec 2009 - 10 Dec 2009 as the dates.
GetH-LNos-V3a.mdb
Checked the date range and got BNo 3487 and a couple of others...
Used 01 Dec 2009 - 10 Dec 2009 as the dates.
GetH-LNos-V3a.mdb
ASKER
Mark:
I will give this a thorough check and give you a feedback on it.
I will give this a thorough check and give you a feedback on it.
ASKER
Mark:
I just noticed that my comment under ID: 26282320 belong to another post. I will go to the appropriate thread and re-post that comment there.
Cheers !!!
Bobby
I just noticed that my comment under ID: 26282320 belong to another post. I will go to the appropriate thread and re-post that comment there.
Cheers !!!
Bobby
ASKER
Mark:
I tested the sample you sent, it works fine but when I placed the query in the office db there was no data populated for date ranges between 12/01/ to 12/14/09.
What other modifications do I need to do in the office db table in order to get it to work?
Office db table has the same table characteristics as tblMain. What am I doing wrong?
I tested the sample you sent, it works fine but when I placed the query in the office db there was no data populated for date ranges between 12/01/ to 12/14/09.
What other modifications do I need to do in the office db table in order to get it to work?
Office db table has the same table characteristics as tblMain. What am I doing wrong?
Ummm... Dunno...
Just the query and the function were changed...
Might be worth trying just the inside queries as standalones - see if they return stuff directly from tblMain. If they do, then it is the INNER JOINS that essentially remove the data, in which case, can make them left outer joins, and see if that makes the difference.
Just the query and the function were changed...
Might be worth trying just the inside queries as standalones - see if they return stuff directly from tblMain. If they do, then it is the INNER JOINS that essentially remove the data, in which case, can make them left outer joins, and see if that makes the difference.
Have your tried changing those INNER JOIN's to LEFT OUTER JOIN's ?
Down the bottom, you will see the list on other tables being joined...
An inner join says that rows from both tables must exist for the criteria used in the ON clause
An outer join says that rows do not have to exist in the table being "outer" or "Left" joined to for the criteria used in the ON clause
e.g.
if tblmain has RID = 3 and LOCALID = 1
and tbllocal has LOCALID = 1 and LOCALITY = "L" then...
select tblmain.RID, tblmain.localid, tbllocal.locality
from tblmain
inner join tbllocal on tbllocal.localID = tblmain.localID
will give you a result : 3, 1, L
But if tblmain has RID = 3 and LOCALID = 2
and tbllocal has LOCALID = 1 and LOCALITY = "L" then...
select tblmain.RID, tblmain.localid, tbllocal.locality
from tblmain
inner join tbllocal on tbllocal.localID = tblmain.localID
will give no result, because localid = 2 does not exist in both places where as
select tblmain.RID, tblmain.localid, tbllocal.locality
from tblmain
left outer join tbllocal on tbllocal.localID = tblmain.localID
will give you a result : 3, 2, NULL
So, where ever you have a column which does not have to have a value (like spaces or NULL) then because it is optional to populate, then you do need an Outer Join.
Hope that now makes sense, and hope it is the basis of your problem...
Down the bottom, you will see the list on other tables being joined...
An inner join says that rows from both tables must exist for the criteria used in the ON clause
An outer join says that rows do not have to exist in the table being "outer" or "Left" joined to for the criteria used in the ON clause
e.g.
if tblmain has RID = 3 and LOCALID = 1
and tbllocal has LOCALID = 1 and LOCALITY = "L" then...
select tblmain.RID, tblmain.localid, tbllocal.locality
from tblmain
inner join tbllocal on tbllocal.localID = tblmain.localID
will give you a result : 3, 1, L
But if tblmain has RID = 3 and LOCALID = 2
and tbllocal has LOCALID = 1 and LOCALITY = "L" then...
select tblmain.RID, tblmain.localid, tbllocal.locality
from tblmain
inner join tbllocal on tbllocal.localID = tblmain.localID
will give no result, because localid = 2 does not exist in both places where as
select tblmain.RID, tblmain.localid, tbllocal.locality
from tblmain
left outer join tbllocal on tbllocal.localID = tblmain.localID
will give you a result : 3, 2, NULL
So, where ever you have a column which does not have to have a value (like spaces or NULL) then because it is optional to populate, then you do need an Outer Join.
Hope that now makes sense, and hope it is the basis of your problem...
ASKER
Mark:
I tested the outer-join-query and there is a small problem with way the LNo / HouseNo were written.
In order for you to understand the problem, I modified record id 746 by setting the [CCon] field to "false".
Your query was expected to list the corresponding LNo and HouseNo on a separate line not grouped with others (see BNo 3487 group)
.....but this did not happen .
I understand that this is a complex query and I am trying to get the correct result which am still having some problems with.
I am not too sure if the problem was as a result of the outer join. (Please see the attached Excel file)
Regards
Bobby
Slight-Query-problem.xls
ASKER
Mark:
In a related question, think you utilized something like this......do you think this might work for this particular thread to correct the problem. I tried it on my own and I got errors.
iif( NZ([tblMain].[CCon],False)
or... this one
iif( NZ([tblMain].[CCon],False)
Here is a lnk to that sample.
https://www.experts-exchange.com/questions/25006963/Grouping-Question.html#26152827
Regards
Bobby
In a related question, think you utilized something like this......do you think this might work for this particular thread to correct the problem. I tried it on my own and I got errors.
iif( NZ([tblMain].[CCon],False)
or... this one
iif( NZ([tblMain].[CCon],False)
Here is a lnk to that sample.
https://www.experts-exchange.com/questions/25006963/Grouping-Question.html#26152827
Regards
Bobby
Yep, remember that example.. Just going through the question / database now will have a look...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Mark:
Could you please attach your sample here for me to compare with mine. I am getting some mixed result here. May be I have a wrong db since I have worked with several.
Could you please attach your sample here for me to compare with mine. I am getting some mixed result here. May be I have a wrong db since I have worked with several.
ASKER
Mark:
I got exactly the same result. I though something else was wrong with my side of db.
There are only two categories of STypeID, "SA" and "ST". The result I am getting fromthe query is a
different from what is expected.
See te attached EXcel file for better explanation
Why am I not getting the diesired result. Your last assisted query seems to have resolved the question in my mind but the resullt is not quite up to my expectations.
Is there a problem with tblMain or any other tables in the sample db?
Slight-Query-problem-v2.xls
I got exactly the same result. I though something else was wrong with my side of db.
There are only two categories of STypeID, "SA" and "ST". The result I am getting fromthe query is a
different from what is expected.
See te attached EXcel file for better explanation
Why am I not getting the diesired result. Your last assisted query seems to have resolved the question in my mind but the resullt is not quite up to my expectations.
Is there a problem with tblMain or any other tables in the sample db?
Slight-Query-problem-v2.xls
The combos of serial number and Stype break up the two that you have in the spreadsheet into the four that I get in the query.
Should we be ignoring serial number when we are grouping ?
Should we be ignoring serial number when we are grouping ?
ASKER
Yes SerilaNo field is part of the grouping. So also is SType and judging bt tblMain records...There are oly 3 categories of Stype "SA", "ST" and "CM" while serialNo field has the same value in all. IN this particular tblMain records. THe BNo should be broken and arranged into two rows:
BNo LNo ...................... STypeID............... SerialNo
3487 121...................... 142 ST ................. <--------------- 1st row
3487 121.......................
3487 36 ST ................ <--------------- 3rd Row
3487 142 SA ................ <--------------- 4th Row
In other words...based on SType and SerialNo...the above four result should be obtained, not more.
Currently, we are getting 6 rows instead of 4.
BNo LNo ...................... STypeID............... SerialNo
3487 121...................... 142 ST ................. <--------------- 1st row
3487 121.......................
3487 36 ST ................ <--------------- 3rd Row
3487 142 SA ................ <--------------- 4th Row
In other words...based on SType and SerialNo...the above four result should be obtained, not more.
Currently, we are getting 6 rows instead of 4.
ASKER
Mark:
I removed all the values in the SerialNo fields just for curiousity sake and I now recieved the correct 4 rows as shown above.
It's been quite an "EXHAUTIVE" thread and thank yu for hanging in there with me.
Again, you were very patient with me all through even though the question so complex, could be very frustrating and you are the "DIE HARD" type of an expert. Bravo !!!
Regards
Bobby
I removed all the values in the SerialNo fields just for curiousity sake and I now recieved the correct 4 rows as shown above.
It's been quite an "EXHAUTIVE" thread and thank yu for hanging in there with me.
Again, you were very patient with me all through even though the question so complex, could be very frustrating and you are the "DIE HARD" type of an expert. Bravo !!!
Regards
Bobby
ASKER
"Mark" is an SQL Legend and a very patient expert.
Superb job....Ride on...
"More grease to your Elbow" !!!
Superb job....Ride on...
"More grease to your Elbow" !!!
ASKER
Mark:
You must have wondered why I created several posts to address the Complex Query problem. I think sometimes ths is very necessary to explore various options available to creating a very accurate report. Each of the question I created is unique though may seem similar - They are actually different questions - each addressing a specific scenerio.
I hope you did not mind that we went this far in resolving possible problems that I may encounter now or in the nearest future. I may not use all the answers obtained from those threads but I am convinced more than ever that I have gotten superb answers with your assistance.
Thanks
Bobby
You must have wondered why I created several posts to address the Complex Query problem. I think sometimes ths is very necessary to explore various options available to creating a very accurate report. Each of the question I created is unique though may seem similar - They are actually different questions - each addressing a specific scenerio.
I hope you did not mind that we went this far in resolving possible problems that I may encounter now or in the nearest future. I may not use all the answers obtained from those threads but I am convinced more than ever that I have gotten superb answers with your assistance.
Thanks
Bobby
No worries, happy to help, and enjoy those heavily data bound "challenges" keeps ones mind active :)
You might want to consider creating a few more attributes on those Course tables and such like. For example, add a new column "CommonGroup" and for Physics / Chemistry you can add a value "Physics, Chemistry" then with the table links, we simply have to use the new column instead of using the IIF all the time.
Understood that they were different questions, and that was a little confusing, but not sure you could have approached it any other way, it would have been very very confusing if they were presented at the same time, so think your approach was fine.
And anytime, there will be experts here, happy and willing to help...
You might want to consider creating a few more attributes on those Course tables and such like. For example, add a new column "CommonGroup" and for Physics / Chemistry you can add a value "Physics, Chemistry" then with the table links, we simply have to use the new column instead of using the IIF all the time.
Understood that they were different questions, and that was a little confusing, but not sure you could have approached it any other way, it would have been very very confusing if they were presented at the same time, so think your approach was fine.
And anytime, there will be experts here, happy and willing to help...
ASKER
Mark_wills:
I have a followup question on this particular post and it's not as brain tasking as this one, I will appreciate your assistance on it when listed.
Please let me know if you are available so I can place the link to the question here.
Regards
Bobby
I have a followup question on this particular post and it's not as brain tasking as this one, I will appreciate your assistance on it when listed.
Please let me know if you are available so I can place the link to the question here.
Regards
Bobby
Sure... thing... always up for a good challenge. Just have to take the kids to soccer and stuff, so post away and I will be there in a few hours...
ASKER
Mark:
Here is the link to the new question.
https://www.experts-exchange.com/questions/26188491/Grouping-Question.html
Here is the link to the new question.
https://www.experts-exchange.com/questions/26188491/Grouping-Question.html
Hope you had a great New Year !
Will download the zip and start looking. Sounds like another "fun" challenge :)