Hi Evoke,
First, I strongly agree with pietlinden: you should bite the bullet and
normalize your data. You are likely to run into issues in the future and if
not you, someone else. Non-normalized data shows up in all kinds of ways in
terms of problems, like this one, that wouldn't exist if your data was
properly normalized.
That said, you CAN do what you want. I can tell you basically what to do,
but since I don't have enough info about the data types stored in your query,
I can't say exactly. You'll have to adapt the idea.
**BACKUP YOUR DATABASE**
***I am going to completely ignore your table with the job choices and
instead of using a combo box on the form we make, use a text box. You will
eventually want a combo box based on that table with 80 jobs. Here's the
key, though. When you test this out using the text box, make sure the value
you enter in the text box reflects the values stored in teh job fields in the
query. If the values in the job fields are the names of jobs (receptionist,
plumber, attorney, etc), then that's what you type in the text box on teh
form. If they are numbers. then you type the number of the job. If those
values ARE numbers, don't worry. When you convert the text box to a combo
box and base it on your other table, it will all work out.
Those two issues aside, here you go:
1. Make a new query and base it on the table that has the client details
(name, dob, etc, jobChoice1, jobChoice2 etc...that table). Pull down all the
fields you want to use in your report, including all the job fields.
2. In the 'criteria' row under field jobChoice1, add the following:
Like "*" & Forms!frmSearchJobChoices!txtJobChoice & "*"
3. In the next job field (jobChoice2), in the row BELOW CRITERIA (the row
labeled 'or'), type the same thing. In the next job field (jobChoice3), in
the row TWO ROWS BELOW CRITERIA, type the same thing. Do that for all the
jobChoice fields. Keep stepping down one row for each successive field.
Save/Close the query.
4. You need a report based on that query. I'll let you worry about the
report design. For testing purposes, just use the report wizard and base it
on the query you made here. Include the name fields of the person, and all
the job fields Don't worry about grouping/sorting. You can do that after
you verify you are getting the results you want.
The only other thing I'll say about report design is that you will have a
small headache using the jobChoice fields in your report because no single
job choice field will return with the value of the job you are looking for.
You will see when the report opens what I mean.
So, to get past that you can pull the value of teh job you searched for from
the form you use to filter the query, which we'll get to now. If you want to
list all the jobs those people listed in the six fields (read: the job you
searched for and the other five each of those people listed), then it's going
to be a little more painful to work around to get things to look right since
you will, again, not know which one of the fields has the job you searched
for since it will be different depending on what field that person entered
that job. Hello Normalization!
Save and close your report.
5. That criteria you applied to the different fields in the query we made
includes a reference to a form control. It was
Forms!frmMyJobChoices!txtJobChoice. You need to make that form, the control
referenced, and a command button. So create a new form (in design view).
You will name it when you close/save it.
6. create a text box. I used text box because I wasn't sure how your table
with the jobs was setup, and I didn't want to have to make up a table of my
own to test this with. For now, just do the text box. You will eventually
want a combo box based on that table with the 80 jobs in it. If you get all
this working, you can go back and change teh control to a combo box. It
really won't be much aditional work at all.
You can draw the text box using the toolbox from the view menu. Open the
toolbox and click on the text box button, then click in your form and drag
down and to the right and release.
7. Make sure the new text box is selected, open teh properties window (f4),
click the 'other' tab, and name it txtJobChoice
8. Add a command button. If you use the toolbox, and you have the control
wizard button clicked, a wizard will start to run automatically after you
drag out the command button. Just cancel it. Make sure the command button
is selected, make sure the properties window is open (f4 if it isn't), click
on the 'events' tab, and find the 'click' event in the list. There is a
white box beside where it says 'click'. Double left click in it to
automatically bring up the words 'event procedure'. If you can't get that to
work, use the arrow further to the right and choose 'event proecudure' from
the drop down. Once you have that, click the button still further to the
right with the dots on it (...). The vba editor will open and you will see a
line that says Sub txtJobChoice_Click()
A little below that line will be an 'End Sub'
9. Between those two lines, type this:
DoCmd.OpenReport "rptYourReportName", acViewPreview
NOTE: rptYourReportName is the name of your report that you made, so replace
rptYourReportName appropriately.
10. Close the vba editor. Close the form. Name it frmMyJobChoices
11. Open the form back up in normal mode. Fill in an appropriate job
choice. (remember my comments at the top about what is an appropriate choice)
Then, click the button.
If my instructions weren't too complicated or long-winded, you should see
the results: 1 record for every person who put the job you searched for in
one of the six jobChoice fields. Remember, if the values in your query for
thoe jobChoice fields are numbers, you need to enter a number in the
txtJobChoice for things to work. When you convert the text box to a combo
box, you will be able to resolve that so that the users are selecting job
names.
HTH,
CW
<waves>