Expression / SQL Statement Help

[nottuh]

I have been trying to write a statement or expression that would calculate
the DSO CHG; the difference between current month DSO to previous month DSO.
For example, the DSO Change for 06/01/08 would state -23.72 [-(76.67-52.95)]
and 05/01/08 would state 22.25 [-(52.95-75.20)] …. 01/01/08 would be null, as
12/01/07 DSO is not available; therefore, I would like to see "NA" placed in
the field verses 0 - as this would be miss leading to the users.

PROD MO DSO DSO CHG
06/01/2008 76.67 -23.72
05/01/2008 52.95 22.25
04/01/2008 75.2 -35.58
03/01/2008 39.62 41.69
02/01/2008 81.31 30.23
01/01/2008 111.54 NA

 

[Michael Gramelspacher]

I have been trying to write a statement or expression that would calculate
the DSO CHG; the difference between current month DSO to previous month DSO.
For example, the DSO Change for 06/01/08 would state -23.72 [-(76.67-52.95)]
and 05/01/08 would state 22.25 [-(52.95-75.20)] …. 01/01/08 would be null, as
12/01/07 DSO is not available; therefore, I would like to see "NA" placed in
the field verses 0 - as this would be miss leading to the users.

PROD MO DSO DSO CHG
06/01/2008 76.67 -23.72
05/01/2008 52.95 22.25
04/01/2008 75.2 -35.58
03/01/2008 39.62 41.69
02/01/2008 81.31 30.23
01/01/2008 111.54 NA

see if this works:

SELECT a.[Prod Mo],
a.DSO,
NZ((SELECT a1.DSO
FROM DSO AS a1
WHERE a1.[prod mo] = (SELECT MAX(a2.[prod mo])
FROM DSO AS a2
WHERE a2.[prod mo] < a.[prod mo])) -
[a].[DSO],
"NA") AS [DSO CHG]
FROM DSO AS a
ORDER BY a.[Prod Mo] DESC;

 

[nottuh]

--
T NOTTUH

I have been trying to write a statement or expression that would calculate
the DSO CHG; the difference between current month DSO to previous month DSO.
For example, the DSO Change for 06/01/08 would state -23.72 [-(76.67-52.95)]
and 05/01/08 would state 22.25 [-(52.95-75.20)] …. 01/01/08 would be null, as
12/01/07 DSO is not available; therefore, I would like to see "NA" placed in
the field verses 0 - as this would be miss leading to the users.

PROD MO DSO DSO CHG
06/01/2008 76.67 -23.72
05/01/2008 52.95 22.25
04/01/2008 75.2 -35.58
03/01/2008 39.62 41.69
02/01/2008 81.31 30.23
01/01/2008 111.54 NA

see if this works:

SELECT a.[Prod Mo],
a.DSO,
NZ((SELECT a1.DSO
FROM DSO AS a1
WHERE a1.[prod mo] = (SELECT MAX(a2.[prod mo])
FROM DSO AS a2
WHERE a2.[prod mo] < a.[prod mo])) -
[a].[DSO],
"NA") AS [DSO CHG]
FROM DSO AS a
ORDER BY a.[Prod Mo] DESC;

Thanks Michael, I been trying your suggestion but realize I need more help
as ACCESS is not my strength. What's confusing me is the "a1" and "a2"
language in your suggestion. Do these represenet additional tables? If I
give your the actual table name could you rewrite the SELECT statement
provided. I should also add that the table is sorted by a field called "RPT
RAC" there is about 900 accounts in total, in which each account is repeated
to the number of PROD MO's are reported - does that make sense?

RPT RAC PROD MO DSO DSO CHG

1101 6/01/2008 76.67 -23.72
1101 5/01/2008 52.95 22.25
1101 4/01/2008 75.2 -35.58
1101 3/01/2008 39.62 41.69
1101 2/01/2008 81.31 30.23
1101 1/01/2008 111.54 NA
1288 ( next account) and so forth....

Table: tbl CRR SUMMARY

Thanks, Tricia

 

[nottuh]

--
T NOTTUH

--
T NOTTUH

I have been trying to write a statement or expression that would calculate
the DSO CHG; the difference between current month DSO to previous month DSO.
For example, the DSO Change for 06/01/08 would state -23.72 [-(76.67-52.95)]
and 05/01/08 would state 22.25 [-(52.95-75.20)] …. 01/01/08 would be null, as
12/01/07 DSO is not available; therefore, I would like to see "NA" placed in
the field verses 0 - as this would be miss leading to the users.

