most recent rows

[Molasses26]

I have a table where the users are supposed to input a row each month for
each account. I need some way to audit the table to make sure I have an
entry for each month in the current 12 months. The table as a field called
BillDate but if it's an account that bills near the end/beginning of the
month you might have 2 BillDates in the same month. For example 3/1/08 is the
Februay BillDate and 3/30/08 is the March BillDate. So I added a field
called BillMoYr but I'm not sure how to write the query to get the current 12
rows for each account.

In the example below I would like to be able to look at the rows below the
line and say "Yes, there is a row for each month on this account." Or beter
yet just get a list of the accounts where there is NOT a row for each month
on the account.

Table:
ACCT BILLDATE BILLMoYr COMMENT
335148 08/29/2006 09/2006 Yes
335148 09/29/2006 10/2006 Yes
335148 10/30/2006 11/2006 Yes
335148 12/01/2006 12/2006 Yes
335148 01/03/2007 01/2007 Yes
335148 02/02/2007 02/2007 Yes
335148 03/02/2007 03/2007 Yes
---------------------------------------------------------
335148 04/02/2007 04/2007 Yes
335148 05/02/2007 05/2007 Yes
335148 06/01/2007 06/2007 Yes
335148 07/02/2007 07/2007 Yes
335148 08/01/2007 08/2007 Yes
335148 08/30/2007 09/2007 Yes
335148 10/01/2007 10/2007 Yes
335148 10/30/2007 11/2007 Yes
335148 12/03/2007 12/2007 Yes
335148 01/03/2008 01/2008 Yes
335148 02/04/2008 02/2008 Yes
335148 03/04/2008 03/2008 Yes

 

[Golfinray]

Just do a save as on the query, rename it with a name like Is Not There
query, and then put Is Null in the criteria field where you want to know if
anything is missing.

 

[John Spencer]

It might be as simple as:

SELECT Acct
FROM BillTable
WHERE BillDate Between #4/1/2007# and #3/31/2008#
GROUP BY Acct
HAVING Count(*) < 12

Or you might have to use BillMoYr and construct the date

SELECT Acct
FROM BillTable
WHERE DateSerial(Right(BillMoYr,4),Left(BillMoYr,2),1)
Between #4/1/2007# and #3/31/2008#
GROUP BY Acct
HAVING Count(*) < 12

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Molasses26]

It seems like it sould work but when I enter it this way I get an error
message that says:
"You tried to execute a query that does not include the specified expression
'BillMoYr' as part of an aggregate function or grouping."

And if I change the GROUP BY statement to include BillMoYr it runs but I get
EVERYTHING instead of just the ones <12.

 

[John Spencer]

Can you post the SQL of the query that is failing. I think you might be
using GROUP BY where you should be using WHERE. However, that is just a
guess.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Molasses26]

Never Mind!!
I had to get a little more creative on scraping out the bill mo/yr and it
was the way I was doing it was causing a problem.
The following SQL finally did the trick!

Thanks for your help!!!

SELECT MASTERMETER
FROM SRCardData
WHERE DateSerial(Right([BillMoYr],4),IIf(Right(Left([BillMoYr],2),1)="/",0 &
Left([BillMoYr],1),Left([BillMoYr],2)),1)
Between #3/1/2007# And #3/1/2008#
GROUP BY MASTERMETER
HAVING Count(*)<12;