Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Location
    Liverpool - UK
    Posts
    12

    Unanswered: Query returns no rows - want a zero

    I am very much a beginner with access and need some help please!

    I have a table which includes a field 'Status' which can be , for example, A,B, C or D.

    I want to count the number of each and output to a report.
    I have written a query which uses criteria to select the records I want and counts the number with 'Status' equal to, say, A

    As long as there are some A's then it returns a single row with the number counted in the CountofStatus field

    Problem is that, at any one time, there may not be any A's hence query returns no rows - just a datasheet with colum headings.

    I want a single row returned with CountofStatus returning Zero.

    Help says use Nz function but not sure of how or where.

    Any help much appreciated by a frustrated Access beginner!!!

  2. #2
    Join Date
    Sep 2004
    Posts
    161
    Bonjour,
    I have testing this query :

    requete = "select Count(*) as expr1 from Table1 Where [Field]='toto'"
    Set toto = CurrentDb.OpenRecordset(requete)
    toto.MoveFirst
    Debug.Print toto!expr1

    If i have no record in table1, the result recordset contain one record with field Expr1=0. Same thing with table do not containing record with field="toto"

  3. #3
    Join Date
    Sep 2004
    Location
    Liverpool - UK
    Posts
    12
    Thanks for the quick response.
    I am sorry to be so stupid - but I am very much a beginner!!

    Where do I type the statements you have provided?? - Do they go in the SQL statement for the query??

    If not could you please tell me what to do?

    Your help is much appreciated

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Philw

    Jepi's solution requires you to implement code and I wonder if, as a beginner, you familiar with how to do this.

    I can offer an alternative solution which you might find simpler to implement.

    Create a table with just one column to hold all possible statuses
    e.g.
    Table name = tblStatus
    field name = Status

    the following query will then give you what you want:

    Code:
    SELECT tblStatus.Status, Count(tblMachine.Status) AS CountOfStatus
    FROM tblMachine RIGHT JOIN tblStatus ON tblMachine.Status = tblStatus.Status
    GROUP BY tblStatus.Status
    HAVING tblStatus.Status="C";
    My example table is called tblMachine and has a field called Status. You can change the "C" bit to whatever you want e.g. [enter required status]

    If you take the last line out, then you will get a listing of counts of all Statuses including zeros.

    HTH
    Chris
    Last edited by howey; 09-15-04 at 10:31.

  5. #5
    Join Date
    Oct 2003
    Posts
    706
    Okay, lemme see if I can help explain things a bit...

    PhilW, what Access is doing with your query is to first locate all of the groups of data that actually exist in your data, then count them. So if there are no records with status="A" in your selection, even if "A" is a possible value, there will be no results for "A." The group does not exist.

    What Howey is suggesting is what's called an outer join. Phil, take your query-window and click directly upon the line that connects any two boxes (in a multi-table query). This line can be a straight-line or it can contain an arrowhead. Fiddle with it; try it. Read the MS help on "left join," "right join," and so-forth; so-called "outer" (vs. "inner," of course) joins.

    Getting rid of the "here-a-join, there-a-join, everywhere-a-join-join" doubletalk ... here's English...

    First, build a table with all the possible values for "status." It's just one table with A, B, C, and so-on; one per row.

    Now, we take your original count-query ... the one you did that groups-by status and counts the number of occurrences ... your original output that didn't contain anything for "A," and we right join that with this table of statuses, joining the two tables on "status."

    If you ran such a query with an ordinary join, the output would not be different. But if we click on that line, make it an arrow (the dialog-box is pretty self-explanatory), we tell Access that we want to see a row in the result for every value in the STATUS table, whether or not there is a matching row for it in the COUNTS table/query.

    (No, I realize that what I'm describing doesn't quite match what you want... and that I'm using database-jargon after all... but just think about the concept.) This new table provides Access, in effect, with a list of all the possible values that could occur, and by using an outer join, we'll get results for each entry, each possibility listed, whether or not it has results.

    You'll get nulls in the non-matching columns, but that's what the NZ() ("null-to-zero") function is good for.

    Do a Google search (advanced search) on the exact-phrase outer join. Read until *pop! the little light-bulb blinks on. Say, "ahhhh... hah!"
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  6. #6
    Join Date
    Sep 2004
    Posts
    161
    One solution to use my query is to construct a query....
    Create a new query, select the option SQL and paste my code. Change the name of the table and field and run

  7. #7
    Join Date
    Sep 2004
    Location
    Liverpool - UK
    Posts
    12
    Very grateful guys - Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •