"Guymella" <Guymella@discussions.microsoft.com> wrote in message
news:D5076273-8831-49E6-BB17-7C8F8E324E18@microsoft.com...
> Im running a report in RS-2005 and i need to create a new table based on
> the
> count of groups in another column.
>
> Curently our databas is in VFP 9 so im using "Microsof OLE DB provider for
> visual foxpro".
>
> the problem is i cant even get a basic GROUP BY command to work in the
> query
> designer.
>
> I keep getting an error message.
> .........
> SQL Execution Error.
>
> Executed SQL statement: SELECT conditioncode, description,
> extendeddescrition, itemid, itemnumber, location, mfgr, qty FROM inventory
> GROUP BY itemid
> Error Source: Microsoft OLE DB provider for Visual FoxPro
> Error Message: SQL: GROUP BY clause is missing or invalid.
> .............................
>
> Is there a special way of calling GROUP BY with the foxpro provider?
> And beyond that is there a special way of JOINing results from a subtotal
> in
> the group to a different table?
>
> OR
>
> I can group and subtotal in a table on the report. can I reference the
> results of a SUM() function in "group footers" on one table, into a
> seperate
> table on the same report. (make the report do the math instead of the
> query.
> like excel)???
>
> MORE DETAILED EXPLINATION.
> My inventory does not include qty. (each part is catalogued separately
> because each part has different serial numbers witch are very important)
> so
> to calculate inventory qty we COUNT DISTINCT itemid's.
> The report I'm running needs to add the qty of the items to the qty of all
> the substitutes we have for the items. (I need to reference the COUNT of
> items in the inventory table to the appropriate "substitute ID" in the
> substitution table, then group by the "root id" and subtotal all the
> referenced qty's in the group.) essentially I need to know how to do a
> VLOOKUP in reporting services.
>

"Guymella" <Guymella@discussions.microsoft.com> wrote in message
news:D5076273-8831-49E6-BB17-7C8F8E324E18@microsoft.com...
> Im running a report in RS-2005 and i need to create a new table based on
> the
> count of groups in another column. .....

> Executed SQL statement: SELECT conditioncode, description,
> extendeddescrition, itemid, itemnumber, location, mfgr, qty FROM inventory
> GROUP BY itemid
> Error Source: Microsoft OLE DB provider for Visual FoxPro
> Error Message: SQL: GROUP BY clause is missing or invalid.

> My inventory does not include qty. (each part is catalogued separately
> because each part has different serial numbers witch are very important)
> so
> to calculate inventory qty we COUNT DISTINCT itemid's.
> The report I'm running needs to add the qty of the items to the qty of all
> the substitutes we have for the items. (I need to reference the COUNT of
> items in the inventory table to the appropriate "substitute ID" in the
> substitution table, then group by the "root id" and subtotal all the
> referenced qty's in the group.) essentially I need to know how to do a
> VLOOKUP in reporting services.
>