Graph

[JoeyB]

I got a form which displays information about one airport, for one year. below the information it is displaying I got a graph with the RowSource put in VBA. It displays the info from the AirportID that has be selected at the combo box. The lines that are displayed are accurate only on the x-axis, the years are displayed by what year I select in cboYear, but I have not coded this in my vba, only but it should display the years not some year specific.



Somebody knows how I can display the years properly instead of only one year?

Props to June7, for the RowSource code

Private Sub cboAirport_AfterUpdate()

Me.boxOne.Visible = True

  ' Set the Phylum combo box to be limited by the selected Kingdom


If Not IsNull(Me.cboAirport) Then

Me.cboYear.RowSource = "SELECT QueryTest.YearID, QueryTest.Year" & _
                       " FROM QueryTest" & _
                       " WHERE AirportID = " & Me.cboAirport & _
                       " ORDER BY Year"
  Me.cboYear = Null
  Me.cboYear.Requery

End If

If Not IsNull(Me.cboAirport) Then

Me.Graph1.RowSource = "SELECT Year, AirMovementMDATotal, AirMovementRegionalTotal, AirMovementINTLTotal " & _
"FROM AircraftMovementT " & _
" WHERE AirportID = " & Me.cboAirport & ""

Me.Graph1.Requery

End If

End Sub

[June7]

Should be able to do graphing without VBA.

Change the graph type to Line (sorry, should have suggested that type in the other thread).

RowSource
SELECT [Year], AirMovementMDATotal, AirMovementRegionalTotal, AirMovementINTLTotal FROM YearT RIGHT JOIN AircraftMovementT ON YearT.YearID = AircraftMovementT.YearID WHERE (((AircraftMovementT.AirportID)=[cboAirport]));

[JoeyB]

Should be able to do graphing without VBA.

Change the graph type to Line (sorry, should have suggested that type in the other thread).

RowSource
SELECT [Year], AirMovementMDATotal, AirMovementRegionalTotal, AirMovementINTLTotal FROM YearT RIGHT JOIN AircraftMovementT ON YearT.YearID = AircraftMovementT.YearID WHERE (((AircraftMovementT.AirportID)=[cboAirport]));

Thanks again, works just fine

Question: in Graph wizard you can not select more than 6 attributes. But what I would like to do is to put make a new form, basically the same as this one, but now with 2 comboboxes, maybe even 3, with Airports, and then put the information about those airport in one single graph. Possible?

[June7]

Might not be easy. What should this graph look like? Nine series lines? Or a column chart?

Test a column chart with this RowSource:

SELECT YearT.Year, AircraftMovementT.AirMovementMDATotal, AircraftMovementT.AirMovementRegionalTotal, AircraftMovementT.AirMovementINTLTotal FROM YearT INNER JOIN AircraftMovementT ON YearT.YearID = AircraftMovementT.YearID WHERE (((YearT.Year) Like "*2012*") AND ((AircraftMovementT.AirportID)=1 Or (AircraftMovementT.AirportID)=2 Or (AircraftMovementT.AirportID)=3));

And this one:

SELECT [Airport] & ":" & [YearT].[Year] AS AirportYear, AircraftMovementT.AirMovementMDATotal, AircraftMovementT.AirMovementRegionalTotal, AircraftMovementT.AirMovementINTLTotal
FROM AirportT INNER JOIN (YearT INNER JOIN AircraftMovementT ON YearT.YearID = AircraftMovementT.YearID) ON AirportT.AirportID = AircraftMovementT.AirportID
WHERE (((YearT.Year) Like "*2012*") AND ((AircraftMovementT.AirportID)=1 Or (AircraftMovementT.AirportID)=2 Or (AircraftMovementT.AirportID)=3));

[JoeyB]

Hi, I have been working today with the graphs, and because there is so much data, it becomes very unclear. The following pictures will illustrate my frustration. And regarding your code, this only shows information for one year, I would like to show information over the 28 years. I might still use it when my boss likes it, I did manage to use commando buttons to select which airport I wanted to show information about. And the best way to do this, is the (for me) hidden query builder.

Frustrations are: bars are to close to each other, legend is small, or with a line graph it will not seperate the airports with color. So the only solution would be 9 lines?!

[JoeyB]

Might not be easy. What should this graph look like? Nine series lines? Or a column chart?

Test a column chart with this RowSource:

SELECT YearT.Year, AircraftMovementT.AirMovementMDATotal, AircraftMovementT.AirMovementRegionalTotal, AircraftMovementT.AirMovementINTLTotal FROM YearT INNER JOIN AircraftMovementT ON YearT.YearID = AircraftMovementT.YearID WHERE (((YearT.Year) Like "*2012*") AND ((AircraftMovementT.AirportID)=1 Or (AircraftMovementT.AirportID)=2 Or (AircraftMovementT.AirportID)=3));

And this one:

