Complex Report Query (CrossTab)

[SrMousse]

Hey everyone!

I need some help here, I am in the final design stages of my database… I am
working on creating some reports and I need some help getting the info I need
in the right place. Let me summarize the problem, then I will give you a
rundown of my table structure and what I need to do, finally explain how far
I am at this point.

I need to create, a complex query for use of a report I am creating. Here
is a summary of what info the final query needs to include:

CurrentRank / LastYrRank / BusType / BusName / BusPhone / etc. / Cat1 / Cat2
/ Cat3 / etc.

*******************
Here is my table Structure
*******************


|tblBusiness
|--------------
| &BusinessID
| BusinessTypeID-----1\---1\--1\
| BusinessName | | |
| ….(etc.) | | |
| | |
|tblBusinessType | | |
|-------------------- | | |
| &BusinessTypeID--%/ | |
| BusinessType | |
| ….(etc.) | |
| |
|tblCategory | |
|------------- | |
| &CategoryID---------1\ | |
| CategoryName | | |
| | |
|tblBusinessCategory | | |
|----------------------- | | |
| &BusinessID----------- |--%/ |
| &CategoryID-------- %/ |
| &CopyYear |
| Value |
|
|tblRank |
|--------- |
| &RankID |
| &BusinessID-------------------%/
| BusinessTypeID
| &Year
| Rank

1= one
%=many
&=PrimaryKey

Now, each BusinessType has several associated BusinessCategories (3-8)… all
of the businesses use that set of categories to create a unique set of
categories along with a value and a year. So, all businesses with a common
businessType will have a common set of categories and values, and will have
a set for each year

Additionally each business within a businessType is ranked by those
categories for each year. (I am currently setting the business rank by year
manually, though I will eventually create a module to do it for me.)


With me so far?


*************************
Here is The Report I need to make
*************************

BusinessType
|-----------------------------------------------------------------------------------------|
| Rank | | Telephone | |
| | |
| 2006 | Business | Fax | $ sold lt. yr | Type Meal |
Owner | |
| 2005 | Address | e-mail/web |(Category*) | (Category) | Category |
etc.|
|-----------------------------------------------------------------------------------------|
| | Bus. 1 | phone | 23,405 | dinner/ |
John Doe | |
| 1 | Street | fax | | dessert
| | |
| 1 | State/Zip | email/web | |
| | |
|------------------------------------------------------------------------------------------|
| | Bus. 2 | phone | 20,185 | breakfast
|Jane Smith| |
| 2 | Street | fax | | lunch
| | |
| 4 | State/Zip | email/web | |
| | |
|------------------------------------------------------------------------------------------|
| | Bus. 3 | phone | 18,958 | lunch |
Ron Man | |
| 3 | Street | fax | |
| | |
| 2 | State/Zip | email/web | |
| | |
|------------------------------------------------------------------------------------------|
| | Bus. 4 | phone | 17,432 | lunch / |
Bob Neff | |
| 4 | Street | fax | | dessert
| | |
| 3 | State/Zip | email/web | |
| | |
|------------------------------------------------------------------------------------------|
| Footer
|
|------------------------------------------------------------------------------------------|

*=Ranking Category

Hope this is helpful… almost done…


*****************
Here is where I am at…
*****************

I have a query named QallBusinesses which contains all tblBusiness linked to
tblBusinessCategory.

Then I have a Cross Tab Query named CTQGolfCourses (for instance) that
limits the year and business type which gives me the below results

BusType / BusName / BusPhone / etc. / Cat1 / Cat2 / Cat3 / etc

This is great… it is almost finished… Now, I need the list to include the
current year and previous year rank (lets say 2006, 2005 respectively) and
sort the list of businesses according to the current year rank… (in case of
rank ties sorting by business name) to end of with this:

CurrentRank / LastYrRank / BusType / BusName / BusPhone / etc. / Cat1 / Cat2
/ Cat3 / etc.

That is where I get stuck! Can I create a nested Crosstab?

I have tried to create a query using CTOGolfCourses and tblRank linking
BusinessID then crosstab that query to get the results I want, however it
just ends up creating a records for each business type to have every
available rank with every available year, so when I crosstab it, it ranks all
the businesses as #1 for both years.

If I can lay this out manually, I know that I can code this using VBA to
generate the exact reports I need, using the varying criteria, however I
cannot quite get the results I need manually.



Wow, this is a long post! Thanks for hanging in there! I really appreciate
any help you can give on this complex problem!