Subtracting rows in access help please.

[pleasehelp]

I have a query with years and sales column. I need to make a column that displays the difference in sales between years. My query is named "Qry" if that is needed. "Year" and "Sales" are the column titles if you need that for the SQL. Please help me out

 

[Geotch]

You need to upload a sample of the database and I could show you how it's done.

 

[spikepl]

@Geotch

Your enthusiasm is commendable, albeit counterproductive. If you solve people's problems for them then there is no end to your enterprise, and you won't be able to wean them off coming to AWF for the slightest obstacle. In stead, point them the right way, give links, examples, whatever enables them to proceed on their own.

@OP

Check out this link http://allenbrowne.com/subquery-01.html

 

[Geotch]

@spikepl - Ok I'll start doing that.

I know it was a lot easier for me to see the me answer, work forward or backwards through the process to replicate.

 

[pleasehelp]

QRY
Year Sales
2005 $11,185,566.49
2006 $13,479,594.55
2007 $15,969,007.83
2008 $15,880,317.72
2009 so on
2010 so on
2011 so on
this is a picture of it. I would like the change in sales from the previous year to be right next to the sales column.
This is the SQL if you need it:
SELECT Invoice.Year, Sum([Invoice]![Quantity]*[Invoice]![Price]) AS Sales
FROM Invoice
GROUP BY Invoice.Year;

Thank you in advance to the person who helps me. I have looked at other threads and I just couldn't figure it out

 

[spikepl]

@Pleasehelp

You got a link - now make an effort

 

[pleasehelp]

@spikepl thank you for the link, but I knew about subquerys, but I do not know to refer to the previous number in the row when im using it. I have tried to use sample subquery SQL statements and edit them to fit my query, but it is to no avail. this is my last resort, I would rather figure it out on my own

*edit* poor grammar

 

[pleasehelp]

SELECT Invoice.Year, Sum([Invoice]![Quantity]*[Invoice]![Price]) AS Sales
FROM Invoice
GROUP BY Invoice.Year;
(Select
Qry.Year,
Qry.Sales,
Qry.Sale AS [Current]
QryPrev.Sales AS Previous,
[Qry]![Sales]-[QryPrev]![Sales] AS [Change in Sales]
FROM
Qry INNER JOIN Qry AS QryPrev
WHERE
QryPrev.Year=([Qry]![Year]-1);

this is the SQL statement that I had, but it shows an error that I have something after the end of the SQL statement. how do I reformat this to make it work.
@spikepl can you please help me figure this out.

 

[Geotch]

Is year a text or number field?

 

[CJ_London]

Note: Year is a reserved word so may well be causing you a problem.

but it shows an error that I have something after the end of the SQL statement

you have

SELECT Invoice.Year, Sum([Invoice]![Quantity]*[Invoice]![Price]) AS Sales
FROM Invoice
GROUP BY Invoice.Year;
(Select

 

[pleasehelp]

I believe that it is a number field and I am really bad at access and have been trying different things from other posts hoping that they would work. Plus QryPrev.Sales doesn't register as the previous sales row from Qry.Sales so im sure I messed that up too
@geotch @cj_london

 

[Geotch]

I'm sure an SQL statement could be written in one query. I've attached an example of how I solve this problem. Follow the queries in order and let me know if you have any questions.

 

[pleasehelp]

@Geotch that seems to work, but my only problem is that I don't have a table with the sales in it, I had to create a function to find it within the query where I need the "change in sales" and the years do not go with the sales because it is an invoice table. I appreciate the help, but would you happen to know how to do it in my situation.

 

[CJ_London]

Providing you change your field 'year' to something else, this should work

SELECT DISTINCT myYear, 
(SELECT Sum([Quantity]*[Price]) FROM Invoice as tmp WHERE myYear=Invoice.myYear) AS Sales, 
(SELECT Sum([Quantity]*[Price]) FROM Invoice as tmp WHERE myYear=Invoice.myYear)-(SELECT Sum([Quantity]*[Price]) FROM Invoice as tmp WHERE myYear=Invoice.myYear-1) AS Diff 
FROM Invoice;

 

[Geotch]

You can query off of an existing query just like a table. Create a query that has sales by year. In that same query subtract 1 from your year. Then you can follow my example.

 

[pleasehelp]

@CJ_London how do I change the field type of my year? is that in right-click properties?

SELECT Invoice.Year, Sum([Invoice]![Price]*[Invoice]![Quantity]) AS Sales
FROM Invoice
GROUP BY Invoice.Year;
SELECT DISTINCT Year,
(SELECT Sum([Quantity]*[Price]) FROM Invoice as tmp WHERE Year=Invoice.Year) AS Sales,
(SELECT Sum([Quantity]*[Price]) FROM Invoice as tmp WHERE Year=Invoice.Year)-(SELECT Sum([Quantity]*[Price]) FROM Invoice as tmp WHERE Year=Invoice.Year-1) AS Diff
FROM Invoice;

Where do I put your statement into my existing SQL view, for it not to read "Characters found after SQL statement".
Sorry for wasting so much of your time

 

[CJ_London]

I meant the name, not the type - you do it in the invoice table.

 

[CJ_London]

Where do I put your statement into my existing SQL view, for it not to read "Characters found after SQL statement".

you don't - it replaces your entire query