Help with update query UDF

  • Thread starter Thread starter Craigbob
  • Start date Start date
C

Craigbob

Hi,

I've got an update/append (1st run is update query subsequent runs are
appends) query that I need some help on. What the query does is summarize a
table of support calls to another table based on product serial number. It
takes all the calls and counts up the number of calls each month and
consolidated them into a month/year basis by serial number. so I essentially
wind up with a table that has a SN, month/year (same field) and # of calls
for each month.

In the all_calls table, each call has a call type (proactive or reactive).
What I want to do is count the number of proactive and reactive calls for
each serial number for each month/year. So teh resulting table looks
something like this (may wrap):

Platform Serial no Notifdate Notifdate_Month Num_of_Calls Region Material
No Serv Location Product Material Install Date Call
Type Proactive_Count Reactive_Count
Femto 010210024R 1 /31/2006 January 2006 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 2 /28/2006 February 2006 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 3 /31/2006 March 2006 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 4 /30/2006 April 2006 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 5 /31/2006 May 2006 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 6 /13/2006 June 2006 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 6 /30/2006 June 2006 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 7 /24/2006 July 2006 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 7 /28/2006 July 2006 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 7 /31/2006 July 2006 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 8 /31/2006 August 2006 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 9 /22/2006 September 2006 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 9 /30/2006 September 2006 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 10/30/2006 October 2006 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 10/31/2006 October 2006 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 11/15/2006 November 2006 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 11/30/2006 November 2006 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 12/31/2006 December 2006 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 1 /9 /2007 January 2007 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 1 /31/2007 January 2007 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 2 /2 /2007 February 2007 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 2 /23/2007 February 2007 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 2 /28/2007 February 2007 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 3 /5 /2007 March 2007 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 3 /23/2007 March 2007 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 3 /30/2007 March 2007 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 3 /31/2007 March 2007 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 4 /9 /2007 April 2007 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 4 /20/2007 April 2007 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 4 /30/2007 April 2007 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 5 /8 /2007 May 2007 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 5 /31/2007 May 2007 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 6 /30/2007 June 2007 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 7 /31/2007 July 2007 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 8 /31/2007 August 2007 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 9 /7 /2007 September 2007 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 9 /14/2007 September 2007 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 9 /20/2007 September 2007 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 9 /30/2007 September 2007 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 10/31/2007 October 2007 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 11/27/2007 November 2007 1 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 11/30/2007 November 2007 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 010210024R 12/31/2007 December 2007 0 MA R20002 2037S M1 R20002 -
Reconditioned, Laser System, Keratome 8/19/2002
Femto 0102-10025 1 /31/2006 January 2006 0 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 2 /28/2006 February 2006 0 NE 20002 2011S-2 M1 20002 -
Laser Sys,Keratome 3/12/2002
Femto 0102-10025 3 /31/2006 March 2006 0 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 4 /30/2006 April 2006 0 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 5 /17/2006 May 2006 1 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 5 /31/2006 May 2006 0 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 6 /30/2006 June 2006 0 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 7 /12/2006 July 2006 1 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 7 /31/2006 July 2006 0 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 8 /10/2006 August 2006 1 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 8 /31/2006 August 2006 0 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 9 /30/2006 September 2006 0 NE 20002 2011S-2 M1 20002 -
Laser Sys,Keratome 3/12/2002
Femto 0102-10025 10/31/2006 October 2006 0 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 11/30/2006 November 2006 0 NE 20002 2011S-2 M1 20002 -
Laser Sys,Keratome 3/12/2002
Femto 0102-10025 12/15/2006 December 2006 1 NE 20002 2011S-2 M1 20002 -
Laser Sys,Keratome 3/12/2002
Femto 0102-10025 12/31/2006 December 2006 0 NE 20002 2011S-2 M1 20002 -
Laser Sys,Keratome 3/12/2002
Femto 0102-10025 1 /31/2007 January 2007 0 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 2 /28/2007 February 2007 0 NE 20002 2011S-2 M1 20002 -
Laser Sys,Keratome 3/12/2002
Femto 0102-10025 3 /23/2007 March 2007 1 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 3 /31/2007 March 2007 0 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 4 /3 /2007 April 2007 1 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 4 /30/2007 April 2007 0 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 5 /31/2007 May 2007 0 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 6 /30/2007 June 2007 0 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 7 /31/2007 July 2007 0 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 8 /31/2007 August 2007 0 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 9 /21/2007 September 2007 1 NE 20002 2011S-2 M1 20002 -
Laser Sys,Keratome 3/12/2002
Femto 0102-10025 9 /30/2007 September 2007 0 NE 20002 2011S-2 M1 20002 -
Laser Sys,Keratome 3/12/2002
Femto 0102-10025 10/31/2007 October 2007 0 NE 20002 2011S-2 M1 20002 - Laser
Sys,Keratome 3/12/2002
Femto 0102-10025 11/30/2007 November 2007 0 NE 20002 2011S-2 M1 20002 -
Laser Sys,Keratome 3/12/2002
Femto 0102-10025 12/31/2007 December 2007 0 NE 20002 2011S-2 M1 20002 -
Laser Sys,Keratome 3/12/2002


Now I'm thinking of using a UDF in the update part of teh query, but for teh
life of me can't figure out how to structure it.

I want the query or UDF to look at the call type field in the all calls
table (table2) and the notifdate field for a particular Serial # and if the
call type is proactive, sum up all the proactive calls for that month and all
teh reactive calls for that month then update the MTBSC table (table 1
structure & data above) proactive/reactive counts. (for the Serial number
Month (summarized)).

Hope that makes sense.

Any help is appreciated.
 
You posted what you want the query results to be.
What does the structure of your table look like?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

WCG Stats Monday 21 August 2023 3
WCG Stats Monday 04 September 2023 3
WCG Stats Friday 15 September 2023 3
WCG Stats Tuesday 12 September 2023 8
WCG Stats Sunday 17 September 2023 3
WCG Stats Saturday 09 September 2023 4
WCG Stats Thursday 24 August 2023 3
WCG Stats Saturday 23 September 2023 3

Back
Top