Issues with Access

[creed]

Well couldnt find a Access forum so figured id' try here.

I'm trying to code a query for a client of mine, and everything works fine except for one field. The client watns to be able to show the previous years sales next to the current year sales. Anyways when I try to do so, it keeps gicving me a Syntax error/missing operator error. Now since I dont use Access everyday (thank god), i'm at a loss. Anyways here's the code:

SELECT monthsales.Unit, monthsales.Year, [Year]-1 AS LastYear, Sum(monthsales.GrossSales) AS ThisYearGrossSales, Sum(monthsales.GrossSales) WHERE monthsales.Year=monthsales.Year-1 AS LastYearGrossSales
FROM monthsales
GROUP BY monthsales.Unit, monthsales.Year, [Year]-1, [ThisYearGrossSales]-[ThisYearReturns], [ThisYearReturns]/[ThisYearGrossSales]*100, [ThisYearGrossSales]-[LastYearGrossSales]/[ThisYearGrossSales]*100
HAVING (((monthsales.Unit)=2019))
ORDER BY monthsales.Year DESC , [Year]-1 DESC;

I've put in bold the area that is givign me grief. If anyone has any thoughs, I'd really appriciate it.

[Belisarius]

Have you tried parenthesis?

[creed]

Yes I have, still keeps givign me the same error message

[Belisarius]

You've got me stumped then. I only have used Access briefly, and I rarely had to write my own queries in it.

[sde]

after looking at it some more, i have a couple questions:

1. "Sum(monthsales.GrossSales) AS ThisYearGrossSales," <-- how does that know to only select this year's gross sales?

2. can you really use the "where" clause in the select portion of a query?

3. if you want results showing by year, shouldn't you put that in the group clause first?

i wasn't aware that you could do all that logic within a sql statement.

i would have used this statement and then compared this year to last year int he programming language logic.

SELECT 
  monthsales.Unit,
  monthsales.Year,
  Sum(monthsales.GrossSales) AS YearGrossSales,
FROM monthsales
GROUP BY
  monthsales.Year,
  monthsales.Unit,  
HAVING (((monthsales.Unit)=2019))
ORDER BY 
  monthsales.Year DESC

i'd be really interested to know if you really can compute all that in the sql itself.

[creed]

Well I'll try to answer your Q's

1. "Sum(monthsales.GrossSales) AS ThisYearGrossSales," <-- how does that know to only select this year's gross sales?

Good question, I have checked it and it is only selecting the items valid for that table for the year that its' grouped into.

2. can you really use the "where" clause in the select portion of a query?

Probably not now that I think if it, if you want to keep adding items to the select statement. However that leaves the problem of how to make it select the items based on a previous year instead of the year that is in the column.

3. if you want results showing by year, shouldn't you put that in the group clause first?

Well I though that I had done that with GROUP BY monthsales.Year, but that only works fine if it's the same yer. For any calculations, I was always under the impression that you could have the database generate a temporary table, get the revelant information it needs, and throw it into the main table that you're working on. However thats' with other databases that I've used, this one seems to be very......I dont know hwo to describe it, but it's not flattering.

[creed]

Found it.

For anyone that is intrested in the answer, it was

DSum("GrossSales","monthsales","(monthsales.Year=" & monthsales.Year-1 & ") AND (monthsales.Unit=2019)") AS LastYearGrossSales

Thanks all

[sde]

not sure that this will work for you, but check out the way i formatted the subquery for the year-1. that worked for me but with a completely different databse format.

SELECT 
  monthsales.Unit, 
  monthsales.Year, 
  [Year]-1 AS LastYear,
  Sum(monthsales.GrossSales) AS ThisYearGrossSales,
 (SELECT SUM(b.YEAR)
    FROM monthsales AS b
    WHERE a.YEAR = b.YEAR - 1) AS LastYearGrossSales
FROM monthsales as a
GROUP BY a.Unit, a.Year, [Year]-1, [ThisYearGrossSales]-[ThisYearReturns], [ThisYearReturns]/[ThisYearGrossSales]*100, [ThisYearGrossSales]-[LastYearGrossSales]/[ThisYearGrossSales]*100
HAVING (((a.Unit)=2019))
ORDER BY a.Year DESC , [Year]-1 DESC;

anything past the group by line i'm not sure if it will work or not.