Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 465
  • Last Modified:

Grouping problem

I created multiple queries in the order listed below then came up with a funtioning Nested query "QryNESTED'

"qryAllMainDataInDateRange", "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)
 
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") AS CCon
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
0
bobby6055
Asked:
bobby6055
  • 29
  • 16
1 Solution
 
Mark WillsTopic AdvisorCommented:
Hi bobby6055,

Hope you had a great New Year !

Will download the zip and start looking. Sounds like another "fun" challenge :)
0
 
bobby6055Author Commented:
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
0
 
bobby6055Author Commented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
bobby6055Author Commented:
Mark:
I am off to Church with my family (1st Sunday of the new year), I will respond to any of your questions later.
0
 
Mark WillsTopic AdvisorCommented:
OK, will look at it, and get together some questions for your return. Have fun.
0
 
bobby6055Author Commented:
ok
0
 
bobby6055Author Commented:
I am back
0
 
Mark WillsTopic AdvisorCommented:
Try this,

1 main query straight from details...

PARAMETERS [Forms]![ReportDateRange]![BeginDate] DateTime, [Forms]![ReportDateRange]![EndDate] DateTime;

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","FALSE") as Ccon, tblLocality.Locality, Q3.SerialNo

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]![BeginDate] And [Forms]![ReportDateRange]![EndDate] And (tblMain.Appdate) Is Not Null) and nz(tblmain.ccon,0)= 0)

   union

   SELECT  tblMain.GroupID, tblMain.BNo,  GetLnos([bno],[sctypeID],[stypeID],[CCon],Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate),
                 tblMain.SCTypeID,GetHnos([bno],[sctypeID],[stypeID],[CCon],Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate), tblMain.StreetName, tblMain.STypeID, sum(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]![BeginDate] And [Forms]![ReportDateRange]![EndDate] And (tblMain.Appdate) Is Not Null) and nz(tblmain.ccon,0)= -1)
   group by  tblMain.GroupID, tblMain.BNo,  GetLnos([bno],[sctypeID],[stypeID],[CCon],Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate),
                   tblMain.SCTypeID,GetHnos([bno],[sctypeID],[stypeID],[CCon],Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate), tblMain.StreetName, tblMain.STypeID, tblMain.CCon,  tblMain.SerialNo, tblMain.LocalID
) 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)

0
 
bobby6055Author Commented:
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
0
 
Mark WillsTopic AdvisorCommented:
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
0
 
bobby6055Author Commented:
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
0
 
bobby6055Author Commented:
correction.

It should be....
....similarly, I changed  record Id 651 with respect to the "STypeId" from "CM" to "SA"
0
 
Mark WillsTopic AdvisorCommented:
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 ?

0
 
bobby6055Author Commented:
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.
0
 
bobby6055Author Commented:
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?
0
 
bobby6055Author Commented:
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.
0
 
Mark WillsTopic AdvisorCommented:
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"

0
 
bobby6055Author Commented:
Mark:
Thanks for trying
0
 
bobby6055Author Commented:
I placed this (Nz(SerialNo,"") in the function, I received several errors.
0
 
bobby6055Author Commented:
The errors shows after I ran the query so I removed the Nz(SerialNo,"") from the function.
I also tried the Nz(SerialNo)
0
 
bobby6055Author Commented:
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 ?
0
 
Mark WillsTopic AdvisorCommented:
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.
0
 
bobby6055Author Commented:
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"
0
 
Mark WillsTopic AdvisorCommented:
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
0
 
bobby6055Author Commented:
Mark:
I will give this a thorough check and give you a feedback on it.
0
 
bobby6055Author Commented:
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
0
 
bobby6055Author Commented:
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?
 
0
 
Mark WillsTopic AdvisorCommented:
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.
0
 
Mark WillsTopic AdvisorCommented:
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...


0
 
bobby6055Author Commented:

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
0
 
bobby6055Author Commented:
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)=True,-1,tblmain.rid) as ccongroup, sum( iif( NZ([tblMain].[CCon],False)=True,0,1)) + max( iif( NZ([tblMain].[CCon],False)=True,1,0)) AS CountOfRID, tblmain.groupid as individual_groupid
 
or... this one
iif( NZ([tblMain].[CCon],False)=True,-1,tblmain.rid) as ccongroup, sum( iif( NZ([tblMain].[CCon],False)=True,0,1)) + max( iif( NZ([tblMain].[CCon],False)=True,1,0)) AS CountOfRID, tblmain.groupid as individual_groupid
Here is a lnk to that sample.

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_25006963.html#26152827
 
Regards
Bobby
0
 
Mark WillsTopic AdvisorCommented:
Yep, remember that example.. Just going through the question / database now will have a look...
0
 
