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

Question

Building the first Microsoft Access SQL queries. It shouldn't be that hard! I have 2 tables:

A user belonging to GroupA logs in. I want to show him only those rows and columns of the Data table that GroupA assigned GroupA , for example:

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

I tried this stupid option:

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

Answer 1

I am using 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 the values ​​of Data1 and Data2 .

---

If you really want to hide your columns, you need to use VBA to 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 instance; myQuery("GroupA") will be

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

Answer 2

It would probably be better to just expand the data table and add a column called data. Do the same for permissions.

Your data table will look something like this:

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

AccessRights:

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

Then you can simply 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' 

Answer 3

@ZygD, Here's the diagram:

 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 will explain. First you define your "object types". You have USER, GROUP and what you called DATA. Also, it's probably nice to use another word instead of DATA. Use something like ITEM or even DATAITEM. In this example, I will use DATA. So, each of these tables has an integer value as a primary key. The primary key is a unique identifier for the entry in the table, and it automatically increases. You can install this in Access.

Now that you have three types of type tables, you need to call them “join tables” to describe the relationship between the “object type” tables. The USER_GROUP table indicates that a user can belong to one or more groups. For example, if User 1 belonged to both group 1 and group 2, then to describe these relations in the table USER_GROUP you must have two entries. The first line will be 1.1, and the second line will be 1.2.

The GROUP_DATA table describes the relationship between GROUP and DATA. For example, group 1 may have access to data 2 and data 3. Again, you will have two rows in the GROUP_DATA table to describe these relationships. The first line will be 1.2, and the second will be 1.3.

Now, since User 1 belongs to group 1, user 1 will have access to data 2 and 3. Then your SQL is 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 

Answer 4

Ok Finally, here is the result you need. The good thing with this solution is that you do not need to run additional scripts, and just pass the group name as a 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 | +--------+--------+--------+