Select Where help 2

[Xzibit7]

Pretty new to SQL but how would I say

SELECT * FROM supervisor WHERE UserInfo2.GroupID=+2;

Access keeps asking me the value of this table in a popup
when I enter the value that is in the table it returns the correct output. What would the syntax so sql knows its another table. BTW this is with the faq answer on how to only allow a person to see there own forms.
Thanks for the help

 

[lespaul]

can you "show" us your table(s) and expected results?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Xzibit7]

UserInfo Table
GroupId UserName ID (primary Key)
1 Ralph 3
1 Brian 1
1 Brian 4
1 Ed 6
2 John 7

Supervisor Table

Project ID NIIN Nomenclature Assignment
3 564564651 Piston ksfpsfksfksfk
1 789845465 Wing blabla
4 545646565 Tubing do this
6 454654448 Wing Do that
7 787984987 Strut fjsfjslfjsljfs

Okay so what I would like is anytime that anyone is in GroupID 1 looks at the form they only see the projects for group 1 and anytime that John or Group 2 is in the form they only see the projects for group 2.

 

[PHV]

What about this ?
SELECT S.[Project ID], S.NIIN, S.Nomenclature, S.Assignment
FROM (Supervisor AS S
INNER JOIN Userinfo AS U ON S.[Project ID] = U.id)
INNER JOIN Userinfo AS C ON U.GroupId = C.GroupId
WHERE C.UserName = CurrentUser();

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Xzibit7]

Yeah I think that's it PH I'm gonna try it(I might not be finished till tom. I'm leaving soon) and I'll let you know. Thank you for all your help.

 

[Xzibit7]

First of all PH thanks the SQL works, I have another problem with this though. I had initially had a supervisor input form that was set to data entry, This form was based on a query were the id's from the supervisor and userinfo table were joined together. The problem is this caused multiple usernames in the userinfo table (whenever a user got asssigned two or more projects) Which resulted in multiples being returned in my query (the one I got help on from PH) okay I thought I would fix this buy having a supervisor.assignedto and the userinfo.username joined together in a query just getting rid of userinfo.id and then there could be only one username in the userinfo table. The problem is this now makes my supervisor input form no longer return a data entry type form based on the query, in fact I no longer can input anything at all into this form. Please help this sucks

 

[Xzibit7]

It just gives me all the records in from that query

 

[Xzibit7]

This is the query my input form is based on

SELECT *
FROM supervisor, Userinfo
WHERE userinfo.username=supervisor.assignedto;


It allows no data entry on the form.

 

[Xzibit7]

Ok I got it