I would love my users (11 to 17 year olds) to be presented with a form 
containing a field where they can request a Merit Badge and have the form 
look up all valid counselors for that merit badge.  The archetecture is in 
place to do this with a merit badge list for them to choose from and a list 
of counselors with links to their information (name, phone, etc.)

I just can't seem to get it to work!

Any ideas?? 
-- 
Dick Patton
Boy Scout Troop 403

On Sun, 4 Apr 2010 13:45:01 -0700, Dick Patton
<DickPatton@discussions.microsoft.com> wrote:

>I would love my users (11 to 17 year olds) to be presented with a form 
>containing a field where they can request a Merit Badge and have the form 
>look up all valid counselors for that merit badge.  The archetecture is in 
>place to do this with a merit badge list for them to choose from and a list 
>of counselors with links to their information (name, phone, etc.)
>
>I just can't seem to get it to work!
>
>Any ideas?? 

You'll need *three* tables for this, not two: a table of Badges; a table of
Counselors; and a third table related one-to-many to each of these, with
fields for BadgeID and CounselorID, and one record for each badge within a
counselor's expertise.

-- 

             John W. Vinson [MVP]

Hi John, me again!!

If you willl recall the other topic about deleteing records from a sub form, 
the same archetecture exists in the merit badge thread.  The Table 
Merit_Badge contains the list of valid merit badges with an auto_number Key.  
Another table contains the info on the counselor (name, address, phone, etc.) 
with his Person_ID (the key).  The third table (just as with schools and 
units ) contains the key pair of MB_code and Person_ID.  (as a side this 
relationship has the same problem as Schools - Units, you can't delete the 
record uning the sub form) This creates the linkage between the counselor and 
his merit badges, once again the key fosters the creation of a many to many 
relationship where there can be many counselors for a Merit_badge and mant 
Merit_badges for a Counselor but each relationship must be unique, so 
duplicate records are not permitted.

I hope this will satisfy the third table requirement, now this issue is how 
do i get the selected merit badge and list only counselors who do that marit 
badge or conversely lsit all merit badges that a given counselor is qualified 
for.

HELP -  I think i am loosin my mind, this seems so simple!!

Thanks loads AGAIN!
-- 
Dick Patton
Boy Scout Troop 403


"John W. Vinson" wrote:

> On Sun, 4 Apr 2010 13:45:01 -0700, Dick Patton
> <DickPatton@discussions.microsoft.com> wrote:
> 
> >I would love my users (11 to 17 year olds) to be presented with a form 
> >containing a field where they can request a Merit Badge and have the form 
> >look up all valid counselors for that merit badge.  The archetecture is in 
> >place to do this with a merit badge list for them to choose from and a list 
> >of counselors with links to their information (name, phone, etc.)
> >
> >I just can't seem to get it to work!
> >
> >Any ideas?? 
> 
> You'll need *three* tables for this, not two: a table of Badges; a table of
> Counselors; and a third table related one-to-many to each of these, with
> fields for BadgeID and CounselorID, and one record for each badge within a
> counselor's expertise.
> 
> -- 
> 
>              John W. Vinson [MVP]
> .
> 

On Wed, 7 Apr 2010 13:35:01 -0700, Dick Patton
<DickPatton@discussions.microsoft.com> wrote:

>Hi John, me again!!
>
>If you willl recall the other topic about deleteing records from a sub form, 
>the same archetecture exists in the merit badge thread.  The Table 
>Merit_Badge contains the list of valid merit badges with an auto_number Key.  
>Another table contains the info on the counselor (name, address, phone, etc.) 
>with his Person_ID (the key).  The third table (just as with schools and 
>units ) contains the key pair of MB_code and Person_ID.  (as a side this 
>relationship has the same problem as Schools - Units, you can't delete the 
>record uning the sub form) This creates the linkage between the counselor and 
>his merit badges, once again the key fosters the creation of a many to many 
>relationship where there can be many counselors for a Merit_badge and mant 
>Merit_badges for a Counselor but each relationship must be unique, so 
>duplicate records are not permitted.
>
>I hope this will satisfy the third table requirement, now this issue is how 
>do i get the selected merit badge and list only counselors who do that marit 
>badge or conversely lsit all merit badges that a given counselor is qualified 
>for.

