crosstab product/report

[cpberg1]

Access 2003
Win XP, 7

hey all,

I'm having some issues with the dispay of some data created in multiple crosstab queries.

I can easily get the data that I want to display, but I'm having a hard time to format the results in something that looks close to what I want. My ideal display preference is in the excel sheet attached. Two options on different sheets, either one would be great.

The closest I've gotten is with a crosstab query but I'm having challenges putting together both values.

Esentially I want to group by Students and Lessons and then show two values pulled from queries. Max flight# per lesson and total hours per lesson. The lesson numbers go quite high (about 100) so the wrap or continuation to next page can hopefully be clean. I've tried crosstabs and creating two crosstab queries to get the appropriate data, but can't then figure out how to get both queries to mesh up on one report.

Some guidance towards the next step is appreciated!

Best, Chris

 

[sxschech]

If I go by how your sample file looks, is the first row from one crosstab and the second row from the other crosstab? If so, you could consider to union the two crosstab queries, then the data should flow fine on the report with only the union query as its record source. You can add col headings in the properties window of each crosstab to ensure that the number of cols are the same for both crosstabs.

 

[cpberg1]

Thanks I'll give a go with a union query when I get home from work.

 

[cpberg1]

Stuck here a bit creating the UNION query for my two cross tab queries. It's my first venture into UNION queries and Crosstab queries and proving quite challenging.

Used the instructions here...
http://office.microsoft.com/en-us/a...s-by-using-a-union-query-HA010206109.aspx#BM2

to come up with this (see attached)

Gives syntax error just before the UNION

Any Ideas?

 

[cpberg1]

http://answers.microsoft.com/en-us/...-quieres/06571a30-264e-41fe-8fd8-4ab66e129145

Did some more web browsing, looks like must do the union first, then run the crosstab. Seems promising, I'll post when confirmed.

 

[cpberg1]

well i'm still quite stuck. I've narrowed it down to the three queries. One is a normal select query that pulls all the data. The two crosstabs are what I need to be unioned but can't make the next leap to get them to match up. If anyone with Higher Access Power! than me can take a quick look I'd really appreciate.

I've slimmed it down to just the necessary data. My ideal "look" is in the excel above.

 

[cpberg1]

ended up combining the two desired fields. Not pretty but functional.

 

[sxschech]

with the data file you provided, based on the initial ID, the union works.

SELECT *
FROM qreportforNealCrosstabFltTime
UNION
SELECT *
FROM qreportforNealCrosstabMaxofLesson

If you need a certain set of fields, do you need to display 86 cols? then in the properties tab of each crosstab, you can indicate the specific fields you would like in the column headings and that way they should match up regardless of if the fields don't exist in one or the other table. Alternatively, if you use the sql view on the cross tab, then you would add the col headings as follows...

On the "PIVOT" line type In (field, field, field) like below:

PIVOT qreportforNealCombined.LessonNum In (1,2,3,4);

Only the fields specified will show up.