report w/multiple criteria for one field?

[liv004]

If anyone is still looking at this, I still need help. I will post update at bottom of page. Please any help is appreciated.

I have not been able to figure out how to get a report to tell me what I need. My Access knowledge is not extensive. I have the 3 following tables and 1 query.

Table1 – “ClientTBL”
*CID (Client ID primary key)
CFirstName (Client First Name)
CLastName (Client Last Name)
EnrollmentDate (Client Enrollment Date)

Table2 – “SkillsTBL” (Will be a static table that is just a list of skills, not updated or not updated often)
*SkillID (Skill ID primary key)
Skills (Skills List)

Table3 – “ClientSkillsTBL”
*CID
*SkillID

Query – “ClientSkillsQry”
Skills
CFirstName
CLastName
CID

[FONT=&quot]It sounded so simple, but I cannot figure this out. All I want is to come up with a way to make a report that will show me which clients match specific skills. This could be just one skill (which would be easy) or it could be multiple skills. Basically if an employer calls in and says they need someone who has experience welding and plumbing. I just want to print something out that gives me that list of individuals matching all skills. Is this possible at all? [/FONT][FONT=&quot] I do have the query that shows me all the clients and all of their skills listed individually and I thought I was on the right path but all the options I have tried show me clients matching any of the skills. I attached my database if needed. It is just a few dummy records & an input form so it is small. Any help is greatly appreciated.
[/FONT]

 

[SpentGeezer]

This puts em into a message box. Alter the code to put in a report.

 

[liv004]

Thank you for your reply. This is a neat idea. I think my co-workers will prefer the report so I will work on that. But first and I'm going to apologize up front...I'm not good with code. I put your code into my database, it is all there and works except for no matter what skill I pick, it says no matches. Based on that would you know what part I missed and didn't replace? Again thank you so much I have been trying to figure this out and it will be great to be able to actually begin using it with my co-workers. Otherwise I would not have gotten this because, I am not even sure what your code means lol. I get some of it. I really need to check out these VBA tutorials to understand more.

 

[SpentGeezer]

Did you try importing your tables from the old database into my one and see if it works? I used the exact same table structure, so it should work. Let me know how that goes.

 

[liv004]

I did figure it out today. The form actually requires you to pick all of the skills that match the client. Even with your original, if you select one skill only it tells you there are no matches, except for the last skill as your last client only has that one skill. I'm not sure if that is what you intended or not. I actually need to be able to select say one skill and find any client who has at least that skill or be able to select a few skills and find any client who has at least all of those skills. So a client can have many skills, but I just need to pull clients who have at least all of the skills requested. Your idea is closer than I have gotten with anything else I have tried though. I feel like I'm so close, yet so far away.

 

[SpentGeezer]

This one has both exact matches and partial matches, still to message box (not report sorry..)

 

[liv004]

ok thanks. I will check it out monday when I get to work. thank you so much for helping me with this.

 

[liv004]

i did try that one today. it still said no matches if i only selected the first skill, but this time after clicking ok, it gave me all of the names.

 

[liv004]

Ok I'm still stumped on this. I have been reading tutorials, samples, tips, and tricks for like 3 weeks now. I will attach my new database to this. I have a listbox on a form now. I can select multiple skills and the report displays. My problem is that it displays if any client has any of the skills. I need it to display only clients that have at least all of the skills selected. Is this possible? The previous suggestions were close as well. They however required the selection of all of the client's skills before the client would be a match. I need to be able to ask for one skill and all clients who have that one skill in their list of skills would come up. Or if I ask for 3 skills, I need the list of clients who have all 3 of those skills in their list of skills. On my database the "MatchClients" Form is what I was using to pull up the report. I like the way it is set up for the most part (once the skills list is actually put it, it is really long, so scrolling the listbox wont be great). Again any help is appreciated, I have been unable to find any examples that someone wants to match multiple criteria on the same field with AND instead of OR