Hello Tim, thanks for answeing me

I programmed a lot years ago in superbase
but................................hahahahahaha
I start to learn again.
I recorded a macro in excel which looks like this

Sub test()

With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DRIVER=SQL
Server;SERVER=PRODAC01\NAVISION;UID=marcv;APP=Micr osoft Office
2003;WSID=PRODAC01;DATABASE=NAV370BE;Trusted_Conne cti" _
), Array("on=Yes")), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT ""Prodac$Item"".No_, ""Prodac$Item"".""Item Available Qty"",
""Prodac$Item"".""Sub Type"", ""Prodac$Item"".""Sub Sub Type"",
""Prodac$Item Ledger Entry"".Quantity" & Chr(13) & "" & Chr(10) & "FROM {oj
NAV370BE.dbo.""Prodac$Item"" ""Prodac$I" _
, _
"tem"" LEFT OUTER JOIN NAV370BE.dbo.""Prodac$Item Ledger Entry""
""Prodac$Item Ledger Entry"" ON ""Prodac$Item"".No_ = ""Prodac$Item Ledger
Entry"".""Item No_""}" & Chr(13) & "" & Chr(10) & "WHERE
(""Prodac$Item"".""Item Available Qty""$0) AND" _
, _
" (""Prodac$Item"".""Sub Type""='SS') AND (""Prodac$Item"".""Sub Sub
Type""<'WIRE')" & Chr(13) & "" & Chr(10) & "ORDER BY ""Prodac$Item"".No_" _
)
.Name = "Query from NavisionWalter"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

This is the SQL fom the microsoft query

SELECT "Prodac$Item".No_, "Prodac$Item"."Item Available Qty",
"Prodac$Item"."Sub Type", "Prodac$Item"."Sub Sub Type", "Prodac$Item Ledger
Entry".Quantity
FROM {oj NAV370BE.dbo."Prodac$Item" "Prodac$Item" LEFT OUTER JOIN
NAV370BE.dbo."Prodac$Item Ledger Entry" "Prodac$Item Ledger Entry" ON
"Prodac$Item".No_ = "Prodac$Item Ledger Entry"."Item No_"}
WHERE ("Prodac$Item"."Item Available Qty"$0) AND ("Prodac$Item"."Sub
Type"='SS') AND ("Prodac$Item"."Sub Sub Type"<'WIRE')
ORDER BY "Prodac$Item".No_

This outputs only one record from the quantity field, not the sum from all
the related quantity records which I need.

Can I change the SQL in microsoft query manualy? or Maybe I use the wrong
aprouch?

Thanks

Walter








"Tim Williams" <saxifrax at pacbell dot net wrote in message
...
It might help if you could describe how you are doing what you have so
far.

ADO?

Sounds like something along the lines of

select
table1.item_nr, sum(table2.quantity)
from
table1,table2
where
table1.item_nr = table2.item_nr(+)
group by
table1.item_nr

would do it, but I'm not sure of exact syntax for SQL Server

Tim




"Walter" wrote in message
...
Hello everybody

I need to import in a Excel sheet data from a Microsoft sql database,
I need the filtered data from some fields from one table and from a
other table which needs to be linked with a field the calculated sum from
the many related records.

Table one Table two, item nr.
field to be linked with table one

filtered
item nr-------------------------------------item nr / quantity
quantity
quantity
quantity
---------------------
sum

sheet result

item nr sum (quantity)

I can do everything except get the single result from the summed quantity
field

Anybody a idea how to achieve this?

Thanks

Walter