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..
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..:
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:
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 & "# "
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);"
CurrentDb.CreateQueryDef "query3", sqlString
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: