Access 2007 - Blank column from JOIN and UNION queries.

[Data Bender]

Greetings! This is my very first question asked on a such a forum. I'm excited to get much needed feedback from such brilliant people as yourselves.
I'm trying to query (in Access 2007) multiple monthly tables, all JOIN'ed to the below GeoIndex table as follows:



The desired result set (or an acceptable alternative) would look like the following:


I have tried queries that first JOIN then UNION, and queries that first UNION then JOIN. However, I always end up with an empty AugEmpID column. Otherwise, the result sets look fine.

I plan to PivotTable MonthlyRoster in Excel, for regular headcount reports.
Please advise. I look forward to your brilliant feedback.

[alansidman]

Your db is nor normalized for your monthly data. Your table for employees should be

Roster
------------
CostCenter
EmplID
Month

then in your query it would look like this:

SELECT GeoIndex.CostCenter, GeoIndex.Region, IIf([rMonth]="Jul",[EmplID],Null) AS JulEmplID, IIf([rMonth]="Aug",[EmplID],Null) AS AugEmplID
FROM GeoIndex INNER JOIN Roster ON GeoIndex.CostCenter = Roster.CostCenter
ORDER BY IIf([rMonth]="Jul",[EmplID],Null), IIf([rMonth]="Aug",[EmplID],Null);

[June7]

A UNION will effectively structure the data as it should have been to begin, as Alan has noted.

The UNION would be

SELECT CostCenter, EmpID, "Jul" As M FROM JulRoster
UNION SELECT CostCenter, EmpID, "Aug" FROM AugRoster
.... ;

Then you can join that query to GeoIndex, or you can include the GeoIndex in the UNION by joining in each SELECT line.

Then you can do a CROSSTAB. Why export to Excel?

[Data Bender]

Thanks for your feedback, alansidman. Per your recommendation, I added "Month" columns to the source data tables to normalize them. Then, I literally copied and pasted your above code, changing the table and column titles as needed. When I ran it the first time, both JulEmpID and AugEmpID columns were blank. So, I removed the "r" from [rMonth], which gave me JulEmpID but no AugEmpID... and now I'm back where I started. Does your above code assume that one table contains all months? Because each month actually has its own table.

Again, thank you for trying to help this Access noob. Your kindness is much appreciated.

[June7]

Alan's suggestion is to normalize the data, this means ONE table with ONE Month column, not 12 columns. Did you read my post #3?

[Data Bender]

Hi, June7. Thank you for your feedback. I took your advice, and ran the following:

SELECT CostCenter, EmpID, "Jul" As M 
FROM JulRoster
UNION SELECT CostCenter, EmpID, "Aug"
FROM AugRoster;

...which resulted as follows (note: I'm actually running Jun and Jul numbers right now, and will add Aug later):



So far, so good.

Then, I ran the following JOIN query:

SELECT GeoIndex.*, June7.EmpID, June7.M
FROM June7
RIGHT JOIN GeoIndex
ON GeoIndex.CostCenter= June7.CostCenter;

And, got this again:

The total records show me that the correct records are there. Yet, for some reason, the result set won't yield the EmpID's. Any ideas? Anyone? Please?

And, to answer your question, I'm exporting to Excel because that's my wheelhouse (not Access, as you may have guessed by now haha).

I look forward to your reply.

[Data Bender]

Thanks, June7, for the clarification of Alan's post.

I did read your post. Thank you for that too.

This is the next best thing to having actual mentors in my office. You all are wonderful!

[June7]

Are the EmpID fields in each table employing a Lookup? I never set Lookups in tables.

Still don't understand why export to Excel if all this data is in Access tables.

[Data Bender]

The EmpID fields are text, with no Lookups.

For you, June7, (and myself) I will learn to generate reports directly from Access. In the meantime, my boss' timeframes require my use of Excel, as I am faster with that application.

[June7]

Then I can't explain the blank field. Would have to examine database

[Data Bender]

That's a total bummer.

I'm positive my employer would not approve of my sharing the database, even in this instance.

Thank you anyway, June7.

[June7]

It is a weird issue. Even if you were using Lookups, something should show in the field. You could remove sensitive data. Follow instructions at bottom of my post.

[Data Bender]

SUCCESS!

In short, I stopped thinking like a spreadsheet and started thinking like a database (which is what I believe alansidman was trying to tell me to do).

First, I cleaned up some column names on the source tables, by changing "CC" and "DeptNo" to "CostCenter". (Perhaps that was unneccesary, but made my SQL code much easier to read for this noob.)

Then, I normalized the output, rather than insisted on a spreadsheet-like result set, which looks like the following:

UNION_Query
----------------
CostCenter
EmpID
EOMDate

Here's the code:

SELECT CostCenter, EmpID, EOMDate
FROM JunRoster
UNION SELECT CostCenter, EmpID, EOMDate
FROM JulRoster
UNION SELECT CostCenter, EmpID, EOMDate
FROM AugRoster;

Finally, I RIGHT JOIN'ed UNION_Query to the GeoIndex, which yielded the following normalized table:

JOIN_Query
--------------
CostCenter
Region
EmpID
EOMDate

SELECT GeoIndex.*, UNION_Query.EmpID, UNION_Query.EOMDate
FROM UNION_Query
RIGHT JOIN GeoIndex
ON UNION_Query.CostCenter = GeoIndex.CostCenter
ORDER BY GeoIndex.CostCenter;

I can now PivotTable the normalized result set in Excel. Yay! (I know... I know, June7. I can do the same in Access with a crosstab. I'll get there. )

In any case, both June7 and alansidman were helpful by confirming that my method and code were sound. I merely needed to change the parameters.

I love this forum, and look forward to giving back!

[Data Bender]

Nutz... I wanted to add to both alan's and June7's reputations. However, I'm only allowed to do so once on this thread.

[alansidman]

I rep'd June for you.