SQL Difference Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is the data from my query:

ID Status RosterDate Number Difference
1 ACTIVE 5/15/2006 835
2 INACTIVE 5/15/2006 71 -764
3 ACTIVE 5/29/2006 852
4 INACTIVE 5/29/2006 75 4

Here is my sql:

SELECT tblStats.ID, tblStats.RosterDate, tblStats.Status, tblStats.Number,
[tblStats].[Number]-(SELECT TOP 1 Dupe.Number FROM [tblStats] AS Dupe WHERE
Dupe.ID < [tblStats].ID and [tblStats].Status='INACTIVE' Order BY
Dupe.Status DESC) AS Difference
FROM tblStats;

What I need to do is group these records by Status. I also need to calculate
the difference for te Actives. And that -764 should not be there, ir should
be zero, because there was no prior week.

Thanks in advance for your assistance.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Don't use the ID as the criteria in the subquery, use the date. To get
the same Status as the main query refer to the main querie's Status
column. E.g.:

SELECT
S.ID,
S.RosterDate,
S.Status,
S.[Number],
S.[Number]- Nz((SELECT Number
FROM [tblStats]
WHERE Status = S.Status
AND RosterDate =
(SELECT MAX(S.RosterDate)
FROM tblStats
WHERE Status = S.Status
AND RosterDate < S.RosterDate)
)) AS Difference

FROM tblStats As S

I used the Nz() function to return a zero whenever the subquery returns
a NULL value.

Since we wanted to find the row that is chronologically before the main
query's row, we had to use the 2nd subquery to get that date.

When using a reference to a main query's table, make sure it is not the
same name as the subquery's table name (use an alias [as I used "S"]
instead of "tblStats", which is the table name in both queries).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRIXeyIechKqOuFEgEQJ2dwCcDMXOa3M8FRi5R4dV9v7UaPtfZuAAoIX8
ckYEaYVq1Siz3h4ejlopf78j
=8B55
-----END PGP SIGNATURE-----
 
Thanks MGFoster,

Is there a way I can make this query work for multiple records. I'm getting
2 errors when I added another week:

At most one record can be returned by this subquery. (Error 3354)
followed by
At most one record can be returned by this subquery. (Error 3354)


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Don't use the ID as the criteria in the subquery, use the date. To get
the same Status as the main query refer to the main querie's Status
column. E.g.:

SELECT
S.ID,
S.RosterDate,
S.Status,
S.[Number],
S.[Number]- Nz((SELECT Number
FROM [tblStats]
WHERE Status = S.Status
AND RosterDate =
(SELECT MAX(S.RosterDate)
FROM tblStats
WHERE Status = S.Status
AND RosterDate < S.RosterDate)
)) AS Difference

FROM tblStats As S

I used the Nz() function to return a zero whenever the subquery returns
a NULL value.

Since we wanted to find the row that is chronologically before the main
query's row, we had to use the 2nd subquery to get that date.

When using a reference to a main query's table, make sure it is not the
same name as the subquery's table name (use an alias [as I used "S"]
instead of "tblStats", which is the table name in both queries).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRIXeyIechKqOuFEgEQJ2dwCcDMXOa3M8FRi5R4dV9v7UaPtfZuAAoIX8
ckYEaYVq1Siz3h4ejlopf78j
=8B55
-----END PGP SIGNATURE-----
Here is the data from my query:

ID Status RosterDate Number Difference
1 ACTIVE 5/15/2006 835
2 INACTIVE 5/15/2006 71 -764
3 ACTIVE 5/29/2006 852
4 INACTIVE 5/29/2006 75 4

Here is my sql:

SELECT tblStats.ID, tblStats.RosterDate, tblStats.Status, tblStats.Number,
[tblStats].[Number]-(SELECT TOP 1 Dupe.Number FROM [tblStats] AS Dupe WHERE
Dupe.ID < [tblStats].ID and [tblStats].Status='INACTIVE' Order BY
Dupe.Status DESC) AS Difference
FROM tblStats;

What I need to do is group these records by Status. I also need to calculate
the difference for te Actives. And that -764 should not be there, ir should
be zero, because there was no prior week.
Click to expand...
Click to expand...
 
Try using one of the aggregate functions to force the return of one value.
Access will require that even when you know the subquery will only return
one record. Max, Min, Avg should all work. First may also work.
Experiment a little and see what happens.

SELECT
S.ID,
S.RosterDate,
S.Status,
S.[Number],
S.[Number]- Nz((SELECT Max(Number)
FROM [tblStats]
WHERE Status = S.Status
AND RosterDate =
(SELECT MAX(S.RosterDate)
FROM tblStats
WHERE Status = S.Status
AND RosterDate < S.RosterDate)
)) AS Difference

FROM tblStats As S

Richard said:
Thanks MGFoster,

Is there a way I can make this query work for multiple records. I'm
getting
2 errors when I added another week:

At most one record can be returned by this subquery. (Error 3354)
followed by
At most one record can be returned by this subquery. (Error 3354)


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Don't use the ID as the criteria in the subquery, use the date. To get
the same Status as the main query refer to the main querie's Status
column. E.g.:

SELECT
S.ID,
S.RosterDate,
S.Status,
S.[Number],
S.[Number]- Nz((SELECT Number
FROM [tblStats]
WHERE Status = S.Status
AND RosterDate =
(SELECT MAX(S.RosterDate)
FROM tblStats
WHERE Status = S.Status
AND RosterDate < S.RosterDate)
)) AS Difference

FROM tblStats As S

I used the Nz() function to return a zero whenever the subquery returns
a NULL value.

Since we wanted to find the row that is chronologically before the main
query's row, we had to use the 2nd subquery to get that date.

When using a reference to a main query's table, make sure it is not the
same name as the subquery's table name (use an alias [as I used "S"]
instead of "tblStats", which is the table name in both queries).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRIXeyIechKqOuFEgEQJ2dwCcDMXOa3M8FRi5R4dV9v7UaPtfZuAAoIX8
ckYEaYVq1Siz3h4ejlopf78j
=8B55
-----END PGP SIGNATURE-----
Here is the data from my query:

ID Status RosterDate Number Difference
1 ACTIVE 5/15/2006 835
2 INACTIVE 5/15/2006 71 -764
3 ACTIVE 5/29/2006 852
4 INACTIVE 5/29/2006 75 4

Here is my sql:

SELECT tblStats.ID, tblStats.RosterDate, tblStats.Status,
tblStats.Number,
[tblStats].[Number]-(SELECT TOP 1 Dupe.Number FROM [tblStats] AS Dupe
WHERE
Dupe.ID < [tblStats].ID and [tblStats].Status='INACTIVE' Order BY
Dupe.Status DESC) AS Difference
FROM tblStats;

What I need to do is group these records by Status. I also need to
calculate
the difference for te Actives. And that -764 should not be there, ir
should
be zero, because there was no prior week.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top