How to do looping and temp tables in access, easy in sql server

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that contains service calls. I want to show the number of
service calls that were still open at the end of each month. ie Month/Year,
Count of calls: results may look like Jan 06 - 2, Feb 06 - 3

I have a query :
SELECT Count(*) AS Expr1
FROM tbl
WHERE ((tbl.EntryDate) Is Not Null And (dbo_tbl.EntryDate)<=[problem value])

As you can see this query does not contain the month/Year column at the
beginning. I need some way of having the month Year column in the query and
to use this month/year data in the query in place of the [problem value]. Is
there anyway of doing something like this.

I need to automate this report so that it returns data for a date range
gathered from a form. I almost need some loop to calculate the date,
construct the query with that date and return the data to some temporary
table, then after the end of the loop, query the temporary table.
 
Dear Peter:

I think it is likely you need:

SELECT Month(EntryDate) AS EntryMonth,
Year(EntryDate) AS EntryYear,
Count(*) AS EntryCount
FROM tbl
WHERE EntryDate Is Not Null
AND EntryDate <= [problem value]
GROUP BY Month(EntryDate), Year(EntryDate)

Tom Ellison
 
Back
Top