Creating a Report from one column of a table

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!

0
sea_minerals
9/6/2007 5:04:47 AM
access 16762 articles. 3 followers. Follow

8 Replies
602 Views

Similar Articles

[PageSpeed] 41

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.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<sea_minerals@yahoo.com> wrote in message
news:1189055087.456789.22890@22g2000hsm.googlegroups.com...
>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! 

0
Allen
9/6/2007 7:22:10 AM
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.







On Sep 6, 4:22 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> 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.
>
> --
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> <sea_miner...@yahoo.com> wrote in message
>
> news:1189055087.456789.22890@22g2000hsm.googlegroups.com...
>
>
>
> >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!- Hide quoted text -
>
> - Show quoted text -


0
sea_minerals
9/6/2007 11:36:42 AM
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.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<sea_minerals@yahoo.com> wrote in message
news:1189078602.087707.57000@57g2000hsv.googlegroups.com...
> 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.
>
>
> On Sep 6, 4:22 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
>> 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_miner...@yahoo.com> wrote in message
>>
>> news:1189055087.456789.22890@22g2000hsm.googlegroups.com...
>>
>>
>>
>> >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!- Hide quoted text - 

0
Allen
9/6/2007 2:27:42 PM
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.




On Sep 6, 11:27 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> 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.
>
> --
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> <sea_miner...@yahoo.com> wrote in message
>
> news:1189078602.087707.57000@57g2000hsv.googlegroups.com...
>
>
>
> > 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.
>
> > On Sep 6, 4:22 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> >> 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_miner...@yahoo.com> wrote in message
>
> >>news:1189055087.456789.22890@22g2000hsm.googlegroups.com...
>
> >> >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!- Hide quoted text -- Hide quoted text -
>
> - Show quoted text -


0
sea_minerals
9/7/2007 1:52:25 AM
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.



On Sep 7, 10:52 am, sea_miner...@yahoo.com wrote:
> 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.
>
> On Sep 6, 11:27 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
>
>
>
> > 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.
>
> > --
> > Allen Browne - Microsoft MVP.  Perth, Western Australia
> > Tips for Access users -http://allenbrowne.com/tips.html
> > Reply to group, rather than allenbrowne at mvps dot org.
>
> > <sea_miner...@yahoo.com> wrote in message
>
> >news:1189078602.087707.57000@57g2000hsv.googlegroups.com...
>
> > > 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.
>
> > > On Sep 6, 4:22 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> > >> 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_miner...@yahoo.com> wrote in message
>
> > >>news:1189055087.456789.22890@22g2000hsm.googlegroups.com...
>
> > >> >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!- Hide quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -


0
sea_minerals
9/7/2007 2:40:16 AM
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.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<sea_minerals@yahoo.com> wrote in message
news:1189132816.852446.126840@22g2000hsm.googlegroups.com...
>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.
>
>
>
> On Sep 7, 10:52 am, sea_miner...@yahoo.com wrote:
>> 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.
>>
>> On Sep 6, 11:27 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
>>
>>
>>
>> > 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.
>>
>> > --
>> > Allen Browne - Microsoft MVP.  Perth, Western Australia
>> > Tips for Access users -http://allenbrowne.com/tips.html
>> > Reply to group, rather than allenbrowne at mvps dot org.
>>
>> > <sea_miner...@yahoo.com> wrote in message
>>
>> >news:1189078602.087707.57000@57g2000hsv.googlegroups.com...
>>
>> > > 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.
>>
>> > > On Sep 6, 4:22 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
>> > >> 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_miner...@yahoo.com> wrote in message
>>
>> > >>news:1189055087.456789.22890@22g2000hsm.googlegroups.com...
>>
>> > >> >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!- Hide quoted text -- Hide 
>> > >> > quoted text -
>>
>> > - Show quoted text -- Hide quoted text -
>>
>> - Show quoted text - 

0
Allen
9/7/2007 5:39:12 AM
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.



On Sep 7, 2:39 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> 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.
>
> --
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users -http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> <sea_miner...@yahoo.com> wrote in message
>
> news:1189132816.852446.126840@22g2000hsm.googlegroups.com...
>
>
>
> >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.
>
> > On Sep 7, 10:52 am, sea_miner...@yahoo.com wrote:
> >> 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.
>
> >> On Sep 6, 11:27 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
>
> >> > 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.
>
> >> > --
> >> > Allen Browne - Microsoft MVP.  Perth, Western Australia
> >> > Tips for Access users -http://allenbrowne.com/tips.html
> >> > Reply to group, rather than allenbrowne at mvps dot org.
>
> >> > <sea_miner...@yahoo.com> wrote in message
>
> >> >news:1189078602.087707.57000@57g2000hsv.googlegroups.com...
>
> >> > > 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.
>
> >> > > On Sep 6, 4:22 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid> wrote:
> >> > >> 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_miner...@yahoo.com> wrote in message
>
> >> > >>news:1189055087.456789.22890@22g2000hsm.googlegroups.com...
>
> >> > >> >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!- Hide quoted text -- Hide
> >> > >> > quoted text -
>
> >> > - Show quoted text -- Hide quoted text -
>
> >> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -


