Pulling report from 2 queries, one with a Sum

[Aaron Connell]

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?

 

[Aaron Connell]

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.

 

[Aaron Connell]

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.

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?

 

[Aaron Connell]

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:

================
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.

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?

 

[Aaron Connell]

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
expert came in here and suggested it was fine.

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

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?

 

[Aaron Connell]

One other thing - I removed all relationships and then the query below could
not be created - "You have chosen fields from record sources which the
wizrard cannot connect. You may have chosen fields from a table and query
based on that table." When I add relationships back in from master table
OFFICE field to OFFICE field in the other 2 tables, I can then at least
complete the 3rd query, the problem is it just shows column headings with no
data. ac

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
expert came in here and suggested it was fine.

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

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?

 

[Aaron Connell]

I wish I could, except the mdb is full of employees' personal information,
names, Social Security numbers. That would be the fast track to me getting
fired. Is there any other way to do this? (sorry it's such a pain in the
neck) ac

Okay ... can you create a new mdb, put only the tables and queries you're
talking about in it, then zip it up and send it to me? (e-mail address removed)

P.S. It HAS to be zipped, because Windows won't let a mdb attachement
through.

One other thing - I removed all relationships and then the query below could
not be created - "You have chosen fields from record sources which the
wizrard cannot connect. You may have chosen fields from a table and query
based on that table." When I add relationships back in from master table
OFFICE field to OFFICE field in the other 2 tables, I can then at least
complete the 3rd query, the problem is it just shows column headings with no
data. ac

Same result (frustrating!):
SELECT [FTEs Query].Office, [FTEs Query].[Sum Of FTE], qry.AuthorizedFTE

[quoted text clipped - 16 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?

 

[Aaron Connell]

I'll try that and will let you know either way. ac

If the sensitive info is in one of the two tables in question, you could copy
it and remove the sensitive fields. But beyond that, I don't have much more
to offer. I'm not the best at using only text.

Maybe someone else will join in. You may want to re-post a new thread since
people may think this one is resolved because of the number of responses.

I wish I could, except the mdb is full of employees' personal information,
names, Social Security numbers. That would be the fast track to me getting
fired. Is there any other way to do this? (sorry it's such a pain in the
neck) ac

Okay ... can you create a new mdb, put only the tables and queries you're
talking about in it, then zip it up and send it to me? (e-mail address removed)

[quoted text clipped - 15 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?

 

[Aaron Connell]

TK - I figured it out!!!!! As I was removing the private employee info, I
thought I would try one more thing before zipping it to you. I redid the
Authorized Emplolyees per Office table and copied the office names from the
Office table (in alphabetical order). Apparently, the original Authorized
table not being in alphabetical order (copied from an unrelated spreadsheet)
was the problem. Thanks for taking the time to look at this. ac

I'll try that and will let you know either way. ac

If the sensitive info is in one of the two tables in question, you could copy
it and remove the sensitive fields. But beyond that, I don't have much more
to offer. I'm not the best at using only text.

Maybe someone else will join in. You may want to re-post a new thread since
people may think this one is resolved because of the number of responses.

I wish I could, except the mdb is full of employees' personal information,
names, Social Security numbers. That would be the fast track to me getting
fired. Is there any other way to do this? (sorry it's such a pain in the
neck) ac

Okay ... can you create a new mdb, put only the tables and queries you're
talking about in it, then zip it up and send it to me? (e-mail address removed)
[quoted text clipped - 15 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?

 

[Aaron Connell]

I'll bet you're right. I tried to change my new Authorized table and it
automatically alphabetizes when I close and re-open. I either typed one
character wrong on my 1st attempt, or set up the table incorrectly as drop
down arrows next to each office name kept it from alphabetizing. I can't
even remember my original method as it's been too long and I fried my brain
trying different combinations. Thanks again! ac

I'm glad it works now. But how data is sorted in a table, whether it went in
alphabetically or not, doesn't matter. That's just not how databases work.
I'm more inclined to suspect something like the trailing spaces idea I had or
something of the like that was causing the comparisons of the office names to
not be an EXACT match. Copying your values from the one table worked because
when you pasted them into the other table, you pasted in EXACT matches. So
while your solution worked, I think it worked for a different reason.

Just for grins, if you copy your office names and paste them in a different
order, I bet it would still work.

Glad you got past it. Good luck.

TK - I figured it out!!!!! As I was removing the private employee info, I
thought I would try one more thing before zipping it to you. I redid the
Authorized Emplolyees per Office table and copied the office names from the
Office table (in alphabetical order). Apparently, the original Authorized
table not being in alphabetical order (copied from an unrelated spreadsheet)
was the problem. Thanks for taking the time to look at this. ac

I'll try that and will let you know either way. ac

[quoted text clipped - 15 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?