Sum Query with 2 Sums on One Line

[BarryC]

I'm trying to write a query something like this: "Sum(Pcs)as Pcs1 where
Date <=[Begin Date], Sum(Pcs)as Pcs2 where Date<=[Begin Date]-1" so
that both Sums appear in One result record.

I have the Select part working as a UNION query, but I don't know how
to merge the 2 lines. Any ideas?

 

[Bob Waggoner]

Barry,

To get both sums in a single query, try something like the following:

PARAMETERS BeginDate Date;
SELECT Sum(Pcs) AS Pcs1, (SELECT Sum(Pcs) FROM Items WHERE Items.[Date] <=
[BeginDate]-1 ) AS Pcs2
FROM Items
WHERE Items.[Date] <= [BeginDate];

Bob

 

[MGFoster]

I'm trying to write a query something like this: "Sum(Pcs)as Pcs1 where
Date <=[Begin Date], Sum(Pcs)as Pcs2 where Date<=[Begin Date]-1" so
that both Sums appear in One result record.

I have the Select part working as a UNION query, but I don't know how
to merge the 2 lines. Any ideas?

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

The easy way is like this:

SELECT SUM(IIf(Date<=[Begin Date], Pcs,0)) As Pcs1,
SUM(IIf(Date<=[Begin Date]-1, Pcs, 0)) As Pcs2
FROM ...

To merge 2 Union queries I usually do this:

SELECT SUM(Pcs_1) As Pcs1, SUM(Pcs_2) As Pcs2
FROM (
SELECT Pcs As Pcs_1, 0 As Pcs_2
FROM <table>
WHERE [Begin Date] >= Date()

UNION ALL

SELECT 0, Pcs
FROM <table>
WHERE [Begin Date] >= Date() + 1
) AS A

For more efficient WHERE clauses put the constant (Date) on the right
side and the column name on the left side of the comparison operator.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQ81xr4echKqOuFEgEQKDeQCeLFN1Enee3JoMOCIZTNMwiq632ocAnic1
nj0vPFwddWeMW6+0qGI/Mlts
=i3oe
-----END PGP SIGNATURE-----