0
sea_minerals
9/7/2007 6:17:30 AM
sea_minerals@yahoo.com wrote in
news:1189129945.008160.42690@r34g2000hsd.googlegroups.com: 

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

>>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.
> 
> 
> 
> 
> On Sep 6, 11:27 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
> wrote: 
>> 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. 
>>
>> --
>> Allen Browne - Microsoft MVP.  Perth, Western Australia
>> Tips for Access users -http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> <sea_miner...@yahoo.com> wrote in message
>>
>> news:1189078602.087707.57000@57g2000hsv.googlegroups.com...
>>
>>
>>
>> > 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.
>>
>> > On Sep 6, 4:22 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
>> > wrote: 
>> >> 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_miner...@yahoo.com> wrote in message
>>
>> >>news:1189055087.456789.22890@22g2000hsm.googlegroups.com...
>>
>> >> >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!- Hide quoted text
>> >> > -- Hide quoted text - 
>>
>> - Show quoted text -
> 
> 
> 



-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

0
Bob
9/7/2007 8:59:56 AM
Reply:

Similar Artilces:

multiple Domains under one domain or multiple forests under one forest
I have to research on below scenario. Asia regional countries are in separate forests/single domains Europe has forest and child domains. Europe is separate systems only connected via WAN only. Our proposal was make one forest under Europe as =91APAC=92, and then we will migrate regional counties under to APAC forest to appropriate Trees. Europe proposed that instead of creating APAC migrate all the Asia regional forest to one of Europe Domain. As example, migrate all Asia user accounts to Germany domain under Europe forest. for me putting every domain to one domain sounds messy f...

First Try at Creating a User Form
I have exhausted my search abilities to find an answer to what should be a simple question. I created a user form that pretty much duplicates the built in Data Form. The major difference is that it does not contain all of the fields in the data base -- just a few of them and it doesn't have all of the controls only a Next and Previous control as well a Close. The form is just what I want but I can't figure out how to get it to display the values of the fields when I open it. That is the first problem.. I want it, like the built in form, to show the values of fields for each recor...

A/P Aging Reports
We are running GP 8.0 and are having problems with the A/P Aging Reports. We cannot print reports to only show only what is open. We check off the option to not include zero balances and no activity but the reports still print them. The reports are up to 500 pages. Are we doing some wrong? Thank you You should select the option of "Fully Paid Documents, Unposted & Multicurrency". You may also want to make sure you don't select "detail". That way you will only get a summary for each vendor. "Rich" wrote: > We are running GP 8.0 and are having pro...

Flowcharting Table relations
Hey guys and girls. New one for you. I am doing an IT audit of Great Plains and need to flowchart how the data files are related to each other. In other words, how does one file feed into another file, and what field or fields in a particular table is used to relate/link to another table. Does anyone where I can find such information? Thanks Check out the Great Plains SDK available on the installation CDs. There are several diagrams that describe table relationships. Also, there are module documents that contain posting flowcharts. Regards, Kevin Rood Corporate Software Consultants...

Help! How I can create...
I need to create a toolbar (if it is) such as the workspace window in VC++ with a CTreeCtrl within... How I can proceed? Thank you! On Thu, 27 Nov 2003 13:03:19 +0100, Gaetano Sferra <rebusmail@iol.it> wrote: > I need to create a toolbar (if it is) such as the workspace window in > VC++ > with a CTreeCtrl within... > How I can proceed? > > Thank you! > > Hi, Check out www.codeproject.com It has all the answers and more. Paul. > Check out www.codeproject.com It has all the answers and more. Hi Paul, please be more specific... I've already se...

Search for data in a column bring all related items in other colum
I have data in the excel sheet with .A column for OrderNo and B Column for items for that Order. A B ORD001 ITEM1 ITEM2 ITEM3 ORD002 ITEM4 ITEM1 ITEM5 There are more than thousand orders.I want search for an Order so that it brings all items with it. I cannot use filter in A column as it doesn't recognise blank cell.So Option to repeat order nos in A for every item involves a lot of data entry. Any Solution ? I'd fill those empty cells in column a with the previous value. And us...

