Limit to Max Number

[Ripper]

I have a table (tblDates) that contains all the available dates and the max
number of times that a person can sign up for that date. in the query,
(qryDateCounter) i count the number times that a person has signed up for
that date, and i want to limit them to the max number from tblDates.

What do I write in the criteria section to allow only the fields that have
not yet reached their maximum show?

 

[KARL DEWEY]

What do I write in the criteria section to allow only the fields that have
not yet reached their maximum show?
It sounds like your table resembles a spreadsheet. What is your table
structure?

 

[Ripper]

tblDates
date field
maxnumber field

qryDateCount
SELECT tblSignup.Date, Count(tblSignup.Date) AS CountOfDate
FROM tblSignup INNER JOIN tblDates ON tblSignup.Date = tblDates.Date
GROUP BY tblSignup.Date;

this is the structure of the tbl and the qry.
thanks for helping.

 

[Marshall Barton]

I have a table (tblDates) that contains all the available dates and the max
number of times that a person can sign up for that date. in the query,
(qryDateCounter) i count the number times that a person has signed up for
that date, and i want to limit them to the max number from tblDates.

What do I write in the criteria section to allow only the fields that have
not yet reached their maximum show?

Please post a Copy/Paste of your query's SQL view.

 

[John Spencer]

Perhaps the following will give you what you want.

SELECT tblSignup.Date
, Count(tblSignup.Date) AS CountOfDate
FROM tblSignup INNER JOIN tblDates ON tblSignup.Date = tblDates.Date
GROUP BY tblSignup.Date
HAVING Count(tblSignUp.Date) < First(tblDates.MaxNumber)

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