Parameters, convolution and multiple query calls

[mrb783]

Okay, so I have a chart/graph to create based on a few bits of information from one of my tables. I have the queries set up to do most of my work for me and the chart/graph creation is not my issue. What I need to do is perform analysis on a given month/year (which the user is prompted for), then return counts of the number of hits I get for a query execution over the past 12 months. Here is an example of the initial table structure:

ID | OpenMonth | OpenYear | Open/Close Status | Close Month | CloseYear

Now, I have a query designed that takes parameters for the Month and Year you want to run the query against that will perform a count on the number of ID's that match the criterion (parameter month/year is greater than the OpenMonth/Year and if the case has been closed, the parameter month/year should be strictly less than the closed month/year) See my SQL statement below (it's long and convoluted, but I promise you it works):

PARAMETERS [Enter Month] IEEESingle, [Enter Year] IEEESingle;
SELECT Count(qryTable1.ID) AS CountOfID, [Enter Month] AS PendingMonth, [Enter Year] AS PendingYear
FROM qryTable1
WHERE (((qryTable1.[Open/Close])="Open") AND (([Enter Month])>[OpenMonth]) AND (([Enter Year])>=[OpenYear])) OR (((qryTable1.[Open/Close])="Closed") AND ((DateValue([Enter Month] & "/1/" & [Enter Year])>DateValue([OpenMonth] & "/1/" & [OpenYear]) And DateValue([Enter Month] & "/1/" & [Enter Year])<DateValue([CloseMonth] & "/1/" & [CloseYear]))=True));

This query returns a single row with the count of Pending Cases for that month. Now, what I want to do is call this query 13 times total within a UNION block in order to build a table for the prior 12 months (e.g. Sept 2007 - Sept 2008) and build something with the following table structure:

CountOfID AS PriorityCount | Month | Year

I know I could write a gigantic SQL query to do this manually, but I would really rather not if I can avoid it. Any ideas on how to do this? I have a feeling it will require me to do some vbscripting...which I would like to avoid if at all possible.

 

[mrb783]

Okay, so that whole "trust me it works" statement was incorrect...at least for any January months...here's the updated initial single-row query:

PARAMETERS [Enter Month] IEEESingle, [Enter Year] IEEESingle;
SELECT Count(qryTable1.ID) AS CountOfID, [Enter Month] AS PendingMonth, [Enter Year] AS PendingYear
FROM qryTable1
WHERE (((qryTable1.[Open/Close])="Open") AND ((DateValue([Enter Month] & "/1/" & [Enter Year])>DateValue([OpenMonth] & "/1/" & [OpenYear]))=True)) OR (((qryTable1.[Open/Close])="Closed") AND ((DateValue([Enter Month] & "/1/" & [Enter Year])>DateValue([OpenMonth] & "/1/" & [OpenYear]) And DateValue([Enter Month] & "/1/" & [Enter Year])<DateValue([CloseMonth] & "/1/" & [CloseYear]))=True));

Now, I did write the big all-in-one query as well and it works quite well...though it will be a pain to maintain. But, that's something that shouldn't need to be done too often I should think. Anyway, here it is in all its (atrociously large) glory. Enjoy!

