Page 1 of 1

Access/SQL question

Posted: Fri Feb 24, 2006 11:24 am
by AngusL
Hey, it's probably a lot lighter than you guys are used to dealing with, but hey. :P

I'm just playing around in Access, as my database experience beyond simple flat files is fairly limited. I'm doing a relational database for a fictional company.

I currently have tblCases - containing all the 'jobs' or 'cases' the company is currently working on.

In this, I have two fields. tblCases.Assigned and tblCases.ProjectManager. The Assigned field has the value chosen (I'm operating this purely from Access at the moment - I'm more interested in the design than implementing it in a programming project) from a drop down menu, where the values are extracting from a lookup on another table (Just a simple "SELECT tblGroups.GroupID FROM tblGroups;" as the row source). So I might have the ability to choose group 1, 2 or 4, say.

For the ProjectManager field, I want this to give a drop down list where the values are all those employees (Stored in another table) who are in that group. I know I'm looking for something of form:

SELECT tblEmployees.Firstname, tblEmployees.Surname, tblEmployees.Department FROM tblEmployees WHERE (tblEmployees.Group=GROUP ASSIGNED FOR THIS ROW);

I'm just not certain how to get this to work... I tried

SELECT tblEmployees.Firstname, tblEmployees.Surname, tblEmployees.Department FROM tblEmployees WHERE (tblEmployees.Group=tblCases.Assigned);

But this just prompts me to enter the value for 'tblCases.Assigned' when I click the drop down arrow. Basically I'm wondering how I would construct a query, to generate the list of possible values for a field in tblCases, which would reference a value in another field on that same row?

P.S I tried the following

SELECT tblEmployees.Firstname, tblEmployees.Surname, tblEmployees.Department FROM tblEmployees WHERE (tblEmployees.Group=(SELECT tblCases.Assigned FROM tblCases);

Which works the way I want it to, but only if tblCases has one row - because it obviously returns all the rows for that query, rather than just the one corresponding to the row for which the query is being run. Which is logical!

Hope this is explained alright...

Posted: Fri Feb 24, 2006 2:38 pm
by raghavan20
I finally confused with your question...I am going to take some assumptions....
if you want to use the value of an option in the HTML select element or from the combo box in the query....you cannot use it directly...instead you have to pass the selected value of a SELECT element as an URL parameters which you can be retrieved using $_GET and then you can assign this value to the query.....

if I am wrong with my assumptions....you can briefly say in two to three lines...what you actually want to do ... thanks..

Posted: Fri Mar 03, 2006 10:27 am
by AngusL
Okay, scratch everything I said above, here's a different way of coming at it.

In Access, you can choose a value for a field from a combo box. The values contained in this combo box can be populated using a SQL query. I have a field named 'ProjectManager' which has such a combo box.

At the moment, I have the 'source' for this combo box as the following SQL query:
'SELECT tblEmployees.Firstname, tblEmployees.Surname FROM (tblEmployees INNER JOIN tblCases ON tblEmployees.Group=tblCases.Assigned);'

However, the result of this is that...

If say, we have Mary Gates, assigned group 4. We also have John Doe, assigned group 2. However, the combo box for Mary Gates' row doesn't just hold those employees in group 4, it also holds those in group 2. This is because an INNER JOIN will return all rows from both tables where there is a match from anywhere in the table. So any employee who belongs to a group which has been assigned to a client will be returned by the client. What I'm looking to do is have something where it will return any employee who belongs to the group which has been assigned to the client on that row.

So the combo box on Mary Gates' row will only contain those employees in group 4, and the combo box in John Doe's row will only contain those employees in group 2.

Does that make considerably more sense?

Posted: Fri Mar 03, 2006 12:39 pm
by RobertGonzalez
Do you want the values for the second drop down list to populate based on the selection of the first drop down list? Also, can you please show your full table structures and a few sample rows so we can see, from the data side, what you are trying to do?