Results 1 to 15 of 15
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamically Update data for graph (Access 2000)

    Your attachment hasn't made it to the post. Make sure that it is less than 100 KB in size, and that you don't preview the post after filling in the "Attach a file" box.

  2. #2
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamically Update data for graph (Access 2000)

    I have a graph in a form. The data comes from many different tables. I want to be able to select the data to be used for the x and y axis from a drop down and the chart then will be automatically updated. I have attached a stripped down example of what I am trying to do.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically Update data for graph (Access 2000)

    Sorry about that. I attached the file. I hope someone can help.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamically Update data for graph (Access 2000)

    You haven't defined relationships (links) between the tables. What is supposed to happen if the user selects fields from two different tables for the X-axis and Y-axis? Let's say the user selects Vertical_Area from the Tail table and Chord from the Wing table. Do you want all combinations of Vertical_Area and Chord, or do you want some kind of aggregate, or ...?

  5. #5
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically Update data for graph (Access 2000)

    The relationship between the tables is the name field found in each table. I do not know what you mean by relationships(links) between the tables.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamically Update data for graph (Access 2000)

    There is only one record in each of the tables (Tail, Weights, Wing) for each name. Why have different tables then? It would be much more efficient to combine the tables. Or is the database you attached not representative for the real one?

  7. #7
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically Update data for graph (Access 2000)

    This database is not representitive of the real database. It is just an extremely stripped down example meant for this problem.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamically Update data for graph (Access 2000)

    See if the attached version goes in the right direction. To see how it works, open the form in design view and select View | Code.

  9. #9
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically Update data for graph (Access 2000)

    Thank You so much. That is exactly what I was looking for. You just made my life so much easier.

  10. #10
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically Update data for graph (Access 2000)

    Hans,

    Yesterday you gave me the following code. I want to know what this means and how does it work: ON [" & strXTable & _
    "].[Name] = [" & strYTable & "].[Name] ? Thanks


    Private Sub cmdUpdate_Click()
    Dim strXField As String
    Dim strYField As String
    Dim strXTable As String
    Dim strYTable As String
    Dim strSQL As String
    If IsNull(Me.X_Axis) Then
    MsgBox "Please select a field for the X axis.", vbInformation
    Me.X_Axis.SetFocus
    Exit Sub
    End If
    If IsNull(Me.Y_Axis) Then
    MsgBox "Please select a field for the Y axis.", vbInformation
    Me.Y_Axis.SetFocus
    Exit Sub
    End If
    If Me.X_Axis = Me.Y_Axis Then
    MsgBox "You selected the same field for both axes." & vbCrLf & _
    "Please select a different one.", vbInformation
    Me.Y_Axis.SetFocus
    Exit Sub
    End If
    strXField = Me.X_Axis.Column(2)
    strXTable = Me.X_Axis.Column(3)
    strYField = Me.Y_Axis.Column(2)
    strYTable = Me.Y_Axis.Column(3)
    If strXTable = strYTable Then
    strSQL = "SELECT [" & strXField & "], [" & strYField & _
    "] FROM [" & strXTable & "] ORDER BY [" & strXField & "]"
    Else
    strSQL = "SELECT [" & strXTable & "].[" & strXField & "], [" & _
    strYTable & "].[" & strYField & "] FROM [" & strXTable & _
    "] INNER JOIN [" & strYTable & "] ON [" & strXTable & _
    "].[Name] = [" & strYTable & "].[Name] ORDER BY [" & _
    strXTable & "].[" & strXField & "]"
    End If

    With Me.Graph0
    .RowSource = strSQL
    End With
    End Sub

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamically Update data for graph (Access 2000)

    The code constructs the SQL statement for a query that serves as row source for the chart. If the user selects fields from 2 different tables from the combo boxes, the SQL statement is of the form

    SELECT Table1.Field1, Table2.Field2 FROM Table1 INNER Join Table2 ON Table1.[Name] = Table2.[Name]

    The ON ... part specifies that the two tables are linked through the [Name] field, i.e. the query only returns records where the value of [Name] is the same in Table1 and in Table2.

  12. #12
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically Update data for graph (Access 2000)

    How do you alter the following code to remove any records that have empty field values?


    strSQL = "SELECT [" & strXTable & "].[" & strXField & "], [" & _
    strYTable & "].[" & strYField & "] FROM [" & strXTable & _
    "] INNER JOIN [" & strYTable & "] ON [" & strXTable & _
    "].[Name] = [" & strYTable & "].[Name] ORDER BY [" & _
    strXTable & "].[" & strXField & "]"

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamically Update data for graph (Access 2000)

    strSQL = "SELECT [" & strXTable & "].[" & strXField & "], [" & _
    strYTable & "].[" & strYField & "] FROM [" & strXTable & _
    "] INNER JOIN [" & strYTable & "] ON [" & strXTable & _
    "].[Name] = [" & strYTable & "].[Name] WHERE [" & _
    strXTable & "].[" & strXField & "] Is Not Null And [" & _
    strYTable & "].[" & strYField & "] Is Not Null
    ORDER BY [" & _
    strXTable & "].[" & strXField & "]"

  14. #14
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    Lexington Park, Maryland, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically Update data for graph (Access 2000)

    I have almost everything working. I have only one problem left. The datasheet is not in the correct format. How do I get it to look the way the datasheet from the example database you sent me looks. The first shot is the datasheet from my DB and the second shot is from your DB.

    Thanks
    Mike

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamically Update data for graph (Access 2000)

    Your chart reads the data by rows, mine by columns. The chart toolbar has buttons to switch between them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •