Create a new column with a record different from a table

[jasarmiento]

Hi,

Hi,

I'm trying to create an Access query where some columns come from an existing table and one column has to bring information from a previous record in the table.

In the example Columns 1,2 & 3 come from an existaing table in my database. I have to create Coumn 4 wich contains the sales from the previos month:

Query
Column1 >>>>> Column2 >>>>> Column3 >>>>> Column4 (This is my question)
Company >>>>> Period >>>>>>> Sales >>>>>>> Sales n-1
XYZ >>>>>>>>> 1 >>>>>>>>>>> 100 >>>>>>>>> null
XYZ >>>>>>>>> 2>>>>>>>>>>> 200 >>>>>>>>100 (comes from period 1)

Thank you in advance

 

[Beetle]

Use a sub query.

For example;

Select YourTable.Company, YourTable.Period, YourTable.Sales, (Select Sales From YourTable As B Where B.Company = YourTable.Company And B.Period = YourTable.Period-1) AS PriorSales
From YourTable;

 

[jasarmiento]

Thank you Sean. It works nicely