Total Positions vacant+filled

Dear all,

I have a table,that shows me a list of job title and total required
for each positions.
And a table for a job offer list that mentioned the position name and
applicant details.
My query looks like this

I have made a query combining between the salary scale and count
joboffers

Position :SALARYSCALE Query
Total required:SALARYSCALE Query
CountOfPOSITION APPLIED FOR:JOBOFFERCOUNTBYPOSITION
REMAININGBALANCE: nz([SALARYSCALE Query]![TotalRequired])-nz
([JOBOFFERCOUNTBYPOSITION]![CountOfPOSITION APPLIED FOR])

I am getting the list of the positions that has been applied for along
with the remaining balance.
But what i really needs is a list for all the position in the salary
scale mentioning the sum of 0 taken or the total job offer.

What i am getting is only the list of the job titles with a job offer
What i need is a list with the vacant job titles

Hope i was clear

Tia -

It is easier for us if you post the query using the SQL View of the query.

To get a list of vacant jobs, you would use the same two sources in your
query, but use an outer join (double-click on the line joinging the two
sources to see the joins). You will want to show ALL records from the
query/table that shows the job title and the number to fill.

Then in your query grid, make sure the job title is selected from both
sources, but the job title from the source with positions and applications
should have in the criteria row "Is Null"

If this is confusing or you need more help, post your SQL Query so we can
better understand your needs.

--
Daryl S


"Tia" wrote:

Dear all,

I have a table,that shows me a list of job title and total required
for each positions.
And a table for a job offer list that mentioned the position name and
applicant details.
My query looks like this

I have made a query combining between the salary scale and count
joboffers

Position :SALARYSCALE Query
Total required:SALARYSCALE Query
CountOfPOSITION APPLIED FOR:JOBOFFERCOUNTBYPOSITION
REMAININGBALANCE: nz([SALARYSCALE Query]![TotalRequired])-nz
([JOBOFFERCOUNTBYPOSITION]![CountOfPOSITION APPLIED FOR])

I am getting the list of the positions that has been applied for along
with the remaining balance.
But what i really needs is a list for all the position in the salary
scale mentioning the sum of 0 taken or the total job offer.

What i am getting is only the list of the job titles with a job offer
What i need is a list with the vacant job titles

Hope i was clear
.

On Nov 25, 6:17*am, Daryl S wrote:
Tia -

It is easier for us if you post the query using the SQL View of the query.. *

To get a list of vacant jobs, you would use the same two sources in your
query, but use an outer join (double-click on the line joinging the two
sources to see the joins). *You will want to show ALL records from the
query/table that shows the job title and the number to fill.

Then in your query grid, make sure the job title is selected from both
sources, but the job title from the source with positions and applications
should have in the criteria row * "Is Null"

If this is confusing or you need more help, post your SQL Query so we can
better understand your needs.

--
Daryl S



"Tia" wrote:
Dear all,

I have a table,that shows me a list of job title and total required
for each positions.
And a table for a job offer list that mentioned the position name and
applicant details.
My query looks like this

I have made a query combining between the salary scale and count
joboffers

Position :SALARYSCALE Query
Total required:SALARYSCALE Query
CountOfPOSITION APPLIED FOR:JOBOFFERCOUNTBYPOSITION
REMAININGBALANCE: nz([SALARYSCALE Query]![TotalRequired])-nz
([JOBOFFERCOUNTBYPOSITION]![CountOfPOSITION APPLIED FOR])

I am getting the list of the positions that has been applied for along
with the remaining balance.
But what i really needs is a list for all the position in the salary
scale mentioning the sum of 0 taken or the total job offer.

What i am getting is only the list of the job titles with a job offer
What i need is a list with the vacant job titles

Hope i was clear
.- Hide quoted text -

- Show quoted text -

