Pulling report from 2 queries, one with a Sum

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

"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

"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

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?

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

Aaron Connell wrote:
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?

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

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

"tkelley via AccessMonster.com" wrote:

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?

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

Aaron Connell wrote:
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?

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

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.

Aaron Connell wrote:
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?
[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?

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

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

"tkelley via AccessMonster.com" wrote:

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.

Aaron Connell wrote:
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?
[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?

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

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

"Aaron Connell" wrote:

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

"tkelley via AccessMonster.com" wrote:

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.

Aaron Connell wrote:
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?
[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?

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

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.

Aaron Connell wrote:
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?

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

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

"tkelley via AccessMonster.com" wrote:

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.

Aaron Connell wrote:
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?

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