Looking for Microsoft Access Assistance

[TonyPSchaefer]

I work in the Training Department. I was asked to generate a report of all the courses required and optional for all current roles/responsibilities in our division. Originally, I figured that this is no big deal. In fact, I already have a spreadsheet listing all roles/responsibilities and every course we offer (it's what I do). I have Xs and Os showing whether a course is required or optional, respectively.

Here are the problems I'm facing:
1) Since the roles are listed in column1 with the courses running from columns2 - infinity, I can't use auto-filter.

2) They don't want an Excel printout, they want a friendly Access report.

I've dabbled with Access in the past but this one's got me spinning my wheels.

The report I envision would look like this:

Position1:
Required:
- Course1
- Course3
- Course5
Optional:
- Course4

So it's got to look for the Xs and then provide the Field Name. Is that possible and who can help me with this?

[a_gray_prius]

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' "

[Mark57]

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.

[TonyPSchaefer]

Thanks guys. Yeah, Excel is my stronger suit than my Access. I'm sure that if I had enough time I would be able to work it out but I don't have the luxury. In fact, I was asked to have this done by the end of the week. I figured it wouldn't be that big of a deal since I have all the data.

[eagle33199]

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:

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:

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

And all the optional courses:

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:

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:

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 ass...


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.

[eagle33199]

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!

[TonyPSchaefer]

Thanks, Eagle. It sounds like the chore I thought it was. I understood the logic behind it but couldn't place how to make it work. Now I realize that I did understand but subconsciously chose to block it out.

All in all, there are 187 courses and 51 affected positions.

It was easy to construct and maintain in Excel because I just had to dump in the course names and positions and then start marking Xs and Os. For an overview, it's easy to look at and understand. It's when they asked for a pretty report that things got complicated.

[eagle33199]

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.