Query Positions by Month

M

MTstraw

I am trying to add a form to a database I have already created. What I am
needing to do is have names show up for personnel who are qualified at a
certian position show up in form view. I need it to show a different qual
every month for recertification. I would like to set up the form so that
when that month comes around all you have to do is open the form and everyone
qualed for the position for that month show up.
 
J

John W. Vinson

I am trying to add a form to a database I have already created. What I am
needing to do is have names show up for personnel who are qualified at a
certian position show up in form view. I need it to show a different qual
every month for recertification. I would like to set up the form so that
when that month comes around all you have to do is open the form and everyone
qualed for the position for that month show up.
Click to expand...

Base the form on an appropriate Query.

Since you haven't posted any indication of how qualifications are stored in
your table, or how you can determine which qual applies to each month, I can't
really suggest how! More details please?

John W. Vinson [MVP]
 
M

MTstraw

The quals are stored as date of qual (dd-MMM-yy). The quals for each month
are January FWS, February, Radar Sup, March Approach, April Tower Sup, etc...
 
J

John W. Vinson

The quals are stored as date of qual (dd-MMM-yy). The quals for each month
are January FWS, February, Radar Sup, March Approach, April Tower Sup, etc...
Click to expand...

If these are fieldnames, you don't have a database; you have a spreadsheet.
"Fields are expensive, records are cheap".

Again: What Tables do you have in your database?
What is the Primary Key of each table?
How are the tables related?
What are the names, datatypes, and relevant content of the fields which affect
this requirement?

John W. Vinson [MVP]
 
M

MTstraw

Only one table labeled Training Chief.
Primary key is last name.
Field Names:
Name (Text)
Rate (Text)
Team (Text)
Field1 (Yes/No)
FP (Date/Time) Due November
Field2 (Yes/No)
FD (Date/Time) Due October
Field3 (Yes/No)
GC (Date/Time) Due September
Field4 (Yes/No)
LC (Date/Time) Due August
Field5 (Yes/No)
TS (Date/Time) Due July
Field6 (Yes/No)
RFC (Date/Time) Due June
Field7 (Yes/No)
CD (Date/Time) Due May
Field8 (Yes/No)
DS (Date/Time) Due April
Field9 (Yes/No)
AA (Date/Time) Due March
Field10 (Yes/No)
AP (Date/Time) Due March
Field11 (Yes/No)
RS (Date/Time) Due February
Field12 (Yes/No)
FWS (Date/Time) Due January

Each field is associated with the position below it (ie. field1 = FP, field2
= FD).
The Yes/No fields are not relevent to the Query/SQL I am trying to create,
just the positions. I am only wanting their name to show up if they are
qualified for that position. What I want it to look like when done.

TS
Abate (Check Box) (Date of Qual)
Anderson (Check Box) (Date of Qual)
Bailey (Check Box) (Date of Qual)
 
J

John W. Vinson

Only one table labeled Training Chief.
Primary key is last name.
Field Names:
Name (Text)
Rate (Text)
Team (Text)
Field1 (Yes/No)
FP (Date/Time) Due November
Field2 (Yes/No)
FD (Date/Time) Due October
Field3 (Yes/No)
GC (Date/Time) Due September
Field4 (Yes/No)
LC (Date/Time) Due August
Field5 (Yes/No)
TS (Date/Time) Due July
Field6 (Yes/No)
RFC (Date/Time) Due June
Field7 (Yes/No)
CD (Date/Time) Due May
Field8 (Yes/No)
DS (Date/Time) Due April
Field9 (Yes/No)
AA (Date/Time) Due March
Field10 (Yes/No)
AP (Date/Time) Due March
Field11 (Yes/No)
RS (Date/Time) Due February
Field12 (Yes/No)
FWS (Date/Time) Due January \
Each field is associated with the position below it (ie. field1 = FP, field2
= FD).
The Yes/No fields are not relevent to the Query/SQL I am trying to create,
just the positions. I am only wanting their name to show up if they are
qualified for that position. What I want it to look like when done.

TS
Abate (Check Box) (Date of Qual)
Anderson (Check Box) (Date of Qual)
Bailey (Check Box) (Date of Qual)
Click to expand...

Sorry, but this table design IS WRONG and is the source of your problems.
You're "Committing Spreadsheet upon a Database", a misdemeanor punishible by
being required to study normalization!

You need *three* tables:

Employees
EmployeeID <Primary Key>
LastName
FirstName
TeamID <link to a table of Teams>
<other biographical information>

Qualifications
QualID <Primary Key, probably an autonumber>
ShortcutName <e.g. "FD", "RFC">
Description <Text or Memo>
MonthDue <Number, 1 - 12>

EmployeeQualifications
EmployeeID <link to Employees>
QualID <link to Qualifications>
DatePassed <Date/Time>
Comments

John W. Vinson [MVP]
 
Top