Percentage calculation using two tables

[vthompson9100@gmail.com]

I am a beginner when using access. I have two tables of information. First table has employee authorized data...position and pay criteria. Second table has the employees assigned...name and position. the tables are linked to excel sheets as these get pulled from different sources. I need to come up with a way to find the percentage of positions filled. Example if I have 2 positions authorized but only 1 position filled it would return 50%.

[ranman256]

Make Query 1 that counts all positions, 2 fields, this is the total of positions ,assigned or not.
Make Query 2 that counts the people filled in a position.
make Query 3, bring in Q1 and Q2, but DO NOT LINK THEM. Bring down the fields and make a new virtual field that calculates:
Pct: [filled count]/[total]

[vthompson9100@gmail.com]

Hugely apprecaite the help. How would I be able to get a breakdown by position. Looks like the above gives a percentage of the whole. Looking to get percentages by position. For example if I have two positions for accountants and 4 pos. for line operators I need to know that breakdown for each. If I have 2 of 2 accountants that would be 100% but for line operators only having 3 pos. Filled leaves me 75% manned.

[ranman256]

No you need to bring down the position field. This counts by position.

[vthompson9100@gmail.com]

What would the expression look like for comparing the data in the different queries? Also probably would need to somehow single out in the expressikn the position name somehow....

Hugely apprecaite the help.

[rpeare]

Can you give examples of your source data, the problem here is being able to count 'available' positions vs 'filled' positions, if you can find a link between the two you're in business, otherwise it becomes a little tricky.

[vthompson9100@gmail.com]

Column headings are: Pay Rate: "6"; position: "accountant/line operator" in the table for those filling a position and very similar in the authorized table

Assigned/Authorized Tables.
PAY RATE POSITION
6 Accountant
5 Manager
3 Line Operator
3 Line Operator
6 Manager

[rpeare]

Ok, assuming both tables have the positions spelled identically I would do it this way:

1. Create a query (assuming the table name is 'AVAILABLE' for available positions)

SELECT [Pay Rate], Position, Count([Position]) as PositionsAvailable FROM Available GROUP BY Position, [Pay Rate]

This query should give you a list of positions and a count of available positions

2. Create a query (assuming the table name is 'FILLED' for filled positions)

SELECT [Pay Rate], Position, Count([Position]) as PositionsFilled FROM Filled GROUP BY Position, [Pay Rate]

this query should give you a list of positions and a count of filled positions

3. Link the two queries together through the position field, make sure the queries are linked FROM the available query TO the filled query, add the positionsavailable field and the positionsfilled field, then do your % filled calcuation using [positionsfilled]/[positionsavailable]

[vthompson9100@gmail.com]

Awesome I will give it try. Thanks.