/* qry_subtrains&locos */
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT dbo_Configuration.Ref1 AS owner, dbo_Client.Ref3 AS DIV, 1 AS Bill, dbo_Call.CallNumber, dbo_Call.LogDatTim,
 dbo_Call.LastName, dbo_Call.ConfigId, dbo_Call.Logon, dbo_Call.CallSubject1, dbo_Call.CallSubject2,
 dbo_Call.CallSubject3, dbo_Call.SupportGroup INTO tTrain
FROM (dbo_Call LEFT JOIN dbo_Configuration ON dbo_Call.ConfigCode = dbo_Configuration.ConfigCode)
 INNER JOIN dbo_Client ON dbo_Call.ClientCode = dbo_Client.ClientCode
WHERE (((dbo_Configuration.Ref1) Not Like "Z*" And (dbo_Configuration.Ref1) Not Like "E:*")
 AND ((dbo_Call.LogDatTim)>=[StartDate] And (dbo_Call.LogDatTim)<=DateAdd("d",1,[EndDate]))
 AND ((dbo_Call.CallSubject1) Not Like "W-*" And (dbo_Call.CallSubject1) Not Like "R-*"
 And (dbo_Call.CallSubject1) Not Like "C-*")
 AND ((dbo_Configuration.ConfigClassificationUnique)=1 Or (dbo_Configuration.ConfigClassificationUnique)=4));

/* qry_locations */
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT dbo_Logon.Ref3 AS Owner, dbo_Client.Ref3 AS DIV, 1 AS Bill, dbo_Call.CallNumber, dbo_Call.LogDatTim,
 dbo_Call.LastName, dbo_Call.ConfigId, dbo_Call.Logon, dbo_Call.CallSubject1, dbo_Call.CallSubject2,
 dbo_Call.CallSubject3, dbo_Call.SupportGroup INTO tLocation
FROM ((dbo_Call LEFT JOIN dbo_Logon ON dbo_Call.LogonCode = dbo_Logon.LogonCode)
 LEFT JOIN dbo_Configuration ON dbo_Call.ConfigCode = dbo_Configuration.ConfigCode)
 INNER JOIN dbo_Client ON dbo_Call.ClientCode = dbo_Client.ClientCode
WHERE (((dbo_Call.LogDatTim)>=[StartDate] And (dbo_Call.LogDatTim)<=DateAdd("d",1,[EndDate]))
 AND ((dbo_Call.CallSubject1) Not Like "W-*" And (dbo_Call.CallSubject1) Not Like "R-*"
 And (dbo_Call.CallSubject1) Not Like "C-*") AND ((dbo_Configuration.ConfigClassificationUnique)<>1));

/* qry_Name */
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT dbo_Client.Ref3 AS Owner, dbo_Client.Ref3 AS DIV, 1 AS Bill, dbo_Call.CallNumber, dbo_Call.LogDatTim,
 dbo_Call.LastName, dbo_Call.ConfigId, dbo_Call.Logon, dbo_Call.CallSubject1, dbo_Call.CallSubject2,
 dbo_Call.CallSubject3, dbo_Call.SupportGroup INTO tName
FROM dbo_Call INNER JOIN dbo_Client ON dbo_Call.ClientCode = dbo_Client.ClientCode
WHERE (((dbo_Call.LogDatTim)>=[StartDate] And (dbo_Call.LogDatTim)<=DateAdd("d",1,[EndDate]))
 AND ((dbo_Call.CallSubject1) Not Like "W-*" And (dbo_Call.CallSubject1) Not Like "R-*" And (dbo_Call.CallSubject1) Not Like "C-*"));

/* qry_all */
SELECT IIf([assign]="HILLSIDE","CME",
IIf([ASSIGN]="BAYSIDE","CME",
IIf([assign]="FREIGHT VICTORIA","FRT",
IIf([assign]="V/LINE PASS","VLP",
IIf([assign]="ARTC","ART",[assign]))))) AS Owner1,
IIf(Len(Trim([tTrain].[Owner]))>0,[tTrain].[Owner],
IIf(Len(Trim([tLocation].[Owner]))>0,[tLocation].[Owner],[tName].[Owner])) AS Assign, 
tName.Owner, tTrain.owner, tLocation.Owner, tName.CallNumber, tName.LogDatTim, tName.Logon,
 tName.ConfigId, tName.CallSubject1, tName.CallSubject2, tName.CallSubject3, tName.LastName
FROM (tName LEFT JOIN tLocation ON tName.CallNumber = tLocation.CallNumber) LEFT JOIN tTrain ON tName.CallNumber = tTrain.CallNumber;

/* qry_Billing_append */
INSERT INTO [Quetzal Faults] ( Owner1, Cust_ID, bill, CallNumber, LogDatTim, LastName, Configuration,
 Location, CallSubject1, CallSubject2, CallSubject3 )
