crosstab queary

[Testas]

Hello,

I am new here, so sorry if it is not a new question for you.



I have two tables. First table is "Employee" with fields: name, surname, department, level A, Level B, Level C, level D. Second table is IT system with fields: system name, level letter.
For examle in first table is record
John, Smith, sale_department, A,B,null,D
second table has records:
Navision, A,
sale_program, B
Report_program, C,
MS_office, D


How to create a query which must to show with which programs John Smith can work. For example, if i filter John Smith from all other employees result must be a column:
Navision,
Sale_program,
MS_office

Thanks for a help

[CJ_London]

looks like your table design is incorrect so difficult to do. Resolve the design and it becomes easy

your levelA-D fields should be in a separate table

EmpID...Level
122...…..A
122...…..B
122...…..D
143...…..A
143...…..C

etc

then you would simply link employee table to this table on EmpID and this table to program table on Level

[Testas]

I was thinking about that. But in this case it is very hard manage systems level because it would be a very large (long) table and if you want to "remove" system you must to find a row with employee ID and system name. In my case you just removing a level letter in one row from one column. In ms excel it is possible to "copy and paste " "rotate" data. mIs it possible something like this in ms access? For example to write query from first table which result will be:
john,
smith,
A,
B,
null,
C
?

[CJ_London]

be clear, excel is about as far removed from database structures as you can get. Simply, they work in completely different ways. To achieve the equivalent of rotate with your present structure you would need a union query to present the data in a normalised structure then apply a crosstab to it.

But in this case it is very hard manage systems level because it would be a very large (long) table and if you want to "remove" system you must to find a row with employee ID and system name

it might be hard in excel, easy in a database

I'm not interested in trying to make a poor structure work, so regret I will have to retire from this thread