 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2008group by name having 2 different groups ( weeks )
Hi everyone, table : Name , Date how to make a query to group records by name having countofname for last 4 weeks as one group and for last 12 weeks ( without last 4 weeks ) as a second group. I hope I could tell. Thanks for any help. Regards vepha  Signature" Dont mess around so much, do something usefull like making the world a better place, like helping people who really need help, like being the ace instead of an empty face! " Make a query that will do the last 4 weeks and another query that will do the other weeks. Combine these two queries with a UNION query. SignatureJerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > Hi everyone, > [quoted text clipped - 8 lines] > Regards > vepha Thanks Jerry for your Reply. Signature" Dont mess around so much, do something usefull like making the world a better place, like helping people who really need help, like being the ace instead of an empty face! " > Make a query that will do the last 4 weeks and another query that will do the > other weeks. Combine these two queries with a UNION query. [quoted text clipped - 11 lines] > > Regards > > vepha Here is one way -- SELECT IIf([Date open] Between Date() And DateAdd("w",-4,Date()),"Last 4 weeks",IIf([Date open] Between DateAdd("w",-5,Date()) And DateAdd("w",-12,Date()),"12 weeks ago until 4 weeks ago","0")) AS Expr1, Sum([Change Requests].x) AS SumOfx FROM [Change Requests] GROUP BY IIf([Date open] Between Date() And DateAdd("w",-4,Date()),"Last 4 weeks",IIf([Date open] Between DateAdd("w",-5,Date()) And DateAdd("w",-12,Date()),"12 weeks ago until 4 weeks ago","0")) HAVING (((IIf([Date open] Between Date() And DateAdd("w",-4,Date()),"Last 4 weeks",IIf([Date open] Between DateAdd("w",-5,Date()) And DateAdd("w",-12,Date()),"12 weeks ago until 4 weeks ago","0")))<>"0")); SignatureKARL DEWEY Build a little - Test a little > Hi everyone, > [quoted text clipped - 8 lines] > Regards > vepha Thanks Karl for your reply. I tried but it returns with no records. I migth doing something wrong. I will check it again. Signature" Dont mess around so much, do something usefull like making the world a better place, like helping people who really need help, like being the ace instead of an empty face! " > Here is one way -- > SELECT IIf([Date open] Between Date() And DateAdd("w",-4,Date()),"Last 4 [quoted text clipped - 21 lines] > > Regards > > vepha Post your SQL statement. SignatureKARL DEWEY Build a little - Test a little > Thanks Karl for your reply. I tried but it returns with no records. I migth > doing something wrong. I will check it again. [quoted text clipped - 24 lines] > > > Regards > > > vepha First Query : qry_groupweeks SELECT DISTINCTROW Count(tbl_qdat.Kunde) AS CountOfKunde, tbl_qdat.Erfassungsdatum, tbl_qdat.Nummer, tbl_qdat.Kunde, tbl_qdat.KundenNr, tbl_qdat.Art, tbl_qdat.Problemkreis, tbl_qdat.[Produkte-Nr], tbl_qdat.Kurztext, tbl_qdat.Verursacher, tbl_qdat.Beschreibung, tbl_qdat.[Verursachende Abteilung], tbl_qdat.Aussteller, tbl_qdat.[Anlyse/Entscheid], tbl_qdat.[Analyse durch], tbl_qdat.Auswertung, tbl_qdat.[Fall abgeschlossen am], tbl_qdat.ABTBeschreibung, tbl_qdat.Schadensumme FROM tbl_qdat GROUP BY tbl_qdat.Erfassungsdatum, tbl_qdat.Nummer, tbl_qdat.Kunde, tbl_qdat.KundenNr, tbl_qdat.Art, tbl_qdat.Problemkreis, tbl_qdat.[Produkte-Nr], tbl_qdat.Kurztext, tbl_qdat.Verursacher, tbl_qdat.Beschreibung, tbl_qdat.[Verursachende Abteilung], tbl_qdat.Aussteller, tbl_qdat.[Anlyse/Entscheid], tbl_qdat.[Analyse durch], tbl_qdat.Auswertung, tbl_qdat.[Fall abgeschlossen am], tbl_qdat.ABTBeschreibung, tbl_qdat.Schadensumme HAVING (((tbl_qdat.Kunde)=IIf([Kunde] Is Not Null,[Kunde],"leer")) AND ((tbl_qdat.KundenNr)=IIf([KundenNr] Is Not Null,[KundenNr],"leer"))) ORDER BY tbl_qdat.Kunde; Second Query to group by weeks zwdtafgroupbyweeks SELECT IIf([Erfassungsdatum] Between Date() And DateAdd("w",-4,Date()),"Last 4 weeks",IIf([Date open] Between DateAdd("w",-5,Date()) And DateAdd("w",-12,Date()),"12 weeks ago until 4 weeks ago","0")) AS Expr1, Sum( qry_groupweeks.CountOfKunde) AS SumOfCountOfKunde FROM qry_groupweeks GROUP BY IIf([Erfassungsdatum] Between Date() And DateAdd("w",-4,Date()),"Last 4 weeks",IIf([Erfassungsdatum] Between DateAdd("w",-5,Date()) And DateAdd("w",-12,Date()),"12 weeks ago until 4 weeks ago","0")) HAVING (((IIf([Erfassungsdatum] Between Date() And DateAdd("w",-4,Date()),"Last 4 weeks",IIf([Erfassungsdatum] Between DateAdd("w",-5,Date()) And DateAdd("w",-12,Date()),"12 weeks ago until 4 weeks ago","0")))<>"0")); Thanks Signature" Dont mess around so much, do something usefull like making the world a better place, like helping people who really need help, like being the ace instead of an empty face! " > Post your SQL statement. > [quoted text clipped - 26 lines] > > > > Regards > > > > vepha I found a couple of things. In the query qry_groupweeks your criteria -- HAVING (((tbl_qdat.Kunde)=IIf([Kunde] Is Not Null,[Kunde],"leer")) AND ((tbl_qdat.KundenNr)=IIf([KundenNr] Is Not Null,[KundenNr],"leer"))) will not work. The false results set criteria as "leer" for a null field. This will never return any records. Second -- SELECT IIf([Erfassungsdatum] Between Date() And DateAdd("w",-4,Date()),"Last 4 weeks", IIf([Date open] Between DateAdd("w",-5,Date()) And DateAdd("w",-12,Date()),"12 weeks ago until 4 weeks ago","0")) AS Expr1, you left one of my field names in the SQL. Use these --- SELECT DISTINCTROW Count(tbl_qdat.Kunde) AS CountOfKunde, tbl_qdat.Erfassungsdatum, tbl_qdat.Nummer, tbl_qdat.Kunde, tbl_qdat.KundenNr, tbl_qdat.Art, tbl_qdat.Problemkreis, tbl_qdat.[Produkte-Nr], tbl_qdat.Kurztext, tbl_qdat.Verursacher, tbl_qdat.Beschreibung, tbl_qdat.[Verursachende Abteilung], tbl_qdat.Aussteller, tbl_qdat.[Anlyse/Entscheid], tbl_qdat.[Analyse durch], tbl_qdat.Auswertung, tbl_qdat.[Fall abgeschlossen am], tbl_qdat.ABTBeschreibung, tbl_qdat.Schadensumme FROM tbl_qdat GROUP BY tbl_qdat.Erfassungsdatum, tbl_qdat.Nummer, tbl_qdat.Kunde, tbl_qdat.KundenNr, tbl_qdat.Art, tbl_qdat.Problemkreis, tbl_qdat.[Produkte-Nr], tbl_qdat.Kurztext, tbl_qdat.Verursacher, tbl_qdat.Beschreibung, tbl_qdat.[Verursachende Abteilung], tbl_qdat.Aussteller, tbl_qdat.[Anlyse/Entscheid], tbl_qdat.[Analyse durch], tbl_qdat.Auswertung, tbl_qdat.[Fall abgeschlossen am], tbl_qdat.ABTBeschreibung, tbl_qdat.Schadensumme ORDER BY tbl_qdat.Kunde; SELECT IIf([Erfassungsdatum] Between Date() And DateAdd("w",-4,Date()),"Last 4 weeks",IIf([Erfassungsdatum] Between DateAdd("w",-5,Date()) And DateAdd("w",-12,Date()),"12 weeks ago until 4 weeks ago","0")) AS Expr1, Sum(qry_groupweeks.CountOfKunde) AS SumOfCountOfKunde FROM qry_groupweeks WHERE (((IIf([Erfassungsdatum] Between Date() And DateAdd("w",-4,Date()),"Last 4 weeks",IIf([Erfassungsdatum] Between DateAdd("w",-5,Date()) And DateAdd("w",-12,Date()),"12 weeks ago until 4 weeks ago","0")))<>"0")) GROUP BY IIf([Erfassungsdatum] Between Date() And DateAdd("w",-4,Date()),"Last 4 weeks",IIf([Erfassungsdatum] Between DateAdd("w",-5,Date()) And DateAdd("w",-12,Date()),"12 weeks ago until 4 weeks ago","0")); SignatureKARL DEWEY Build a little - Test a little > First Query : > qry_groupweeks [quoted text clipped - 68 lines] > > > > > Regards > > > > > vepha Second query returns with no records :((( Signature" Dont mess around so much, do something usefull like making the world a better place, like helping people who really need help, like being the ace instead of an empty face! " > I found a couple of things. > In the query qry_groupweeks your criteria -- [quoted text clipped - 115 lines] > > > > > > Regards > > > > > > vepha Check for hard returns that may have gotten in due to posting and pasting. Start removing part of criteria to see where problem is. SignatureKARL DEWEY Build a little - Test a little > Second query returns with no records :((( > [quoted text clipped - 117 lines] > > > > > > > Regards > > > > > > > vepha Thanks a lot for your help Karl. Is there a way for me to thank you?. Can i donate something for you somewhere? I have done it like below. it seems it works as i wanted. first query SELECT DISTINCTROW Count(tbl_qdat.Kunde) AS CountOfKunde, tbl_qdat.Erfassungsdatum, tbl_qdat.Nummer, tbl_qdat.Kunde, tbl_qdat.KundenNr, tbl_qdat.Art, tbl_qdat.Problemkreis, tbl_qdat.[Produkte-Nr], tbl_qdat.Kurztext, tbl_qdat.Verursacher, tbl_qdat.Beschreibung, tbl_qdat.[Verursachende Abteilung], tbl_qdat.Aussteller, tbl_qdat.[Anlyse/Entscheid], tbl_qdat.[Analyse durch], tbl_qdat.Auswertung, tbl_qdat.[Fall abgeschlossen am], tbl_qdat.ABTBeschreibung, tbl_qdat.Schadensumme FROM tbl_qdat GROUP BY tbl_qdat.Erfassungsdatum, tbl_qdat.Nummer, tbl_qdat.Kunde, tbl_qdat.KundenNr, tbl_qdat.Art, tbl_qdat.Problemkreis, tbl_qdat.[Produkte-Nr], tbl_qdat.Kurztext, tbl_qdat.Verursacher, tbl_qdat.Beschreibung, tbl_qdat.[Verursachende Abteilung], tbl_qdat.Aussteller, tbl_qdat.[Anlyse/Entscheid], tbl_qdat.[Analyse durch], tbl_qdat.Auswertung, tbl_qdat.[Fall abgeschlossen am], tbl_qdat.ABTBeschreibung, tbl_qdat.Schadensumme HAVING (((tbl_qdat.Kunde)=IIf([Kunde] Is Not Null,[Kunde],"leer"))) ORDER BY tbl_qdat.Kunde; second SELECT Sum(qry_groupweeks.CountOfKunde) AS AnzahlFehler, qry_groupweeks.Kunde, IIf([Erfassungsdatum] Between Date() And DateAdd("d",-20,Date()),"Letzte 20 Tagen","0") AS Letzte20Tagen, IIf([Erfassungsdatum] Between DateAdd("d",-21,Date()) And DateAdd("d",-40,Date()),"Letzte 40-21 Tagen","0") AS Letzte40o20Tagen, IIf([Erfassungsdatum] Between DateAdd("d",-41,Date()) And DateAdd("d",-60,Date()),"Letzte 60-41 Tagen","0") AS Letzte60o40Tagen, IIf([Erfassungsdatum] Between DateAdd("d",-61,Date()) And DateAdd("d",-80,Date()),"Letzte 80-61 Tagen","0") AS Letzte80o60Tagen, IIf([Erfassungsdatum] Between DateAdd("d",-81,Date()) And DateAdd("d",-100,Date()),"Letzte 100-81 Tagen","0") AS Letzte100o80Tagen, IIf([Erfassungsdatum] Between DateAdd("d",-101,Date()) And DateAdd("d",-120,Date()),"Letzte 120-101 Tagen","0") AS Letzte120o100Tagen FROM qry_groupweeks GROUP BY qry_groupweeks.Kunde, IIf([Erfassungsdatum] Between Date() And DateAdd("d",-20,Date()),"Letzte 20 Tagen","0"), IIf([Erfassungsdatum] Between DateAdd("d",-21,Date()) And DateAdd("d",-40,Date()),"Letzte 40-21 Tagen","0"), IIf([Erfassungsdatum] Between DateAdd("d",-41,Date()) And DateAdd("d",-60,Date()),"Letzte 60-41 Tagen","0"), IIf([Erfassungsdatum] Between DateAdd("d",-61,Date()) And DateAdd("d",-80,Date()),"Letzte 80-61 Tagen","0"), IIf([Erfassungsdatum] Between DateAdd("d",-81,Date()) And DateAdd("d",-100,Date()),"Letzte 100-81 Tagen","0"), IIf([Erfassungsdatum] Between DateAdd("d",-101,Date()) And DateAdd("d",-120,Date()),"Letzte 120-101 Tagen","0") HAVING (((Sum(qry_groupweeks.CountOfKunde))>[mehr als wie viel Fehler?])); Thanks again, Regards vepha Signature" Dont mess around so much, do something usefull like making the world a better place, like helping people who really need help, like being the ace instead of an empty face! " > Check for hard returns that may have gotten in due to posting and pasting. > Start removing part of criteria to see where problem is. [quoted text clipped - 120 lines] > > > > > > > > Regards > > > > > > > > vepha |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.