Create a Query joining the three tables. Use a criterion on the merit badge
specifying which badge you're interested in, and include the CounselorID or
CounselorName in the query. This will show only those counselors who have
records in the resolver table for that badge.

This has nothing to do with the inability to delete the record, which makes no
sense to me if the structure is as you describe it, unless the Subform's
AllowDeletes property is turned off. What error do you get when you try to
delete a record?

I'd be willing to take a look at the database if it's not confidential; if you
would like me to do so please use Compact and Repair to compact it, put it in
a zipped folder and email it to me at jvinson <at> wysard of info <dot> com.
There's something about it that isn't getting communicated on this forum!

-- 

             John W. Vinson [MVP]

Hi John,

I suspect that you are right, I probably have something in the Schema messed 
up, I have done several DB's but that is not my Companies primary business.   
All of them have been BSA related and at least up to now have served the 
scouts well.

I would be pleased to have you look at the data base, however, I am working 
with "live" data.  So I will save the DB under another name and delete the 
data except for sample records. There is nothing confidential except the data.

The ultamate goal is to put it on a secure web site to allow Scouts and 
Leaders to gain access to relevant info on our Boy Scout District, here in FL.

Am I correct that your email is "jvinson@wysard of info.com", I did not 
think you could have spaces in an email address?

I will work to send the system to you today 4/8/10!

Thanks again,

Dick
-- 
Dick Patton
Boy Scout Troop 403


"John W. Vinson" wrote:

> On Wed, 7 Apr 2010 13:35:01 -0700, Dick Patton
> <DickPatton@discussions.microsoft.com> wrote:
> 
> >Hi John, me again!!
> >
> >If you willl recall the other topic about deleteing records from a sub form, 
> >the same archetecture exists in the merit badge thread.  The Table 
> >Merit_Badge contains the list of valid merit badges with an auto_number Key.  
> >Another table contains the info on the counselor (name, address, phone, etc.) 
> >with his Person_ID (the key).  The third table (just as with schools and 
> >units ) contains the key pair of MB_code and Person_ID.  (as a side this 
> >relationship has the same problem as Schools - Units, you can't delete the 
> >record uning the sub form) This creates the linkage between the counselor and 
> >his merit badges, once again the key fosters the creation of a many to many 
> >relationship where there can be many counselors for a Merit_badge and mant 
> >Merit_badges for a Counselor but each relationship must be unique, so 
> >duplicate records are not permitted.
> >
> >I hope this will satisfy the third table requirement, now this issue is how 
> >do i get the selected merit badge and list only counselors who do that marit 
> >badge or conversely lsit all merit badges that a given counselor is qualified 
> >for.
> 
> Create a Query joining the three tables. Use a criterion on the merit badge
> specifying which badge you're interested in, and include the CounselorID or
> CounselorName in the query. This will show only those counselors who have
> records in the resolver table for that badge.
> 
> This has nothing to do with the inability to delete the record, which makes no
> sense to me if the structure is as you describe it, unless the Subform's
> AllowDeletes property is turned off. What error do you get when you try to
> delete a record?
> 
> I'd be willing to take a look at the database if it's not confidential; if you
> would like me to do so please use Compact and Repair to compact it, put it in
> a zipped folder and email it to me at jvinson <at> wysard of info <dot> com.
> There's something about it that isn't getting communicated on this forum!
> 
> -- 
> 
>              John W. Vinson [MVP]
> .
> 

On Thu, 8 Apr 2010 11:31:01 -0700, Dick Patton
<DickPatton@discussions.microsoft.com> wrote:

>The ultamate goal is to put it on a secure web site to allow Scouts and 
>Leaders to gain access to relevant info on our Boy Scout District, here in FL.

WOOPS!

Access is NOT suitable for use on a website. I love the program, but it has
its limits, and this is one of them. You can use an Access database for the
tables, but you'll need an ASP or other web application to connect to it over
the web.

>Am I correct that your email is "jvinson@wysard of info.com", I did not 
>think you could have spaces in an email address?
>

You can't - edit them out (and please don't post my or your own email address
in clear on the newsgroups, to prevent spammers from harvesting them).
-- 

             John W. Vinson [MVP]

Hi John,

You are correct that will work just fine, however, how do I do that from 
within a query form and list the selected counselors in tabular format.

When you get the data base try running the top leader query.  What would 
great is if there werer a field to enter a name (like the search option) only 
show all those records which meet the criteria.  

