Help with creating an update statement based on cross tab view

[eerich]

Hi everyone,

I'm struggling to figure out a way to update data based on a crosstab view. My scenario is as follows:

I have 'OBJECTS' as my row data and roles as the header:

I need to update the roles to have the inquiry objects IF they do not have access to the object already. So for T100, I would need to update the 'MktUpdate' role since 'MktInquire' has view access. For TS16R5, I would need to update all 3 to have access, etc.

I thought this could be achieved by using the following SQL to check the results but it didn't work:

SELECT t.UserID, t.ObjectID, t.Permission, t2.UserID, t2.ObjectID, t2.Permission
FROM Test as t LEFT JOIN Test as t2 ON t.ObjectID = t2.ObjectID
WHERE (t.UserID = 'MktInquire' AND t2.UserID NOT IN ('MktInquire')) AND
t2.ObjectID IS NULL

I don't get any results with the above.

Can anyone please help.

 

[SkipVought]

Hi,

Why would you create a table with non-normalized data, when you could REPORT the transform/pivot using Count, rather than X?

Or maybe you already have non-normalized data. That ought to be corrected to store in a proper normalized table, so you won't need to ask such a question. You are asking for sorrow and tears!


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[eerich]

Skip,

The 'x' and 'y' just reprsent whether the objects are executable or view only. The data in the grid illustrated is from my crosstab query. I'm trying to figure a way to update the view in the normalized table where if an object is in the inquire role but not already in the other roles, i could simply update the table to add it to the other roles.

 

[dhookom]

If it's in the crosstab, you should be able to get it in a standard query. Any query that contains a crosstab or any other group by query will not be updateable. You could append the records from the crosstab to a table where the [Objects] field is a primary key and then join on the appended table to update.

Duane
Hook'D on Access
MS Access MVP

 

[eerich]

I'm sorry I don't really understand. Could you suggest the SQL to identify objects that are in MktInquire but not in the others and how I would be able to append. That has been my struggle. The code I posted upstream returns no results, which I know is not correct.

 

[dhookom]

I expect you would use a query with a subquery like:

SELECT *
FROM Test 
WHERE ObjectID NOT IN (SELECT ObjectID FROM Test WHERE UserID IN (value1, value2,...);

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Should have had a where clause
[CODE SQL]
SELECT *
FROM Test
WHERE ObjectID NOT IN (SELECT ObjectID FROM Test T2 WHERE UserID IN (value1, value2,...) AND t2.UserID = 'MktInquire';
[/code]

not sure if this is going to work. Maybe post some actual data records and requirements.


Duane
Hook'D on Access
MS Access MVP

 

[eerich]

Duane,

The above query was very close. When the 3 non-inquiry roles were all blank, the query selected the 'MktInquire' object as expected. However, when any combination of roles had an existing value, e.g.--> ('T100' and 'T7236' are in 'MktInquire', 'MktPower', 'MktMgr', but not in 'MktUpdate'), the query would not return these objects in the list noting that 'MktUpdate' would require an update. I'm not certain what to try next.

Also, I tried to replace the 'IN' clause from your original suggestion with 'NOT IN ('MktInquire') and LIKE and it wouldn't work. Any ideas why that would happen? Example of the code I tried below:

SELECT *
FROM test
WHERE ObjectID NOT IN
(SELECT ObjectID 
FROM Test as T2
WHERE UserID NOT IN ('MktInquire')) AND test.UserID LIKE '*' & 'Inq' & '*';

 

[dhookom]

I guess I don't understand the logic behind

I need to update the roles to have the inquiry objects IF they do not have access to the object already. So for T100, I would need to update the 'MktUpdate' role since 'MktInquire' has view access. For TS16R5, I would need to update all 3 to have access, etc.

Duane
Hook'D on Access
MS Access MVP

 

[eerich]

In the chart posted at the top of the thread, I'm simply trying to achieve the following:

Where there is an 'x' in the 'MktInquire' column, I need to also have an 'x' in the other roles, unless the other role already has a value ('x' or 'y'). If it is blank, I need to assing the MktInquire value.

 

[dhookom]

Do you have a table of all unique values of Mkt... fields and another table of all the unique objects? These would get you started with a query that contains all possible combinations. You could combine these as a cartesian query and then use an outer join to a query of MktInquire Objects. Use Is Null against the cartesian to determine which Mkt... values aren't in your original table.

Duane
Hook'D on Access
MS Access MVP

 

[eerich]

There's only 2 values: x or y. So should I duplicate the table? How do I construct a Cartesian query?

 

[dhookom]

X and Y values are probably values in the permission field? Maybe you should just post some actual records with actual table and field names. Then post records after the process you want to occur.

Duane
Hook'D on Access
MS Access MVP