PROD MO DSO DSO CHG
06/01/2008 76.67 -23.72
05/01/2008 52.95 22.25
04/01/2008 75.2 -35.58
03/01/2008 39.62 41.69
02/01/2008 81.31 30.23
01/01/2008 111.54 NA

see if this works:

SELECT a.[Prod Mo],
a.DSO,
NZ((SELECT a1.DSO
FROM DSO AS a1
WHERE a1.[prod mo] = (SELECT MAX(a2.[prod mo])
FROM DSO AS a2
WHERE a2.[prod mo] < a.[prod mo])) -
[a].[DSO],
"NA") AS [DSO CHG]
FROM DSO AS a
ORDER BY a.[Prod Mo] DESC;

Thanks Michael, I been trying your suggestion but realize I need more help
as ACCESS is not my strength. What's confusing me is the "a1" and "a2"
language in your suggestion. Do these represenet additional tables? If I
give your the actual table name could you rewrite the SELECT statement
provided. I should also add that the table is sorted by a field called "RPT
RAC" there is about 900 accounts in total, in which each account is repeated
to the number of PROD MO's are reported - does that make sense?
Table: tbl CRR SUMMARY
RPT RAC PROD MO DSO DSO CHG

1101 6/01/2008 76.67 -23.72
1101 5/01/2008 52.95 22.25
1101 4/01/2008 75.2 -35.58
1101 3/01/2008 39.62 41.69
1101 2/01/2008 81.31 30.23
1101 1/01/2008 111.54 NA
1288 ( next account) and so forth....

Here is my SQL statement which I'm getting the following error message:
"At most one record can be returned by this subquery."

SELECT [tbl CRR SUMMARY].[RPT RAC], [tbl CRR SUMMARY].[PROD MO], [tbl CRR
SUMMARY].[DSO],
NZ((SELECT a1.DSO FROM [tbl CRR SUMMARY] AS a1
WHERE a1.[PROD MO] = (SELECT MAX(a2.[prod mo])
FROM [tbl CRR SUMMARY] AS a2
WHERE a2.[prod mo] < [tbl CRR SUMMARY].[prod mo]))-[tbl CRR
SUMMARY].[DSO],"NA") AS [DSO CHG]
FROM [tbl CRR SUMMARY]
ORDER BY [tbl CRR SUMMARY].[Prod Mo] DESC;

 

[Lynn Trapp]

SELECT [tbl CRR SUMMARY].[RPT RAC], [tbl CRR SUMMARY].[PROD MO], [tbl CRR
SUMMARY].[DSO],
NZ((SELECT a1.DSO FROM [tbl CRR SUMMARY] AS a1
WHERE a1.[PROD MO] = (SELECT MAX(a2.[prod mo])
FROM [tbl CRR SUMMARY] AS a2
WHERE a2.[prod mo] < [tbl CRR SUMMARY].[prod mo]))-[tbl CRR
SUMMARY].[DSO],"NA") AS [DSO CHG]
FROM [tbl CRR SUMMARY]
ORDER BY [tbl CRR SUMMARY].[Prod Mo] DESC;

When you use a subquery in the field list of a query, it must return
only one record. Try running this part of your query to verify that
you are returning too many records

NZ((SELECT a1.DSO FROM [tbl CRR SUMMARY] AS a1
WHERE a1.[PROD MO] = (SELECT MAX(a2.[prod mo])
FROM [tbl CRR SUMMARY] AS a2
WHERE a2.[prod mo] < [tbl CRR SUMMARY].[prod mo]))-[tbl CRR
SUMMARY].[DSO],"NA") AS [DSO CHG]

Lynn Trapp
www.ltcomputerdesigns.com

 

[Michael Gramelspacher]

Thanks Michael, I been trying your suggestion but realize I need more help
as ACCESS is not my strength. What's confusing me is the "a1" and "a2"
language in your suggestion. Do these represenet additional tables? If I
give your the actual table name could you rewrite the SELECT statement
provided. I should also add that the table is sorted by a field called "RPT
RAC" there is about 900 accounts in total, in which each account is repeated
to the number of PROD MO's are reported - does that make sense?

RPT RAC PROD MO DSO DSO CHG