Oh how I miss Fortran and IBM SQL, it was so easy to do this stuff with with 
SQL calls to Fortran and IBM IDMS!!!

Thanks again,

Dick

-- 
Dick Patton
Boy Scout Troop 403


"Dick Patton" wrote:

> Hi John,
> 
> I suspect that you are right, I probably have something in the Schema messed 
> up, I have done several DB's but that is not my Companies primary business.   
> All of them have been BSA related and at least up to now have served the 
> scouts well.
> 
> I would be pleased to have you look at the data base, however, I am working 
> with "live" data.  So I will save the DB under another name and delete the 
> data except for sample records. There is nothing confidential except the data.
> 
> The ultamate goal is to put it on a secure web site to allow Scouts and 
> Leaders to gain access to relevant info on our Boy Scout District, here in FL.
> 
> Am I correct that your email is "jvinson@wysard of info.com", I did not 
> think you could have spaces in an email address?
> 
> I will work to send the system to you today 4/8/10!
> 
> Thanks again,
> 
> Dick
> -- 
> Dick Patton
> Boy Scout Troop 403
> 
> 
> "John W. Vinson" wrote:
> 
> > On Wed, 7 Apr 2010 13:35:01 -0700, Dick Patton
> > <DickPatton@discussions.microsoft.com> wrote:
> > 
> > >Hi John, me again!!
> > >
> > >If you willl recall the other topic about deleteing records from a sub form, 
> > >the same archetecture exists in the merit badge thread.  The Table 
> > >Merit_Badge contains the list of valid merit badges with an auto_number Key.  
> > >Another table contains the info on the counselor (name, address, phone, etc.) 
> > >with his Person_ID (the key).  The third table (just as with schools and 
> > >units ) contains the key pair of MB_code and Person_ID.  (as a side this 
> > >relationship has the same problem as Schools - Units, you can't delete the 
> > >record uning the sub form) This creates the linkage between the counselor and 
> > >his merit badges, once again the key fosters the creation of a many to many 
> > >relationship where there can be many counselors for a Merit_badge and mant 
> > >Merit_badges for a Counselor but each relationship must be unique, so 
> > >duplicate records are not permitted.
> > >
> > >I hope this will satisfy the third table requirement, now this issue is how 
> > >do i get the selected merit badge and list only counselors who do that marit 
> > >badge or conversely lsit all merit badges that a given counselor is qualified 
> > >for.
> > 
> > Create a Query joining the three tables. Use a criterion on the merit badge
> > specifying which badge you're interested in, and include the CounselorID or
> > CounselorName in the query. This will show only those counselors who have
> > records in the resolver table for that badge.
> > 
> > This has nothing to do with the inability to delete the record, which makes no
> > sense to me if the structure is as you describe it, unless the Subform's
> > AllowDeletes property is turned off. What error do you get when you try to
> > delete a record?
> > 
> > I'd be willing to take a look at the database if it's not confidential; if you
> > would like me to do so please use Compact and Repair to compact it, put it in
> > a zipped folder and email it to me at jvinson <at> wysard of info <dot> com.
> > There's something about it that isn't getting communicated on this forum!
> > 
> > -- 
> > 
> >              John W. Vinson [MVP]
> > .
> > 

Okay, is there are mechanism to convert the code?

Dick
-- 
Dick Patton
Boy Scout Troop 403


"John W. Vinson" wrote:

> On Thu, 8 Apr 2010 11:31:01 -0700, Dick Patton
> <DickPatton@discussions.microsoft.com> wrote:
> 
> >The ultamate goal is to put it on a secure web site to allow Scouts and 
> >Leaders to gain access to relevant info on our Boy Scout District, here in FL.
> 
> WOOPS!
> 
> Access is NOT suitable for use on a website. I love the program, but it has
> its limits, and this is one of them. You can use an Access database for the
> tables, but you'll need an ASP or other web application to connect to it over
> the web.
> 
> >Am I correct that your email is "jvinson@wysard of info.com", I did not 
> >think you could have spaces in an email address?
> >
> 
> You can't - edit them out (and please don't post my or your own email address
> in clear on the newsgroups, to prevent spammers from harvesting them).
> -- 
> 
>              John W. Vinson [MVP]
> .
> 

I understand about the Spamers, did you get the DBMS?
-- 
Dick Patton
Boy Scout Troop 403


