Gasman
Enthusiastic Amateur
- Local time
- Today, 10:37
- Joined
- Sep 21, 2011
- Messages
- 14,038
Hi everyone,
I have been tasked with collating call data from 3 different systems. Currently only working with two for creation/ testing purposes. Systems are called BT and CR
I am not storing every call, but creating and importing daily summary data from the call logs each week.
In my tblcallSummary table I have a field to identify which system the data comes from.
I have a form that shows that data in two subforms, filtered by employee combobox.
Currently I have two subforms sfrmBTSummary and sfrmCRSummary supplying that data. each has a criteria of "BT" or "CR" in the filter property of the form.
For the CrossTab (weekly summary) I am only working with BT data at present, so the query looks like
and the crosstab query then looks like
However the crosstab needs to be dynamic, so the IN clause of that query is modified when I open the subform for that data. All appears to be working fine.
However is it possible not to have to duplicate, even triplicate queries and forms at all?
What I am thinking of is a form that will handle any of the systems data depending on a parameter/filter of BT,CR or CX (the other system)?
I am aware I can supply parameters to a qdf for a form/report that supplies data to that object, but for the queries, they are one query removed from the crosstab form.?
What I am trying to avoid is a change to a form (or report later on) that has to done in three places each time?
What would be my best approach for this issue please?
TIA
I have been tasked with collating call data from 3 different systems. Currently only working with two for creation/ testing purposes. Systems are called BT and CR
I am not storing every call, but creating and importing daily summary data from the call logs each week.
In my tblcallSummary table I have a field to identify which system the data comes from.
I have a form that shows that data in two subforms, filtered by employee combobox.
Currently I have two subforms sfrmBTSummary and sfrmCRSummary supplying that data. each has a criteria of "BT" or "CR" in the filter property of the form.
For the CrossTab (weekly summary) I am only working with BT data at present, so the query looks like
Code:
SELECT tblWeekEndDate.WeekEndDate, tblCallSummary.CallSystem, tblCallSummary.Caller, tblCallSummary.WeekNumber, Sum(tblCallSummary.CallSecs) AS SumOfCallSecs
FROM tblWeekEndDate INNER JOIN tblCallSummary ON tblWeekEndDate.WeekNumber = tblCallSummary.WeekNumber
GROUP BY tblWeekEndDate.WeekEndDate, tblCallSummary.CallSystem, tblCallSummary.Caller, tblCallSummary.WeekNumber
HAVING (((tblCallSummary.CallSystem)="BT"))
ORDER BY tblWeekEndDate.WeekEndDate, tblCallSummary.Caller;
Code:
TRANSFORM Sum([SumOfCallSecs]/86400) AS Duration
SELECT qryWeekCallSummary.WeekEndDate
FROM qryWeekCallSummary
GROUP BY qryWeekCallSummary.WeekEndDate
PIVOT qryWeekCallSummary.Caller IN ("Bradley Forrest","Chad Carter","Funeral Plans","Natalie Rowe","Nathan Davies","Sam Overfield");
However is it possible not to have to duplicate, even triplicate queries and forms at all?
What I am thinking of is a form that will handle any of the systems data depending on a parameter/filter of BT,CR or CX (the other system)?
I am aware I can supply parameters to a qdf for a form/report that supplies data to that object, but for the queries, they are one query removed from the crosstab form.?
What I am trying to avoid is a change to a form (or report later on) that has to done in three places each time?
What would be my best approach for this issue please?
TIA