Pulling report from 2 queries, one with a Sum

In MS Access 2003 I'm trying to make a Report pulling from 2 queries. One
query pulls from a table of offices with their authorized staffing amounts.
The other query pulls from a query that pulls the Sum of the number of times
each office appears in a table. When I make the report, pulling from the 2
queries, the report comes out blank. I'm trying to report 3 columns - the
list of offices, authorized staffing per office, number of times office
appears in the master table of all employees. Any idea what I'm missing?

So you've created a thrid query from the two by linking their like ID field,
and are using that third query as the report's source?

Aaron Connell wrote:
In MS Access 2003 I'm trying to make a Report pulling from 2 queries. One
query pulls from a table of offices with their authorized staffing amounts.
The other query pulls from a query that pulls the Sum of the number of times
each office appears in a table. When I make the report, pulling from the 2
queries, the report comes out blank. I'm trying to report 3 columns - the
list of offices, authorized staffing per office, number of times office
appears in the master table of all employees. Any idea what I'm missing?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200811/1

Correct. However, the third query is blank, thus the related report is also
blank. I've also tried making the report from the 2 original queries, with
the same blank result. Thanks for responding.

"tkelley via AccessMonster.com" wrote:

So you've created a thrid query from the two by linking their like ID field,
and are using that third query as the report's source?

Aaron Connell wrote:
In MS Access 2003 I'm trying to make a Report pulling from 2 queries. One
query pulls from a table of offices with their authorized staffing amounts.
The other query pulls from a query that pulls the Sum of the number of times
each office appears in a table. When I make the report, pulling from the 2
queries, the report comes out blank. I'm trying to report 3 columns - the
list of offices, authorized staffing per office, number of times office
appears in the master table of all employees. Any idea what I'm missing?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200811/1

We're both missing something, it sounds straight forward. I think you're
going to have to post the SQL for each of the queries.

tkelley wrote:
So you've created a thrid query from the two by linking their like ID field,
and are using that third query as the report's source?

In MS Access 2003 I'm trying to make a Report pulling from 2 queries. One
query pulls from a table of offices with their authorized staffing amounts.
[quoted text clipped - 3 lines]
list of offices, authorized staffing per office, number of times office
appears in the master table of all employees. Any idea what I'm missing?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200811/1

FTEs Query pulls the total FTEs from the FTE query and works:
SELECT DISTINCTROW FTEs.Office, Sum(FTEs.FTE) AS [Sum Of FTE]
FROM FTEs
GROUP BY FTEs.Office;

AuthorizedFTE Query pulls the number of Authorized employees per office and
it also works:
SELECT AuthorizedFTE.Office, AuthorizedFTE.AuthorizedFTE
FROM AuthorizedFTE;

FTEs Query Query1 is supposed to pull from the 2 queries shown above, but i
just shows 3 column headings and nothing else. I've tried several
combinations:
SELECT [FTEs Query].Office, [FTEs Query].[Sum Of FTE],
AuthorizedFTE.AuthorizedFTE
FROM AuthorizedFTE INNER JOIN [FTEs Query] ON AuthorizedFTE.Office = [FTEs
Query].Office;


Do the tables they pull from have to be in the same alphabetic order, i.e.
Office name showing from A-Z? Again, thanks for looking at this. ac


"tkelley via AccessMonster.com" wrote:

We're both missing something, it sounds straight forward. I think you're
going to have to post the SQL for each of the queries.

tkelley wrote:
So you've created a thrid query from the two by linking their like ID field,
and are using that third query as the report's source?

In MS Access 2003 I'm trying to make a Report pulling from 2 queries. One
query pulls from a table of offices with their authorized staffing amounts.
[quoted text clipped - 3 lines]
list of offices, authorized staffing per office, number of times office
appears in the master table of all employees. Any idea what I'm missing?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200811/1

I'm still stumped. But this has me confused:

================
AuthorizedFTE Query pulls the number of Authorized employees per office and
it also works:
SELECT AuthorizedFTE.Office, AuthorizedFTE.AuthorizedFTE
FROM AuthorizedFTE;
================
The name of your query is "AuthorizedFTE" ... but it's selecting from a table
called "AuthorizedFTE" ? Can you confirm that. I didn't even think that was
possible.


Aaron Connell wrote:
FTEs Query pulls the total FTEs from the FTE query and works:
SELECT DISTINCTROW FTEs.Office, Sum(FTEs.FTE) AS [Sum Of FTE]
FROM FTEs
GROUP BY FTEs.Office;

AuthorizedFTE Query pulls the number of Authorized employees per office and
it also works:
SELECT AuthorizedFTE.Office, AuthorizedFTE.AuthorizedFTE
FROM AuthorizedFTE;

