Crosstab to create TRUE/FALSE values

[smally]

Hello, I'm trying to create a query that looks up values from a junction table (tblCategoryJunction) with just 2 fields


I'd like to create a query where the first field would be EmployeeID, and all other fields to be CategoryLabel.

Basically it's a crosstab, however I'd like the values in the Category fields to be either true or false.
True if the employee and category is in the junction table
False if there is not

 

[plog]

Just to be clear, using the data in the database your provieded, you want a crosstab query that is 11 columns by 22 rows? Where every value is either True or False and no blanks? Correct?

 

[smally]

Yes(ish)

22 rows, 12 columns (1 for employeeID, 11 for categories showing true/false)

 

[plog]

That's right, 12 columns. What you need is a listing of all Category/Employee permutations (11x22) to bump your junction table against. You achieve that with a Cartesian Product (https://en.wikipedia.org/wiki/Cartesian_product). Basically, its a query with multiple tables that are unjoined. Use this SQL for that:

SELECT tblCourseCategories.CategoryID, tblEmployees.EmployeeID
FROM tblCourseCategories, tblEmployees;

Name that 'EmployeesCategories_sub1'. Then to get all the data you want, you would use this query:

SELECT EmployeesCategories_sub1.EmployeeID, tblCourseCategories.CategoryLabel, IIf(IsNull([tblCategoryJunction].[CategoryID]),"False","True") AS [Exists]
FROM (EmployeesCategories_sub1 LEFT JOIN tblCategoryJunction ON (EmployeesCategories_sub1.CategoryID = tblCategoryJunction.CategoryID) AND (EmployeesCategories_sub1.EmployeeID = tblCategoryJunction.EmployeeID)) INNER JOIN tblCourseCategories ON EmployeesCategories_sub1.CategoryID = tblCourseCategories.CategoryID;

That's just a straight SELECT query, you can CrossTab it to get the exact layout you want. The problem with Cartesian Products and Crosstabs, and especially crosstabs built on top of cartesian products is that the more data you have the slower they get. So if you have a few hundred records in your Category/Employees tables its going to get really slow to run this query.

 

[smally]

Is there a quicker alternative? In my full database's junction table there are over 4000 records?

 

[plog]

Not really, you want to report on all permutations and you want to verify all permutations, so you need to make that full list and then check it against the junction.

You might make a table out of the sub1 query and then use that table instead of the sub-query in the final query. The problem with that though is you will need to remake that table whenever the employee or category table gets changed.