"Dick Patton" wrote:

> Hi John,
> 
> You are correct that will work just fine, however, how do I do that from 
> within a query form and list the selected counselors in tabular format.
> 
> When you get the data base try running the top leader query.  What would 
> great is if there werer a field to enter a name (like the search option) only 
> show all those records which meet the criteria.  
> 
> Oh how I miss Fortran and IBM SQL, it was so easy to do this stuff with with 
> SQL calls to Fortran and IBM IDMS!!!
> 
> Thanks again,
> 
> Dick
> 
> -- 
> Dick Patton
> Boy Scout Troop 403
> 
> 
> "Dick Patton" wrote:
> 
> > Hi John,
> > 
> > I suspect that you are right, I probably have something in the Schema messed 
> > up, I have done several DB's but that is not my Companies primary business.   
> > All of them have been BSA related and at least up to now have served the 
> > scouts well.
> > 
> > I would be pleased to have you look at the data base, however, I am working 
> > with "live" data.  So I will save the DB under another name and delete the 
> > data except for sample records. There is nothing confidential except the data.
> > 
> > The ultamate goal is to put it on a secure web site to allow Scouts and 
> > Leaders to gain access to relevant info on our Boy Scout District, here in FL.
> > 
> > Am I correct that your email is "jvinson@wysard of info.com", I did not 
> > think you could have spaces in an email address?
> > 
> > I will work to send the system to you today 4/8/10!
> > 
> > Thanks again,
> > 
> > Dick
> > -- 
> > Dick Patton
> > Boy Scout Troop 403
> > 
> > 
> > "John W. Vinson" wrote:
> > 
> > > On Wed, 7 Apr 2010 13:35:01 -0700, Dick Patton
> > > <DickPatton@discussions.microsoft.com> wrote:
> > > 
> > > >Hi John, me again!!
> > > >
> > > >If you willl recall the other topic about deleteing records from a sub form, 
> > > >the same archetecture exists in the merit badge thread.  The Table 
> > > >Merit_Badge contains the list of valid merit badges with an auto_number Key.  
> > > >Another table contains the info on the counselor (name, address, phone, etc.) 
> > > >with his Person_ID (the key).  The third table (just as with schools and 
> > > >units ) contains the key pair of MB_code and Person_ID.  (as a side this 
> > > >relationship has the same problem as Schools - Units, you can't delete the 
> > > >record uning the sub form) This creates the linkage between the counselor and 
> > > >his merit badges, once again the key fosters the creation of a many to many 
> > > >relationship where there can be many counselors for a Merit_badge and mant 
> > > >Merit_badges for a Counselor but each relationship must be unique, so 
> > > >duplicate records are not permitted.
> > > >
> > > >I hope this will satisfy the third table requirement, now this issue is how 
> > > >do i get the selected merit badge and list only counselors who do that marit 
> > > >badge or conversely lsit all merit badges that a given counselor is qualified 
> > > >for.
> > > 
> > > Create a Query joining the three tables. Use a criterion on the merit badge
> > > specifying which badge you're interested in, and include the CounselorID or
> > > CounselorName in the query. This will show only those counselors who have
> > > records in the resolver table for that badge.
> > > 
> > > This has nothing to do with the inability to delete the record, which makes no
> > > sense to me if the structure is as you describe it, unless the Subform's
> > > AllowDeletes property is turned off. What error do you get when you try to
> > > delete a record?
> > > 
> > > I'd be willing to take a look at the database if it's not confidential; if you
> > > would like me to do so please use Compact and Repair to compact it, put it in
> > > a zipped folder and email it to me at jvinson <at> wysard of info <dot> com.
> > > There's something about it that isn't getting communicated on this forum!
> > > 
> > > -- 
> > > 
> > >              John W. Vinson [MVP]
> > > .
> > > 

On Fri, 9 Apr 2010 10:23:02 -0700, Dick Patton
<DickPatton@discussions.microsoft.com> wrote:

>I understand about the Spamers, did you get the DBMS?

Just got it and I'll take a look. I'm tied up this afternoon, it may be a day
or two.

I'm not at all an expert on Web interfaces but I'd suggest you find out about
them immediately, before you invest too much time in an Access database. At
least until Office 10 you'll be barking up the wrong tree - Access Forms
simply aren't designed to work on webpages!
-- 

             John W. Vinson [MVP]