The a, a1, a2 are aliases for the table DSO. DSO is used three times. Aliases
can make a query much easier to read and understand.

Notice that by making DSO CHG appear as 'NA' when 0, it shifts the column
alignment to the left, which does not look nice in the query display.

Try this:

SELECT a.[RPT RAC],
a.[PROD MO],
a.DSO,
IIF(a.DSO - a1.DSO = 0,"NA",a.DSO - a1.DSO) AS [DSO CHG]
FROM DSO AS a
LEFT JOIN DSO AS a1
ON a.[RPT RAC] = a1.[RPT RAC]
WHERE (((a1.[PROD MO]) = NZ((SELECT MAX(a2.[PROD MO])
FROM DSO AS a2
WHERE a2.[RPT RAC] = a.[RPT RAC]
AND a2.[PROD MO] < a.[PROD MO]),[a].[PRODMO])))
ORDER BY a.[RPT RAC],
a.[PROD MO] DESC;

 

[nottuh]

How do you run part of a query?
--
T NOTTUH

SELECT [tbl CRR SUMMARY].[RPT RAC], [tbl CRR SUMMARY].[PROD MO], [tbl CRR
SUMMARY].[DSO],
NZ((SELECT a1.DSO FROM [tbl CRR SUMMARY] AS a1
WHERE a1.[PROD MO] = (SELECT MAX(a2.[prod mo])
FROM [tbl CRR SUMMARY] AS a2
WHERE a2.[prod mo] < [tbl CRR SUMMARY].[prod mo]))-[tbl CRR
SUMMARY].[DSO],"NA") AS [DSO CHG]
FROM [tbl CRR SUMMARY]
ORDER BY [tbl CRR SUMMARY].[Prod Mo] DESC;

When you use a subquery in the field list of a query, it must return
only one record. Try running this part of your query to verify that
you are returning too many records

NZ((SELECT a1.DSO FROM [tbl CRR SUMMARY] AS a1
WHERE a1.[PROD MO] = (SELECT MAX(a2.[prod mo])
FROM [tbl CRR SUMMARY] AS a2
WHERE a2.[prod mo] < [tbl CRR SUMMARY].[prod mo]))-[tbl CRR
SUMMARY].[DSO],"NA") AS [DSO CHG]

Lynn Trapp
www.ltcomputerdesigns.com

 

[nottuh]

IT WORKED! THANK YOU SO MUCH FOR YOUR TIME AND EXPERTISE!
--
T NOTTUH

Thanks Michael, I been trying your suggestion but realize I need more help
as ACCESS is not my strength. What's confusing me is the "a1" and "a2"
language in your suggestion. Do these represenet additional tables? If I
give your the actual table name could you rewrite the SELECT statement
provided. I should also add that the table is sorted by a field called "RPT
RAC" there is about 900 accounts in total, in which each account is repeated
to the number of PROD MO's are reported - does that make sense?

RPT RAC PROD MO DSO DSO CHG

The a, a1, a2 are aliases for the table DSO. DSO is used three times. Aliases
can make a query much easier to read and understand.

Notice that by making DSO CHG appear as 'NA' when 0, it shifts the column
alignment to the left, which does not look nice in the query display.

Try this:

SELECT a.[RPT RAC],
a.[PROD MO],
a.DSO,
IIF(a.DSO - a1.DSO = 0,"NA",a.DSO - a1.DSO) AS [DSO CHG]
FROM DSO AS a
LEFT JOIN DSO AS a1
ON a.[RPT RAC] = a1.[RPT RAC]
WHERE (((a1.[PROD MO]) = NZ((SELECT MAX(a2.[PROD MO])
FROM DSO AS a2
WHERE a2.[RPT RAC] = a.[RPT RAC]
AND a2.[PROD MO] < a.[PROD MO]),[a].[PRODMO])))
ORDER BY a.[RPT RAC],
a.[PROD MO] DESC;

 

[Michael Gramelspacher]

IT WORKED! THANK YOU SO MUCH FOR YOUR TIME AND EXPERTISE!

I see that I overlooked your message line giving your real table name, but since
it worked, I gather you corrected the table names.

The fist query could be made to work easily enough, but the second may be
faster.

All the best.

 

[Lynn Trapp]

How do you run part of a query?

Open your query. Copy the portion of the query that is the subquery.
Past it into the SQL view of a new query, then run it from there.

Lynn Trapp
www.ltcomputerdesigns.com