Queryring records based on values in another table / un-editable record problem

[aldridge_p]

Hi hope someone might be able to shed some light.

I have the following scenario wher eI am trying to query data based on values held in another table

Scenario

3 Tables and an SQL query.

Table 1 (Raw Data)
Field 1 - identifier key
Field 2 - Status (Data type = Text/Combo Box (Row Source = SELECT [CI - Status Values].Status FROM [CI - Status Values])
Field 3 - name
..and so on...

Table 2 (CI - Status Values)
Field 1 - Status (Data type = Text)

Table 3 (CI-SV Completed Values)
Field 1 - Completed Value (Data type = Text)

Objective:
Create an sql query to select all fields from [Raw Data] based on the values held in [CI-SV Coompleted Values].[Completed Value].

I know this is wrong, but my brain has turned to mush and can't for the life of me think where I am going wrong, any help would be appreciated:


SELECT [Raw data].*
FROM [Raw data]
WHERE ([Raw Data].[Status] = [CI-SV Completed Values].[Completed Value]
ORDER BY [Raw Data].[Name];

Paul helped me out with this with the following query:

SELECT [Raw data].*
FROM [Raw data] INNER JOIN [CI-SV Incomplete Values] ON [Raw Data].[Status] = [CI-SV Incomplete Values].[Incomplete Value]
WHERE [Raw data].[UCT member] = [Please enter your UCT name]
ORDER BY [Raw Data].[Name];

Problem I have know is that when I try to edit one of these records, either directly in the query OR through the form, access gives out something like "recordset is uneditable". I tried indexing the field in the incomplete values table (No dupliacted), which has worked and allows me to edit the record results of the query. The problem that I have now is that I can only update the values in Table1 - Field2 [Raw Data].[Status] to one of the values held in Table3- Field1 [CI-SV Incomplete Values].[Incomplete Value] and I need to be able to update it to any of the values held in Table2 - Field 1 [CI - Status Values].Status Value.

I hope that is a good enough explanation. Again, many thanks for any help.

[June7]

Why do you have two tables for values? Are the fields in tables same?

Not making sense. Combobox RowSource is Table2 but the query joins to Table3.

Instead of INNER join try Left or Right (whichever one is 'show all records from Table1 and only those from ...')

[aldridge_p]

Good point!
I had the structure because... Each record is categorised by its current status. I had defined these record status's in a table so that they can be maintained as the initial design involves many different statut's that can be further categorised into the following
1) Completed
2) Pending
3) Incomplete

I've just condensed the above tables down into Table 2 by adding another field, so now, rather than 5 tables, I have the following two

Table 1

Field Name	Data Type	Description
Process ID	Autonumber	Primary Key
Status	Text-Combo Box (SELECT [CI - Status Values].[Status Text] FROM [CI - Status Values]	Current record status
Staff member	Text - combo box (Selects another table)	Member of staff
etc	etc	etc

Table 2 - (holds the different status's, and now the sub-category that they fall into)

Field Name	Data Type	Description
Status Text	Text	Ie "Waiting for call back", "Waiting for documentation" "Pending approval" etc.
Status Valie	Text	Ie. "Complete" "Pending" "Incomplete"

Query:

SELECT [Raw data].*
FROM [Raw data], [CI - Status Values]
WHERE ((([Raw data].[UCT member])=[Please enter your UCT name]) AND (([CI - Status Values].[Status Value])="Incomplete"))
ORDER BY [Raw data].[Cheque issue date];


The query works fine with this deisgn, but now I am back to the problem that 'The recordset is not updateable'. Any ideas of where to go from here?

Many thanks for all your help!

Pat

[RuralGuy]

I'll assist June with this link: http://access.mvps.org/access/lookupfields.htm

[aldridge_p]

Thanks, but I'm not too sure I understand what its saying, even if I do, I have no idea where to go from here? Any suggestions?

Many thanks.

Pat

[RuralGuy]

The point is to not use "Lookup Fields" in your tables. Lookup TABLES and the RowSource of a ComboBox on a form is just fine.

[aldridge_p]

Okay, I think I get what your saying now.

However, I've just removed the lookup from the fields in the 'Raw data' table and made sure that the look-up only exists in the form and I am still getting the error 'The recordset is not updateable' when trying to a record from the query in form view.
Should I have amended the query as well?

Thanks for your patience.

Pat

[RuralGuy]

Perhaps this link will be useful: http://allenbrowne.com/ser-61.html

[June7]

Can you provide the project for analysis?

Now I understand why you had the two tables. That was okay after all. But one table might have benefit. Are some status texts associated with only certain status category? If they can all be mixed and matched then the one table really doesn't have benefit.