You’re offline. This is a read only version of the page.
90
Dear all,
I hope you can help me with this matter. I created an Access 2010 database with a linked table using ODBC. When I run a query on this table using the group by parameter I get an error. I changed the query in a pass trough query, but this resolves in the same error. The goal is to summize the amounts on the customer table (Table: Detailed Cust_Ledg_ Entry) in Nav 2009.
With kind regards,
Roger
"
*This post is locked for comments
hi,
this seems to be an error with the odbc driver.
1. option:
create the neded view in the sql server. to do that right click on the views item (under database name) and select new view. you can create a view by writing the sql code or using a graphical designer. maybe your choice.
be sure to write a correct sql statement. try it in a sql command window before creating the sql view.
sample:
SELECT Shippers.ShipperName, Employees.LastName,
COUNT(Orders.OrderID) AS NumberOfOrders
FROM ((Orders
INNER JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID)
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY ShipperName,LastName;
as you can see: ShipperName AND LastName are part of the group by clause.
that means: if you use a group by clause nearly every field name must be part of the group by clause except the aggregate entries.
2. option:
why not try to create the needed query using a nav query?
best regards
hi,
only a suggestion
if you are using NAV 2009 on SQL Server, try to create Sql Views with sum & group by on SQL Server and after link the View in Access with ODBC.
Great posts about this issue here:
sl-dynamics.blogspot.it/.../creating-sql-server-views-to-show.html
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (