CODEWITHSUNDEEP
sqlms-access
Eddie
Eddie

Reputation: 3

How to Construct MS Access Query that uses the results of sub queries to compare against one another

Using MS access 2010 with a linked table to SQL Server 2005. The linked table has 5 Columns:

PName - STRING
OName - STRING
FName - STRING  
Readable - BOOLEAN  
Editable - BOOLEAN

Sample data:

PName   FName                readable   editable
UK      Case.First_Name         0       0
UK      Case.Last_Name          0       0
UK      Case.Middle_Initial     0       0
UK      Case.Phone_Number       0       0
UK      Case.Username           -1      -1
USA     Case.First_Name         0       0
USA     Case.Last_Name          0       0
USA     Case.Middle_Initial     0       0
USA     Case.Phone_Number       0       0
USA     Case.Username           -1      -1

Looking to create a query that will return a list of all the FName by PName but only show the FName if the Readable value is different across all the PName or the Editable value is different across all the PName.

example output:

                      UK           USA
                   Edit|Read    EDIT|Read
Case.First_Name    T   |  T       F | T
Case.UserName      F   |  F       F | T

In the above the "Case.First_Name" is editable by the UK but not by the USA and hence why I would like to see this row

The Case.UserName Is not Readable by the UK but is by the USA and hence why I would like to see this row

What should not be outputted:

                      UK           USA
                   Edit|Read    EDIT|Read
Case.First_Name    T   |  T       T | T
Case.UserName      F   |  F       F | F

Both of the above rows have identical readablity and editability permissions for both the UK and the USA, and hence I do not want to see these rows.

The below query will get me the raw data I need which I can then flip into a pivot table:

SELECT PName, FName, Readable, Editable
FROM ProfileFLS
WHERE PName Like "U*";

This is then exported to excel and using an If statement.

Note that the number of profiles are dynamic.

Thanks for your help

Latest SQL:

SQL is as follows, I have also tried changing the ="True" to 0 

TRANSFORM Sum(IIf(dbo_ProfileFLS.readable="TRUE","Read_True","Read_False")) AS Readable
SELECT dbo_ProfileFLS.fieldname
FROM dbo_ProfileFLS
WHERE (((dbo_ProfileFLS.objectname)="Case") AND ((dbo_ProfileFLS.Profile) Like "UK*"))
GROUP BY dbo_ProfileFLS.fieldname, dbo_ProfileFLS.readable
PIVOT dbo_ProfileFLS.Profile;

Upvotes: 0

Views: 195

Answers (2)

koriander
koriander

Reputation: 3258

You can either use MS Access wizard to create a crosstab query or you can create a SQL-Passthrough query with PIVOT to have your SQL Server do the processing.

Edit: Added the following based on the revised question

You might be able to get away without using a crosstab. It depends on how flexible your query needs to be or if you can construct the query on the fly. For the UK/USA example above you can use:

SELECT dbo_ProfileFLS.FName,
       dbo_ProfileFLS.Readable AS [UK Read], 
       dbo_ProfileFLS.Editable AS [UK Edit],
       dbo_ProfileFLS_1.Readable AS [USA Read],
       dbo_ProfileFLS_1.Editable AS [USA Edit]
FROM   dbo_ProfileFLS
INNER JOIN dbo_ProfileFLS AS dbo_ProfileFLS_1
ON     dbo_ProfileFLS.FName = dbo_ProfileFLS_1.FName
WHERE  dbo_ProfileFLS.PName="UK"
  AND  dbo_ProfileFLS_1.PName="USA" 
  AND  (dbo_ProfileFLS.Readable<>[dbo_ProfileFLS_1].[Readable]
       OR dbo_ProfileFLS.Editable<>[dbo_ProfileFLS_1].[Editable]);

If you really want to use a crosstab, then the problem is that you can only use a column for the Value (as you have seen already). You can overcome this by concatenating the Readable and Editable fields. Since the crosstab uses a Group By but in fact you want the plain values, you need to use the aggregation function First. For example:

TRANSFORM First(Readable & " | " & Editable) AS ReadEdit
SELECT dbo_ProfileFLS.FName
FROM dbo_ProfileFLS
GROUP BY dbo_ProfileFLS.FName
PIVOT dbo_ProfileFLS.PName & " Read | Edit";

This query takes the complete table, of course. To get only the correct values, you need a a variation of the first query to use as a filter:

SELECT dbo_ProfileFLS.ID AS UK_ID,
       dbo_ProfileFLS_1.ID AS USA_ID
