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
--
" 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! "
KARL DEWEY said:
Check for hard returns that may have gotten in due to posting and pasting.
Start removing part of criteria to see where problem is.
--
KARL DEWEY
Build a little - Test a little
vepha said:
Second query returns with no records
((
--
" 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! "
KARL DEWEY said:
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"));
--
KARL DEWEY
Build a little - Test a little
:
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
--
" 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.
--
KARL 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.
--
" 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
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"));
--
KARL DEWEY
Build a little - Test a little
:
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
