Class Date Due

[John]

I want to be able to generate one single report showing
anyone that has any Classes they need to attend due to
them having yearly training due. We should be able to look
at this and see if, out of 100 people, anyone that needs
to goto a class. The Fields consist of Name, Class1,
Class2, Class3. the inputs would be Jon Doe, (his date due
for Class1) 01Aug03, and so on for the next two class.

I can already pull those that are due for a class by the
class name, but I really want to be able to see a listing
of anyone that's due a class and in what they are due with
date. I feel that this can be done with a query, I just
lack the knowledge on how to make one on this scale.

 

[Bullschmidt]

Here is an example I put together using the query by form concept with
two dates as criteria.

Have a form perhaps called MyForm with the following 2 controls:
ctrDateMin
ctrDateMax

And have a command button on the form to open the report with something
like the following code behind the OnClick property:
DoCmd OpenReport "MyReport", A_PREVIEW
DoCmd Maximize

Of course the form could also have a command button to open another form
with something like the following code behind the OnClick property:
DoCmd OpenForm "MyOtherForm"
DoCmd Maximize

Base the report on a query. In the query's criteria for the date field
perhaps put in something like the following:
Between [Forms]![MyForm]![ctrDateMin] And [Forms]![MyForm]![ctrDateMax]

And of course if the command button were opening a form instead of a
report, the form could also be based on the same query or a similar one.

--
J. Paul Schmidt, Freelance Access and ASP Developer
http://www.Bullschmidt.com/Login.asp - Database on the Web Demo
http://www.Bullschmidt.com/Access
Sample Access Invoices Database


Posted via http://dbforums.com

 

[John Vinson]

I want to be able to generate one single report showing
anyone that has any Classes they need to attend due to
them having yearly training due. We should be able to look
at this and see if, out of 100 people, anyone that needs
to goto a class. The Fields consist of Name, Class1,
Class2, Class3. the inputs would be Jon Doe, (his date due
for Class1) 01Aug03, and so on for the next two class.

Part of the difficulty arises because this table is not properly
normalized: your Class field is a repeated field, essentially a
one-to-many relationship embedded in a single table. A more normalized
structure would have THREE tables not one - an Employee table with an
EmployeeID (names are *not* unique and do not make good keys!); a
Class table listing all the classes and their dates; and a
ClassDueDates table with fields EmployeeID, ClassID and DueDate. Jon
Doe would then have three records in this table (or perhaps two, or
four, or five) and it would be a simple matter to find who had classes
due within a given range of dates.

I can already pull those that are due for a class by the
class name, but I really want to be able to see a listing
of anyone that's due a class and in what they are due with
date. I feel that this can be done with a query, I just
lack the knowledge on how to make one on this scale.

With your current structure, you'll need a "Normalizing Union Query"
to turn this "wide flat" table into a "tall skinny" one. In the SQL
window type:

SELECT [Name], [Class1] AS Class
UNION ALL
SELECT [Name], [Class2]
UNION ALL
SELECT [Name], [Class3]

Then create a query based on this query with a date criterion such as

BETWEEN Date() AND DateAdd("m", 1, Date())

to find all classes due within the coming month.