Sorguda Veri Sayma İşlemi

1 2
25/08/2017, 15:21

ozanakkaya

Merhaba, örneklendirmenize göre ek sefer kısmının sıfır olması gerekmiyor mu? ACT_UDFCHAR05 alanı da sayılacak mı?
25/08/2017, 15:38

ertans

(25/08/2017, 15:21)ozanakkaya yazdı: Merhaba, örneklendirmenize göre ek sefer kısmının sıfır olması gerekmiyor mu? ACT_UDFCHAR05 alanı da sayılacak mı?

Evet sayın ozanakkaya sayılacak
25/08/2017, 15:55

ertans

Sayın ozanakkaya yeni database i ekliyorum.
25/08/2017, 16:01

ozanakkaya

Merhaba,

Sorgunun Sql kodunu aşağıdaki ile değiştirerek deneyiniz.

INSERT INTO tbl_konsolide ( ACT_START, ACT_EVENT, EVT_DESC, add_text, yayinlansin, tahliye, iptal, gecikme, eksefer )
SELECT tbl_olay.ACT_START, tbl_olay.ACT_EVENT, tbl_olay.EVT_DESC, tbl_olay.add_text, tbl_olay.yayinlansin, DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND  ACT_UDFCHAR04 = '" & "Tahliye" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")+DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND  ACT_UDFCHAR05 = '" & "Tahliye" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#") AS tahliye, DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR04 = '" & "Iptal" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")+DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR05 = '" & "Iptal" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#") AS iptal, DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR04 = '" & "Gecikme" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")+DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR05 = '" & "Gecikme" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#") AS gecikme, DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR04 = '" & "Ek sefer" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")+DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR05 = '" & "Ek sefer" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#") AS eksefer
FROM tbl_olay
GROUP BY tbl_olay.ACT_START, tbl_olay.ACT_EVENT, tbl_olay.EVT_DESC, tbl_olay.add_text, tbl_olay.yayinlansin, DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND  ACT_UDFCHAR04 = '" & "Tahliye" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")+DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND  ACT_UDFCHAR05 = '" & "Tahliye" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#"), DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR04 = '" & "Iptal" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")+DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR05 = '" & "Iptal" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#"), DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR04 = '" & "Gecikme" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")+DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR05 = '" & "Gecikme" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#"), DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR04 = '" & "Ek sefer" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")+DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR05 = '" & "Ek sefer" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")
HAVING (((tbl_olay.ACT_START)=[Forms]![frm_tarih_sec]![ilktarih]));
25/08/2017, 16:34

ertans

(25/08/2017, 16:01)ozanakkaya yazdı: Merhaba,

Sorgunun Sql kodunu aşağıdaki ile değiştirerek deneyiniz.

INSERT INTO tbl_konsolide ( ACT_START, ACT_EVENT, EVT_DESC, add_text, yayinlansin, tahliye, iptal, gecikme, eksefer )
SELECT tbl_olay.ACT_START, tbl_olay.ACT_EVENT, tbl_olay.EVT_DESC, tbl_olay.add_text, tbl_olay.yayinlansin, DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND  ACT_UDFCHAR04 = '" & "Tahliye" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")+DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND  ACT_UDFCHAR05 = '" & "Tahliye" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#") AS tahliye, DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR04 = '" & "Iptal" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")+DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR05 = '" & "Iptal" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#") AS iptal, DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR04 = '" & "Gecikme" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")+DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR05 = '" & "Gecikme" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#") AS gecikme, DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR04 = '" & "Ek sefer" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")+DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR05 = '" & "Ek sefer" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#") AS eksefer
FROM tbl_olay
GROUP BY tbl_olay.ACT_START, tbl_olay.ACT_EVENT, tbl_olay.EVT_DESC, tbl_olay.add_text, tbl_olay.yayinlansin, DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND  ACT_UDFCHAR04 = '" & "Tahliye" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")+DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND  ACT_UDFCHAR05 = '" & "Tahliye" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#"), DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR04 = '" & "Iptal" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")+DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR05 = '" & "Iptal" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#"), DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR04 = '" & "Gecikme" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")+DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR05 = '" & "Gecikme" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#"), DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR04 = '" & "Ek sefer" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")+DCount("sirano","tbl_olay","[ACT_EVENT]= '" & [ACT_EVENT] & "' AND ACT_UDFCHAR05 = '" & "Ek sefer" & "' AND [ACT_START]=#" & Format([ACT_START],"mm\/dd\/yyyy") & "#")
HAVING (((tbl_olay.ACT_START)=[Forms]![frm_tarih_sec]![ilktarih]));

Sayın ozanakkaya çok teşekkürler gayet iyi çalışıyor.
1 2