Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: Query/SQL to show all/blank rows where there are no data in related table

    Hello there, many thanks for any help with this.

    Say I have a table with data in representing district, school, group and then figures for that district/school/group. And say in the group column I have values for All pupils, Male pupils and Female pupils but some schools do not have any female pupils for example so obviously the query doesn't display a row for these schools BUT I NEED IT TO. Does that make sense?

    E.g table as below:

    Code:
    District  School   Group   Figure1   Figure2   .... Etc.
    11111    12101   All        45.5       56.8
    11111    12101   Male     33.2       12.33
    11111    11101   All        98.3       45.8
    11111    11101   Male     54.9       49.2
    11111    11101   Female  78.5       43.2
    Ok So I want a query that can specify the rows that NEED to be included e.g. All, Male, Female and for it to actually display this:

    Code:
    District  School   Group   Figure1   Figure2   .... Etc.
    11111    12101   All        45.5       56.8
    11111    12101   Male     33.2       12.33
    11111    12101   Female             
    11111    11101   All        98.3       45.8
    11111    11101   Male     54.9       49.2
    11111    11101   Female  78.5       43.2
    I have actually achieved this using a sort of cartesian join BUT it is such a pain because I've got to check each figure (and there are many) to see if there are results and then put null if there aren't etc. I wanted to know if there is a quick and 'official' method for doing this as someone at work suggested 'purposely' inserting blank rows into the table!!! Surely this can't be right???

    I'd just like to know the official and proper way of achieving this please. I understand I will have to have a table with values in as below:

    Code:
    tblGroup
    All
    Male
    Female

    Many many thanks for any help

    -Jenny

  2. #2
    Join Date
    Sep 2009
    Posts
    2
    For information the method I have used to do this is by using sql as below but in the massive, complex tables we use this just seems a bit excessive. Is this the correct method to do this?
    Many thanks
    Code:
    SELECT tblData.District, tblData.School, tblGroup.Group, Sum(IIf(tblGroup.Group=tblData.Group,[Figure1],Null)) AS F1, Sum(IIf(tblGroup.Group=tblData.Group,[Figure2],Null)) AS F2, Sum(IIf(tblGroup.Group=tblData.Group,[Figure3],Null)) AS F3, Sum(IIf(tblGroup.Group=tblData.Group,[Figure4],Null)) AS F4
    FROM tblData, tblGroup
    GROUP BY tblData.District, tblData.School, tblGroup.Group;

Posting Permissions

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