SELECT [Airport] & ":" & [YearT].[Year] AS AirportYear, AircraftMovementT.AirMovementMDATotal, AircraftMovementT.AirMovementRegionalTotal, AircraftMovementT.AirMovementINTLTotal
FROM AirportT INNER JOIN (YearT INNER JOIN AircraftMovementT ON YearT.YearID = AircraftMovementT.YearID) ON AirportT.AirportID = AircraftMovementT.AirportID
WHERE (((YearT.Year) Like "*2012*") AND ((AircraftMovementT.AirportID)=1 Or (AircraftMovementT.AirportID)=2 Or (AircraftMovementT.AirportID)=3));

SELECT [Airport] & ":" & [YearT].[Year] , AircraftMovementT.AirMovementMDATotal, AircraftMovementT.AirMovementRegionalTotal, AircraftMovementT.AirMovementINTLTotal FROM YearT INNER JOIN (AirportT INNER JOIN AircraftMovementT ON AirportT.AirportID = AircraftMovementT.AirportID) ON YearT.YearID = AircraftMovementT.YearID WHERE (((AircraftMovementT.AirportID)=[cboAirport] OR (AircraftMovementT.AirportID)=[cboAirport2]));

Code I eventually used

[June7]

Yes, removing the year filter from the bar chart RowSource was appropriate. I just used it to show a simple graph. I should have suggested trying without it.

Try this for line chart:

Build and save this query: MovementUNION

SELECT AirportID, Year, "MDATotal" AS Source, AirMovementMDATotal AS Data FROM YearT RIGHT JOIN AircraftMovementT ON YearT.YearID = AircraftMovementT.YearID
UNION SELECT AirportID, Year, "RegTotal", AirMovementRegionalTotal FROM YearT RIGHT JOIN AircraftMovementT ON YearT.YearID = AircraftMovementT.YearID
UNION SELECT AirportID, Year, "IntlTotal", AirMovementIntlTotal FROM YearT RIGHT JOIN AircraftMovementT ON YearT.YearID = AircraftMovementT.YearID;

That is a UNION query and must type or copy/paste in SQL View of query builder. This is essentially normalizing the data structure.

RowSource for the line chart:
TRANSFORM Sum(MovementUNION.Data) AS SumOfData
SELECT MovementUNION.Year
FROM MovementUNION
WHERE (((MovementUNION.AirportID)=1 Or (MovementUNION.AirportID)=2 Or (MovementUNION.AirportID)=3))
GROUP BY MovementUNION.Year
PIVOT AirportID & ": " & Source;

[JoeyB]

Yes, removing the year filter from the bar chart RowSource was appropriate. I just used it to show a simple graph. I should have suggested trying without it.

Try this for line chart:

Build and save this query: MovementUNION

SELECT AirportID, Year, "MDATotal" AS Source, AirMovementMDATotal AS Data FROM YearT RIGHT JOIN AircraftMovementT ON YearT.YearID = AircraftMovementT.YearID
UNION SELECT AirportID, Year, "RegTotal", AirMovementRegionalTotal FROM YearT RIGHT JOIN AircraftMovementT ON YearT.YearID = AircraftMovementT.YearID
UNION SELECT AirportID, Year, "IntlTotal", AirMovementIntlTotal FROM YearT RIGHT JOIN AircraftMovementT ON YearT.YearID = AircraftMovementT.YearID;

That is a UNION query and must type or copy/paste in SQL View of query builder. This is essentially normalizing the data structure.

RowSource for the line chart:
TRANSFORM Sum(MovementUNION.Data) AS SumOfData
SELECT MovementUNION.Year
FROM MovementUNION
WHERE (((MovementUNION.AirportID)=1 Or (MovementUNION.AirportID)=2 Or (MovementUNION.AirportID)=3))
GROUP BY MovementUNION.Year
PIVOT AirportID & ": " & Source;

The graph is working. But now I want the WHERE clause to be arbitrary by using 3 comboboxes. So I put under criteria in querybuilder under AirportID a WHERE clause with [cboAirport] Or [cboAirport2] Or [cboAirport3]

This gives me the following error:
[cboAirport]:

SELECT DISTINCT [AirportT].[AirportID], [AirportT].[Airport] FROM AirportT ORDER BY [Airport];

RowSource:

TRANSFORM Sum(MovementUNION.Data) AS SumOfData SELECT MovementUNION.Year FROM MovementUNION WHERE (((MovementUNION.AirportID)=[cboAirport] Or (MovementUNION.AirportID)=[cboAirport2] Or (MovementUNION.AirportID)=[cboAirport3])) GROUP BY MovementUNION.Year, MovementUNION.AirportID PIVOT AirportID & ": " & Source;

[June7]

I don't have much need for CROSSTAB queries. None of my graphs require them. CROSSTABS are weird about dynamic filter parameters. Review http://allenbrowne.com/ser-67.html#Param

[JoeyB]

I don't have much need for CROSSTAB queries. None of my graphs require them. CROSSTABS are weird about dynamic filter parameters. Review http://allenbrowne.com/ser-67.html#Param

So what would you suggest then?

[June7]

Use one of Allen Browne's techniques.