monthly query

[Johnwats]

I want to ask the same criteria (to find items of a certain date) in 18
different fields. Is there an easy way, or do i just copy & paste?
I want to run the same query every month, is there a quick way of updating
the month, or do i have to enter the complete criteria every time.

 

[John W. Vinson]

I want to ask the same criteria (to find items of a certain date) in 18
different fields. Is there an easy way, or do i just copy & paste?
I want to run the same query every month, is there a quick way of updating
the month, or do i have to enter the complete criteria every time.

Do you have 18 date/time fields and need to query all of them? If so, your
table structure is almost certainly WRONG. What's the structure of your table?

You can use Access' builtin date/time functions to create a dynamic criterion.
Since you chose not to post any information about *what* certain date you are
searching for, I can't make a specific suggestion; but two hints -

Date()

will give you today's date

DateSerial(Year(Date()), Month(Date()), 1)

will give you the first day of the current month

 

[Johnwats]

Thanks for the reply. It wouldn't be a surprise if the table structure was
wrong, this is my first attempt.

I have a group of students who can take a selection of 18 different modules.
I have a table (& form) with all 18 modules on. I enter the date a student
completes a particular module. Each month i want to query who completed a
module that month. So the criteria changes each month.
I can run the query & revise it each month, but i have to do it all
manually. I am hoping there is a way of speeding things up.

thanks
John

 

[John W. Vinson]

Thanks for the reply. It wouldn't be a surprise if the table structure was
wrong, this is my first attempt.

I have a group of students who can take a selection of 18 different modules.
I have a table (& form) with all 18 modules on. I enter the date a student
completes a particular module. Each month i want to query who completed a
module that month. So the criteria changes each month.
I can run the query & revise it each month, but i have to do it all
manually. I am hoping there is a way of speeding things up.

thanks
John

Yep. Your structure is wrong (but it's a very typical mistake for people
learning Access!)

"Fields are expensive, records are cheap". What will you do when the
administration cancels a module, or adds two more? Redesign your table, your
form, all your queries, all your forms, all your reports? Ouch!

Each Student can take many Modules. Each Module can be taken by many Students.
This is a classic many to many relationship, and the correct structure
involves THREE tables:

Students
StudentID <Primary Key>
LastName
FirstName
DateOfBirth
<other appropriate individual biographic data>

Modules
ModuleNo <Primary Key>
ModuleName
<information about the module as a real-life thing>

Enrollment
StudentID <who's involved in this module>
ModuleNo <which module they are involved in>
StartDate
CompletionDate
<other info, e.g. comments, satisfactory/unsatisfactory, etc.>

If a student takes 11 modules, there would be eleven records in the Enrollment
table. If you add a new module, you just need to add a new record in the
Modules table; to find who completed which modules during a month, you have
*ONE* field to search - CompletionDate - in a three table query.

The criterion for "completed during the previous month" would be

= DateSerial(Year(Date()), Month(Date()) - 1, 1) AND < DateSerial(Year(Date()), Month(Date()), 1)

on the DateCompleted field (though you can use this as a criterion on your 18
fields while you're still using this wide-flat spreadsheet design).

 

[Johnwats]

I knew i was going wrong, but couldn't adjust my thinking. I was on the
verge of giving up, but will now try again & hope i can put your advice into
practice.

Just a last (dumb) question. In your example, you had a primary key in the
first & second table, but not in the third one. Do i need one there?

Many thanks you have been a real help.

 

[John W. Vinson]

I knew i was going wrong, but couldn't adjust my thinking. I was on the
verge of giving up, but will now try again & hope i can put your advice into
practice.

Just a last (dumb) question. In your example, you had a primary key in the
first & second table, but not in the third one. Do i need one there?

Sorry! Yes, *EVERY* table needs a primary key. Contrary to the impression
Access may give, it is *not* necessary that the primary key be an Autonumber;
if you have (say) an administration-assigned student ID, or a stable, unique
ModuleNumber, those fields would be perfectly suitable as a PK.

Many thanks you have been a real help.

You're welcome! Good luck.

 

[Jason]

Why have to have primary key?

Sorry! Yes, *EVERY* table needs a primary key. Contrary to the impression
Access may give, it is *not* necessary that the primary key be an Autonumber;
if you have (say) an administration-assigned student ID, or a stable, unique
ModuleNumber, those fields would be perfectly suitable as a PK.


You're welcome! Good luck.

 

[John W. Vinson]

Why have to have primary key?

So you can uniquely identify the record. If there is no primary key there is
nothing to prevent you from having two, or three, or 871 identical records; a
JOIN or a DELETE query will not be able to distinguish which of them you want.

 

[Jason]

Even though index is set to yes (no duplicates)?

So you can uniquely identify the record. If there is no primary key there is
nothing to prevent you from having two, or three, or 871 identical records; a
JOIN or a DELETE query will not be able to distinguish which of them you want.

 

[John W. Vinson]

Even though index is set to yes (no duplicates)?

That will help, but it will not allow you to establish referential integrity,
whereas a primary key will. What's your objection to the PK?

 

[Jason]

No objection - what you are saying that there must be a table with a field
set to yes (no duplicates) and also set this field as pk. I have on at least
one occasion used two or three fields as the primary key (individual fields
could have duplicate records but the combination of these fields can not
have a duplicate record).

 

[John W. Vinson]

No objection - what you are saying that there must be a table with a field
set to yes (no duplicates) and also set this field as pk. I have on at least
one occasion used two or three fields as the primary key (individual fields
could have duplicate records but the combination of these fields can not
have a duplicate record).

Sorry... should have clarified. Your table needs a Primary Key, but that key
need not consist of just one field, it could have ten.