one to many report

[kenneth.branning]

I'm having some trouble getting a report to show the one line with the station name and then all the data on the same line. Below is the query I created.
Any Ideas on how to do this in either a query or in a report?

Thanks



Station QuerySTN NameAugust 2011December 2011January 2012November 2011October 2011September 2011AITKIN 2E






1.11AITKIN 2E



1
AITKIN 2E


3

AITKIN 2E
0.1



AITKIN 2E

0.5


BABBITT3




BAYFIELD 1SW WWTP3.76




HURLEY

1


VIRGINIA

2


WINNIBIGOSHISH DAM

2.01


WRIGHT

1

[kenneth.branning]

Sorry the query didn't show up like it was suppose to but here is the what it is suppose to be.

Station Query
STN NameAugust 2011December 2011January 2012November 2011October 2011September 2011
AITKIN 2E 1.11
AITKIN 2E 1
AITKIN 2E 3
AITKIN 2E 0.1
AITKIN 2E 0.5

[RayMilhon]

A couple of questions.

1. Is this a crosstab Query?
2. Why are the column Headers which are Month and years sorted alphabetically?
3. AITKIN 2E 1.11 Is that a Station or is that all of the data?

[kenneth.branning]

A couple of questions.

1. Is this a crosstab Query? Yes it is a crosstab query
2. Why are the column Headers which are Month and years sorted alphabetically? It must be done alphabetically, I really had not noticed.
3. AITKIN 2E 1.11 Is that a Station or is that all of the data? AITKIN 2E is the station and 1.11 is the data. So there is 5 months of data for the station AITKIN 2E and it is 1.11, 1,3,0.1,0.5.

[RayMilhon]

Ok next question what exactly is the query used for? a Report, a Form, do the users run the query themselves. Exported to another application?

[June7]

Problem with basing a report on a crosstab is its dynamic nature. Can't always depend on the same field names every time the query runs. Stabilizing a crosstab so that it can be perpetually relied on can be difficult.

Your sample output does not show a result with a grouping on station and data horizontally on one line. This seems odd if you used a crosstab. Is this the issue you have encountered?

Provide a sample of source data.

[kenneth.branning]

My ultimate goal is to get a report that shows the one station on each row and the monthly data to show up on in each column. I had never set up a cross tab query before so in looking around for my solution I thought the crosstab would solve my problem.
I have two tables one that has the station data and the other table has the monthly precipitation data.

[RayMilhon]

Went back and looked at the original post and you did specify report. My bad for not re-reading the original.

I would also recommend not using a crosstab for the same reasons June7 mentioned

Do a regular query with the station, date and value in that query

Group on the station in the group footer put the station field. Then if you'r using a calendar year put in a textbox with the control source =Sum(IIF(month(datefield)=1,[value],0)) Do that for each month.

if its a fiscal year than change the month(datefield) = 1 to month(datefield) = whatever the first month of the fiscal year is and increment by one.

If it's a rolling calender for each month it's a bit more complicated but doable

The control source becomes =(Sum(IIF(Month(datefield) = Month(DateAdd("m",-13,date())),[value],0)
change each box across by subtracting 1 from the box to the left So the next one would be

=(Sum(IIF(Month(datefield) = Month(DateAdd("m",-12,date())),[value],0) and so on. I use -13 because I'm assuming you want the previous 12 complete months and since date() returns todays date. Also depending on how far back your data goes you may need to include the year I'm assuming here that your date criteria is a 12 month period. If more you definitely need to include the year.

[kenneth.branning]

RayMilhon,
Thank you very much I will give your recommendations a try. I pretty sure that it will give me what I want.

[RayMilhon]

Your welcome and let us know how it goes.

[June7]

If pictures help, Ray's suggestion is demonstrated by this tutorial http://datapigtechnologies.com/flashfiles/crosstab.html