SQL query for an access database needed

Question

Hey guys, first off all sorry, i can't login using my yahoo provider. anyways I have this problem. Let me explain it to you, and then I'll show you a picture.

I have a access db table. It has 'report id', 'recpient id', and 'recipient name' and 'report req'. What the table "means" is that do the user using that report still require it or can we decommission it.

Here is how the data looks like (blocked out company userids and usernames): *check the link below, I cant post pictures cuz yahoo open id provider isnt working.

So basically I need to have 3 select queries:

1) Select all the reports where for each report, ALL the users have said no to 'reportreq'. In plain English, i want a listing of all the reports that we have to decommission because no user wants it.

2) Select all the reports where the report is required, and the batchprintcopy is more then 0. This way we can see which report needs to be printed and save paper instead of printing all the reports.

3)A listing of all the reports where the reportreq field is empty. I think i can figure this one out myself.

This is using Access/VBA and the data will be exported to an excel spreadsheet. I just a simple query if it exists, OR an alogorithm to do it quickly. I just tried making a "matrix" and it took about 2 hours to populate.

https://docs.google.com/uc?id=0B2EMqbpeBpQkMTIyMzA5ZjMtMGQ3Zi00NzRmLWEyMDAtODcxYWM0ZTFmMDFk&hl=en_US

Answer 1

1) This query works by taking each report ID and looking for a row where someone has not marked it as "not required" (with the assumption that 'n', and 'N' are the only ways to indicate that). If it finds any rows for that report ID that are still required.

SELECT DISTINCT report_id FROM table_name AS outer
  WHERE NOT EXISTS
    (SELECT report_id FROM table_name
    WHERE report_req NOT IN ("n","N")
      AND report_id=outer.report_id);

2) This query just adds up the values of batchprintcopy on a per-report_id basis (where the report is required, same assumption as above).

SELECT report_id, SUM(batchprintcopy) FROM table_name
  WHERE report_req NOT IN ("n","N")
    AND batchprintcopy > 0
  GROUP BY report_id;

3)

SELECT report_id FROM table_name
  WHERE report_req IS NULL OR report_req = "";

Answer 2

I suggest:

SELECT DISTINCT o.reportid, o.ReportReq
FROM All_Reports AS o
WHERE o.reportid Not In (SELECT reportid FROM All_Reports
    WHERE reportreq <>"N" Or reportreq Is Null)

There is a problem with this query in that I note that the sample document has a value for batchprintcopies where reportreq is null, so here are three possibilities:

1 Exclude reports where reportreq is null:

 SELECT reportid, SUM(batchprintcopies) FROM All_Reports
 WHERE reportreq <>"N"
 GROUP BY reportid
 HAVING Sum(batchprintcopies)>0

2 Group By reportreq to allow for further descisions:

 SELECT reportid, reportreq, Sum(batchprintcopies) AS SumOfCopies
 FROM All_Reports
 GROUP BY reportid, reporteeq
 HAVING Sum(batchprintcopies)>0

3 Include reports where reportreq is null:

 SELECT reportid, SUM(batchprintcopies) FROM All_Reports
 WHERE reportreq <>"N" Or reportreq Is Null
 GROUP BY reportid
 HAVING Sum(batchprintcopies)>0

It is unlikely, but not impossible that a field (column) contains a zero-length string. I reckon they should be avoided.

 SELECT reportid FROM All_Reports
 WHERE reportreq IS NULL OR reportreq = "";