SQL Table
Hello, In Receivables Management, Customer Maintenance, there is a popup for the internet address for each customer's Address ID. In that popup is a field for the Email Address which is contained inside the Internet Information box. Can some please tell me where this email address is stored in the SQL database? Thanks. -- Bob Try SY01200 Hope this helps, rc. "Bob B" wrote: > Hello, > > In Receivables Management, Customer Maintenance, there is a popup for the > internet address for each customer's Address ID. In that popup is a field for > the Email...

Linking to a file from a report
Total newb here. I want to have a link to a PDF file from a report. In theory the file name will be different for each record. Each record has a field for file name whether or not there is a file available (blank or null). All the files are in the same folder. If I understand correctly, the path needs to be stored in the table holding the file name. I have different users entering the data in a form but I don't want them to have to enter in the file path, just the file name. Also, I don't want the file path displayed on the report, just the file name. Is this done with a...

Subtotal in a pivot table
I have a question on calculating something in a pivot table. What I am trying to get is a % of the subtotal break in my table. I have a calculated field but cannot figure out how to get it to calc. the % off the shop total not the Total for the table. See the example below. For Shop 246-Database I want the Sum of % of shop total to be based off the Database DeliveryAmt (10575) / Shop 246 total DeliveryAmt (17317) = 61%, not the pivot table total (185852) = 5.7%. Retail should be Retail DeliveryAmt (6742) / Shop 246 total DeliveryAmt (17317) = 39%, not the pivot table total (185852)...

how do I find an average number of specific words in a column
I am attempting to calculate a number of specific word occurrences. In example, I have a column with yes in certain cells, and no in the others. How can I display the total number of yes and no occurrences? I am fairly new to Excel and know very little about coding in it. Hi you can use the countif function to return the numbers of "yes" and "no" e.g. =COUNTIF(A1:A100,"yes") will count the number of "yes" answers in the range A1 to A100 likewise, =COUNTIF(A1:A100,"no") will count the number of "no" answers in the same range H...

How do I lock N left hand columns
How do I lock N left hand columns in excel so that when I want to see some right hand columns and slide the columns on the right to the left the locked columns do not move and remain visible? Don J ------------------------------------------------------------------------ With A,B,C visible select D1 and hit Window / Freeze panes. This will freeze A,B,C in view If you hit D2 and do it you will also lock the headers in view (ie Row 1) Whatever cell you select it will freeze to the left and above that cell. -- Regards Ken.........................

MS Query Column Headings and Calculated Fields
I'm using MS Query to pull data from an ODBC data source. Everything works fine and I can extract data just great. I'm wondering, however, calculating some fields with the query. I can't seem to add a column heading without enclosing the heading in quotes. That would normally be fine, but everytime I go to edit the query and extra set of quotes is added to the column heading. Is there any way to get around this? I also would like to know if there is a way to use calculated fields in other calculations. If so, is there any documentation out there on how to do this? Thanks, -...

Report of Activities by Opportunity??
I want to see all the sellers activities for an opportunity. Can someone tell me if such a report is possible to build... and if possible how (quick hint)? Thanx a bunch. Mehdi you need to load the srs report designer into visual studio.net and start designing your report. Go to the link below and search in downloads for a few videos about creating reports with crm ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm "MA" <mamor@NOPSPAM.com> wrote in message news:3079a4cb13df4c6e853d1a90a2dfb055@ureader.com... >I want...

Best way to add a row to a table with calculations?
Suppose I have a worksheet like this: 1 2 Date Days 3 5/25/08 35 4 4/20/08 45 5 3/06/08 46 6 1/20/08 41 ... The value in the Date column is calculated (=A3-A4). I would like to add a row between rows 2 and 3 and have it acquire all of the properties of row 3. The best procedure I could come up with is: 1. Right click on row 3, select Insert. This inserts a row between 2 and 3, but it acquires the properties of row 2. 2. Select row 4 (old row 3) and press Ctrl-C (copy). 3. Select row 3 (new row) and press Ctrl-V (paste). This seems to work. Is there a better wa...

Hide one month in a chart
I have data from July 2006 to January 2007 that I need to chart. The only catch is that there is no data for Dec 06 (we do not capture this data due to the Xmas rush). On the chart I would like to remove Dec 06 but have not succeeded. Any ideas? In the cell where the Dec 6 data should go type =NA() This will display as #N/A Excel will ignore it when it charts But Dec 6 will still be on the x-axis (it would be misleading to not have it) best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email <dkbleechmore@optusnet.com.au> wrote in message news:1170456927...

