Help with update query UDF

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.
 
K

KARL DEWEY

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

Top