I may have gotten myself in trouble when I half stumbled/ half rushed through the building of my queries and overall reporting design to meet some deadlines.
I pull data from Siebel and then do a number of things to manage the data in the Access 2002 database. On a level of 1-10 in Access I am a solid 3. I've gotten help from a previous post to get this far but realize I need to just put everything I've got out here on the table to get this one worked out.
Here are the two queries I'm stuck on:
qryActivityReport2
SELECT [Created By], -Sum([Type]="Call - Inbound") AS CallIn, -Sum([Type]="Call - Outbound") AS CallOut, -Sum([Type]="Email - Inbound") AS EmailIn, -Sum([Type]="Email - Outbound") AS EmailOut, (SELECT Count(*) from tblActivity r2 Where ISNULL([SR #]) and ([Type] = "Call - Inbound") and R1.[Created By] = r2.[Created By]) AS Quickcall
FROM tblActivity AS R1
WHERE ([Created By] IN (Select Badge from tblNames))
GROUP BY [Created By];
qrySRReport
SELECT [Owner], -Sum([Source]="Phone") AS SRPhone, -Sum([Source]="Email") AS SREmail, Sum((SELECT Count(*) from tblSR r2 Where ([Resolution] = "Redirect - Outside of SomeCo") and (r1.[Owner] = r2.[Owner])) + (SELECT Count(*) from tblSR r3 Where ([Resolution] = "Redirect - Out of Warranty") and (r1.[Owner] = r3.[Owner]))+ (SELECT Count(*) from tblSR r4 Where ([Resolution] = "Redirect - Internal To SomeCo") and (r1.[Owner] = r4.[Owner]))) AS SRRedirects
FROM tblSR AS r1
WHERE ([Owner] IN (Select Badge from tblNames))
GROUP BY [Owner];
Both reports work thank Almighty. But I have...
Two problems.
1. I need to merge the two queries, or otherwise compile the results of both into one table that logs the date ranges being used and the calculated fields, one row per person/owner.
DateStart, Datestop, Owner, Callin, Callout, SRTotal, etc...
1/1/2004, 1/20/2004, 0001, 5, 10, 30, ....
2. I need to use a date range from my form, frmReports that has two fields, both (mm/dd/yyyy) and need to be used for selecting the dates in each query to use for the calculations. I tried this WHERE statement for qrySRReport but it failed, producted 0 results.
WHERE ([Owner] IN (Select Badge from tblNames)) AND ([Agent Opened] >= forms!frmReports!dateStart) and ([Agent Opened] <= forms!frmReports!dateStop)
I'm having some extreme problems with my dates even in a simple way, if this helps.
select *
from tblSR
where [Agent Opened] = #1/20/2004#;
that returns 0 results, when there are about 50 If I go look. All date columns/table fields are in a Short Date format, although the raw input is including the 1/1/2001 1:20 PM
Any general design recommendations or specific code help will be greatly appreciated. Please note that I only post after I've "utilized" a good half day searching for an answer online and in help!
Thank you!
I pull data from Siebel and then do a number of things to manage the data in the Access 2002 database. On a level of 1-10 in Access I am a solid 3. I've gotten help from a previous post to get this far but realize I need to just put everything I've got out here on the table to get this one worked out.
Here are the two queries I'm stuck on:
qryActivityReport2
SELECT [Created By], -Sum([Type]="Call - Inbound") AS CallIn, -Sum([Type]="Call - Outbound") AS CallOut, -Sum([Type]="Email - Inbound") AS EmailIn, -Sum([Type]="Email - Outbound") AS EmailOut, (SELECT Count(*) from tblActivity r2 Where ISNULL([SR #]) and ([Type] = "Call - Inbound") and R1.[Created By] = r2.[Created By]) AS Quickcall
FROM tblActivity AS R1
WHERE ([Created By] IN (Select Badge from tblNames))
GROUP BY [Created By];
qrySRReport
SELECT [Owner], -Sum([Source]="Phone") AS SRPhone, -Sum([Source]="Email") AS SREmail, Sum((SELECT Count(*) from tblSR r2 Where ([Resolution] = "Redirect - Outside of SomeCo") and (r1.[Owner] = r2.[Owner])) + (SELECT Count(*) from tblSR r3 Where ([Resolution] = "Redirect - Out of Warranty") and (r1.[Owner] = r3.[Owner]))+ (SELECT Count(*) from tblSR r4 Where ([Resolution] = "Redirect - Internal To SomeCo") and (r1.[Owner] = r4.[Owner]))) AS SRRedirects
FROM tblSR AS r1
WHERE ([Owner] IN (Select Badge from tblNames))
GROUP BY [Owner];
Both reports work thank Almighty. But I have...
Two problems.
1. I need to merge the two queries, or otherwise compile the results of both into one table that logs the date ranges being used and the calculated fields, one row per person/owner.
DateStart, Datestop, Owner, Callin, Callout, SRTotal, etc...
1/1/2004, 1/20/2004, 0001, 5, 10, 30, ....
2. I need to use a date range from my form, frmReports that has two fields, both (mm/dd/yyyy) and need to be used for selecting the dates in each query to use for the calculations. I tried this WHERE statement for qrySRReport but it failed, producted 0 results.
WHERE ([Owner] IN (Select Badge from tblNames)) AND ([Agent Opened] >= forms!frmReports!dateStart) and ([Agent Opened] <= forms!frmReports!dateStop)
I'm having some extreme problems with my dates even in a simple way, if this helps.
select *
from tblSR
where [Agent Opened] = #1/20/2004#;
that returns 0 results, when there are about 50 If I go look. All date columns/table fields are in a Short Date format, although the raw input is including the 1/1/2001 1:20 PM
Any general design recommendations or specific code help will be greatly appreciated. Please note that I only post after I've "utilized" a good half day searching for an answer online and in help!
Thank you!