Access Query Help - Active Employees

[Littlemalky]

Hi, I need help with an Access Query that I'm not sure how to structure to get what I want. Essentially, I want to see who is in each sales position and see a null value for positions that aren't filled.

I have one table [tbl_SALES_POSITIONS_ROLES] with all 113 position ID's under the field name [SALES_POSITION_ID]. I'm doing a LEFT JOIN on [tbl_SALES_POSITION_EMPLOYEE_MAP].[POSITION_ID] field. This table structure is [ID], [POSITION_ID], [EMPLOYEE_ID], [POSITION_START_DATE], [POSITION_END_DATE]. I don't have any kind of Active Employee Flag, and hoping maybe I don't need one, but maybe I will? The [tbl_SALES_POSITION_EMPLOYEE_MAP].[POSITION_ID] field can have duplicates as employees leave and new ones come on board.

If I want my end result to look like [SALES_POSITION_ID], [EMPLOYEE_ID], [POSITION_START_DATE] with all 113 positions uniquely listed, how do I create a proper criteria to filter out the [POSITION_END_DATE] field so this works properly? Simply setting an "is null" criteria is taking away some of my 113 positions in the results.

 

[xenou]

Hi,
What do you mean by "filter out"?

If I want my end result to look like [SALES_POSITION_ID], [EMPLOYEE_ID], [POSITION_START_DATE] with all 113 positions uniquely listed, how do I create a proper criteria to filter out the [POSITION_END_DATE] field so this works properly?

What do you want filtered out? The field isn't even in your list of fields for the end result.

One guess, if you want employees that are active, then that field should be null, so you should probably (in fact) be setting is null as a criteria here. You would not get all 113 positions, if, for instance, some of them were not filled at all, though.

 

[Micron]

To get all rows from one table and from another where the joined fields are equal is impossible, I'd say. The issue is that usually, the joined fields have no data in one or the other, at least in some of the records. Try a UNION query.

 

[Littlemalky]

Hi, thanks for your responses. I think what may need to be done is to write a subquery to do this which I can't figure out how to do. I'm trying to avoid layering two actual queries on top of each other to do this.

So i built the "subquery" as it's own standing query to accomplish what I'm trying to return and it looks like this:

SELECT tbl_SALES_POSITION_EMPLOYEE_MAP.POSITION_ID, tbl_SALES_POSITION_EMPLOYEE_MAP.EMPLOYEE_ID
FROM tbl_SALES_POSITION_EMPLOYEE_MAP
WHERE (((tbl_SALES_POSITION_EMPLOYEE_MAP.POSITION_END_DATE) Is Null))

This gives me my "filtered" active list of people. Now, I'm not sure how to actually make this into a subquery with the JOIN on the other table. Am I making sense? I'm not super SQL savvy so I'm having a hard time articulating exactly what I'm trying to do.

 

[Micron]

AFIAK, a subquery is a sql statement that makes up one field of another query; i.e. it is nested within another query. Put another way, you put an entire sql statement into a criteria cell in one field of the main query field. To create a query and join that to a table (or even a query) in another query is not a sub query. So if you really mean a sub query, I really struggle with those and wouldn't be much help. I don't see why you can't solve this with a UNION query.

 

[xenou]

This gives me my "filtered" active list of people. Now, I'm not sure how to actually make this into a subquery with the JOIN on the other table.

There are two choices. One is to use a subquery:

select * from MyTable A
inner join 
	(
	SELECT tbl_SALES_POSITION_EMPLOYEE_MAP.POSITION_ID, tbl_SALES_POSITION_EMPLOYEE_MAP.EMPLOYEE_ID
	FROM tbl_SALES_POSITION_EMPLOYEE_MAP
	WHERE (((tbl_SALES_POSITION_EMPLOYEE_MAP.POSITION_END_DATE) Is Null))
	) AS B
on A.SomeID = B.SomeID

The other choice is to save the "inner" or "subquery" as an actual saved query and then just use it by name:

Query1:
SELECT tbl_SALES_POSITION_EMPLOYEE_MAP.POSITION_ID, tbl_SALES_POSITION_EMPLOYEE_MAP.EMPLOYEE_ID
FROM tbl_SALES_POSITION_EMPLOYEE_MAP
WHERE (((tbl_SALES_POSITION_EMPLOYEE_MAP.POSITION_END_DATE) Is Null))

Query2:
select * from MyTable A inner join Query1 B on A.SomeID = B.SomeID

Still not clear on exactly what you want. Seems simple enough but you are passing on your confusion and making me confused now too. Maybe just give some sample data and hoped for results (including some examples of the records you want filtered out).