only display certain query results

[Guest]

Have a query that searches for a specific training course subject.
However , the course could be in any number of fields,
ie the form for each employee has 30 fields labelled
trainingsubject1, trainingsubject2 etc. and it also has an expiry
date for each subjects. So I would like a report to show only
a specific course for all employees, at the moment the query
returns fields i don't need - ie if I have 5 employees who have
taken the course and they have taken it at different times then
their training lists would be different - ie employee 1 could have the
course listed in trainingsubject2, employee 2 in trainingsubject4 etc.
so if the trainingsubject number if different for all employees, and
lets say its only in subject fields 1 to 5, then query returns the subject
fields 1 to 5 for every one.
Anyone have any ideas how to sort this out ?

 

[Duane Hookom]

Normalize, Normalize, Normalize, Normalize, Normalize, Normalize, Normalize,
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

If you can't change the structure consider using a union query like:
SELECT EmployeeID, TrainingSubject1 as TrainingSubject, TrainDate1 as
TrainDate
FROM tblSpreadsheet
WHERE TrainingSubject1 is not Null
UNION ALL
SELECT EmployeeID, TrainingSubject2, TrainDate2
FROM tblSpreadsheet
WHERE TrainingSubject2 is not Null
UNION ALL
SELECT EmployeeID, TrainingSubject3, TrainDate3
FROM tblSpreadsheet
WHERE TrainingSubject3 is not Null
UNION ALL
--etc--

 

[Rick B]

Your database structure is wrong. You tried to make a spreadsheet in
Access.

If a person can take more than one class, then you must create a second
table for their classes. If they take one class, they'd have one record in
this table. If they take 50 classes, they'd have 50 records in this table.

In the following, you'd have one record for each person, one record for each
class, and multiple records in the third table.


tblPerson
PersonID
FirstName
LastName
etc.


tblClasses
ClassID
Description
Etc.


tblPersonClasses
PersonID
ClassID
Date
etc.



This is a classic many-to-many relationship. Rebuild your structure.

 

[Guest]

cheers rick for the input,

i have multiple tables - Training Company Table, Training Expiry Date Table,
Training Subject Table, is this what you mean ? or should there be tables
that have a single
field from each table group ? also the training company and subject is part
of a combo box, is this correct way to do it ? (ps. will change naming of
tables to tbl formats)

Cheers

 

[Rick B]

No, the table wher you track which classes they have taken is wrong. You
state that your table look slike this...

Name Course1 Course2 Course3 Course4
John ECON101 ACCT101 ECON102
Sue ACCT101 ACCT102 ECON101
Etc.




You need to have a table as...


STUDETID COURSE DATE
1234 ECON101 Jan 01, 2004
1234 ACCT101 Jun 05, 2004
1234 ECON102 Jan 02, 2005
1680 ACCT101 Jan 01, 2004
1680 ACCT102 Jun 05, 2004
1680 ECON101 Jan 02, 2005

Etc.



Of course you will need your other tables to define who student 1234 is, and
what ECON101 is. But, you don't track the classes in a single record as you
currently do. That is how you'd do it in a spreadsheet, not a database.
This of a spreadsheet as a ledger where items are listed across a single big
sheet of paper. A database is more like an index card fie box where each
item is on a separate index card.