THis is the sqlSELECT [SALARYSCALE Query].Position, [SALARYSCALE
Query].TotalRequired, JOBOFFERCOUNTBYPOSITION.[CountOfPOSITION APPLIED
FOR], nz([SALARYSCALE Query]![TotalRequired])-nz
([JOBOFFERCOUNTBYPOSITION]![CountOfPOSITION APPLIED FOR]) AS
REMAININGBALANCE
FROM [SALARYSCALE Query] INNER JOIN JOBOFFERCOUNTBYPOSITION ON
[SALARYSCALE Query].ID=JOBOFFERCOUNTBYPOSITION.JOBOFFERSACTIVE.
[POSITION APPLIED FOR].Value
ORDER BY [SALARYSCALE Query].ID;

Tia -

I have updated your query below. The SELECT statement uses the same
tables, but the join is now an outer join, which will allow us to pull
records from one table where there are no matches in the second table. You
can paste this in a new SQL View window and test it out:

[SALARYSCALE Query].Position, [SALARYSCALE
Query].TotalRequired, nz(JOBOFFERCOUNTBYPOSITION.[CountOfPOSITION APPLIED
FOR],0), nz([SALARYSCALE Query]![TotalRequired],0)-nz
([JOBOFFERCOUNTBYPOSITION]![CountOfPOSITION APPLIED FOR],0) AS
REMAININGBALANCE
FROM [SALARYSCALE Query] LEFT JOIN JOBOFFERCOUNTBYPOSITION ON
[SALARYSCALE Query].ID=JOBOFFERCOUNTBYPOSITION.JOBOFFERSACTIVE.
[POSITION APPLIED FOR].Value
ORDER BY [SALARYSCALE Query].ID;

--
Daryl S


"Tia" wrote:

On Nov 25, 6:17 am, Daryl S wrote:
Tia -

It is easier for us if you post the query using the SQL View of the query..

To get a list of vacant jobs, you would use the same two sources in your
query, but use an outer join (double-click on the line joinging the two
sources to see the joins). You will want to show ALL records from the
query/table that shows the job title and the number to fill.

Then in your query grid, make sure the job title is selected from both
sources, but the job title from the source with positions and applications
should have in the criteria row "Is Null"

If this is confusing or you need more help, post your SQL Query so we can
better understand your needs.

--
Daryl S



"Tia" wrote:
Dear all,

I have a table,that shows me a list of job title and total required
for each positions.
And a table for a job offer list that mentioned the position name and
applicant details.
My query looks like this

I have made a query combining between the salary scale and count
joboffers

Position :SALARYSCALE Query
Total required:SALARYSCALE Query
CountOfPOSITION APPLIED FOR:JOBOFFERCOUNTBYPOSITION
REMAININGBALANCE: nz([SALARYSCALE Query]![TotalRequired])-nz
([JOBOFFERCOUNTBYPOSITION]![CountOfPOSITION APPLIED FOR])

I am getting the list of the positions that has been applied for along
with the remaining balance.
But what i really needs is a list for all the position in the salary
scale mentioning the sum of 0 taken or the total job offer.

What i am getting is only the list of the job titles with a job offer
What i need is a list with the vacant job titles

Hope i was clear
.- Hide quoted text -

- Show quoted text -

THis is the sqlSELECT [SALARYSCALE Query].Position, [SALARYSCALE
Query].TotalRequired, JOBOFFERCOUNTBYPOSITION.[CountOfPOSITION APPLIED
FOR], nz([SALARYSCALE Query]![TotalRequired])-nz
([JOBOFFERCOUNTBYPOSITION]![CountOfPOSITION APPLIED FOR]) AS
REMAININGBALANCE
FROM [SALARYSCALE Query] INNER JOIN JOBOFFERCOUNTBYPOSITION ON
[SALARYSCALE Query].ID=JOBOFFERCOUNTBYPOSITION.JOBOFFERSACTIVE.
[POSITION APPLIED FOR].Value
ORDER BY [SALARYSCALE Query].ID;
.