Best approach to avoid duplication? (1 Viewer)

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
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;
and the crosstab query then looks like
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 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
 

isladogs

MVP / VIP
Local time
Today, 10:37
Joined
Jan 14, 2017
Messages
18,186
Hi Gasman

Maybe I'm missing something, but if you want to have a combo box to select the call system, then why can't you use:

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)=[COLOR="Red"] '"  & Me.cboCallSystem & "'[/COLOR]))
  ORDER BY tblWeekEndDate.WeekEndDate, tblCallSummary.Caller;

If this works for you, your IN clause may need to be modified accordingly???
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:37
Joined
Sep 21, 2011
Messages
14,038
Sorry Ridders, that is what I will entually write, just testing with BT data for now.
That is the query that supplies data to the crosstab query. That is what I meant by one query removed from the form.?
If it was the actual query for the form/report, I know how to amend to suit, but how do I do it for an query indirect fom the form?

Query one - need parameter
Query two - crosstab query, data from query one
Form - displays crosstab data in a subform. Depnding on a combo? supply parameter for query 1.

3 systems, so if I wish to see all 3 subforms at once, rather than one at a time, could this be done without duplicating objects like having to have

Form_Crosstab_BTdata
Form_Crosstab_CRData
Form_Crosstab_CXdata

all identical except for the data in them?

Hmm, just wondering if I can get all the data with one query?
Thought there was a reason why I needed to take it in steps to get to the crosstab result. :confused:
 

isladogs

MVP / VIP
Local time
Today, 10:37
Joined
Jan 14, 2017
Messages
18,186

Gasman

Enthusiastic Amateur
Local time
Today, 10:37
Joined
Sep 21, 2011
Messages
14,038
Ok, I now recall the reason I did it this way. It was due to trying to sum the call duration.
To diisplay it in hh:nn:ss, I first need to have the values summed, I cannot do it on the fly in one query?
So query 1 just sums the call duration, and query two then just divides that amount by 86400 and the control on the form is formatted as hh:nn:ss

Does that make sense.?
 

isladogs

MVP / VIP
Local time
Today, 10:37
Joined
Jan 14, 2017
Messages
18,186
I've just checked something similar in one of my programs where I count the total number of logins and total time logged in by day or week or month etc.

I also used a 2 stage query process for this purpose, possibly for the same reason as you.
1. Calculate the totals grouped by day/week/month etc
2. Create crosstab SQL based on these for plotting charts
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:37
Joined
Sep 21, 2011
Messages
14,038
Hi Ridders,

Thank you for checking.
I believe now that I could move the criteria for the system to the crosstab query.
It was just that I was taking it one step at a time due to my inexperience.
I'm going to give that a go when I have a moment.
 

Users who are viewing this thread

Top Bottom