Query Question

[anunat]

i have a table for example:

id sales filedate
1 500 6/1/2012
2 200 6/1/2012


.
.
1 400 5/1/2012
2 175 5/1/2012
.
.
1 300 4/1/2012
2 180 4/1/2012
.
.

My table will not have any dates other than the first day of a month.
I would like to find the difference between the sales for the same id numbers for the dates 5/1/2012 and 6/1/2012 and show the following: id sale diff

How do I do this?

Note: My actual table has 24 months of sales data as columns for each date.

[June7]

You need to pull a value from another record. Requires subquery. For one method review http://allenbrowne.com/subquery-01.html

Another way is query with a constructed field that calculates the filedate minus 1 month. Then join this query to the original table on the unique ID. This will put the the sales data on same row.

However, with either method, complication with your data is unique ID. Looks like will require compound identifier of ID/filedate.

[anunat]

The subquery link you provided really helped. I was able to get the results I wanted. Thank you!

I have another related question:

I am trying to use a column derived in the select statement in the where clause. I gathered online that I should use a nested select statement.

SELECT *
FROM (SELECT Main.POD, Main.DwId, Main.Product, Main.ps02 AS May2012, Main.ps02 - (SELECT TOP 1 Dupe.ps01 FROM Main AS Dupe WHERE Dupe.POD = Main.POD And Dupe.DwId = Main.DwId And Dupe.Product = Main.Product And Dupe.Dte = Forms!RestatementForm!compdate ORDER BY Dupe.DwId) AS DiffMay2012 FROM Main WHERE (Main.Dte=DateValue(Forms!RestatementForm!Focusdat e)) AS data
WHERE data.DiffMay2012=1;

If I did not do the nested statement, my results show fine. When I include the nested select statement to check if data.DiffMay2012=1, it filters the data correctly. However, DiffMay2012 column is completely blank for all rows while all other columns are what they should be.

I can't understand what I am doing wrong here. Please help!

If this is not the right way, then please suggest a way to use a derived column name in the where clause.

[June7]

I am surprised this works at all. I see an odd number of parens. Should be in pairs. Put another closing paren before AS data or remove the opening paren in front of Main.Dte=.

[anunat]

I did have another closing parenthesis. I have several other fields. While I was removing them to post the question, I must have removed the parenthesis.

I am still not sure why the field DiffMay2012 column disappears when I do the nesting

[June7]

Could be because when a field constructed by expression is also used in WHERE clause, the expression must be in the clause, not the alias fieldname. Example:

SELECT *, Year(DateRecd) & Month(DateRecd) AS YrMo FROM tablename WHERE Year(DateRecd) & Month(DateRecd)=[tbxYear] & [tbxMonth];

Maybe this carries through the alias tablename. I don't recall ever trying this query structure. If you want to provide data for testing, follow instructions at bottom of my post.