FTEs Query Query1 is supposed to pull from the 2 queries shown above, but i
just shows 3 column headings and nothing else. I've tried several
combinations:
SELECT [FTEs Query].Office, [FTEs Query].[Sum Of FTE],
AuthorizedFTE.AuthorizedFTE
FROM AuthorizedFTE INNER JOIN [FTEs Query] ON AuthorizedFTE.Office = [FTEs
Query].Office;

Do the tables they pull from have to be in the same alphabetic order, i.e.
Office name showing from A-Z? Again, thanks for looking at this. ac

We're both missing something, it sounds straight forward. I think you're
going to have to post the SQL for each of the queries.
[quoted text clipped - 7 lines]
list of offices, authorized staffing per office, number of times office
appears in the master table of all employees. Any idea what I'm missing?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200811/1

That is correct, the table and the query have the same name and it doesn't
cause a problem with the query. ac

"tkelley via AccessMonster.com" wrote:

I'm still stumped. But this has me confused:

================
AuthorizedFTE Query pulls the number of Authorized employees per office and
it also works:
SELECT AuthorizedFTE.Office, AuthorizedFTE.AuthorizedFTE
FROM AuthorizedFTE;
================
The name of your query is "AuthorizedFTE" ... but it's selecting from a table
called "AuthorizedFTE" ? Can you confirm that. I didn't even think that was
possible.


Aaron Connell wrote:
FTEs Query pulls the total FTEs from the FTE query and works:
SELECT DISTINCTROW FTEs.Office, Sum(FTEs.FTE) AS [Sum Of FTE]
FROM FTEs
GROUP BY FTEs.Office;

AuthorizedFTE Query pulls the number of Authorized employees per office and
it also works:
SELECT AuthorizedFTE.Office, AuthorizedFTE.AuthorizedFTE
FROM AuthorizedFTE;

FTEs Query Query1 is supposed to pull from the 2 queries shown above, but i
just shows 3 column headings and nothing else. I've tried several
combinations:
SELECT [FTEs Query].Office, [FTEs Query].[Sum Of FTE],
AuthorizedFTE.AuthorizedFTE
FROM AuthorizedFTE INNER JOIN [FTEs Query] ON AuthorizedFTE.Office = [FTEs
Query].Office;

Do the tables they pull from have to be in the same alphabetic order, i.e.
Office name showing from A-Z? Again, thanks for looking at this. ac

We're both missing something, it sounds straight forward. I think you're
going to have to post the SQL for each of the queries.
[quoted text clipped - 7 lines]
list of offices, authorized staffing per office, number of times office
appears in the master table of all employees. Any idea what I'm missing?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200811/1

Ok, so indulge me and rename the query to "qry..." and see what happens in
your third query. That's an ambiguous reference, and I'd be shocked if any
expert came in here and suggested it was fine.

Anyway, like I said, indulge me and try it.

Aaron Connell wrote:
That is correct, the table and the query have the same name and it doesn't
cause a problem with the query. ac

I'm still stumped. But this has me confused:

[quoted text clipped - 34 lines]
list of offices, authorized staffing per office, number of times office
appears in the master table of all employees. Any idea what I'm missing?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200811/1

Same result (frustrating!):
SELECT [FTEs Query].Office, [FTEs Query].[Sum Of FTE], qry.AuthorizedFTE
FROM qry INNER JOIN [FTEs Query] ON qry.Office = [FTEs Query].Office;

ac

"tkelley via AccessMonster.com" wrote:

Ok, so indulge me and rename the query to "qry..." and see what happens in
your third query. That's an ambiguous reference, and I'd be shocked if any
expert came in here and suggested it was fine.

Anyway, like I said, indulge me and try it.

Aaron Connell wrote:
That is correct, the table and the query have the same name and it doesn't
cause a problem with the query. ac

I'm still stumped. But this has me confused:

[quoted text clipped - 34 lines]
list of offices, authorized staffing per office, number of times office
appears in the master table of all employees. Any idea what I'm missing?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200811/1

Make sure your Office fields are indeed equal. For that field, does either
table have trailing spaces or something like that?

Aaron Connell wrote:
Same result (frustrating!):
SELECT [FTEs Query].Office, [FTEs Query].[Sum Of FTE], qry.AuthorizedFTE
FROM qry INNER JOIN [FTEs Query] ON qry.Office = [FTEs Query].Office;

ac

Ok, so indulge me and rename the query to "qry..." and see what happens in
your third query. That's an ambiguous reference, and I'd be shocked if any
[quoted text clipped - 10 lines]
list of offices, authorized staffing per office, number of times office
appears in the master table of all employees. Any idea what I'm missing?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ports/200811/1