Combining and streamlining multiple queries SQL included

[Tony in Michigan]

Please see the below SQL.

I have a field that is stored as two decimal data, which I validate against
zero decimal expected values. The expected value is rounded prior to
summing. To calculate my field value for validation takes me three steps,
plus sorting to remove the invalid, <>0 values, for investigation.

I'd like to combine/reduce/eliminate some of the steps, perhaps go directly
from the source tables, directly to a results output table, without the
interim tables.

Step 1

;;;;; Rounds values to 0 decimal places

UPDATE [RECON ENTRY] SET [RECON ENTRY].round_EV = Round([RECON
ENTRY]![HTS_VALUE],0);


Step 2
;;;;; Sums rounded values makes table to compare against true data

SELECT [RECON ENTRY].ENTRYNUM, Sum([RECON ENTRY].Round_EV) AS SumOfRound_EV
INTO [validation data]
FROM [RECON ENTRY]
GROUP BY [RECON ENTRY].ENTRYNUM;

Step 3
;;;;; Compares calculated values to expected values, makes table of results.
<>0 are invalid, and are reviewed manually.

SELECT [validation data].ENTRYNUM, [validation
data]![SumOfRound_EV]-[AAI_liq 2008]![entered value] AS Expr1 INTO
[validation results]
FROM [validation data] INNER JOIN [AAI_liq 2008] ON [validation
data].ENTRYNUM = [AAI_liq 2008].[entry number]
ORDER BY [validation data]![SumOfRound_EV]-[AAI_liq 2008]![entered value]
DESC;

 

[MGFoster]

Please see the below SQL.

I have a field that is stored as two decimal data, which I validate against
zero decimal expected values. The expected value is rounded prior to
summing. To calculate my field value for validation takes me three steps,
plus sorting to remove the invalid, <>0 values, for investigation.

I'd like to combine/reduce/eliminate some of the steps, perhaps go directly
from the source tables, directly to a results output table, without the
interim tables.

Step 1

;;;;; Rounds values to 0 decimal places

UPDATE [RECON ENTRY] SET [RECON ENTRY].round_EV = Round([RECON
ENTRY]![HTS_VALUE],0);


Step 2
;;;;; Sums rounded values makes table to compare against true data

SELECT [RECON ENTRY].ENTRYNUM, Sum([RECON ENTRY].Round_EV) AS SumOfRound_EV
INTO [validation data]
FROM [RECON ENTRY]
GROUP BY [RECON ENTRY].ENTRYNUM;

Step 3
;;;;; Compares calculated values to expected values, makes table of results.
<>0 are invalid, and are reviewed manually.

SELECT [validation data].ENTRYNUM, [validation
data]![SumOfRound_EV]-[AAI_liq 2008]![entered value] AS Expr1 INTO
[validation results]
FROM [validation data] INNER JOIN [AAI_liq 2008] ON [validation
data].ENTRYNUM = [AAI_liq 2008].[entry number]
ORDER BY [validation data]![SumOfRound_EV]-[AAI_liq 2008]![entered value]
DESC;

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

Perhaps this:

SELECT RE.EntryNum, RE.SumOfHTS - AL.[entered value] AS ValidationResult
FROM (
SELECT EntryNum, SUM(Int(HTS_VALUE)) As SumOfHTS
FROM [Recon Entry]
GROUP BY EntryNum
) As RE
INNER JOIN [AAI_liq 2008] As AL ON RE.EntryNum = AL.[entry number]
WHERE RE.SumOfHTS - AL.[entered value] <> 0

I used Int() instead of Round(x,0) - does the same thing, but I believe
it does it faster.

If you want both =0 and <>0 ValidationResults you could remove the WHERE
clause and put in an ORDER BY clause:

ORDER BY RE.SumOfHTS - AL.[entered value]

This will give you the =0 at the top of the returned results.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **


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

iQA/AwUBSgpH/4echKqOuFEgEQJmVQCghJIwhZ81k/W6jiESRAdXKc2f4yUAn1RX
iNydSouNXxcNhKXBUwdvtP0C
=hfC0
-----END PGP SIGNATURE-----

 

[MGFoster]

Please see the below SQL.

I have a field that is stored as two decimal data, which I validate
against zero decimal expected values. The expected value is rounded
prior to summing. To calculate my field value for validation takes me
three steps, plus sorting to remove the invalid, <>0 values, for
investigation.

I'd like to combine/reduce/eliminate some of the steps, perhaps go
directly from the source tables, directly to a results output table,
without the interim tables.

Step 1

;;;;; Rounds values to 0 decimal places

UPDATE [RECON ENTRY] SET [RECON ENTRY].round_EV = Round([RECON
ENTRY]![HTS_VALUE],0);


Step 2
;;;;; Sums rounded values makes table to compare against true data

SELECT [RECON ENTRY].ENTRYNUM, Sum([RECON ENTRY].Round_EV) AS
SumOfRound_EV INTO [validation data]
FROM [RECON ENTRY]
GROUP BY [RECON ENTRY].ENTRYNUM;

Step 3
;;;;; Compares calculated values to expected values, makes table of
results. <>0 are invalid, and are reviewed manually.

SELECT [validation data].ENTRYNUM, [validation
data]![SumOfRound_EV]-[AAI_liq 2008]![entered value] AS Expr1 INTO
[validation results]
FROM [validation data] INNER JOIN [AAI_liq 2008] ON [validation
data].ENTRYNUM = [AAI_liq 2008].[entry number]
ORDER BY [validation data]![SumOfRound_EV]-[AAI_liq 2008]![entered
value] DESC;

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

Perhaps this:

SELECT RE.EntryNum, RE.SumOfHTS - AL.[entered value] AS ValidationResult
FROM (
SELECT EntryNum, SUM(Int(HTS_VALUE)) As SumOfHTS
FROM [Recon Entry]
GROUP BY EntryNum
) As RE
INNER JOIN [AAI_liq 2008] As AL ON RE.EntryNum = AL.[entry number]
WHERE RE.SumOfHTS - AL.[entered value] <> 0

I used Int() instead of Round(x,0) - does the same thing, but I believe
it does it faster.

If you want both =0 and <>0 ValidationResults you could remove the WHERE
clause and put in an ORDER BY clause:

ORDER BY RE.SumOfHTS - AL.[entered value]

This will give you the =0 at the top of the returned results.

HTH,

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

Obviously I was wrong about the Int() vs. Round(). I don't know what I
was thinking. Use the Round() function.

Regrets,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBSgyfGoechKqOuFEgEQKjEwCg24S2R5NQW4tE3RCw0NEHw4H5cm0An1bV
TNLqn0087LLinoFusJkyQlLZ
=EtF8
-----END PGP SIGNATURE-----