IS there a simple wat to create a query form

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