By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 1,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

access database query HELP!

P: n/a
Trying to set up an admin page for an online training registration
page. I am having trouble with a sql statement. Here is the info on the
tables.

I have two tables: classPerson and reglist

classPerson - has two fields: idr, idc
*idr = id number for a person
idc = id number for a class being offered that they have signed up for

reglist - has two fields (actually has more, but to simplify I am only
mentioning two of the fields): idr, lname
*idr = id number for a person
lname = last name of the person

* denotes there is a relationship between them

I have two asp pages. enrollmentlist.asp and enrollmentlist2.asp. On
enrollment I have a drop down list of all of the classes you can choose
to see who is registered for our training. Once you choose a class and
hit submit, I send the value of the class (idc) to the next page
(enrollmentlist2.asp). I do a simple request.form and create a variable
(idcIn) for the class id. Now here is where I am having trouble. I want
to take the inputted value (idcIn) and query classPerson to find out
who (idr) is registered for the class (idc). I then want to display the
person's last names who are taking the class.

In other words, look in
classPerson and find where idc = idcIn
I then want to find all idr values in classPerson that have the same
idc value.
Then based on the list of idr I want to query the reglist table and
display all lname that match the idr.

Any advice would be greatly appreciated.
Jim

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Here's a guess:

SELECT R.lname
FROM reglist As R INNER JOIN classPerson AS C
ON R.idr = C.idr
WHERE idc = idcIn
ORDER BY R.lname

If you're running this from an ASP page you'll have to format it in VBS,
or whatever script language you're using. Be sure to set the idc
parameter correctly - data type delimiters. If it is a string use
single quotes as the delimiter; if a number - no delimiters; date - in
Access # delimiters w/ date in USA format: #m/d/yyyy#. An example of
idcIn as a numeric data type:

"WHERE idc = " & idcIn & " ORDER BY R.lname"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQla//oechKqOuFEgEQJb2QCghCqKlr/NsQ+qahuW1ejiKzHodRMAoNGQ
wiJKFTkNAMhnu7e+kDWMPSQh
=7ztM
-----END PGP SIGNATURE-----

ni*****@nku.edu wrote:
Trying to set up an admin page for an online training registration
page. I am having trouble with a sql statement. Here is the info on the
tables.

I have two tables: classPerson and reglist

classPerson - has two fields: idr, idc
*idr = id number for a person
idc = id number for a class being offered that they have signed up for

reglist - has two fields (actually has more, but to simplify I am only
mentioning two of the fields): idr, lname
*idr = id number for a person
lname = last name of the person

* denotes there is a relationship between them

I have two asp pages. enrollmentlist.asp and enrollmentlist2.asp. On
enrollment I have a drop down list of all of the classes you can choose
to see who is registered for our training. Once you choose a class and
hit submit, I send the value of the class (idc) to the next page
(enrollmentlist2.asp). I do a simple request.form and create a variable
(idcIn) for the class id. Now here is where I am having trouble. I want
to take the inputted value (idcIn) and query classPerson to find out
who (idr) is registered for the class (idc). I then want to display the
person's last names who are taking the class.

In other words, look in
classPerson and find where idc = idcIn
I then want to find all idr values in classPerson that have the same
idc value.
Then based on the list of idr I want to query the reglist table and
display all lname that match the idr.

Any advice would be greatly appreciated.
Jim

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.