andreas_udby
Registered User.
- Local time
- Today, 03:24
- Joined
- May 7, 2001
- Messages
- 76
I have a report that pulls together all kinds of data. In essence, it gives the CEO a list of departments (called "services") within our hospital, with numbers of employees assigned and vacancies. Below each department header (the report is grouped by service) is a list of the current open positions and pertinent information about them (title, FTE, class, status, etc.). At the end of the report, he wants to have totals of the current FTE on hand and the number of vacancies across the facility. (FTE = "Full Time Equivalent", with 1.0 FTE being a 40-hour per week position.)
The Tables
We use this same database to track our recruiting workflow, so the main table is tblPositions, which lists each position by announcement number, followed by about thirty different pieces of information.
There is another table called tblService, which has the name of each service, its assigned FTE, and its current number of vacancies, along with other info.
The Query
This report is known as the Strength Report, so my query is called qryStrength. It has several other queries feeding into it, which perform various calculations and manipulations in order to give it the info it needs. For instance, qryStrengthStatus scans tblPositions in order to assign a status code to each position (R = Recruiting, NR = Not Recruiting, S = Selected, etc.; it does this by looking at dates when advertisements were posted, whether a name has been entered in the "Selectee" field, and so forth).
Because of the way the data is structured, qryStrength has a list of open positions along with their status, the service they are assigned to, and, this is important, the total assigned FTE and on-duty FTE for that position's service. The positions are the unique entities, not the services, in this case.
The Report
The report comes out fine, showing each service, its FTE numbers, and notes, with the various open positions underneath it. However, because the FTE numbers show up on each record in the query, putting a Running Sum box in the report footer doesn't give me the 800 or so FTE we actually have; it gives me 13,000+ FTE, because it's totalling up the FTE numbers on every record.
I can create a seperate query that gives us the total FTE strength, but I'm not sure how to tie it into this report, since this report has qryStrength as its record source. Can anyone point me in a better direction on this? Can I somehow sum a Text Box rather than the field underlying it?
Thanks!
Andreas
The Tables
We use this same database to track our recruiting workflow, so the main table is tblPositions, which lists each position by announcement number, followed by about thirty different pieces of information.
There is another table called tblService, which has the name of each service, its assigned FTE, and its current number of vacancies, along with other info.
The Query
This report is known as the Strength Report, so my query is called qryStrength. It has several other queries feeding into it, which perform various calculations and manipulations in order to give it the info it needs. For instance, qryStrengthStatus scans tblPositions in order to assign a status code to each position (R = Recruiting, NR = Not Recruiting, S = Selected, etc.; it does this by looking at dates when advertisements were posted, whether a name has been entered in the "Selectee" field, and so forth).
Because of the way the data is structured, qryStrength has a list of open positions along with their status, the service they are assigned to, and, this is important, the total assigned FTE and on-duty FTE for that position's service. The positions are the unique entities, not the services, in this case.
The Report
The report comes out fine, showing each service, its FTE numbers, and notes, with the various open positions underneath it. However, because the FTE numbers show up on each record in the query, putting a Running Sum box in the report footer doesn't give me the 800 or so FTE we actually have; it gives me 13,000+ FTE, because it's totalling up the FTE numbers on every record.
I can create a seperate query that gives us the total FTE strength, but I'm not sure how to tie it into this report, since this report has qryStrength as its record source. Can anyone point me in a better direction on this? Can I somehow sum a Text Box rather than the field underlying it?
Thanks!
Andreas
