Access/SQL question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
AngusL
Forum Contributor
Posts: 155
Joined: Fri Aug 20, 2004 4:28 am
Location: Falkirk, Scotland

Access/SQL question

Post 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...
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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..
AngusL
Forum Contributor
Posts: 155
Joined: Fri Aug 20, 2004 4:28 am
Location: Falkirk, Scotland

Post 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?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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?
Post Reply