CODEWITHSUNDEEP
sqlms-access-2007
Chasester
Chasester

Reputation: 704

Access 2007: Query that counts from several tables

I haven't used Access in probably ten years, so my apologies if this is drop dead simple. I have three tables. Table 1 - User info Table 2 - Report info Table 3 - Correction info.

Table 2 and 3 link together via table 1. (Table 2 uses Table 1's id's, and table 3 uses table 1's abbreviated name).

I need to have a query that counts all reports per user by date, and also count correction info by user by date.

Example Query:

-user-| -date- | -table 1 count- | -table 2 count-

User 1 | 08/01/2012 | 33 reports | 2 Correction

User 2 | 08/01/2012 | 4 reports | 0 Correction

User 1 | 08/02/2012 | 0 reports | 1 Correction

(the zero's mean no records were found but one was found for that date in the other table).

Here's what I have for SQL thus far.

SELECT 
    tblUsers.userfullname, Count(txtReports.reportnumber) AS CountOfreportnumber,
    Count(tblRawData.reportnum) AS CountOfRawReport
FROM (tblUsers INNER JOIN txtReports ON tblUsers.userID = txtReports.userID) 
    INNER JOIN tblRawData ON tblUsers.userabbrev = tblRawData.username  
GROUP BY tblUsers.userfullname;

But all it gives me is partial matches with the same total in each column.

any pointers?

UPDATE: Example Data: tblUsers:

userID,userfullname,userabbrev
850,Test ML,test-ml
817,Test RL,test-rl
872,Test LS,test-ls
1071,Test MF,test-mf
934,Test TF,test-tf
796,Test JK,test-jk
1073,TEST RA,test-ra
779,TEST AW,test-aw
852,TEST EP,test-ep
987,TEST MM,test-mm
938,TEST SS,test-ss
19,TEST MT,test-mt
1095,TEST JL,test-jl
780,TEST LN,test-ln
909,TEST JR,test-jr
290,TEST BY,test-by
892,TEST JC,test-jc
920,TEST TR,test-tr
894,TEST PW,test-pw
873,TEST JR,test-jr
827,TEST AC,test-ac
783,TEST RP,test-rp
829,TEST AP,test-ap

txtReports

userID,reportdate,reportnumber
850,8/13/2012,823
817,8/13/2012,835
872,8/13/2012,841
872,8/13/2012,842
872,8/13/2012,850
1071,8/13/2012,862
934,8/13/2012,863
796,8/13/2012,800
796,8/13/2012,803
1073,8/13/2012,804
1073,8/13/2012,808
1073,8/13/2012,809
1073,8/13/2012,815
1073,8/13/2012,816
796,8/13/2012,817
779,8/13/2012,818
779,8/13/2012,819
779,8/13/2012,820
779,8/13/2012,821
779,8/13/2012,822
850,8/13/2012,824
850,8/13/2012,826
850,8/13/2012,827
852,8/13/2012,829
852,8/13/2012,830
850,8/13/2012,831
817,8/13/2012,832
817,8/13/2012,833
987,8/13/2012,834
1095,8/13/2012,836
850,8/13/2012,837
872,8/13/2012,839
852,8/13/2012,840
987,8/13/2012,843
852,8/13/2012,845
852,8/13/2012,846
850,8/13/2012,847
817,8/13/2012,848
938,8/13/2012,849
817,8/13/2012,851
850,8/13/2012,853
817,8/13/2012,854
19,8/13/2012,855
780,8/13/2012,856
780,8/13/2012,857
909,8/13/2012,858
1071,8/13/2012,859
19,8/13/2012,860
909,8/13/2012,861
290,8/13/2012,838
892,8/13/2012,844
987,8/13/2012,852
892,8/14/2012,864
872,8/14/2012,866
892,8/14/2012,869
872,8/14/2012,870
872,8/14/2012,871
872,8/14/2012,872
934,8/14/2012,880
850,8/14/2012,865
850,8/14/2012,867
850,8/14/2012,868
1095,8/14/2012,873
850,8/14/2012,874
817,8/14/2012,875
850,8/14/2012,876
1095,8/14/2012,877
852,8/14/2012,881
852,8/14/2012,882
1095,8/14/2012,885
920,8/14/2012,919
920,8/14/2012,922
19,8/14/2012,879
872,8/14/2012,878
894,8/15/2012,884
873,8/15/2012,886
827,8/15/2012,889
796,8/15/2012,891
796,8/15/2012,892
796,8/15/2012,893
852,8/15/2012,883
852,8/15/2012,887
852,8/15/2012,888
873,8/15/2012,890
783,8/15/2012,895
783,8/15/2012,896
852,8/15/2012,902
808,8/15/2012,904
872,8/15/2012,914
872,8/15/2012,915

tblRawData

reportnum,username,reportdate
832,test-rl,08/13/12
837,test-mf,08/13/12
797,test-jk,08/13/12
811,test-ra,08/13/12
838,test-by,08/13/12
739,test-ra,08/13/12
817,test-jk,08/13/12
818,test-aw,08/13/12
819,test-aw,08/13/12
820,test-aw,08/13/12
821,test-aw,08/13/12
822,test-aw,08/13/12
852,test-mm,08/14/12
857,test-ln,08/14/12
753,test-ap,08/14/12
868,test-mf,08/14/12
745,test-ra,08/14/12
765,test-ra,08/14/12
748,test-ra,08/14/12
766,test-ra,08/14/12
794,test-ra,08/14/12
807,test-ra,08/14/12
812,test-ra,08/14/12
814,test-ra,08/14/12
815,test-ra,08/14/12
767,test-ra,08/14/12
768,test-ra,08/14/12
804,test-ra,08/14/12
873,test-jl,08/14/12
875,test-rl,08/14/12
837,test-mf,08/15/12
881,test-ep,08/15/12
883,test-ep,08/15/12
498,test-jr,08/15/12
887,test-ep,08/15/12
890,test-jr,08/15/12

Update with the addition of filtering by date.

SELECT u.userfullname, nz(rd.rawcount,0) AS rawcount, nz(rp.rptcount,0) AS rptcount
FROM (tblUsers AS u LEFT JOIN (SELECT username, reportdate, count(reportnum) AS rawcount FROM tblRawData GROUP BY username, reportdate)  AS rd ON u.userabbrev = rd.username) LEFT JOIN (SELECT userID, reportdate, count(reportnumber) AS rptcount FROM txtReports GROUP BY userID, reportdate)  AS rp ON u.userID = rp.userID
WHERE (((rd.reportdate)>=[start date] And (rd.reportdate)<=[end date])) OR (((rp.reportdate)>=[start date] And (rp.reportdate)<=[end date]));

Upvotes: 0

Views: 977

Answers (2)

Omnikrys
Omnikrys

Reputation: 2558

You need to get the counts for each table then join them and display them or you will get an aggregating mess and none of your counts will be right.

SELECT u.userfullname, nz(rd.rawcount,0) AS rawcount, nz(rp.rptcount,0) AS rptcount 
FROM (tblUser AS u 
    LEFT JOIN (SELECT username, count(reportnum) AS rawcount FROM tblRawData GROUP BY username) AS rd 
    ON u.userabbrev = rd.username) 
    LEFT JOIN (SELECT userID, count(reportnumber) AS rptcount FROM txtReports GROUP BY userID) AS rp 
    ON u.userID = rp.userID
WHERE (Not p.Payments Is Null) OR (Not n.Notes Is Null);

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91356

Try:

SELECT tblUsers.userfullname, tr.CountReport, td.CountRaw
FROM (tblUsers 
LEFT JOIN (
    SELECT r.userID, Count(r.reportnumber) AS CountReport
    FROM txtReports r
    GROUP BY r.userID)  AS tr 
ON tblUsers.userID = tr.userID) 
LEFT JOIN (
    SELECT tblRawData.username, Count(tblRawData.reportnum) AS CountRaw
    FROM tblRawData
    GROUP BY tblRawData.username)  AS td 
ON tblUsers.userabbrev = td.username

Upvotes: 1

Related Questions