CODEWITHSUNDEEP
sqlms-accessselect
ZygD
ZygD

Reputation: 24416

Combining 2 queries - getting column names in one and using results in another query

Building my first Microsoft Access SQL queries. That should not be this hard!
I have 2 tables:

Data table AccessRights table

A user belonging to GroupA logged in. I want to show him only those Data table rows and columns which GroupA is assigned to, like this:

+--------+--------+--------+
| Group  |  Data3 | Data4  |
+--------+--------+--------+
| GroupA |   9    |   4    | 
| GroupA |   1    |   5    |
+--------+--------+--------+

I tried this silly option:

SELECT (select Data from AccessRights where GroupA = "y")
FROM Data
WHERE Data.Group = "GroupA";

Upvotes: 6

Views: 1637

Answers (4)

itsben
itsben

Reputation: 1027

@ZygD, Here's the schema:

USER
user_id int primary key auto_increment
user_name varchar(100)
password varchar(100)

GROUP
group_id int primary key auto_increment
group_name varchar(100)

DATA
data_id int primary key auto_increment 
data_name varchar(100)

USER_GROUP
user_id int
group_id int


GROUP_DATA
group_id
data_id

I'll explain. First you define your "object types". You have a USER, GROUP and what you have called DATA. Aside it's probably a good idea to use another word instead of DATA. Use something like ITEM, or even DATAITEM. For this example I will use DATA. Ok, so each one of these tables has an integer value as it's primary key. The primary key is the unique identifier for the record in the table and it increments automatically. You can set that up in Access.

Now that you have your three object type tables, you need what are called "join tables" to describe the relationships between the "object type" tables. The USER_GROUP table says that a user can belong to one or more groups. For example, if User 1 belonged to both Group 1 and Group 2, then you would have two records in the USER_GROUP table to describe those relationships. The first row would be 1,1 and the second row would be 1,2.

The GROUP_DATA table describes the relationship between GROUP and DATA. For example, Group 1 could have access to Data 2 and Data 3. Again, you would have two rows in the GROUP_DATA table to describe those relationships. The first row would be 1,2 and the second would be 1,3.

Now, because User 1 belongs to Group 1, then User 1 will have access to Data 2 and 3. Your SQL then becomes simplified:

// Authenticate the user with user_name and password:
select @user_id = a.user_id from user a where a.user_name = @user_name and a.password = @password

// Get DATA by user_id
select c.data_id, c.data_name from user a join group b on a.user_id = b.user_id join data c on b.data_id = c.data_id where a.user_id = @user_id

Upvotes: 1

Builder
Builder

Reputation: 1056

Ok Finally here is the result you required. The good thing with this solution is that you dont need to run extra script and also just pass the group name as parameter and it will return only the columns you need. Enjoy. :)

declare @aa varchar (200) = ''
declare @sql varchar(500) = ''
declare @groupinfo varchar(100) = 'GroupA'

Select @aa = coalesce (case when @aa = '' then Data else @aa + ',' + Data end ,'')
  from [AccessRights] where GroupA = 'y'

Set @sql = 'Select [Group],' +  @aa + ' from Data where [Group] = ' + '''' + @groupinfo + ''''
Exec(@sql)

+--------+--------+--------+
| Group  |  Data3 | Data4  |
+--------+--------+--------+
| GroupA |   9    |   4    | 
| GroupA |   1    |   5    |
+--------+--------+--------+

Upvotes: 0

shA.t
shA.t

Reputation: 16968

I use this query:

SELECT 
    Data.[Group], 
    IIf((SELECT GroupA FROM AccessRights WHERE Data = "Data1")="y",[Data1],Null) AS Data_1, 
    IIf((SELECT GroupA FROM AccessRights WHERE Data = "Data2")="y",[Data2],Null) AS Data_2, 
    IIf((SELECT GroupA FROM AccessRights WHERE Data = "Data3")="y",[Data3],Null) AS Data_3, 
    IIf((SELECT GroupA FROM AccessRights WHERE Data = "Data4")="y",[Data4],Null) AS Data_4
FROM 
    Data
WHERE 
    ((Data.[Group])="GroupA");

For this result:

Group   | Data_1 | Data_2 | Data_3 | Data_4
--------+--------+--------+--------+--------
GroupA  |        |        | 9      | 4
GroupA  |        |        | 1      | 5

I just hide values of Data1 and Data2.

If you really want to hide your columns you need to use VBA that I create a VBA function that will give your final query string based on your group:

Function myQuery(groupName As String) As String
    Dim strResult As String
    Dim rs As Recordset
    Dim i As Integer

    strResult = "SELECT [DATA].[Group]"

    Set rs = CurrentDb.OpenRecordset("SELECT [Data], [" & groupName & "] FROM AccessRights WHERE [" & groupName & "] = ""y""")

    For i = 0 To rs.RecordCount
        strResult = strResult & "," & rs.Fields("Data").Value
        rs.MoveNext
    Next i

    strResult = strResult & " FROM [Data] WHERE ((Data.[Group])=""" & groupName & """)"

    myQuery = strResult
End Function

For example; myQuery("GroupA") will be 

SELECT [DATA].[Group],Data3,Data4 FROM [Data] WHERE ((Data.[Group])="GroupA")

Upvotes: 3

voglster
voglster

Reputation: 833

It would probably be better just to pivot your data table and add a column named data. Do the same for access rights.

You data table would look something like this:

Group, Data, Value
Groupa,Data1,1
Groupb,Data2,7
...

AccessRights like this:

Data, Group, Valid
Data1, GroupA, Y
Data2, GroupA, N

Then you could just join the two tables together and filter as needed.

Select * 
FROM Data D 
  JOIN AccessRights A 
     on D.data = A.data and D.Group = A.Group
WHERE A.Valid = 'Y' 
      and D.Group = 'GroupA'

Upvotes: 4

Related Questions