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

Question

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

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";

Answer 1

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'

Answer 2

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")