Urgent help -VBA Query

[Pogas]

Hi I am a newbie.I have to make a query from two tables

tblPEAT
--------
ID
SiteCode
C1
C2
C3
C4
C5
C6

tblSites
---------
SiteCode
SiteName

I want to write a VBA code that will

1.Counts the all fields ie (C1,C2,C3,C4 etc) where values
are not null for a particulat site code
2.sums all fields (C1,C2,C3,C4 etc) for values >1 for a
particular site code

Any help will be very much appreciated

Thanks

 

[Tom Ellison]

Dear Pogas:

To do specifically just what you have said, you would not need
tblSites at all.

1. Count non-null columns C1, C2, C3, and C4

SELECT SiteCode, COUNT(C1) AS CtC1, COUNT(C2) AS CtC2,
COUNT(C3) AS CtC3, COUNT(C4) AS CtC4
FROM tblPEAT
GROUP BY SiteCode

2. Sum C1, C2, C3, and C4 where value is > 1 by site code:

SELECT SiteCode,
(SELECT SUM(C1) FROM tblPEAT T1
WHERE T1.SiteCode = T.SiteCode AND T1.C1 > 1) AS SumC1,
(SELECT SUM(C2) FROM tblPEAT T1
WHERE T1.SiteCode = T.SiteCode AND T1.C2 > 1) AS SumC2,
(SELECT SUM(C3) FROM tblPEAT T1
WHERE T1.SiteCode = T.SiteCode AND T1.C3 > 1) AS SumC3,
(SELECT SUM(C4) FROM tblPEAT T1
WHERE T1.SiteCode = T.SiteCode AND T1.C4 > 1) AS SumC4
FROM tblPEAT T
GROUP BY SiteCode

The complexity of this second query is brought on by the fact that you
must separately filter the rows being summed for each of the 4 sets.
If you filter all together like this:

SELECT SiteCode, SUM(C1) AS SumC1, SUM(C2) AS SumC2,
SUM(C3) AS SumC3, SUM(C4) AS SumC4
FROM tblPEAT
WHERE C1 > 1 AND C2 > 1 AND C3 > 1 AND C4 > 1
GROUP BY SiteCode

This will omit values of C1 where C1 > 1 but C2 <= 1, probably not
what you meant at all. So, the 4 sums must be filtered separately.

If you want to study the technique used above, you would look up
"correlated subquery" in your favorite query references (books,
on-line, etc.)

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts