Multiple Graphs on a report 2

[JosephMwaba]

Hi there,
i have created a database that produce 10 graphs on a report. The graphs get their data from a query and there's a parameter value to be entered. everytime i try to run the report, it asks me to enter the parameter value 10 times for each graph. and even when i want to print the report it does the same.
I really need some serious help on this.

 

[JosephMwaba]

when it appears one, it shows me exactly what i want but without the station name.

it says #Name? on the report header. you see i want the name of the station to appear on top of the report with all its 10 graphs 9 which are appearing just firn right now)

 

[M8KWR]

I am a bit confussed why its saying #Name? on the report header because the graphs are using that parameter to obtain there information, so it should say the Station name that was typed in on the form.

Is the text box Unbound???

Are you missing one graph as well???

 

[JosephMwaba]

the text box is unbound.

I am not missing any Graphs...i have them all perfectly.
The name will only appear when i put a recordsourse on the report.....but it will have 5 duplicates!

 

[M8KWR]

the reocrdsource is causing the duplicates for some reason.

Can you run the query, and see how many records it give you without running the report.

Is got to be a something simple, but i can not see what your seeing, so frustrating....

post your query, and i will have a look at it for you.

In the table(s), that the query looks at is there multiple entries for each station or just the one.

 

[JosephMwaba]

When i run the query, it works just fine. It gives me the different dates and the scores and the Station!!
In The table there's one entry per station per day!!

This is the SQL Statment
SELECT Tbl_SubAttributes.Stations, Tbl_Inspection.Date, Tbl_Inspection.Shift, Tbl_Inspection.Inspector, Tbl_SubAttributes.[S1,1], Tbl_SubAttributes.[S1,2], Tbl_SubAttributes.[S1,3], Tbl_SubAttributes.[S2,1], Tbl_SubAttributes.[S2,2], Tbl_SubAttributes.[S2,3], Tbl_SubAttributes.[S2,4], Tbl_SubAttributes.[S3,1], Tbl_SubAttributes.[S3,2], Tbl_SubAttributes.[S3,3], (([S1,1]+[S1,2]+[S1,3]+[S2,1]+[S2,2]+[S2,3]+[S2,4]+[S3,1]+[S3,2]+[S3,3]))/10 AS Expr1
FROM Tbl_Inspection INNER JOIN Tbl_SubAttributes ON Tbl_Inspection.Numberauto = Tbl_SubAttributes.Autonumber
WHERE (((Tbl_SubAttributes.Stations)=[forms]![f_SubAttributesHeader]![txtStationName]) AND ((Tbl_Inspection.Date) Between Date() And Date()-14))
ORDER BY Tbl_Inspection.Date DESC;

 

[M8KWR]

if you run the query with the information that you want displayed on the form, how many records do you see. I think you would see 5.

What i think you need to do is in the report edit the record source. insert the Graph-Weekly query, select the Stations field, and press the backwards "E". If you leave your mouse over it, it says total. This will change the field and say group by. This will make sure its looking at just the one record.

Also, just a point, depending on if this will help the flexibility and back tracking this report. Like what you have done with the station name, linking it to the form, you can also do this with the date range. You could set the fields automatically with what you have in the query, but if the user wishes to change/alter them then they could.

Just a thought...

Let me know how you get on.

 

[JosephMwaba]

what do you mean by "press the backwards

 

[M8KWR]

in a query the is a symbol in the toolbar, that i call a backward E.

This enables you to group field value together.

So for example if insert a field from a table and a normal query would give you: -

Station1
Station2
Station2
Station2
Station3
Station1
Station3
Station2
Station1

If you press the backwards E symbol it would give you

Station1
Station2
Station3


I hope that explain, i forget the proper name for the symbol (sorry), is it "sigma"....

 

[JosephMwaba]

OH! I see what you mean!
I'll try it now

 

[JosephMwaba]

when i do that.....it changes all the fields to group by.
when i only leave group by in the station fields and try to run the query,...........it say...you tried to executr a qury that does not include the specified expression "S1'1" as an aggregate function...something like that!

 

[M8KWR]

Do not alter the main query.

Alter the record source in the report.

In here add the query, the insert only the station name field, then press the backward E symbol, and try that.

 

[JosephMwaba]

well..............................what can i say.............you are simply a Genius....!!!!!!!!!
Thank you very much for your help!!! I really appreciate it.........!
i will be in touch next time i am stuck...if you don't mind!