Import MS Word 2003 Table to Excel
I have a word document that has many tables. I need to take each table and put them into one cell in excel if possible. I have several test documents that I was able to transfer the information from Word to Excel in a row per record. I need to keep the tables with their respective row(record). Any help is greatly appreciated. ...

Jet reports
Looking at info. about the product. >>www.jetreports.com << Does anyone know anything about this reporting software? Is it any good? It looks like a MS GP's product. If that is the case is it meant to replace FRx? Thanks ...

Creating a simple form in Outlook 2003
This is a multi-part message in MIME format. ------=_NextPart_000_000B_01C3FAEF.C4CC0AA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Can someone walk me through how I would set up a simple form in Outlook, = that when sent, the form would be in active visual mode when received by = a recipient? I don't find the help very helpful. I.e.... how about as an example it would ask the question "What day = would be best for you:". To the right of that I would like a simple drop = down menu that would have the choices Monday to ...

2 Y Axes: Lines and a Stacked Column
Hello, I'm using Excel 2003 SP3 and having trouble with the following... Sample data: X Axis Y Axis1 Y Axis1 Y Axis2 Y Axis2 Y Axis2 Date DataA DataB DataC DataD DataE 1/31/09 4.3 3.6 10% 40% 50% 2/28/09 2.9 1.9 30% 60% 10% 3/31/09 1.2 6.4 15% 10% 75% I need Y Axis1 to be two simple lines and Y Axis2 a stacked column that sums to 100%. Can't figure this out. Please advise... Thanks! Jeff First, clear the cell above the dates,...

Transfer data from Excel col. A to columns B-E in the same sheet
I have an Excel 2003 spreadsheet with only one column of player data: column A. The first three data items in column A are the same for every player: Name, Address and Phone. Every player also has at least one comment but could have any number of comments. Each player’s data is separated from the next by a blank cell in column A. Sometimes, a player’s last few comments are blank resulting in multiple blank cells in column A before the data for the next player starts. I need help writing an Excel 2003 VBA macro to: 1. Copy just the player’s name, but not the Name: label, to c...

Pivot Table Query #5
Hello I use Pivot Tables a lot in my stats and although I can normally work with them quite well, theres a reoccuring issue that I can't seem to resolve. I have one particular workbook that has lots of different worksheets and one at the end, with a variety of pivot tables on it. At the end of each month, I update the data on the worksheets, then on each pivot table, I use the wizard option to attempt to go back and re-select the relevant updated data. Sometimes this is successful, but on other (random) occasions, it won't let me and gives me the message 'A PivotTable...

Print Access report along with an outside checklist
Good afternoon Is it possible to print a report that's in Access and then right after print an Excel checklist? Background: I work for a large construction company that does regular maintenance on their equipment. Right now we print a parts ticket of the parts required for the particular service they are doing & then we print a copy of the manufacturer's check list of what should be done (which is an excel document). They would like to automate this procedure so that when you print the parts ticket it will automatically print the required manufacturer's ...

amortization tables
Looking for some macros or add-ins to assist with Amortization tables. Any help would be appreciated -- Message posted from http://www.ExcelForum.com > Looking for some macros or add-ins to assist with Amortization tables. One way to proceed is to start with an existing template. For example, you could open http://office.microsoft.com/templates/default.aspx and search the "Templates" section for "amort". shadestreet Here's one that may be useful to you http://www.dicks-blog.com/excel/2004/06/amortization_ta.html -- Dick Kusleika MVP - Excel Excel Blog ...

Delete records when certain records have duplicate column data
Hi, I'm new to excel. I want to delete (sort of) duplicate records. My spreadsheet has many columns. My spreadsheet has many records I want to delete records where the data in just a few columns is the same in multiple records. (e.g. if the values in columns "A" "B" "D" "F" in any record is duplicated in multiple rows ..delete all matching records/rows. A= house number B= street name D=apt number F=city Bonus points: Can a macro/button be created that will allow me to load a spreadsheet and then somehow run the above filter/function on the ...

Rows in a report 02-12-10
I have a report with mulitple text boxes in the detail of the report. One of the text boxes has a number in it. I want the report to output that many rows on the report even if the other text boxes are blank. Any way to do this? Thanks in advance. Check out Allen Browne's page http://www.allenbrowne.com/ser-39.html. -- Duane Hookom Microsoft Access MVP "PJ" wrote: > I have a report with mulitple text boxes in the detail of the report. One of > the text boxes has a number in it. I want the report to output that many > rows on the report...