Query Syntax Question

[Littlemalky]

Hi, I have a question on how to build this query so that it functions correctly. I am most familiar with building queries with the objects rather than direct SQL so I think that's where I need help. I am working in Access 2013 for reference.

I have table "tbl_TERRITORY", with two fields [TERRITORY_ID] and [TERRITORY_DESCRIPTION]. I'm joining this on a table that maps a sales employee to each territory (this is a one to many relationship, as there can be multiple sales employees per territory). Table name is "tbl_TERRITORY_EMPLOYEE_MAP", with the following relevant fields [TERRITORY_ID], [EMPLOYEE_ID], and [SALES_ROLE_ID].

My join is on [TERRITORY_ID], naturally. What I'd like to display is a list of all the territories with the corresponding sales employees where [SALES_ROLE_ID] equals 1 in one column. Then in the next column I want to list, again, all of the employees where [SALES_ROLE_ID] equals 2.

Can this be done in the same query, or would I have to build a union?

 

[Rijnsent]

Hi Malky,

I'm trying to understand what your output would look like, something like this?:

Column1 (SALES_ROLE_ID=1)
Belgium - John
France - John
Belgium - Peter
Germany - Peter
etc.

And in the same query:
Column2 (SALES_ROLE_ID=2)
France - Don
Belgium - Grace
Germany - Grace
etc.

The issue with that setup is that both lists don't have to be of equal length, therefore hard to put in 1 query. What you could do is create a query that outputs e.g.:
Column1: Territory&Employee, Column2: Salesrole1: 0 or 1, Column3: Salesrole2: 0 or 1
France - John, 0, 1
Belgium - Grace, 1, 1
etc.

If you can give a bit more info on the desired output I could try to whip up some SQL.

Cheers,

Koen

 

[Littlemalky]

Hi, thank you for replying. I was able to accomplish this with a union query and creating two more queries on top of it just to group everything properly so it's super inefficient. Just trying to see if it can all be done in one query.

It should look like this:

Column 1: [TERRITORY_ID]
1
2
3
4

Column 2: [TERRITORY_DESCRIPTION]
California
Arizona
Nevada
Utah

Column 3: [REP_ID] Where [SALES_ROLE_ID] = 1
Tom
John
Martha
Susan

Column 4: [REP_ID] Where [SALES_ROLE_ID] = 2
Frank
Rachel
Marc
Adam

 

[Rijnsent]

Hi Malky,

In MS Access, you could make a CrossTab query with this SQL. This crosstable shows the first found REP_ID for each combination of TERRITORY_ID and SALES_ROLE_ID:

TRANSFORM First(tbl_TERRITORY_EMPLOYEE_MAP.REP_ID) AS FirstOfREP_ID
SELECT tbl_TERRITORY.TERRITORY_ID, tbl_TERRITORY.TERRITORY_DESCRIPTION
FROM tbl_TERRITORY INNER JOIN tbl_TERRITORY_EMPLOYEE_MAP ON tbl_TERRITORY.TERRITORY_ID = tbl_TERRITORY_EMPLOYEE_MAP.TERRITORY_ID
GROUP BY tbl_TERRITORY.TERRITORY_ID, tbl_TERRITORY.TERRITORY_DESCRIPTION
PIVOT tbl_TERRITORY_EMPLOYEE_MAP.SALES_ROLE_ID;

The nasty bit comes in when you would have 2 people for the same region with the same role, as this would only show you the first person. You could do a COUNT crosstab to rule that out or have to rewrite it to a SELECT query to get multiple values in 1 cell.

Hope that helps,

Koen

 

[Littlemalky]

That's so cool! I didn't know you could do a crosstab. Thank you for teaching me something new.