Fix CrossTabQuery or use a different Table Setup

[sxschech]

I'm pondering moving an excel ap into access. Primarily because there are many tabs and linked formula and when we need to add a new group or even one new row to an existing group, adjusting all the rows and formula become quite tedious. The various tabs have a "window effect" in that the summary tabs look at 6 week snapshots while the detail tabs contain a semester terms worth of data.

The data in excel go across for weeks and down for majors and status and compare 3 years for each of the 6 week windows on same page at the summary level.

I brought in a week's worth of data from each year and set up a table. I "uncrosstabed" the data and wrote this crosstab query to display the results. (rough draft)

[6] is a temporary name until I figure out how to automate it, that redefines week 39 from field called WeekNo for the 6th week of data to be displayed in the "moving window". As we need to compare the exact week number from each of 3 yrs and using the date may be a bit tricky, for selecting time period and for crosstab display.

[tt]SELECT Yr1.Semester, Yr1.School, Yr1.Major, Yr1.App_Status, Yr1.[6], Yr2.[6], Yr3.[6]
FROM ((qryAdminStats_GP1 AS Yr1 INNER JOIN qryAdminStats_GP1 AS Yr2 ON (Yr1.App_Status = Yr2.App_Status) AND (Yr1.Major = Yr2.Major) AND (Yr1.School = Yr2.School) AND (Yr1.Semester = Yr2.Semester)) INNER JOIN qryAdminStats_GP1 AS Yr3 ON (Yr2.App_Status = Yr3.App_Status) AND (Yr2.Major = Yr3.Major) AND (Yr2.School = Yr3.School) AND (Yr2.Semester = Yr3.Semester)) INNER JOIN tblAppStatus ON Yr3.App_Status = tblAppStatus.AppStatus
WHERE (((Yr1.Yr)="2004") AND ((Yr2.Yr)='2005') AND ((Yr3.Yr)='2006'))
ORDER BY Yr1.Semester, Yr1.School, Yr1.Major, tblAppStatus.AppStatus_Sort;
[/tt]

Issues so far
1. How to have query display all majors for a given school, example if Yr1 has Major "Test", Yr2 and Yr3 do not. When I tried chaning the joins, got an ambiuous error.

2. Is it correct to have data in table like this or can I leave data in original format as I'll be needing to copy and paste from the excel file into access and would like the least amount of reformatting needed.
[tt]
Yr Term Date WeekNo School Major Status Stat_Count
2005 F 6/10/05 39 ART Graphic Applied 8
2005 F 6/10/05 39 ART Graphic Accepted 5
..
2005 F 6/17/05 39 ART Graphic Applied 9
2005 F 6/17/05 39 ART Graphic Accepted 5
..
2006 F 6/09/06 39 ART Graphic Applied 5
2006 F 6/09/06 39 ART Graphic Accepted 3
..
2006 F 6/16/06 39 ART Graphic Applied 5
2006 F 6/16/06 39 ART Graphic Accepted 3
..
..

Detail
Exel Format
Yr Term Date School Major 6/09/06 6/16/06
WeekNo 38 39
--------------------------------------------------
2006 F ART Graphic Applied 5 5
2006 F ART Graphic Accepted 3 3
.. 6/10/05 6/17/05
2005 F ART Graphic Applied 8 9
2005 F ART Graphic Accepted 5 5
..
..


Summary 2005 | 2006
WeekNo 35 36 37 38 39 | 35 36 37 38 39
Graph App 8 9 | 5 5
Acc 5 5 | 3 3
NetI 0 0 | 1 0
Test App 2 1 | 0 0
Acc 1 1 | 0 0
NetI 3 0 | 0 0
[/tt]

Notes:
Current Data is entered into the table/excel detail sheets from a series of reports that are run weekly, if source doesn't have data it doesn't show on source output, but we still would like to display as shown above for Test

Also there is a caculated field on the summary "NetI", this can be overriden when a separate report indicates that the NetI number is greater than what the calculation derives, it's a timing issue as to when the main report comes out and the NetI data is available.

There are many more calcs on the output, but I imagine once this part is taken care of, I can then write the reports in access to do the calculations.

Any thoughts/advice would be greatly appreciated.

 

[dhookom]

1. You can always take the result of your crosstab and join it to a query that has all of the Majors.

2. I generally import the Excel worksheet into a temporary table. Then I use code to step throught the import to cull the values and update/append to a normalized table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]