PARAMETERS [Enter Month] IEEESingle, [Enter Year] IEEESingle;
SELECT *
FROM (

SELECT Count(qryTable1.ID) AS CountOfID, [Enter Month] AS PendingMonth, [Enter Year] AS PendingYear
FROM qryTable1
WHERE (((qryTable1.[Open/Close])="Open") AND ((DateValue([Enter Month] & "/1/" & [Enter Year])>DateValue([OpenMonth] & "/1/" & [OpenYear]))=True)) OR (((qryTable1.[Open/Close])="Closed") AND ((DateValue([Enter Month] & "/1/" & [Enter Year])>DateValue([OpenMonth] & "/1/" & [OpenYear]) And DateValue([Enter Month] & "/1/" & [Enter Year])<DateValue([CloseMonth] & "/1/" & [CloseYear]))=True))

UNION
SELECT Count(qryTable1.ID) AS CountOfID, (iif(([Enter Month]-1)<=0,12+([Enter Month]-1),([Enter Month]-1))) AS PendingMonth, (iif(([Enter Month]-1)<=0,([Enter Year]-1),[Enter Year])) AS PendingYear
FROM qryTable1
WHERE (((qryTable1.[Open/Close])="Open") AND ((DateValue((iif(([Enter Month]-1)<=0,12+([Enter Month]-1),([Enter Month]-1))) & "/1/" & (iif(([Enter Month]-1)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]))=True)) OR (((qryTable1.[Open/Close])="Closed") AND ((DateValue((iif(([Enter Month]-1)<=0,12+([Enter Month]-1),([Enter Month]-1))) & "/1/" & (iif(([Enter Month]-1)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]) And DateValue((iif(([Enter Month]-1)<=0,12+([Enter Month]-1),([Enter Month]-1))) & "/1/" & (iif(([Enter Month]-1)<=0,([Enter Year]-1),[Enter Year])))<DateValue([CloseMonth] & "/1/" & [CloseYear]))=True))

UNION
SELECT Count(qryTable1.ID) AS CountOfID, (iif(([Enter Month]-2)<=0,12+([Enter Month]-2),([Enter Month]-2))) AS PendingMonth, (iif(([Enter Month]-2)<=0,([Enter Year]-1),[Enter Year])) AS PendingYear
FROM qryTable1
WHERE (((qryTable1.[Open/Close])="Open") AND ((DateValue((iif(([Enter Month]-2)<=0,12+([Enter Month]-2),([Enter Month]-2))) & "/1/" & (iif(([Enter Month]-2)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]))=True)) OR (((qryTable1.[Open/Close])="Closed") AND ((DateValue((iif(([Enter Month]-2)<=0,12+([Enter Month]-2),([Enter Month]-2))) & "/1/" & (iif(([Enter Month]-2)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]) And DateValue((iif(([Enter Month]-2)<=0,12+([Enter Month]-2),([Enter Month]-2))) & "/1/" & (iif(([Enter Month]-2)<=0,([Enter Year]-1),[Enter Year])))<DateValue([CloseMonth] & "/1/" & [CloseYear]))=True))

UNION
SELECT Count(qryTable1.ID) AS CountOfID, (iif(([Enter Month]-3)<=0,12+([Enter Month]-3),([Enter Month]-3))) AS PendingMonth, (iif(([Enter Month]-3)<=0,([Enter Year]-1),[Enter Year])) AS PendingYear
FROM qryTable1
WHERE (((qryTable1.[Open/Close])="Open") AND ((DateValue((iif(([Enter Month]-3)<=0,12+([Enter Month]-3),([Enter Month]-3))) & "/1/" & (iif(([Enter Month]-3)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]))=True)) OR (((qryTable1.[Open/Close])="Closed") AND ((DateValue((iif(([Enter Month]-3)<=0,12+([Enter Month]-3),([Enter Month]-3))) & "/1/" & (iif(([Enter Month]-3)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]) And DateValue((iif(([Enter Month]-3)<=0,12+([Enter Month]-3),([Enter Month]-3))) & "/1/" & (iif(([Enter Month]-3)<=0,([Enter Year]-1),[Enter Year])))<DateValue([CloseMonth] & "/1/" & [CloseYear]))=True))

UNION
SELECT Count(qryTable1.ID) AS CountOfID, (iif(([Enter Month]-4)<=0,12+([Enter Month]-4),([Enter Month]-4))) AS PendingMonth, (iif(([Enter Month]-4)<=0,([Enter Year]-1),[Enter Year])) AS PendingYear
FROM qryTable1
WHERE (((qryTable1.[Open/Close])="Open") AND ((DateValue((iif(([Enter Month]-4)<=0,12+([Enter Month]-4),([Enter Month]-4))) & "/1/" & (iif(([Enter Month]-4)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]))=True)) OR (((qryTable1.[Open/Close])="Closed") AND ((DateValue((iif(([Enter Month]-4)<=0,12+([Enter Month]-4),([Enter Month]-4))) & "/1/" & (iif(([Enter Month]-4)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]) And DateValue((iif(([Enter Month]-4)<=0,12+([Enter Month]-4),([Enter Month]-4))) & "/1/" & (iif(([Enter Month]-4)<=0,([Enter Year]-1),[Enter Year])))<DateValue([CloseMonth] & "/1/" & [CloseYear]))=True))

UNION
SELECT Count(qryTable1.ID) AS CountOfID, (iif(([Enter Month]-5)<=0,12+([Enter Month]-5),([Enter Month]-5))) AS PendingMonth, (iif(([Enter Month]-5)<=0,([Enter Year]-1),[Enter Year])) AS PendingYear
FROM qryTable1
WHERE (((qryTable1.[Open/Close])="Open") AND ((DateValue((iif(([Enter Month]-5)<=0,12+([Enter Month]-5),([Enter Month]-5))) & "/1/" & (iif(([Enter Month]-5)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]))=True)) OR (((qryTable1.[Open/Close])="Closed") AND ((DateValue((iif(([Enter Month]-5)<=0,12+([Enter Month]-5),([Enter Month]-5))) & "/1/" & (iif(([Enter Month]-5)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]) And DateValue((iif(([Enter Month]-5)<=0,12+([Enter Month]-5),([Enter Month]-5))) & "/1/" & (iif(([Enter Month]-5)<=0,([Enter Year]-1),[Enter Year])))<DateValue([CloseMonth] & "/1/" & [CloseYear]))=True))

UNION
SELECT Count(qryTable1.ID) AS CountOfID, (iif(([Enter Month]-6)<=0,12+([Enter Month]-6),([Enter Month]-6))) AS PendingMonth, (iif(([Enter Month]-6)<=0,([Enter Year]-1),[Enter Year])) AS PendingYear
FROM qryTable1
WHERE (((qryTable1.[Open/Close])="Open") AND ((DateValue((iif(([Enter Month]-6)<=0,12+([Enter Month]-6),([Enter Month]-6))) & "/1/" & (iif(([Enter Month]-6)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]))=True)) OR (((qryTable1.[Open/Close])="Closed") AND ((DateValue((iif(([Enter Month]-6)<=0,12+([Enter Month]-6),([Enter Month]-6))) & "/1/" & (iif(([Enter Month]-6)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]) And DateValue((iif(([Enter Month]-6)<=0,12+([Enter Month]-6),([Enter Month]-6))) & "/1/" & (iif(([Enter Month]-6)<=0,([Enter Year]-1),[Enter Year])))<DateValue([CloseMonth] & "/1/" & [CloseYear]))=True))

UNION
SELECT Count(qryTable1.ID) AS CountOfID, (iif(([Enter Month]-7)<=0,12+([Enter Month]-7),([Enter Month]-7))) AS PendingMonth, (iif(([Enter Month]-7)<=0,([Enter Year]-1),[Enter Year])) AS PendingYear
FROM qryTable1
WHERE (((qryTable1.[Open/Close])="Open") AND ((DateValue((iif(([Enter Month]-7)<=0,12+([Enter Month]-7),([Enter Month]-7))) & "/1/" & (iif(([Enter Month]-7)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]))=True)) OR (((qryTable1.[Open/Close])="Closed") AND ((DateValue((iif(([Enter Month]-7)<=0,12+([Enter Month]-7),([Enter Month]-7))) & "/1/" & (iif(([Enter Month]-7)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]) And DateValue((iif(([Enter Month]-7)<=0,12+([Enter Month]-7),([Enter Month]-7))) & "/1/" & (iif(([Enter Month]-7)<=0,([Enter Year]-1),[Enter Year])))<DateValue([CloseMonth] & "/1/" & [CloseYear]))=True))

UNION
SELECT Count(qryTable1.ID) AS CountOfID, (iif(([Enter Month]-8)<=0,12+([Enter Month]-8),([Enter Month]-8))) AS PendingMonth, (iif(([Enter Month]-8)<=0,([Enter Year]-1),[Enter Year])) AS PendingYear
FROM qryTable1
WHERE (((qryTable1.[Open/Close])="Open") AND ((DateValue((iif(([Enter Month]-8)<=0,12+([Enter Month]-8),([Enter Month]-8))) & "/1/" & (iif(([Enter Month]-8)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]))=True)) OR (((qryTable1.[Open/Close])="Closed") AND ((DateValue((iif(([Enter Month]-8)<=0,12+([Enter Month]-8),([Enter Month]-8))) & "/1/" & (iif(([Enter Month]-8)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]) And DateValue((iif(([Enter Month]-8)<=0,12+([Enter Month]-8),([Enter Month]-8))) & "/1/" & (iif(([Enter Month]-8)<=0,([Enter Year]-1),[Enter Year])))<DateValue([CloseMonth] & "/1/" & [CloseYear]))=True))

UNION
SELECT Count(qryTable1.ID) AS CountOfID, (iif(([Enter Month]-9)<=0,12+([Enter Month]-9),([Enter Month]-9))) AS PendingMonth, (iif(([Enter Month]-9)<=0,([Enter Year]-1),[Enter Year])) AS PendingYear
FROM qryTable1
WHERE (((qryTable1.[Open/Close])="Open") AND ((DateValue((iif(([Enter Month]-9)<=0,12+([Enter Month]-9),([Enter Month]-9))) & "/1/" & (iif(([Enter Month]-9)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]))=True)) OR (((qryTable1.[Open/Close])="Closed") AND ((DateValue((iif(([Enter Month]-9)<=0,12+([Enter Month]-9),([Enter Month]-9))) & "/1/" & (iif(([Enter Month]-9)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]) And DateValue((iif(([Enter Month]-9)<=0,12+([Enter Month]-9),([Enter Month]-9))) & "/1/" & (iif(([Enter Month]-9)<=0,([Enter Year]-1),[Enter Year])))<DateValue([CloseMonth] & "/1/" & [CloseYear]))=True))

UNION
SELECT Count(qryTable1.ID) AS CountOfID, (iif(([Enter Month]-10)<=0,12+([Enter Month]-10),([Enter Month]-10))) AS PendingMonth, (iif(([Enter Month]-10)<=0,([Enter Year]-1),[Enter Year])) AS PendingYear
FROM qryTable1
WHERE (((qryTable1.[Open/Close])="Open") AND ((DateValue((iif(([Enter Month]-10)<=0,12+([Enter Month]-10),([Enter Month]-10))) & "/1/" & (iif(([Enter Month]-10)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]))=True)) OR (((qryTable1.[Open/Close])="Closed") AND ((DateValue((iif(([Enter Month]-10)<=0,12+([Enter Month]-10),([Enter Month]-10))) & "/1/" & (iif(([Enter Month]-10)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]) And DateValue((iif(([Enter Month]-10)<=0,12+([Enter Month]-10),([Enter Month]-10))) & "/1/" & (iif(([Enter Month]-10)<=0,([Enter Year]-1),[Enter Year])))<DateValue([CloseMonth] & "/1/" & [CloseYear]))=True))

UNION
SELECT Count(qryTable1.ID) AS CountOfID, (iif(([Enter Month]-11)<=0,12+([Enter Month]-11),([Enter Month]-11))) AS PendingMonth, (iif(([Enter Month]-11)<=0,([Enter Year]-1),[Enter Year])) AS PendingYear
FROM qryTable1
WHERE (((qryTable1.[Open/Close])="Open") AND ((DateValue((iif(([Enter Month]-11)<=0,12+([Enter Month]-11),([Enter Month]-11))) & "/1/" & (iif(([Enter Month]-11)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]))=True)) OR (((qryTable1.[Open/Close])="Closed") AND ((DateValue((iif(([Enter Month]-11)<=0,12+([Enter Month]-11),([Enter Month]-11))) & "/1/" & (iif(([Enter Month]-11)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]) And DateValue((iif(([Enter Month]-11)<=0,12+([Enter Month]-11),([Enter Month]-11))) & "/1/" & (iif(([Enter Month]-11)<=0,([Enter Year]-1),[Enter Year])))<DateValue([CloseMonth] & "/1/" & [CloseYear]))=True))

UNION
SELECT Count(qryTable1.ID) AS CountOfID, (iif(([Enter Month]-12)<=0,12+([Enter Month]-12),([Enter Month]-12))) AS PendingMonth, (iif(([Enter Month]-12)<=0,([Enter Year]-1),[Enter Year])) AS PendingYear
FROM qryTable1
WHERE (((qryTable1.[Open/Close])="Open") AND ((DateValue((iif(([Enter Month]-12)<=0,12+([Enter Month]-12),([Enter Month]-12))) & "/1/" & (iif(([Enter Month]-12)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]))=True)) OR (((qryTable1.[Open/Close])="Closed") AND ((DateValue((iif(([Enter Month]-12)<=0,12+([Enter Month]-12),([Enter Month]-12))) & "/1/" & (iif(([Enter Month]-12)<=0,([Enter Year]-1),[Enter Year])))>DateValue([OpenMonth] & "/1/" & [OpenYear]) And DateValue((iif(([Enter Month]-12)<=0,12+([Enter Month]-12),([Enter Month]-12))) & "/1/" & (iif(([Enter Month]-12)<=0,([Enter Year]-1),[Enter Year])))<DateValue([CloseMonth] & "/1/" & [CloseYear]))=True))

)

AS qryPendingCases
ORDER BY PendingYear, PendingMonth;

 

[WayPay]

it will be a pain to maintain. But, that's something that shouldn't need to be done too often

I hope it will never need to be done. I hope that if it ever does need to be done, you are the one doing it. I was doing a rant here, but I deleted it in favor of something constructive . I think the whole thing can be done in one fairly elegant (but largish ) query.

I dont't know what the ID represents (and don't need to), but is there a simple date attached to it rather than the month/year fields? That would make the solution even simpler.