Best approach to avoid duplication?

Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 02:17
Joined
Sep 21, 2011
Messages
17,013
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
 
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???
 
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:
 
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.?
 
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
 
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.
 
You must log in or register to reply here.

Similar threads

W
Hello, New here
Replies
2
Views
291
DHookom
DHookom
D
Not sure how best to display the information
2
Replies
37
Views
1,176
Mike Krailo
Mike Krailo
LarryE
Solved Archiving records instead of deleting
Replies
12
Views
895
Pat Hartman
P
J
stream data to excel
Replies
8
Views
1,244
anhdavid
A
T
odbc error when using query from excel
Replies
3
Views
548
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom