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

 

[SkipVought]

Hi,

Seems odd for a GroupID to have a VALUE of +2.

An ID is an identifier, and in most cases should be a STRING (enclosed in tics).

But +2???????

Skip,

_[red][/red]

 

[Xzibit7]

Hey, I dont really don't understand it either. I got it from faq181-641. I changed it two +2 because I thought that the DeptID could be a 1 or a 2 depending on which user was logged on and just stuck with the format they used. I'm probably way off though. (-:

 

[Xzibit7]

wow I really need to start proofreading my posts a little better %-)

 

[SkipVought]

Your WHERE clause should have a field name from your table that you equate to the UserInfo2.GroupID, somehting like

SELECT * 
FROM supervisor 
WHERE supervisor.GroupID=UserInfo2.GroupID

Skip,

_[red][/red]

 

[Xzibit7]

I am so confused right now, I thought the code would see who is using the database right now and then based on that users dept id give that as the where input....If I would add another supervisor groupid column I dont know how that would match up with the GroupId in userinfo I would have to manually add a group id to each project depending on which group inputs it(I think).

 

[SkipVought]

Well then YOU have to supply the UserID to a sub query in order to return the GroupID to use in this query, something like...

SELECT * 
FROM supervisor 
WHERE supervisor.GroupID=(Select GroupID From WhateverTable Where Userid=TheUserIdOfTheGuyRunningTheQuery)

Skip,

_[red][/red]

 

[Xzibit7]

Okay thanks I have an idea of how to do it now.

 

[PHV]

Or something like this ?
SELECT S.*
FROM supervisor AS S INNER JOIN UserInfo AS U ON S.GroupID = U.GroupID
WHERE U.UserName = 'Xzibit7'

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

 

[Xzibit7]

Could you say something like currentuser() on that last part instead of xzibit7?

 

[PHV]

WHERE U.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]

Thanks

 

[Xzibit7]

Okay one more question I think I figured out how to make this work without any VB just not sure if I can do it with SQL. Okay I'm going to make a supervisor input table (data entry) that is based on a query this table is going to have the Supervisor table whose primary key is ProjectID and then other supervisor input stuff and a USerInfo table which has the fields ID(primary key) UserName(same as in security file) and DeptID(1 or 2). Okay so these two tables are queried together with ProjectID as the relationship.
Then on the form I'm going to have a supervisor update tab that goes to another form which will show all the supervisor projects for that dept only. I think I can do this by selecting where the two ProjectId's are equal but then how would I say in sql I want to only see the deptID associated with the current user?

 

[PHV]

Have a look here:

http://r937.com/relational.html

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

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

 

[Xzibit7]

The devshed page is handy but I cant seem to figure out in sql how to say that a certain username is going to have a 1 or 2 for deptid which is the where that I need..i.e. this usernames deptid is 1 so use that in the query.

 

[Xzibit7]

Could you say

Select * from supervisor, Userinfo where Userinfo.id = supervisor.id and userinfo.deptid = (select DeptId from userinfo where userinfo.username = CurrentUser();

 

[PHV]

Why not simply this ?
SELECT *
FROM supervisor INNER JOIN Userinfo ON supervisor.id = Userinfo.id
WHERE Userinfo.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

 

[PHV]

OK, I think you want CurrentUser have the same DeptId than the supervisor ?
SELECT S.*
FROM (supervisor AS S
INNER JOIN Userinfo AS U ON S.id = U.id)
INNER JOIN Userinfo AS C ON U.DeptId = C.DeptId
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]

I think the problem is that I have one guy (he is in a different group) who needs to have just his data shown and the rest should be able to see all the data(except his). So I thoughta deptid would work,1 for the regualar group and 2 for him. My problem is seeing who is on the database then finding what group they are in then using that to join the tables.

 

[Xzibit7]

Sorry did not see your post, Thanks PH