Crosstab - Outer Join

I have a crosstab with years as the row headings. I want all years in the table to show up, despite whether or not the criteria I specify discludes any certain year (that year would return 0 values). The problem is I can't seem to get this to work, even with an outer join to a query of all possible years.

Example:

1996-2005 are all the possible years. However, when certain criteria are specified, the query does not display 1996 because there is no data for 1996 with those specifications. What would be a reason an outer join isn't forcing it to display 1996?

I have several subreports based on this range of years, and if they don't all display all years, they don't align correctly.

Thanks for any help.

Nevermind. It seems I must query the crosstab, using an outer join in the new query. Maybe someone can explain why this is necessary, though.

Thanks.

It is important to understand how an outer join differs from an inner join (the normal join type). Help should help.

Tiro, without accusing you of anything bad, this is a sample of what we call "flat file thinking."

The data model for Excel is rows and columns that intersect to form cells that appear on the screen whether any data resides there or not.

The data model for Access is tables with records that have fields that in datasheet mode, look much like a spreadsheet - but the resemblance is terribly misleading, and therein lies the "trap" you fell into.

Access uses a "sparse" data model that says sometimes it is OK if you look for something but there is nothing there. ("Sparse" in Access ==> no records act as placeholders unless YOU build them in the first place). When you follow this logic through, it becomes possible to understand that Access is built to handle this case of "no data for 1996" because it has, to put it in anthropomorphic terms, low expectations.

You, on the other hand, have imposed your expectations inappropriately and are now disappointed to find that they have not been met. This is a case where the FULL version of Murphy's Law is appropriate:

"If anything CAN go wrong, it WILL go wrong." (That's the common part.) "So design in a way that it CANNOT go wrong in the first place." (That's the part that everyone forgets.) - Paraphrased from Ed Murphy, White Sands, NV, USA, 1950's and 1960's.

The "outer join" is one of the possible ways to assure that something cannot go wrong. Not the only way, but appropriate for your situation, at least.

Pat, as far as I know, I do know the difference between inner and outer joins. The Access help file certainly doesn't seem to tell me anything new.

Doc, thanks for the help, though I must admit that I'm trying to understand the applicability of everything you said. I had no expectations for what I was attempting - just the attempt, itself, which didn't prove disappointing once I achieved the results I was looking for. I'm a bit confused by your statement that my expectations were inappropriate, but that an outer join is appropriate for my situation. I was just curious why an outer join will achieve my desired results when I query a crosstab, but won't in the crosstab, itself. It seemed to me that I don't have enough understanding of crosstabs. Hell, I don't know. Maybe I was going about it the wrong way to begin with. I'll briefly let you know what I was dealing with:

I recently took over this database from someone else, and I think some people might actually cry if they saw the state it was (is?) in. There were dozens of queries that returned single numbers, which were used in reports, which were in turn printed out and used to manually enter data into a Word document... don't ask me. The number of those queries was multiplied because there was one for each year. The main table is a flat file - there's nothing relational about it, though there clearly should be. Upon several discoveries, I've actually laughed out loud.

The purpose of this databse is to track data for 4 different regions in the US. Instead of the numerous queries and reports that previously existed, I've toned it down to 7 crosstabs, 7 subreports, and 1 main report. The crosstabs each display a cross from years to regions for 7 different stats (averages and counts). The 7 subreports format the data, and the main report meshes it, with the ability to limit the results to any or all regions. For it to mesh, I needed to return 0-values for data that didn't exist in any given year. Anyway, that's what I came up with on a first shot.

I am always looking for, and open to, new ideas on how to make something better and more efficient (including myself). The more I learn, the better-equipped I will be to accomplish this.