Setting a max record limit in access

Question

I have a table that holds several hundred records but I with a special property. I only want the user to be able to select that property 50 times though. How can I set a limit and return an error when the limit is reached?

Answer 1

select top 50 * from ...

Answer 2

I believe what renick was trying to say was before your save operation you could do something like

SELECT Count(*) 
FROM tblFoo
WHERE Bar=’Thing_you_only_want_50_of’

You would then check to see if this figure is greater than equal to or greater than 50, if it is then return an error and not then let the user save.

As far as I know there is no way to restrict that at the DB level however access 2010 does have more controls such as triggers etc. Not sure what version you are using but if you are on 2010 it might be worth checking out

Answer 3

The question is very unclear. It's not clear if "select" means "display limited to 50 items" or "allow user to create data records that are limited to 50 items". I won't address the first interpretation, since it doesn't seem to me to be relevant.

Assuming certain other things, such as a Jet/ACE back end (the only way the question makes sense to me), how you accomplish this depends on your version of Access.

1. up to and including Access 2007: you'll have to apply the limitation in the user interface of your application. EDIT: As @onedaywhen has pointed out in his answer, it's possible to use DDL in SQL 92 mode to add a CHECK CONSTRAINT that operates on the current record based on groups of other records. I did not know this was possible when I posted.

2. in Access 2010, you can avail yourself of the new table-level data macros (which work like triggers) and limit the user to the 50 selections.

I'm not providing details for either of these, as there's simply not enough information provided to do so.

Answer 4

To add to @Kevin Ross, the mechanism "at the DB level" to enforce the rule is a CHECK constraint e.g.

ALTER TABLE tblFoo ADD
   CONSTRAINT tblFoo_bar__50_limit
      CHECK (NOT EXISTS (
                         SELECT T1.Bar
                           FROM tblFoo AS T1
                          WHERE T1.Bar = 'Thing_you_only_want_50_of'
                          GROUP 
                             BY T1.Bar
                         HAVING COUNT(*) > 50
                        ));

CHECK constraints have existed in Jet from version 4.0 (circa Access 2000) and still exists in ACE (e.g. Access 2010).

You need to create the CHECK constraint using SQL DDL while in ANSI-92 Query Mode. No, you can't create CHECK constraints using DAO or the Access UI but that doesn't mean they don't exist ;)

If for some reason you have philosophical objection to SQL DDL, you could do similar things with an additional 'sequence' column, row-level Validation Rules and a compound UNIQUE constraint, all of which can be created using DAO or the Access UI and have been available in Jet for more years than I can remember.

Here's a rough sketch of what that alternative approach could look like:

ALTER TABLE tblFoo ADD 
   Bar__sequence INTEGER;

ALTER TABLE tblFoo ADD 
   CONSTRAINT tblFoo_bar_sequence__values
      CHECK (
             (
              Bar <> 'Thing_you_only_want_50_of' 
              AND Bar__sequence IS NULL
             )
             OR (
                 Bar = 'Thing_you_only_want_50_of' 
                 AND Bar__sequence BETWEEN 1 AND 50
                )
            );

ALTER TABLE tblFoo ADD 
   CONSTRAINT tblFoo_bar__50_limit
      UNIQUE (Bar__sequence);

In this case, the results of the above three SQL DDL statements can be achieved using the Table Designer in the Access UI i.e. add the column, amend the Table Validation Rule and add a unique index.