Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: chart help !

  1. #1
    Join Date
    Jul 2003
    Posts
    292

    Unanswered: chart help !

    I have a form which the user enters in dates of which records they want to graph...
    The records pulled are from table "tblInspections" and from field "INSP"
    The first two letters of field INSP are counted..

    ie

    INSP
    AB12
    AB1
    AB
    CO
    CO3

    The count is stored in a query "query3" as follows
    AB .. 3
    CO .. 2

    Then on the same form "form1" I insert a graph with this query..

    My question is.. Is there a way to add something to the bottom of my query to have data that I specify add up?

    ie.. this is how I would want my query to look like in order to graph...

    AB .. 3
    CO .. 2
    Civil .. 5 ( total AB + tot. CO )

    I want to add this is how my SQL looks like in my query..:
    Code:
    SELECT Count(tblInspections.INSP) AS CountOfINSP, Left([INSP],2) AS InspectionType
    FROM tblInspections INNER JOIN tblQR ON (tblInspections.strProject = tblQR.strProject) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strReference = tblQR.strReference)
    WHERE (((tblInspections.strDate) Between #9/26/2003# And #9/26/2003#))
    GROUP BY Left([INSP],2);
    and this is the code behind the button:
    Code:
    Private Sub Command0_Click()
    Dim sqlString As String
    sqlString = ""
    
    If Not (IsNull(Me.txtStartOpen) Or IsNull(Me.txtStartEnd)) Then
    sqlString = " tblInspections.strDate Between #" & Me.txtStartOpen & "# And #" & Me.txtStartEnd & "# "
    End If
    '...
    
    If sqlString <> "" Then sqlString = " Where" & sqlString
    sqlString = "SELECT Count(tblInspections.INSP) AS CountOfINSP, Left([INSP],2) AS InspectionType FROM tblInspections INNER JOIN tblQR ON (tblInspections.strReference = tblQR.strReference) AND (tblInspections.strArea = tblQR.strArea) AND (tblInspections.strProject = tblQR.strProject) " & sqlString & " GROUP BY Left([INSP],2);"
    
    Debug.Print sqlString
    CurrentDb.QueryDefs.Delete "query3"
    CurrentDb.CreateQueryDef "query3", sqlString
    Me.Refresh
    Me.Requery
    
    End Sub

    thanks for your help

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    The addition would have to be done in a separate query. You could put the two of them together with a union query.

  3. #3
    Join Date
    Jul 2003
    Posts
    292
    If you dont mind could you explain to me how to do Union queries ?

    I never work with them before

    thanks

  4. #4
    Join Date
    Jul 2003
    Posts
    292
    I did a little researching and I came up with what a union is.. the joining of two queries ?

    I started to create my second query in order to join them together.. but I need a little help if you don't mind..

    In my field INSP.. I want to find all records with an AB and CO and add them together..

    AB and CO would be labeled Civil

    Heres my SQL so far...

    SELECT Count(tblInspections.INSP) AS CountOfINSP, Left([INSP],2) AS Civil
    FROM tblInspections
    GROUP BY Left([INSP],2);

    All that does is gives me a count of INSPection

    How do i get it to just count and add AB and CO

    thanks

  5. #5
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Create a second query using the query you just created as the source. Make it do a Sum. It will have to have the same number of columns as the first query. Finally, make a third query that will union them:

    Select A.Col1, A.Col2 from TableA as A

    Union All

    Select B.Col1, B.Col2 from TableB as B;

  6. #6
    Join Date
    Jul 2003
    Posts
    292
    When you say.. Make it do a SUM.. how do you do that.. w/o suming the entire field ?

    thanks

  7. #7
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    SELECT Sum(CountOfINSP) AS CountOfINSP, "Civil" As Civil
    FROM PreviousQueryName;

  8. #8
    Join Date
    Jul 2003
    Posts
    292
    ok i think I got my two queries now.. how do I UNION them and get the correct answer ?

    thanks for your help

  9. #9
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    as I posted on the 5th:

    Select TableA.Col1, TableA.Col2 from TableA

    Union All

    Select TableB.Col1, TableB.Col2 from TableB;

  10. #10
    Join Date
    Jul 2003
    Posts
    292
    I think I have an idea..

    take the SQL of my first query..

    UNION ALL

    the SQL of my second query..

    and this will give me my answer ?

    what do i use as a source for my 3rd query ?

    thanks

  11. #11
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    That will not give you what you expect. How about:

    Select Qy1.Col1, Qy1.Col2 from Qy1

    Union All

    Select Qy2.Col1, Qy2.Col2 from Qy2;

    Save this as your third query.

  12. #12
    Join Date
    Jul 2003
    Posts
    292
    Ok I used the source from my 2nd query...

    I think I did something wrong.. cause im not coming up with the right answer...

    On my form "form1" - The user inputs dates to pull for those specific records.. when the user hit graph.. it graphs those records on the form ..

    What I want is an additional section of that pie chart to be:

    "Civil" - This would be all AB + CO

    I created the Union query.. but i cant seem to come with the correct answer..

    I've attached a copy of my .mdb.. its in access 97

    Do you see where what I could be doing wrong..

    thanks so much
    Attached Files Attached Files

  13. #13
    Join Date
    Jul 2003
    Posts
    292
    Ok I did wat you suggested..

    and now I have all my fields that need to be graphed..
    but Civil is not the number that I want..

    It displays the number 10

    I wanted Civil to be 3

    since.. AB = 2 and CO = 1

    I think 10 is the sum of how many records there are displayed not including civil

    how would i do that ?


    thanks

    heres how the new sql for the 3rd query looks:

    SELECT query3.CountOfINSP, query3.InspectionType
    FROM Query3
    UNION ALL
    Select query3a.CountOfCountOFINSP, query3a.Civil
    FROM query3a;
    Last edited by TonyT; 12-09-03 at 16:48.

  14. #14
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    When you run this:

    Select query3a.CountOfCountOFINSP, query3a.Civil
    FROM query3a;

    what do you get? 3 or 10?
    What do you get when you run Query3A? 3 or 10? IF you are getting 10 then the problem is with the query3a query.

  15. #15
    Join Date
    Jul 2003
    Posts
    292
    Yea i get a 10.. I was looking at that query also..

    This is how that query looks like..

    SELECT Count(query3.CountOfINSP) AS CountOfCountOfINSP, "Civil" AS Civil
    FROM query3;


    How do i get it to add AB and CO together ?

    thanks

Posting Permissions

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