Dispose by Group

[Guest]

Hello all,

I am designing a database keeping track of books. Books are stored in a
box. Tables in the database are designed to keep track of books individually
per box, which means I have a field for Box # and book #. Depending on the #
of books in each box, the records are varied in the table. Additionally, I
have a field for the year of the book (e.g. "2007"), and each box may have
books for various years. The company policy is to destroy the books that are
15 years old from the book year by box; however, if there is at least one
book in the box that is less than 14 years old, we wait until all the books
in the box meet that 15 years threshold. Then we destroy the whole box.
Before we destroy the boxes, we log them in the database as destroyed. Then
we destroy them.

Presently, I have a form to log those destroyed boxes; however, I do not
have a feature prompt the users that you can not destroy those boxes because
we have some books that have not met the 15-year threshold and prevent the
users from destroying them.

Since the database keeps track of a to-be-destroyed date, I do have a report
showing which boxes to destroy. The report shows a list of those books that
have not met the 15-year threshold, if any.

As you know, even though we have a report to show those boxes, the users can
mistakenly log and destroy the boxes that have not met the 15-year threshold.


Is there a way to have that feature on the form to prevent the users from
destroying them?

Thanks.

 

[Guest]

Use these two queries --
DisposeByGroup_1 ----
SELECT Book_Box.Box, Book_Box.Book_Year
FROM Book_Box
WHERE (((Book_Box.Book_Year)>Year(Date())-14));

DisposeByGroup_1 ----
SELECT Book_Box.Box
FROM Book_Box LEFT JOIN DisposeByGroup_1 ON Book_Box.Box =
DisposeByGroup_1.Box
WHERE (((DisposeByGroup_1.Box) Is Null))
GROUP BY Book_Box.Box;