Mark WillsTopic AdvisorCommented:
Well, seems that the big part about rows appearing or not was the left join.

But you have also discovered another problem and that is the nullability of CCON

So, need to use nz(ccon,0) in most places - certainly in the functions for Lno and Hno.

Interesting to note that 142 will still appear as both a group by item (ie ccon = true) and as a standalone (ie ccon = false) because of row ID 770, so was a good choice.

Anyway, have a look at the query below, and dont forget to add the nz(ccon,0) into those functions ie :

mySQL = mySQL & " WHERE Groupid = '" & myG & "' AND BNo=" & myBNo & " AND SCTypeID='" & mySC & "' AND StypeID='" & myS & "' AND nz([CCon],0)=" & myGrp & " and nz(SerialNo,"""")='" & mySNo & "' AND"




PARAMETERS [Forms]![ReportDateRange]![BeginDate] DateTime, [Forms]![ReportDateRange]![EndDate] DateTime;

SELECT tblGroupS.GType, Q3.BNo, Q3.LNo, tblSCType.SCType, tblSType.SType, Q3.HouseNo, Q3.StreetName, Q3.QFlow, Q3.QFlow/4 AS QFlowA, iif(Q3.CCon=-1,"TRUE","FALSE") AS Ccon, tblLocal.Local, Q3.SerialNo
FROM ((((

    SELECT  DISTINCT tblMain.GroupID, tblMain.BNo, tblMain.SCTypeID, tblMain.LNo,  tblMain.HouseNo, 
            tblMain.StreetName, tblMain.STypeID, tblMain.QFlow, [CCon], tblMain.SerialNo, tblMain.LocalID
    FROM    tblMain
    WHERE   (((tblMain.GroupID)In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')) AND ((tblMain.SCTypeID)  In ("MTH","LA","RC"))
    AND     ((tblMain.Appdate) Between [Forms]![ReportDateRange]![BeginDate] 
    AND     [Forms]![ReportDateRange]![EndDate] And (tblMain.Appdate) Is Not Null) and nz(tblmain.ccon,0)= 0)

    UNION

    SELECT  tblMain.GroupID, tblMain.BNo,  tblMain.SCTypeID, 
            GetLnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate),
            GetHnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate), 
            tblMain.StreetName, tblMain.STypeID, sum(tblMain.QFlow), tblMain.CCon, tblMain.SerialNo, tblMain.LocalID
    FROM    tblMain
    WHERE  (((tblMain.GroupID)In ('BOE','LT','DDC','DEC','DEP','DOC','DOH','DOS','DOT','EDT','STU','MTA','PAU','ASL','TTR')) AND ((tblMain.SCTypeID)  In ("MTH","LA","RC"))
    AND     ((tblMain.Appdate) Between [Forms]![ReportDateRange]![BeginDate] AND [Forms]![ReportDateRange]![EndDate] 
    AND      (tblMain.Appdate) Is Not Null) 
    AND       nz(tblmain.ccon,0)= -1)

    GROUP BY  tblMain.GroupID, tblMain.BNo, tblMain.SCTypeID,
              GetLnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate), 
              GetHnos(Groupid,[bno],[sctypeID],[stypeID],nz([CCon],0),Nz(SerialNo,""),Forms!ReportDateRange!BeginDate,Forms!ReportDateRange!EndDate), 
              tblMain.StreetName, tblMain.STypeID, tblMain.CCon,  tblMain.SerialNo, tblMain.LocalID

) AS Q3 
LEFT JOIN tblGroupS ON Q3.GroupID = tblGroupS.GroupID) 
LEFT JOIN tblLocal ON Q3.LocalID = tblLocal.LocalID) 
LEFT JOIN tblSCType ON Q3.SCTypeID = tblSCType.SCTypeID) 
LEFT JOIN tblSType ON Q3.STypeID = tblSType.STypeID;

Open in new window

0
 
bobby6055Author Commented:
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.
0
 
Mark WillsTopic AdvisorCommented:
0
 
bobby6055Author Commented:
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
0
 
Mark WillsTopic AdvisorCommented:
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 ?
0
 
bobby6055Author Commented:
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.......................143                       SA                            ..................      <--------------- 2nd Row
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.
0
 
bobby6055Author Commented:
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
0
 
bobby6055Author Commented:
"Mark" is an SQL Legend and a very patient expert.

Superb job....Ride on...
"More grease to your Elbow" !!!
0
 
bobby6055Author Commented:
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
0
 
Mark WillsTopic AdvisorCommented:
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...

0
 
bobby6055Author Commented:
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
0
 
Mark WillsTopic AdvisorCommented:
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...
0
 
bobby6055Author Commented:
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 29
  • 16
Tackle projects and never again get stuck behind a technical roadblock.
Join Now