Looking for Microsoft Access Assistance

I don't have much experience with Excel per se, but my first instinct would be to set up a few SQL queries on your data table. Like for the Required, a " SELECT className from Classes where isRequired is 'x' "

 

To me a pivot table in Excel would be more powerful, easy, and friendly and it can use your access db as a source. I know that's not what you or they asked for but the access front end is my weak area. It does have the advantage that you are familiar with Excel and already use it for some of your stuff.

 

A few tips for you, Tony:

First, your table layout makes things rather difficult, but not impossible. More importantly, it will make it a huge pain to maintain, as additional roles or courses are added. A much easier table structure would have 3 columns - Role, Course, and state. Then each Role would have multiple rows in the DB, one for each Course that role has to take, with "Required" or "Optional" for the state. For example:

Code:

Role      Course  State
Position1 Course1 Required
Position1 Course3 Required
Position1 Course4 Optional
Position1 Course5 Required

With that table, it's extremely easy to get all of the required:

Code:

SELECT Course
FROM CourseMatrix
where Role = 'Position1' and State = 'Required'

And all the optional courses:

Code:

SELECT Course
FROM CourseMatrix
where Role = 'Position1' and State = 'Optional'

for a given role.

Now, given your table structure, we would have to do something like this for the required courses:

Code:

SELECT IIF(Role = 'Position1', 'Course1')
FROM CourseMatrix
WHERE Course1='X' AND Role='Position1'
UNION
SELECT IIF(Role = 'Position1', 'Course2')
FROM CourseMatrix
WHERE Course2='X' AND Role='Position1'
UNION
SELECT IIF(Role = 'Position1', 'Course3')
FROM CourseMatrix
WHERE Course3='X' AND Role='Position1'
UNION
SELECT IIF(Role = 'Position1', 'Course4')
FROM CourseMatrix
WHERE Course4='X' AND Role='Position1'
UNION
SELECT IIF(Role = 'Position1', 'Course5')
FROM CourseMatrix
WHERE Course5='X' AND Role='Position1'

And this for the optional courses:

Code:

SELECT IIF(Role = 'Position1', 'Course1')
FROM CourseMatrix
WHERE Course1='O' AND Role='Position1'
UNION
SELECT IIF(Role = 'Position1', 'Course2')
FROM CourseMatrix
WHERE Course2='O' AND Role='Position1'
UNION
SELECT IIF(Role = 'Position1', 'Course3')
FROM CourseMatrix
WHERE Course3='O' AND Role='Position1'
UNION
SELECT IIF(Role = 'Position1', 'Course4')
FROM CourseMatrix
WHERE Course4='O' AND Role='Position1'
UNION
SELECT IIF(Role = 'Position1', 'Course5')
FROM CourseMatrix
WHERE Course5='O' AND Role='Position1'

Obviously, as the number of courses increases, this becomes a huge pain in the nice person...


Short description of how the long queries work:
Each select statement checks for a single course for that role, and if it's found to be an X (for the first part) or an O (for the second), it returns the course name (that's the part in the IIF section). By taking each of the select statements and joining them with the UNION command, the results are appended to each other. I would STRONGLY recommend spending some time to rework the table structure to make the queries easier, as in my first examples above - it will save you a TON of headaches down the road.

 

Oh and PS. I work with Databases pretty much all day, every day. If you have any more questions Tony, i'd be happy to help!

 

If i were you... i would probably spend a small amount of time writing a little throw away program to parse through the current data (exported to a CSV) and organize it the way you want for Access, outputting to another CSV file. It would probably run pretty quickly, and make things a whole lot easier - especially since maintaining the access database (or the table in excel) that is produced would probably be almost as easy as it was before. Simply loop over the first line to get all the classes into a list, then for each line the first cell is the position, the rest of the cells match the index on the list, their state telling you what to put in the state column. Should be pretty straight forward.