Average in VB

[Rayner]

Hello, I'm helping a friend with his VB assignement, and he has to do an Average in a very simple Table. I can do this in Access, but VB won't accept the code I paste from Access into VB, even though the Connection is to an Access Table.

What we need is to find the Average Sales for each Region (Northern & Southern) and overall Average Sales.

In Access, the SQL is as follows:

Code: Select all

SELECT DISTINCTROW tblSales.Salesperson, Format$([tblSales].[SalesDate],'mmmm yyyy') AS [SalesDate By Month], Avg(tblSales.Northernsales) AS [Avg Of Northernsales], Avg(tblSales.Southernsales) AS [Avg Of Southernsales], Avg(tblSales.Totalsales) AS [Avg Of Totalsales]
FROM tblSales
GROUP BY tblSales.Salesperson, Format$([tblSales].[SalesDate],'mmmm yyyy'), Year([tblSales].[SalesDate])*12+DatePart('m',[tblSales].[SalesDate])-1;

If I copy and paste this into VB, I get the following error:
Generated SELECT Statement:
Error is SELECT clause: expression near 'DISTINCT ROW'.
Missing FROM clause
Error in SELECT clause: expression near '$'
Error in SELECT clause: expression near ','.
Error in GROUP BY clause
Unable to parse query text.


Any help GREATLY appreciated!

Thanks in advance.

[HansV]

DISTINCTROW is specific to Jet SQL (the dialect of SQL used by Access), it is not a standard SQL keyword. Does it help if your friend omits DISTINCTROW?

[Rayner]

Wow, Hans. Thanks for your super-quick reply! I deleted the Distinct line (we're working on this together tonight), but the other errors are still there i.e. the $ and ,

I deleted the Format $ part too, but I still have an error about the comma and the missing FROM clause.
Here's the Code now:

Code: Select all

SELECT tblSales.Salesperson, ([tblSales].[SalesDate], 'mmmm yyyy') AS [SalesDate By Month], AVG(tblSales.Northernsales) AS [Avg Of Northernsales], AVG(tblSales.Southernsales) AS [Avg Of Southernsales], AVG(tblSales.Totalsales) AS [Avg Of Totalsales]
FROM tblSales
GROUP BY tblSales.Salesperson, ([tblSales].[SalesDate], 'mmmm yyyy'), [Year]([tblSales].[SalesDate]) * 12 + DatePart('m', [tblSales].[SalesDate]) - 1;

Thanks again for your help! :)

[HansV]

You can't just remove the Format function but leave the rest unchanged:

Try this:

SELECT Salesperson, Format([SalesDate], 'mmmm yyyy') AS [SalesDate By Month], AVG(Northernsales) AS [Avg Of Northernsales], AVG(Southernsales) AS [Avg Of Southernsales], AVG(Totalsales) AS [Avg Of Totalsales] FROM tblSales GROUP BY Salesperson, Format([SalesDate], 'mmmm yyyy')

If that doesn't work either, try

SELECT Salesperson, 100*Year([SalesDate])+Month([SalesDate]) AS [SalesDate By Month], AVG(Northernsales) AS [Avg Of Northernsales], AVG(Southernsales) AS [Avg Of Southernsales], AVG(Totalsales) AS [Avg Of Totalsales] FROM tblSales GROUP BY Salesperson, 100*Year([SalesDate])+Month([SalesDate])

[Rayner]

Thanks Hans.
That's brilliant! You are AWESOME!

Thank you so much, not only for getting it working but for being so amazingly fast to reply!

Have a great night.

Take care