SELECT First(qry_all.Owner1) AS FirstOfOwner1, First(IIf([CALLSUBJECT1]="PHONE D-TERM",[OWNER1],
IIf([CALLSUBJECT1]="EXCHANGE",[OWNER1],
IIf([CALLSUBJECT1]="PHONE-I/QUARTZ",[OWNER1],
IIf([OWNER1]="PHONE-C'MANDER",[OWNER1],
IIf([CALLSUBJECT1]="PHONE QMASTER",[OWNER1],
IIf([CALLSUBJECT1]="SECURITY",[OWNER1],
IIf([OWNER1] & [CALLSUBJECT1]="FRTRADIO BASE EQ","FRT",
IIf([CALLSUBJECT1]="RADIO BASE EQ","MEG",
IIf([OWNER1]="NXB","MEG",
IIf([OWNER1]="CGE","MEG",
IIf([OWNER1]="CME","MEG",
IIf([OWNER1]="ART","ART",
IIf([OWNER1]="CMT","CMT",
[OWNER1])))))))))))))) AS Cust_ID, First(1) AS bill, First(qry_all.CallNumber) AS FirstOfCallNumber,
 Format([LogDatTim],"dd/mm/yyyy") AS GroupDate, First(qry_all.LastName) AS FirstOfLastName,
 qry_all.ConfigId AS Configuration, First(qry_all.Logon) AS Location, qry_all.CallSubject1,
 qry_all.CallSubject2, First(qry_all.CallSubject3) AS FirstOfCallSubject3
FROM qry_all
GROUP BY Format([LogDatTim],"dd/mm/yyyy"), qry_all.ConfigId, qry_all.CallSubject1, qry_all.CallSubject2;

/* qry_NonBillable */
UPDATE [Quetzal Faults] SET [Quetzal Faults].bill = 0
WHERE ((([Quetzal Faults].CallSubject1) Like "CENTRACOM" Or ([Quetzal Faults].CallSubject1) Like "SECURITY"))
 OR ((([Quetzal Faults].CallSubject2)="TELMAX 21")) OR ((([Quetzal Faults].CallSubject2)="TELMAX"))
 OR ((([Quetzal Faults].CallSubject2)="FLEXCOMMS")) OR ((([Quetzal Faults].CallSubject2)="QUETZAL"))
 OR ((([Quetzal Faults].CallSubject1)="PHONE T/T")) OR ((([Quetzal Faults].CallSubject1)="EXCHANGE")
 AND (([Quetzal Faults].CallSubject2)="PABX SYSTEM"))
 OR ((([Quetzal Faults].CallSubject1)="LOCO-LTR FAULT") AND (([Quetzal Faults].CallSubject2)="LOCAL T/RADIO"))
 OR ((([Quetzal Faults].CallSubject1)="RADIO PORTABLE/FIXED") AND (([Quetzal Faults].CallSubject2)="TWO WAY RADIO"))
 OR ((([Quetzal Faults].CallSubject1) Like "SEPAC*") AND (([Quetzal Faults].Owner1)="FRT"))
 OR ((([Quetzal Faults].CallSubject1)="TTB-LOCO FAULT") AND (([Quetzal Faults].CallSubject2)="NUTR TTBR"))
 OR ((([Quetzal Faults].CallSubject1)="FLEX COMM TERM") AND (([Quetzal Faults].Owner1)="FRT"))
 OR ((([Quetzal Faults].CallSubject1)="POST PHONE") AND (([Quetzal Faults].Owner1)="FRT"))
 OR ((([Quetzal Faults].CallSubject1)="MDC CONSOLE") AND (([Quetzal Faults].Owner1)="FRT"))
 OR ((([Quetzal Faults].CallSubject1)="SHUNT RADIO") AND (([Quetzal Faults].Owner1)="FRT"))
 OR ((([Quetzal Faults].CallSubject1)="PCM LINK") AND (([Quetzal Faults].Owner1)="FRT"))
 OR ((([Quetzal Faults].CallSubject1)="ASW - COMMS") AND (([Quetzal Faults].Owner1)="FRT"))
 OR ((([Quetzal Faults].CallSubject1)="BASE STN EQUIP") AND (([Quetzal Faults].Owner1)="FRT"))
 OR ((([Quetzal Faults].CallSubject1)="BASE STN EQUIP") AND (([Quetzal Faults].Owner1)="ART"))
 OR ((([Quetzal Faults].CallSubject1)="VOICEMAIL")) OR ((([Quetzal Faults].CallSubject1)="PHONE D-TERM"))
 OR ((([Quetzal Faults].CallSubject1)="PHONE-IQUARTZ")) OR ((([Quetzal Faults].CallSubject1)="PHONE EXICOM"))
 OR ((([Quetzal Faults].CallSubject1)="PHONE QMASTER")) OR ((([Quetzal Faults].CallSubject2)="SECURITY"))
 OR ((([Quetzal Faults].CallSubject2)="ETAS")) OR ((([Quetzal Faults].CallSubject2)='LTR RADIO (End to End)'))
 OR ((([Quetzal Faults].CallSubject1)="SMR RADIO")) OR ((([Quetzal Faults].Owner1)="VRT"))
 OR ((([Quetzal Faults].CallSubject1) Like "*LOCO*")) OR ((([Quetzal Faults].CallSubject1) Like "RADIO DYNON"));