FROM   dbo_ProfileFLS
INNER JOIN dbo_ProfileFLS AS dbo_ProfileFLS_1 
ON     dbo_ProfileFLS.FName = dbo_ProfileFLS_1.FName
WHERE  dbo_ProfileFLS.PName="UK"
  AND  dbo_ProfileFLS_1.PName="USA" 
  AND  (dbo_ProfileFLS.Readable<>[dbo_ProfileFLS_1].[Readable]
       OR dbo_ProfileFLS.Editable<>[dbo_ProfileFLS_1].[Editable]);

If you name it crosstabFilter, then you cross-join it with dbo_ProfileFLS and use the condition:

WHERE crosstabFilter.UK_ID=ID OR crosstabFilter.USA_ID=ID

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123689

Thanks for editing your question. Things are much clearer now.

Let's start by tweaking your sample data so we can get some actual results.

PName   FName               readable    editable
UK      Case.First_Name     0           -1
UK      Case.Last_Name      0           0
UK      Case.Middle_Initial 0           0
UK      Case.Phone_Number   0           0
UK      Case.Username       -1          -1
USA     Case.First_Name     0           0
USA     Case.Last_Name      0           0
USA     Case.Middle_Initial -1          0
USA     Case.Phone_Number   0           0
USA     Case.Username       -1          -1
Canada  Case.First_Name     0           0
Canada  Case.Last_Name      0           -1
Canada  Case.Middle_Initial 0           0
Canada  Case.Phone_Number   0           0
Canada  Case.Username       0           0

Now consider the following query. It is a self-join that looks for a match on [FName], and a mis-match on [readable] OR [editable]:

SELECT p1.FName, 
    p1.PName AS PName1, p1.readable AS read1, p1.editable AS edit1,
    p2.PName AS PName2, p2.readable AS read2, p2.editable AS edit2
FROM permissionsTbl p1 INNER JOIN permissionsTbl p2
    ON p1.FName=p2.FName 
        AND (p1.readable<>p2.readable OR p1.editable<>p2.editable)
ORDER BY 1, 2 ;

It returns

FName               PName1  read1   edit1   PName2  read2   edit2
Case.First_Name     Canada  0       0       UK      0       -1
Case.First_Name     UK      0       -1      Canada  0       0
Case.First_Name     UK      0       -1      USA     0       0
Case.First_Name     USA     0       0       UK      0       -1
Case.Last_Name      Canada  0       -1      USA     0       0
Case.Last_Name      Canada  0       -1      UK      0       0
Case.Last_Name      UK      0       0       Canada  0       -1
Case.Last_Name      USA     0       0       Canada  0       -1
Case.Middle_Initial Canada  0       0       USA     -1      0
Case.Middle_Initial UK      0       0       USA     -1      0
Case.Middle_Initial USA     -1      0       UK      0       0
Case.Middle_Initial USA     -1      0       Canada  0       0
Case.Username       Canada  0       0       USA     -1      -1
Case.Username       Canada  0       0       UK      -1      -1
Case.Username       UK      -1      -1      Canada  0       0
Case.Username       USA     -1      -1      Canada  0       0

and shows us every case in which there is a mis-match in read or edit permissions.

Now we'll tweak that to give us a DISTINCT list of the [FName] values that appeared in the above list...

SELECT DISTINCT p1.FName 
FROM permissionsTbl p1 INNER JOIN permissionsTbl p2
    ON p1.FName=p2.FName 
        AND (p1.readable<>p2.readable OR p1.editable<>p2.editable) ;

...and use it as a subquery to extract those FName rows from the original table

SELECT * FROM permissionsTbl
WHERE FName IN
(
    SELECT DISTINCT p1.FName 
    FROM permissionsTbl p1 INNER JOIN permissionsTbl p2
        ON p1.FName=p2.FName 
            AND (p1.readable<>p2.readable OR p1.editable<>p2.editable)
) ;

...which returns...

PName   FName               readable    editable
UK      Case.First_Name     0           -1
UK      Case.Last_Name      0           0
UK      Case.Middle_Initial 0           0
UK      Case.Username       -1          -1
USA     Case.First_Name     0           0
USA     Case.Last_Name      0           0
USA     Case.Middle_Initial -1          0
USA     Case.Username       -1          -1
Canada  Case.First_Name     0           0
Canada  Case.Last_Name      0           -1
Canada  Case.Middle_Initial 0           0
Canada  Case.Username       0           0

...and can be used to feed your downstream processes.

Upvotes: 2

Related Questions