Subquery Sum Containing Parameters

D

Diana

Hello once again!

Still working on this difficult query...

--What I am trying to accomplish is to have some parameters in a
subquery to reference a date and add (sum) the records that are
returned (note that the BY DATE is the most important part of this
statement!).

--Before noticing that the parameters that are OUTSIDE of the
subqueries are not being referenced IN the subquery, I had this
statement referencing FROM and TO dates in my main query:

WHERE (PO.DATE_FLD BETWEEN '" & cellValue1 & "' AND '" & cellValue2 &
"' )

--This runs just fine, no problems, even with the whole difficult
query preceeding. Now, I want to take this WHERE statement and put it
into my subquery (added in the FROM statement of my main query):

INNER JOIN (SELECT INVOICE.DATE_FLD, X_INVOIC.ITEM_CODE AS [ITEMSUM],
sum(X_INVOIC.QTY_SHIP) AS [Invoice_Sum] FROM X_INVOIC INNER JOIN
INVOICES ON (X_INVOIC.ORDER_NO = INVOICES.DOC_NO) WHERE
(INVOICES.DATE_FLD BETWEEN '" & cellValue1 & "' AND '" & cellValue2 &
"' ) GROUP BY X_INVOIC.ITEM_CODE) InvoiceItemSum ON ITEMS.ITEMNO =
InvoiceItemSum.ITEMSUM

--When I try to do this, however, I get a "Type mismatch" error. Any
ideas? I need this parameter to be referenced in four different
subqueries in my SQL statement. Here is the VBA in full that I wish to
eventually run:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub Connect2()

Dim cellValue1 As String
Dim cellValue2 As String

cellValue1 = Range("B3").Value
cellValue2 = Range("B4").Value
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Everest;Description=Everest
data;UID=;PWD=;APP=Microsoft Office
2003;WSID=ANDYHP5300;DATABASE=EVEREST_VGI;Network=DBMSS" _
), Array("OCN")), Destination:=Range("A8"))
.CommandText = Array( _
"SELECT DISTINCT ITEMS.ITEMNO, ITEMS.DESCRIPT, ITEMS.CATEGORY,
ITEMS.CUSTDATE1, QTYREC.QTY_REC1, " _
, _
"InvoiceItemSum.Invoice_Sum, X_STK_AREA.Q_STK,
InvoiceSUM.ITEM_SUM, POSUM.ITEM_SUM2, ITEMS.AVG_COST, ITEMS.AVG_SP "
_
, _
"FROM X_STK_AREA INNER JOIN ITEMS ON (X_STK_AREA.ITEM_NO =
ITEMS.ITEMNO)INNER JOIN X_PO ON (X_PO.ITEM_CODE = ITEMS.ITEMNO) INNER
JOIN X_INVOIC ON (X_INVOIC.ITEM_CODE = ITEMS.ITEMNO) INNER JOIN PO ON
(PO.DOC_NO = X_PO.ORDER_NO) " _
, _
"INNER JOIN (SELECT INVOICE.DATE_FLD, X_INVOIC.ITEM_CODE AS
[ITEMSUM], sum(X_INVOIC.QTY_SHIP) AS [Invoice_Sum] FROM X_INVOIC INNER
JOIN INVOICES ON (X_INVOIC.ORDER_NO = INVOICES.DOC_NO) WHERE
(INVOICES.DATE_FLD BETWEEN '" & cellValue1 & "' AND '" & cellValue2 &
"' ) GROUP BY X_INVOIC.ITEM_CODE) InvoiceItemSum ON ITEMS.ITEMNO =
InvoiceItemSum.ITEMSUM " _
, _
"INNER JOIN (SELECT X_INVOIC.ITEM_CODE AS [ITEMSUM],
sum(X_INVOIC.ITEM_QTY) AS [ITEM_SUM] FROM X_INVOIC INNER JOIN INVOICES
ON (X_INVOIC.ORDER_NO = INVOICES.DOC_NO) WHERE (INVOICES.DATE_FLD
BETWEEN '" & cellValue1 & "' AND '" & cellValue2 & "' ) GROUP BY
X_INVOIC.ITEM_CODE) InvoiceSUM ON ITEMS.ITEMNO = InvoiceSUM.ITEMSUM "
_
, _
"INNER JOIN (SELECT X_PO.ITEM_CODE AS [ITEMSUM2],
sum(X_PO.ITEM_QTY) AS [ITEM_SUM2] FROM X_PO INNER JOIN PO ON
(X_PO.ORDER_NO = PO.DOC_NO) WHERE (PO.DATE_FLD BETWEEN '" &
cellValue1 & "' AND '" & cellValue2 & "' ) GROUP BY X_PO.ITEM_CODE)
POSUM ON ITEMS.ITEMNO = POSUM.ITEMSUM2 " _
, _
"INNER JOIN (SELECT X_PO.ITEM_CODE AS [QTYRECI],
SUM(X_PO.QTY_REC) AS [QTY_REC1] FROM X_PO INNER JOIN PO ON
(X_PO.ORDER_NO = PO.DOC_NO) WHERE (PO.DATE_FLD BETWEEN '" & cellValue1
& "' AND '" & cellValue2 & "' ) GROUP BY X_PO.ITEM_CODE) QTYREC ON
ITEMS.ITEMNO = QTYREC.QTYRECI " _
, _
"WHERE ((ITEMS.ACTIVE='T') AND (ITEMS.INVENTORED='T') AND
(X_STK_AREA.AREA_CODE='MAIN') AND (X_INVOIC.STATUS='8') AND
(X_PO.STATUS In (2,3)) " _
, _
"AND (PO.DATE_FLD BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "' )) ORDER BY ITEMS.ITEMNO")
.Name = "Query from Venom Everest1"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
End Sub
 
D

Diana

Hello once again!

Still working on this difficult query...

--What I am trying to accomplish is to have some parameters in a
subquery to reference a date and add (sum) the records that are
returned (note that the BY DATE is the most important part of this
statement!).

--Before noticing that the parameters that are OUTSIDE of the
subqueries are not being referenced IN the subquery, I had this
statement referencing FROM and TO dates in my main query:

WHERE (PO.DATE_FLD BETWEEN '" & cellValue1 & "' AND '" & cellValue2 &
"' )

--This runs just fine, no problems, even with the whole difficult
query preceeding. Now, I want to take this WHERE statement and put it
into my subquery (added in the FROM statement of my main query):

INNER JOIN (SELECT INVOICE.DATE_FLD, X_INVOIC.ITEM_CODE AS [ITEMSUM],
sum(X_INVOIC.QTY_SHIP) AS [Invoice_Sum] FROM X_INVOIC INNER JOIN
INVOICES ON (X_INVOIC.ORDER_NO = INVOICES.DOC_NO) WHERE
(INVOICES.DATE_FLD BETWEEN '" & cellValue1 & "' AND '" & cellValue2 &
"' ) GROUP BY X_INVOIC.ITEM_CODE) InvoiceItemSum ON ITEMS.ITEMNO =
InvoiceItemSum.ITEMSUM

--When I try to do this, however, I get a "Type mismatch" error. Any
ideas? I need this parameter to be referenced in four different
subqueries in my SQL statement. Here is the VBA in full that I wish to
eventually run:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub Connect2()

Dim cellValue1 As String
Dim cellValue2 As String

cellValue1 = Range("B3").Value
cellValue2 = Range("B4").Value
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=Everest;Description=Everest
data;UID=;PWD=;APP=Microsoft Office
2003;WSID=ANDYHP5300;DATABASE=EVEREST_VGI;Network=DBMSS" _
), Array("OCN")), Destination:=Range("A8"))
.CommandText = Array( _
"SELECT DISTINCT ITEMS.ITEMNO, ITEMS.DESCRIPT, ITEMS.CATEGORY,
ITEMS.CUSTDATE1, QTYREC.QTY_REC1, " _
, _
"InvoiceItemSum.Invoice_Sum, X_STK_AREA.Q_STK,
InvoiceSUM.ITEM_SUM, POSUM.ITEM_SUM2, ITEMS.AVG_COST, ITEMS.AVG_SP "
_
, _
"FROM X_STK_AREA INNER JOIN ITEMS ON (X_STK_AREA.ITEM_NO =
ITEMS.ITEMNO)INNER JOIN X_PO ON (X_PO.ITEM_CODE = ITEMS.ITEMNO) INNER
JOIN X_INVOIC ON (X_INVOIC.ITEM_CODE = ITEMS.ITEMNO) INNER JOIN PO ON
(PO.DOC_NO = X_PO.ORDER_NO) " _
, _
"INNER JOIN (SELECT INVOICE.DATE_FLD, X_INVOIC.ITEM_CODE AS
[ITEMSUM], sum(X_INVOIC.QTY_SHIP) AS [Invoice_Sum] FROM X_INVOIC INNER
JOIN INVOICES ON (X_INVOIC.ORDER_NO = INVOICES.DOC_NO) WHERE
(INVOICES.DATE_FLD BETWEEN '" & cellValue1 & "' AND '" & cellValue2 &
"' ) GROUP BY X_INVOIC.ITEM_CODE) InvoiceItemSum ON ITEMS.ITEMNO =
InvoiceItemSum.ITEMSUM " _
, _
"INNER JOIN (SELECT X_INVOIC.ITEM_CODE AS [ITEMSUM],
sum(X_INVOIC.ITEM_QTY) AS [ITEM_SUM] FROM X_INVOIC INNER JOIN INVOICES
ON (X_INVOIC.ORDER_NO = INVOICES.DOC_NO) WHERE (INVOICES.DATE_FLD
BETWEEN '" & cellValue1 & "' AND '" & cellValue2 & "' ) GROUP BY
X_INVOIC.ITEM_CODE) InvoiceSUM ON ITEMS.ITEMNO = InvoiceSUM.ITEMSUM "
_
, _
"INNER JOIN (SELECT X_PO.ITEM_CODE AS [ITEMSUM2],
sum(X_PO.ITEM_QTY) AS [ITEM_SUM2] FROM X_PO INNER JOIN PO ON
(X_PO.ORDER_NO = PO.DOC_NO) WHERE (PO.DATE_FLD BETWEEN '" &
cellValue1 & "' AND '" & cellValue2 & "' ) GROUP BY X_PO.ITEM_CODE)
POSUM ON ITEMS.ITEMNO = POSUM.ITEMSUM2 " _
, _
"INNER JOIN (SELECT X_PO.ITEM_CODE AS [QTYRECI],
SUM(X_PO.QTY_REC) AS [QTY_REC1] FROM X_PO INNER JOIN PO ON
(X_PO.ORDER_NO = PO.DOC_NO) WHERE (PO.DATE_FLD BETWEEN '" & cellValue1
& "' AND '" & cellValue2 & "' ) GROUP BY X_PO.ITEM_CODE) QTYREC ON
ITEMS.ITEMNO = QTYREC.QTYRECI " _
, _
"WHERE ((ITEMS.ACTIVE='T') AND (ITEMS.INVENTORED='T') AND
(X_STK_AREA.AREA_CODE='MAIN') AND (X_INVOIC.STATUS='8') AND
(X_PO.STATUS In (2,3)) " _
, _
"AND (PO.DATE_FLD BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "' )) ORDER BY ITEMS.ITEMNO")
.Name = "Query from Venom Everest1"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=True
End With
End Sub
Click to expand...

This must be too much of a mind-jumble looking at all of this code,
I've gotten no responses. =P
I'll reword it...

When I use my query to get data from specific cells (using parameters)
and put it into a subquery, I get a "Type mismatch" error:

INNER JOIN (SELECT INVOICE.DATE_FLD, X_INVOIC.ITEM_CODE AS [ITEMSUM],
sum(X_INVOIC.QTY_SHIP) AS [Invoice_Sum] FROM X_INVOIC INNER JOIN
INVOICES ON (X_INVOIC.ORDER_NO = INVOICES.DOC_NO)
WHERE (INVOICES.DATE_FLD BETWEEN '" & cellValue1 & "' AND '" &
cellValue2 & "' ) GROUP BY X_INVOIC.ITEM_CODE) InvoiceItemSum ON
ITEMS.ITEMNO = InvoiceItemSum.ITEMSUM

But when I use the parameter by itself it runs just fine:

WHERE (PO.DATE_FLD BETWEEN '" & cellValue1 & "' AND '" & cellValue2 &
"' )

Hehe...let's see if that helps spark some assistance!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum with Parameters in a Subquery 5
Run Time Error 1004: Application-Defined or Object-Defined Error 6
Subquery Confusion 2
Extremely long SQL Array 4
Excel VBA Passing Parameters Through Cell 4

Top