Creating a Report from one column of a table

[sea_minerals]

I am not sure if this is possible to do but am hoping it is.

I've created a database that gives information on clients and jobs.
People fill out a form of a person details and tick different jobs -
theres about 50 to choose, but no more than 10 would be picked for one
person. Once they are ticked, it is shown in the table all correctly.


What i am hoping to do is produce a report for each job - over 50
reports. But the report only shows one of the column/ticked boxes.

For example, if theres 20 people and out of them 5 have the job
'retail' ticked, the report for Retail will only show them 5 people.

Some people will appear more than once - depending on how many tick
boxes have been marked.

Is this possible to do for a report? At the moment i am view every
person - i've managed to get people wanting a certain job at the top -
with the tick showing but would like to have it just as the people
wanting that, and the others do not show.


Any help would be very much appreciated!

 

[Allen Browne]

What you suggest is not the ideal way to approach this. Instead of having a
check box in the client table for each job, you need another table.

You already have:
- a Client table, with a ClientID primary key
- a Job table, with a JobID primary key.

Create a 3rd table with fields:
- ClientID relates to Client.ClientID
- JobID relates to Job.JobID
The primary key can be the combination of the 2 fields (or an AutoNumber if
you prefer.) Save it with a name such as ClientJob.

On your client form, remove the 50 check boxes, and put a subform there
instead. The continuous subform is bound to the ClientJob table. You add a
new *record* in the subform for each job the person is involved in.

For more information about why to go this way, see:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

Once you have that, you will probably find it easy to create a query using
the 3 tables, and make the report from that. You can then filter the report
to one person, or one job, or whatever. Post back if you get that far and
are still stuck.

 

[sea_minerals]

Thank you for the reply.

I have tried what you said but i dont think it came out right.

Is there a way to have the subform as drop down boxes instead of check
boxes?

The people that will be using it, have very basic computer skills - so
im trying to keep it very simple and easy to add information - thats
why the check boxes were used - so they can fill in client details
and then just tick the jobs, no other typing etc.

thanks again.

 

[Allen Browne]

Yes: you can use a combo box for selecting the JobID in the subform. That's
generally the way to do it.

You don't need the ClientID combo in the subform: it automatically gets the
ClientID value from the main form if the subform is set up correctly.

 

[sea_minerals]

Thanks for the reply.

Its proving to be a lot more complicated!

With a subform i continue to get 'type mismatch expression' or
something.

Is it possible to have - say 10 drop down boxes, each with the list of
jobs (theres about 70). Then users can scroll down to a job, select it
and can do it 10 times for each client.

From those jobs they selected that information goes into the table.

Users wont be reading or accessing the table. Hopefully, there will be
reports for each job (over 70) and they can view which clients are
interested in that kind of work.

If it doesn't work, its driving me up the wall already! i may just
have to go with the ticks and have the report include every person but
a specific job at the top showing the tick.

thanks again.

 

[sea_minerals]

I managed to fix the error that kept coming up. I had three tables,
Client, job and CLientJob (with IDs), and they were linked, but it was
set to text instead of number, which i am assuming is why it wouldn't
work.

The subform now opens! and actually looks like a table, not a white
box.

Now, i just gotta get the jobs in there! I need a list so people can
choose form pre-entered stuff - people will type things different,
spell it wrong so it wouldn't all be the same.

 

[Allen Browne]

In the Database window (or Nav Pane if using A2007), under Forms, select the
subform and open in design view.

Open the Properties box, and set Default View to Continuous. This will allow
you to add a Form Header section, color, size and arrange the column as you
want (since you aren't happy with the Datasheet view that looks like a
table.)

While you are there in design view, you have a combo for the Job? Set the
Row Source property of the combo to a query that loads the fields you want,
e.g.:
SELECT JobID, JobName FROM tblJob ORDER BY JobName;
You can then set the Column Count and the Column Widths property so the
combo has the columns from the query and displays what you want. For the
example above, you would use:
Column Count 2
Column Widths 0

There is a learning curve in understanding how to get this stuff right, but
its worth the effort.

 

[sea_minerals]

Thank you!

It finally worked with combo boxes instead - it sure does look better
than have 70 tick boxes. It made your eyes go funny!

I have put 4 combo boxes - and each have a list - of about 70 jobs.
People can only choose 4, but thats really more than enough come to
think of it.

Am i able to create a report - or a query (and then a report from
that) based on just one job in the drop down list. Eg. if someone
clicked on Accounting in any of the 4 combo boxes- that client details
will appear. if a client did not have it select they will not show in
the report.

thank you so much for your help. its really appreciated.

 

[Bob Quintal]

(e-mail address removed) wrote in

Thanks for the reply.

Its proving to be a lot more complicated!

With a subform i continue to get 'type mismatch expression' or
something.

Is it possible to have - say 10 drop down boxes, each with the
list of jobs (theres about 70). Then users can scroll down to a
job, select it and can do it 10 times for each client.

You are thinking spreadsheet, not database.

You have to create 3 tables for what you want to do, one for persons
(one person per row), one table that lists the jobs, one job per
row, and a third table that relates one person to one job.
if a person has 5 jobs, then there shall be 5 jobs for that person,
if 50 people pick the same job, there are 50 rows in this third
table.

This way you need one combobox to pick the job, and if the same
person picks a second job, you add a row to the subform and use the
same combobox, but on the second row, to pick the next job.

Users wont be reading or accessing the table. Hopefully, there
will be reports for each job (over 70) and they can view which
clients are interested in that kind of work.

If it doesn't work, its driving me up the wall already! i may just
have to go with the ticks and have the report include every person
but a specific job at the top showing the tick.

thanks again.