G
Guest
I am trying to write an expression for a query that uses a field from the prior record, but I'm not sure how to tell it that I want the prior record, not the current one. Any suggestions
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
T
Tom Ellison
Dear Brooke:
The rows in a table or query are not in any particular order, except
if you impose an order on them. So, the term "prior" has only the
meaning you place on it.
If you have a set of columns in a table or report that puts then into
an order, then the term "prior" has meaning in the sense of that
order.
One thing to watch for is that not all "orderings" are unique. If the
data are not unique for the set of columns on which you order it, they
will still appear on the screen or on a report in a specific order,
but actually those rows which have the identical values for the
ordering columns are shown in an arbitrary order that is not available
to you when you are programming. This means that, for an "ambiguous
ordering" there is not always a specific "prior" row of data.
Rather, there is a "tie" for which row is the "prior" row in this
case, and a query cannot resolve this "tie" to show you one specific
row as the "prior" row. You may need to add some arbitrary column to
the ordering if you have one that is guaranteed to "break the tie."
Now the way to find the "prior" row of data (once you've solved the
ambiguity problem) is called a correlated subquery.
If you would provide a query that shows everything except the
column(s) in which you want the value(s) from the prior row, making
sure it is uniquely ordered, and tell me which value(s) from the prior
row you want to see, I can give you a sample of how you can do this.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
The rows in a table or query are not in any particular order, except
if you impose an order on them. So, the term "prior" has only the
meaning you place on it.
If you have a set of columns in a table or report that puts then into
an order, then the term "prior" has meaning in the sense of that
order.
One thing to watch for is that not all "orderings" are unique. If the
data are not unique for the set of columns on which you order it, they
will still appear on the screen or on a report in a specific order,
but actually those rows which have the identical values for the
ordering columns are shown in an arbitrary order that is not available
to you when you are programming. This means that, for an "ambiguous
ordering" there is not always a specific "prior" row of data.
Rather, there is a "tie" for which row is the "prior" row in this
case, and a query cannot resolve this "tie" to show you one specific
row as the "prior" row. You may need to add some arbitrary column to
the ordering if you have one that is guaranteed to "break the tie."
Now the way to find the "prior" row of data (once you've solved the
ambiguity problem) is called a correlated subquery.
If you would provide a query that shows everything except the
column(s) in which you want the value(s) from the prior row, making
sure it is uniquely ordered, and tell me which value(s) from the prior
row you want to see, I can give you a sample of how you can do this.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
M
MGFoster
Brooke said:I am trying to write an expression for a query that uses a field from the prior record, but I'm not sure how to tell it that I want the prior record, not the current one. Any suggestions?
Refer to this MS KB article:
http://support.microsoft.com:80/support/kb/articles/q101/0/81.asp
B
Brooke Williams
My primary key is the date. There is a total field [end]. I would like to
use [end] from 3/23 to figure out 3/24's data, and so on.
Thank you.
not the current one. Any suggestions?
use [end] from 3/23 to figure out 3/24's data, and so on.
Thank you.
prior record, but I'm not sure how to tell it that I want the prior record,Tom Ellison said:Dear Brooke:
The rows in a table or query are not in any particular order, except
if you impose an order on them. So, the term "prior" has only the
meaning you place on it.
If you have a set of columns in a table or report that puts then into
an order, then the term "prior" has meaning in the sense of that
order.
One thing to watch for is that not all "orderings" are unique. If the
data are not unique for the set of columns on which you order it, they
will still appear on the screen or on a report in a specific order,
but actually those rows which have the identical values for the
ordering columns are shown in an arbitrary order that is not available
to you when you are programming. This means that, for an "ambiguous
ordering" there is not always a specific "prior" row of data.
Rather, there is a "tie" for which row is the "prior" row in this
case, and a query cannot resolve this "tie" to show you one specific
row as the "prior" row. You may need to add some arbitrary column to
the ordering if you have one that is guaranteed to "break the tie."
Now the way to find the "prior" row of data (once you've solved the
ambiguity problem) is called a correlated subquery.
If you would provide a query that shows everything except the
column(s) in which you want the value(s) from the prior row, making
sure it is uniquely ordered, and tell me which value(s) from the prior
row you want to see, I can give you a sample of how you can do this.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
not the current one. Any suggestions?
T
Tom Ellison
Dear Brooke:
I suggest you put together a query as I requested, and post it. That
gives me something from which I can start. Otherwise, I can only give
you theory about how to do it. I believe you'll catch on better if I
build one for you.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
I suggest you put together a query as I requested, and post it. That
gives me something from which I can start. Otherwise, I can only give
you theory about how to do it. I believe you'll catch on better if I
build one for you.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
My primary key is the date. There is a total field [end]. I would like to
use [end] from 3/23 to figure out 3/24's data, and so on.
Thank you.
prior record, but I'm not sure how to tell it that I want the prior record,Tom Ellison said:Dear Brooke:
The rows in a table or query are not in any particular order, except
if you impose an order on them. So, the term "prior" has only the
meaning you place on it.
If you have a set of columns in a table or report that puts then into
an order, then the term "prior" has meaning in the sense of that
order.
One thing to watch for is that not all "orderings" are unique. If the
data are not unique for the set of columns on which you order it, they
will still appear on the screen or on a report in a specific order,
but actually those rows which have the identical values for the
ordering columns are shown in an arbitrary order that is not available
to you when you are programming. This means that, for an "ambiguous
ordering" there is not always a specific "prior" row of data.
Rather, there is a "tie" for which row is the "prior" row in this
case, and a query cannot resolve this "tie" to show you one specific
row as the "prior" row. You may need to add some arbitrary column to
the ordering if you have one that is guaranteed to "break the tie."
Now the way to find the "prior" row of data (once you've solved the
ambiguity problem) is called a correlated subquery.
If you would provide a query that shows everything except the
column(s) in which you want the value(s) from the prior row, making
sure it is uniquely ordered, and tell me which value(s) from the prior
row you want to see, I can give you a sample of how you can do this.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
not the current one. Any suggestions?
B
Brooke Williams
Tom,
I'm not sure if this is what you want, but here's my Query:
[Date], [Access], [Seperate]. [Access] and [Seperate] would be entered by
the user on a form. Since the values are dependent on the prior day's.
I want to have two expressions (or just one if I could figure that out) that
give you the initial and ending values for the day. The values that I want
are Current day's Actual =Prior day's Actual +[Access]-[Seperate].
I hope that makes sense.
Brooke
I'm not sure if this is what you want, but here's my Query:
[Date], [Access], [Seperate]. [Access] and [Seperate] would be entered by
the user on a form. Since the values are dependent on the prior day's.
I want to have two expressions (or just one if I could figure that out) that
give you the initial and ending values for the day. The values that I want
are Current day's Actual =Prior day's Actual +[Access]-[Seperate].
I hope that makes sense.
Brooke
Tom Ellison said:Dear Brooke:
I suggest you put together a query as I requested, and post it. That
gives me something from which I can start. Otherwise, I can only give
you theory about how to do it. I believe you'll catch on better if I
build one for you.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
My primary key is the date. There is a total field [end]. I would like to
use [end] from 3/23 to figure out 3/24's data, and so on.
Thank you.
theTom Ellison said:Dear Brooke:
The rows in a table or query are not in any particular order, except
if you impose an order on them. So, the term "prior" has only the
meaning you place on it.
If you have a set of columns in a table or report that puts then into
an order, then the term "prior" has meaning in the sense of that
order.
One thing to watch for is that not all "orderings" are unique. If the
data are not unique for the set of columns on which you order it, they
will still appear on the screen or on a report in a specific order,
but actually those rows which have the identical values for the
ordering columns are shown in an arbitrary order that is not available
to you when you are programming. This means that, for an "ambiguous
ordering" there is not always a specific "prior" row of data.
Rather, there is a "tie" for which row is the "prior" row in this
case, and a query cannot resolve this "tie" to show you one specific
row as the "prior" row. You may need to add some arbitrary column to
the ordering if you have one that is guaranteed to "break the tie."
Now the way to find the "prior" row of data (once you've solved the
ambiguity problem) is called a correlated subquery.
If you would provide a query that shows everything except the
column(s) in which you want the value(s) from the prior row, making
sure it is uniquely ordered, and tell me which value(s) from the prior
row you want to see, I can give you a sample of how you can do this.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Tue, 23 Mar 2004 08:31:08 -0800, Brooke
I am trying to write an expression for a query that uses a field from
prior record, but I'm not sure how to tell it that I want the prior record,
not the current one. Any suggestions?
B
Brooke Williams
Or should I be using something like an update query?
Tom Ellison said:Dear Brooke:
I suggest you put together a query as I requested, and post it. That
gives me something from which I can start. Otherwise, I can only give
you theory about how to do it. I believe you'll catch on better if I
build one for you.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
My primary key is the date. There is a total field [end]. I would like to
use [end] from 3/23 to figure out 3/24's data, and so on.
Thank you.
theTom Ellison said:Dear Brooke:
The rows in a table or query are not in any particular order, except
if you impose an order on them. So, the term "prior" has only the
meaning you place on it.
If you have a set of columns in a table or report that puts then into
an order, then the term "prior" has meaning in the sense of that
order.
One thing to watch for is that not all "orderings" are unique. If the
data are not unique for the set of columns on which you order it, they
will still appear on the screen or on a report in a specific order,
but actually those rows which have the identical values for the
ordering columns are shown in an arbitrary order that is not available
to you when you are programming. This means that, for an "ambiguous
ordering" there is not always a specific "prior" row of data.
Rather, there is a "tie" for which row is the "prior" row in this
case, and a query cannot resolve this "tie" to show you one specific
row as the "prior" row. You may need to add some arbitrary column to
the ordering if you have one that is guaranteed to "break the tie."
Now the way to find the "prior" row of data (once you've solved the
ambiguity problem) is called a correlated subquery.
If you would provide a query that shows everything except the
column(s) in which you want the value(s) from the prior row, making
sure it is uniquely ordered, and tell me which value(s) from the prior
row you want to see, I can give you a sample of how you can do this.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Tue, 23 Mar 2004 08:31:08 -0800, Brooke
I am trying to write an expression for a query that uses a field from
prior record, but I'm not sure how to tell it that I want the prior record,
not the current one. Any suggestions?
T
Tom Ellison
Dear Brooke:
What we have here is probably a failure to communicate. Sorry.
Please create a query that does what you want, except for the
column(s) you want to see from the "prior" record. Sort the records
by as many columns as is necessary to create a unique sort (per my
earlier explanation). Choose the SQL view of this query, and paste
the code into your next post. Specify which columns from the "prior"
record you want to see. I'll try to take it from there.
OK?
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
What we have here is probably a failure to communicate. Sorry.
Please create a query that does what you want, except for the
column(s) you want to see from the "prior" record. Sort the records
by as many columns as is necessary to create a unique sort (per my
earlier explanation). Choose the SQL view of this query, and paste
the code into your next post. Specify which columns from the "prior"
record you want to see. I'll try to take it from there.
OK?
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Tom,
I'm not sure if this is what you want, but here's my Query:
[Date], [Access], [Seperate]. [Access] and [Seperate] would be entered by
the user on a form. Since the values are dependent on the prior day's.
I want to have two expressions (or just one if I could figure that out) that
give you the initial and ending values for the day. The values that I want
are Current day's Actual =Prior day's Actual +[Access]-[Seperate].
I hope that makes sense.
Brooke
Tom Ellison said:Dear Brooke:
I suggest you put together a query as I requested, and post it. That
gives me something from which I can start. Otherwise, I can only give
you theory about how to do it. I believe you'll catch on better if I
build one for you.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
My primary key is the date. There is a total field [end]. I would like to
use [end] from 3/23 to figure out 3/24's data, and so on.
Thank you.
Dear Brooke:
The rows in a table or query are not in any particular order, except
if you impose an order on them. So, the term "prior" has only the
meaning you place on it.
If you have a set of columns in a table or report that puts then into
an order, then the term "prior" has meaning in the sense of that
order.
One thing to watch for is that not all "orderings" are unique. If the
data are not unique for the set of columns on which you order it, they
will still appear on the screen or on a report in a specific order,
but actually those rows which have the identical values for the
ordering columns are shown in an arbitrary order that is not available
to you when you are programming. This means that, for an "ambiguous
ordering" there is not always a specific "prior" row of data.
Rather, there is a "tie" for which row is the "prior" row in this
case, and a query cannot resolve this "tie" to show you one specific
row as the "prior" row. You may need to add some arbitrary column to
the ordering if you have one that is guaranteed to "break the tie."
Now the way to find the "prior" row of data (once you've solved the
ambiguity problem) is called a correlated subquery.
If you would provide a query that shows everything except the
column(s) in which you want the value(s) from the prior row, making
sure it is uniquely ordered, and tell me which value(s) from the prior
row you want to see, I can give you a sample of how you can do this.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Tue, 23 Mar 2004 08:31:08 -0800, Brooke
I am trying to write an expression for a query that uses a field from the
prior record, but I'm not sure how to tell it that I want the prior record,
not the current one. Any suggestions?
B
Brooke Williams
SELECT [501].Date, [501].[Perm Access1], [501].[Perm Separated1],
Sum([501].SCHD1) AS SumOfSCHD1, Sum([501].[UN-1]) AS [SumOfUN-1],
[501].[Detail Perm IN+1], [501].[Detail perm OUT-1], [501].[Loan perm in1],
[501].[loan perm out1], Sum([501].PLWOP1) AS SumOfPLWOP1, Sum([501].[PS/L1])
AS [SumOfPS/L1], [501].[PA/L1], Sum([501].PTRG1) AS SumOfPTRG1,
Sum([501].POTHER1) AS SumOfPOTHER1, Sum([501].PEXTLWOP1) AS SumOfPEXTLWOP1,
Sum([501].FMLA1) AS SumOfFMLA1, [Perm Access1]-[Perm Separated1] AS [Asg
Actual]
FROM 501
GROUP BY [501].Date, [501].[Perm Access1], [501].[Perm Separated1],
[501].[Detail Perm IN+1], [501].[Detail perm OUT-1], [501].[Loan perm in1],
[501].[loan perm out1], [501].[PA/L1], [Perm Access1]-[Perm Separated1];
I need a column that will take [Asg Actual] and add the prior day's value
to the current day's. =[Prior Day's Asg Actual]+[Access1]-[Separated1]
Is that more help?
Sum([501].SCHD1) AS SumOfSCHD1, Sum([501].[UN-1]) AS [SumOfUN-1],
[501].[Detail Perm IN+1], [501].[Detail perm OUT-1], [501].[Loan perm in1],
[501].[loan perm out1], Sum([501].PLWOP1) AS SumOfPLWOP1, Sum([501].[PS/L1])
AS [SumOfPS/L1], [501].[PA/L1], Sum([501].PTRG1) AS SumOfPTRG1,
Sum([501].POTHER1) AS SumOfPOTHER1, Sum([501].PEXTLWOP1) AS SumOfPEXTLWOP1,
Sum([501].FMLA1) AS SumOfFMLA1, [Perm Access1]-[Perm Separated1] AS [Asg
Actual]
FROM 501
GROUP BY [501].Date, [501].[Perm Access1], [501].[Perm Separated1],
[501].[Detail Perm IN+1], [501].[Detail perm OUT-1], [501].[Loan perm in1],
[501].[loan perm out1], [501].[PA/L1], [Perm Access1]-[Perm Separated1];
I need a column that will take [Asg Actual] and add the prior day's value
to the current day's. =[Prior Day's Asg Actual]+[Access1]-[Separated1]
Is that more help?
Tom Ellison said:Dear Brooke:
What we have here is probably a failure to communicate. Sorry.
Please create a query that does what you want, except for the
column(s) you want to see from the "prior" record. Sort the records
by as many columns as is necessary to create a unique sort (per my
earlier explanation). Choose the SQL view of this query, and paste
the code into your next post. Specify which columns from the "prior"
record you want to see. I'll try to take it from there.
OK?
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Tom,
I'm not sure if this is what you want, but here's my Query:
[Date], [Access], [Seperate]. [Access] and [Seperate] would be entered by
the user on a form. Since the values are dependent on the prior day's.
I want to have two expressions (or just one if I could figure that out) that
give you the initial and ending values for the day. The values that I want
are Current day's Actual =Prior day's Actual +[Access]-[Seperate].
I hope that makes sense.
Brooke
fromTom Ellison said:Dear Brooke:
I suggest you put together a query as I requested, and post it. That
gives me something from which I can start. Otherwise, I can only give
you theory about how to do it. I believe you'll catch on better if I
build one for you.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Tue, 23 Mar 2004 11:43:35 -0700, "Brooke Williams"
My primary key is the date. There is a total field [end]. I would like to
use [end] from 3/23 to figure out 3/24's data, and so on.
Thank you.
Dear Brooke:
The rows in a table or query are not in any particular order, except
if you impose an order on them. So, the term "prior" has only the
meaning you place on it.
If you have a set of columns in a table or report that puts then into
an order, then the term "prior" has meaning in the sense of that
order.
One thing to watch for is that not all "orderings" are unique. If the
data are not unique for the set of columns on which you order it, they
will still appear on the screen or on a report in a specific order,
but actually those rows which have the identical values for the
ordering columns are shown in an arbitrary order that is not available
to you when you are programming. This means that, for an "ambiguous
ordering" there is not always a specific "prior" row of data.
Rather, there is a "tie" for which row is the "prior" row in this
case, and a query cannot resolve this "tie" to show you one specific
row as the "prior" row. You may need to add some arbitrary column to
the ordering if you have one that is guaranteed to "break the tie."
Now the way to find the "prior" row of data (once you've solved the
ambiguity problem) is called a correlated subquery.
If you would provide a query that shows everything except the
column(s) in which you want the value(s) from the prior row, making
sure it is uniquely ordered, and tell me which value(s) from the prior
row you want to see, I can give you a sample of how you can do this.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Tue, 23 Mar 2004 08:31:08 -0800, Brooke
I am trying to write an expression for a query that uses a field
theprior record, but I'm not sure how to tell it that I want the prior record,
not the current one. Any suggestions?
T
Tom Ellison
Dear Brooke:
Well, you didn't make it simple, eh?
When a Jet query is saved, Access adds a bunch of non-essential stuff,
which I'll omit for the sake of brevity. Access will add it back
anyway.
First, since there is only one table, I omit it from all columns.
SELECT Date, [Perm Access1], [Perm Separated1],
Sum(SCHD1) AS SumOfSCHD1, Sum([UN-1]) AS [SumOfUN-1],
[Detail Perm IN+1], [Detail perm OUT-1], [Loan perm in1],
[loan perm out1], Sum(PLWOP1) AS SumOfPLWOP1,
Sum([PS/L1]) AS [SumOfPS/L1], [PA/L1], Sum(PTRG1) AS SumOfPTRG1,
Sum(POTHER1) AS SumOfPOTHER1, Sum(PEXTLWOP1) AS SumOfPEXTLWOP1,
Sum(FMLA1) AS SumOfFMLA1,
[Perm Access1] - [Perm Separated1] AS [Asg Actual],
(SELECT [Asg Actual] FROM 501 T1
WHERE T1.Date = (SELECT MAX(Date) FROM 501 T2
WHERE T2.Date < T.Date)) + [Access1] - [Separated1]
AS YourNewColumn
FROM 501 T
GROUP BY Date, [Perm Access1], [Perm Separated1],
[Detail Perm IN+1], [501].[Detail perm OUT-1], [Loan perm in1],
[loan perm out1], [PA/L1], [Perm Access1]-[Perm Separated1];
ORDER BY Date
This is getting sufficiently complex that I'm not confident Jet is
going to handle it, but give it a try.
For it to have any chance of working, the Date column must be unique.
There's a boundary condition here for the first recorded Date, which
would give a null value. Adding an Nz will give an answer as though
the prior day's [Asg Actual] were zero:
SELECT Date, [Perm Access1], [Perm Separated1],
Sum(SCHD1) AS SumOfSCHD1, Sum([UN-1]) AS [SumOfUN-1],
[Detail Perm IN+1], [Detail perm OUT-1], [Loan perm in1],
[loan perm out1], Sum(PLWOP1) AS SumOfPLWOP1,
Sum([PS/L1]) AS [SumOfPS/L1], [PA/L1], Sum(PTRG1) AS SumOfPTRG1,
Sum(POTHER1) AS SumOfPOTHER1, Sum(PEXTLWOP1) AS SumOfPEXTLWOP1,
Sum(FMLA1) AS SumOfFMLA1,
[Perm Access1] - [Perm Separated1] AS [Asg Actual],
Nz((SELECT [Asg Actual] FROM 501 T1
WHERE T1.Date = (SELECT MAX(Date) FROM 501 T2
WHERE T2.Date < T.Date)), 0) + [Access1] - [Separated1]
AS YourNewColumn
FROM 501 T
GROUP BY Date, [Perm Access1], [Perm Separated1],
[Detail Perm IN+1], [501].[Detail perm OUT-1], [Loan perm in1],
[loan perm out1], [PA/L1], [Perm Access1]-[Perm Separated1];
ORDER BY Date
A variation is possible if there are no skipped dates (and assuming
Date is a date/time column contain only dates and no times) where the
prior day is always the current day less one:
SELECT Date, [Perm Access1], [Perm Separated1],
Sum(SCHD1) AS SumOfSCHD1, Sum([UN-1]) AS [SumOfUN-1],
[Detail Perm IN+1], [Detail perm OUT-1], [Loan perm in1],
[loan perm out1], Sum(PLWOP1) AS SumOfPLWOP1,
Sum([PS/L1]) AS [SumOfPS/L1], [PA/L1], Sum(PTRG1) AS SumOfPTRG1,
Sum(POTHER1) AS SumOfPOTHER1, Sum(PEXTLWOP1) AS SumOfPEXTLWOP1,
Sum(FMLA1) AS SumOfFMLA1,
[Perm Access1] - [Perm Separated1] AS [Asg Actual],
(SELECT [Asg Actual] FROM 501 T1
WHERE T1.Date = T.Date - 1) + [Access1] - [Separated1]
AS YourNewColumn
FROM 501 T
GROUP BY Date, [Perm Access1], [Perm Separated1],
[Detail Perm IN+1], [501].[Detail perm OUT-1], [Loan perm in1],
[loan perm out1], [PA/L1], [Perm Access1]-[Perm Separated1];
ORDER BY Date
That's simpler, faster, and more likely to work with Jet. But if you
skip Sunday or some such, then it's not going to work.
You could use the Nz for the boundary condition here also, but I
prefer not to do so at first. I'd rather see NULLs whenever there is
a skipped date for initial testing. Otherwise you would have
incorrect calculations whenever there are skipped dates. Remember,
this alternative method assumes there are no skipped dates, so it's
better if a skipped date results in no value in the new column than an
incorrect value. That make it easier to spot.
So, does this make for some progress? Does any of this work out?
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Well, you didn't make it simple, eh?
When a Jet query is saved, Access adds a bunch of non-essential stuff,
which I'll omit for the sake of brevity. Access will add it back
anyway.
First, since there is only one table, I omit it from all columns.
SELECT Date, [Perm Access1], [Perm Separated1],
Sum(SCHD1) AS SumOfSCHD1, Sum([UN-1]) AS [SumOfUN-1],
[Detail Perm IN+1], [Detail perm OUT-1], [Loan perm in1],
[loan perm out1], Sum(PLWOP1) AS SumOfPLWOP1,
Sum([PS/L1]) AS [SumOfPS/L1], [PA/L1], Sum(PTRG1) AS SumOfPTRG1,
Sum(POTHER1) AS SumOfPOTHER1, Sum(PEXTLWOP1) AS SumOfPEXTLWOP1,
Sum(FMLA1) AS SumOfFMLA1,
[Perm Access1] - [Perm Separated1] AS [Asg Actual],
(SELECT [Asg Actual] FROM 501 T1
WHERE T1.Date = (SELECT MAX(Date) FROM 501 T2
WHERE T2.Date < T.Date)) + [Access1] - [Separated1]
AS YourNewColumn
FROM 501 T
GROUP BY Date, [Perm Access1], [Perm Separated1],
[Detail Perm IN+1], [501].[Detail perm OUT-1], [Loan perm in1],
[loan perm out1], [PA/L1], [Perm Access1]-[Perm Separated1];
ORDER BY Date
This is getting sufficiently complex that I'm not confident Jet is
going to handle it, but give it a try.
For it to have any chance of working, the Date column must be unique.
There's a boundary condition here for the first recorded Date, which
would give a null value. Adding an Nz will give an answer as though
the prior day's [Asg Actual] were zero:
SELECT Date, [Perm Access1], [Perm Separated1],
Sum(SCHD1) AS SumOfSCHD1, Sum([UN-1]) AS [SumOfUN-1],
[Detail Perm IN+1], [Detail perm OUT-1], [Loan perm in1],
[loan perm out1], Sum(PLWOP1) AS SumOfPLWOP1,
Sum([PS/L1]) AS [SumOfPS/L1], [PA/L1], Sum(PTRG1) AS SumOfPTRG1,
Sum(POTHER1) AS SumOfPOTHER1, Sum(PEXTLWOP1) AS SumOfPEXTLWOP1,
Sum(FMLA1) AS SumOfFMLA1,
[Perm Access1] - [Perm Separated1] AS [Asg Actual],
Nz((SELECT [Asg Actual] FROM 501 T1
WHERE T1.Date = (SELECT MAX(Date) FROM 501 T2
WHERE T2.Date < T.Date)), 0) + [Access1] - [Separated1]
AS YourNewColumn
FROM 501 T
GROUP BY Date, [Perm Access1], [Perm Separated1],
[Detail Perm IN+1], [501].[Detail perm OUT-1], [Loan perm in1],
[loan perm out1], [PA/L1], [Perm Access1]-[Perm Separated1];
ORDER BY Date
A variation is possible if there are no skipped dates (and assuming
Date is a date/time column contain only dates and no times) where the
prior day is always the current day less one:
SELECT Date, [Perm Access1], [Perm Separated1],
Sum(SCHD1) AS SumOfSCHD1, Sum([UN-1]) AS [SumOfUN-1],
[Detail Perm IN+1], [Detail perm OUT-1], [Loan perm in1],
[loan perm out1], Sum(PLWOP1) AS SumOfPLWOP1,
Sum([PS/L1]) AS [SumOfPS/L1], [PA/L1], Sum(PTRG1) AS SumOfPTRG1,
Sum(POTHER1) AS SumOfPOTHER1, Sum(PEXTLWOP1) AS SumOfPEXTLWOP1,
Sum(FMLA1) AS SumOfFMLA1,
[Perm Access1] - [Perm Separated1] AS [Asg Actual],
(SELECT [Asg Actual] FROM 501 T1
WHERE T1.Date = T.Date - 1) + [Access1] - [Separated1]
AS YourNewColumn
FROM 501 T
GROUP BY Date, [Perm Access1], [Perm Separated1],
[Detail Perm IN+1], [501].[Detail perm OUT-1], [Loan perm in1],
[loan perm out1], [PA/L1], [Perm Access1]-[Perm Separated1];
ORDER BY Date
That's simpler, faster, and more likely to work with Jet. But if you
skip Sunday or some such, then it's not going to work.
You could use the Nz for the boundary condition here also, but I
prefer not to do so at first. I'd rather see NULLs whenever there is
a skipped date for initial testing. Otherwise you would have
incorrect calculations whenever there are skipped dates. Remember,
this alternative method assumes there are no skipped dates, so it's
better if a skipped date results in no value in the new column than an
incorrect value. That make it easier to spot.
So, does this make for some progress? Does any of this work out?
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
SELECT [501].Date, [501].[Perm Access1], [501].[Perm Separated1],
Sum([501].SCHD1) AS SumOfSCHD1, Sum([501].[UN-1]) AS [SumOfUN-1],
[501].[Detail Perm IN+1], [501].[Detail perm OUT-1], [501].[Loan perm in1],
[501].[loan perm out1], Sum([501].PLWOP1) AS SumOfPLWOP1, Sum([501].[PS/L1])
AS [SumOfPS/L1], [501].[PA/L1], Sum([501].PTRG1) AS SumOfPTRG1,
Sum([501].POTHER1) AS SumOfPOTHER1, Sum([501].PEXTLWOP1) AS SumOfPEXTLWOP1,
Sum([501].FMLA1) AS SumOfFMLA1, [Perm Access1]-[Perm Separated1] AS [Asg
Actual]
FROM 501
GROUP BY [501].Date, [501].[Perm Access1], [501].[Perm Separated1],
[501].[Detail Perm IN+1], [501].[Detail perm OUT-1], [501].[Loan perm in1],
[501].[loan perm out1], [501].[PA/L1], [Perm Access1]-[Perm Separated1];
I need a column that will take [Asg Actual] and add the prior day's value
to the current day's. =[Prior Day's Asg Actual]+[Access1]-[Separated1]
Is that more help?
Tom Ellison said:Dear Brooke:
What we have here is probably a failure to communicate. Sorry.
Please create a query that does what you want, except for the
column(s) you want to see from the "prior" record. Sort the records
by as many columns as is necessary to create a unique sort (per my
earlier explanation). Choose the SQL view of this query, and paste
the code into your next post. Specify which columns from the "prior"
record you want to see. I'll try to take it from there.
OK?
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
Tom,
I'm not sure if this is what you want, but here's my Query:
[Date], [Access], [Seperate]. [Access] and [Seperate] would be entered by
the user on a form. Since the values are dependent on the prior day's.
I want to have two expressions (or just one if I could figure that out) that
give you the initial and ending values for the day. The values that I want
are Current day's Actual =Prior day's Actual +[Access]-[Seperate].
I hope that makes sense.
Brooke
Dear Brooke:
I suggest you put together a query as I requested, and post it. That
gives me something from which I can start. Otherwise, I can only give
you theory about how to do it. I believe you'll catch on better if I
build one for you.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Tue, 23 Mar 2004 11:43:35 -0700, "Brooke Williams"
My primary key is the date. There is a total field [end]. I would like to
use [end] from 3/23 to figure out 3/24's data, and so on.
Thank you.
Dear Brooke:
The rows in a table or query are not in any particular order, except
if you impose an order on them. So, the term "prior" has only the
meaning you place on it.
If you have a set of columns in a table or report that puts then into
an order, then the term "prior" has meaning in the sense of that
order.
One thing to watch for is that not all "orderings" are unique. If the
data are not unique for the set of columns on which you order it, they
will still appear on the screen or on a report in a specific order,
but actually those rows which have the identical values for the
ordering columns are shown in an arbitrary order that is not available
to you when you are programming. This means that, for an "ambiguous
ordering" there is not always a specific "prior" row of data.
Rather, there is a "tie" for which row is the "prior" row in this
case, and a query cannot resolve this "tie" to show you one specific
row as the "prior" row. You may need to add some arbitrary column to
the ordering if you have one that is guaranteed to "break the tie."
Now the way to find the "prior" row of data (once you've solved the
ambiguity problem) is called a correlated subquery.
If you would provide a query that shows everything except the
column(s) in which you want the value(s) from the prior row, making
sure it is uniquely ordered, and tell me which value(s) from the prior
row you want to see, I can give you a sample of how you can do this.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Tue, 23 Mar 2004 08:31:08 -0800, Brooke
I am trying to write an expression for a query that uses a field from
the
prior record, but I'm not sure how to tell it that I want the prior
record,
not the current one. Any suggestions?