none
Datasheet Returns Zero Results if Value is Blank RRS feed

  • Question

  • I have 5 tables that relate to 1:

    This datasheet has a criteria that ID.Status = 1

    The issue I'm having, is if Clients.ClientLegalStatus has no value set, then the datasheet returns no records. If I completely remove the refrences to the LeaglStatus table, I get my results. I don't know why this is happening.

    Friday, May 27, 2016 3:06 PM

Answers

  • Status.SatusName is based on Client.ClientStatus = Status.StatusID, that works without issue.

    However, if I have one record with Client.ClientStatus with a value of 1, but I have another record with Client.ClientStatus null, it won't return that record.

    Hi adhaas,

    In that case change the INNER JOIN to a LEFT JOIN.

    Imb.

    Saturday, June 11, 2016 5:27 AM
  • Yes, I tried to use queries because in my mind, that made sense. Apparently that's now how Access Web App works. I found on some other forums that the field I was putting my SQL in is expecting an "Expression", hence the name "Expression Builder":

    So I didn't use that at all. Instead, when creating the datasheet view for 'Clients', when adding a column from another table (Status) that has a relationship with the main table (Clients) you can define the relationship. In my case, 'Status.StatusID' = 'Client.ClientStatus', but I want to pull the 'Status.StatusName'.

    So I add 'Status.StatusName' as a column and I get this prompt:

    From here, in the first drop-down on the left, you select the related column in 'Status' which in this case is 'StatusID'. In the first drop-down on the right, you select the related column in 'Clients' which in this case is 'ClientStatus'. Then you select "One record in 'Status' matches many records in 'Clients'" and click "OK".

    This however does not solve the issue of having null values. If any of these relationships have a null value, then the entire record is redacted. I resolved this by adding an 'N/A' value to all my tables relating to 'Clients'. In the form, I set the "Default Value" to '=7' or whatever record number matches 'N/A' for that table.

    • Marked as answer by ahaas.TP Tuesday, June 21, 2016 2:44 PM
    Tuesday, June 14, 2016 5:49 PM

All replies

  • AdHaas -

    Show us your SQL, please.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Saturday, May 28, 2016 8:04 PM
  • This datasheet has a criteria that ID.Status = 1

    The issue I'm having, is if Clients.ClientLegalStatus has no value set, then the datasheet returns no records. If I completely remove the refrences to the LeaglStatus table, I get my results. I don't know why this is happening.

    Hi adhaas,

    Probably you use an INNER JOIN between LegalStatus and Clients.

    You can try to use a OUTER JOIN (LEFT JOIN of RIGHT JOIN).

    Imb.

    Saturday, May 28, 2016 8:53 PM
  • As Imb points out an INNER JOIN, which is the default unless you have set the join type otherwise via the relationship dialogue, will only return rows where there is a match in both tables, so you will need to use an OUTER JOIN.  I would add a few other comments about your model:

    1.  None of the relationships are enforced.  The enforcement of referential integrity is a fundamental mechanism of the database relational model, so all relationships should be enforced.  An unenforced relationship prejudices the integrity of the database.

    2.  Using the generic ID as a column name for the primary key of all tables can lead to ambiguity as to what is being referenced.  Always use a name which clearly describes the attribute represented by the column, e.g.  ClientID, LegalStatusID etc.

    3.  Other than in situations where two foreign keys reference the primary key of the same table, or a foreign key is referencing the key of its own table, it is advisable to give a foreign key the same name as the primary key which it references.  This makes for greater clarity in SQL statements.  This is of course an additional reason not to use the generic ID as the name of keys.  If the foreign names which you have used derive from the use of the lookup field wizard when assigning the data type to the foreign key columns, note that this feature is generally deprecated by experienced developers, and should be avoided.  For reasons why see http://www.mvps.org/access/lookupfields.htm

    Ken Sheridan, Stafford, England

    Saturday, May 28, 2016 11:00 PM
  • Hi adhaas85,

    here I think Imb-hb and Ken Sheridan have give a proper suggestion that can help you to solve your issue.

    please check the suggestion given by them and if you think it is helpful to solve your issue please mark the suggestion given by them as an Answer.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, May 30, 2016 2:19 AM
    Moderator
  • AdHaas -

    Show us your SQL, please.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    I'm using the Query Builder (maybe my first mistake?) as you can see in my screenshot. I'm not sure how to get it dto display the SQL. 
    Tuesday, May 31, 2016 2:58 PM
  • As Imb points out an INNER JOIN, which is the default unless you have set the join type otherwise via the relationship dialogue, will only return rows where there is a match in both tables, so you will need to use an OUTER JOIN.  I would add a few other comments about your model:

    I can't find how to opwn the relationships dialogue. I'm using the query builder which I thought would be easier, but appears to be making this more difficult.

    1.  None of the relationships are enforced.  The enforcement of referential integrity is a fundamental mechanism of the database relational model, so all relationships should be enforced.  An unenforced relationship prejudices the integrity of the database.

    I had to read up on what this meant. So this is to restrict a value from being inputted that doesn't exist a primary key. I'm not sure how to do that.

    2.  Using the generic ID as a column name for the primary key of all tables can lead to ambiguity as to what is being referenced.  Always use a name which clearly describes the attribute represented by the column, e.g.  ClientID, LegalStatusID etc.

    I renamed all the ID columns accordingly.


    3.  Other than in situations where two foreign keys reference the primary key of the same table, or a foreign key is referencing the key of its own table, it is advisable to give a foreign key the same name as the primary key which it references.  This makes for greater clarity in SQL statements.  This is of course an additional reason not to use the generic ID as the name of keys.  If the foreign names which you have used derive from the use of the lookup field wizard when assigning the data type to the foreign key columns, note that this feature is generally deprecated by experienced developers, and should be avoided.  For reasons why see http://www.mvps.org/access/lookupfields.htm

    I do have this, but I haven't used it yet. Both the Clients.ClientAssignedFrom and Clients.ClientAssignedTotake their values from Coordinators.CoordinatorID. I was previously using lookup fields but ran into issues with that as well. I also read that it should be avoided.

    I had to read up on what this meant. So this is to restrict a value from being inputted that doesn't exist a primary key. I'm not sure how to do that.

    • Edited by ahaas.TP Tuesday, May 31, 2016 3:20 PM
    Tuesday, May 31, 2016 3:20 PM
  • 1.  I'm using the query builder which I thought would be easier, but appears to be making this more difficult.

    2.  I can't find how to open the relationships dialogue.

    3.  Both the Clients.ClientAssignedFrom and Clients.ClientAssignedTo take their values from Coordinators.CoordinatorID.

    1.  Queries and relationships are not the same things.  It is the former which enforces the integrity of the database by selecting 'Enforce Referential Integrity' in the relationships dialogue.  A relationship is created in the database's relationships window, which is opened by selecting Relationships on the database Tools ribbon.  Having added the tables to the window, the relationship is created by dragging from the primary key of the referenced table to the relevant foreign key of the referencing table.  This will open the relationships dialogue.

    2.  To open the dialogue for an existing relationship right click on the line between the tables and select 'Edit Relationship'.   The dialogue also includes options to enforce the referential operations 'Cascade Update Related Fields' and  'Cascade Delete Related Records'.  The former is unnecessary if the primary key of the referenced table is an autonumber, as the values of such a column cannot be changed,  but if the primary key is not an autonumber, then in most circumstances cascade updates would be enforced.  The enforcement of cascade deletes is a matter of judgement.  It is an integral part of a well designed relational database when used correctly, but can easily lead to the inadvertent loss of substantial amounts of data if used incorrectly.

    3.  Where the primary key of a single table is referenced by two foreign key columns in a referencing table, two separate relationships are created.  In the relationships window add the referenced table once, and the referencing table twice.  The second instance of the referencing table will be given a _1 suffix.  Create the relationship by dragging from the primary key of the referenced table separately to one of the foreign key columns in each of the two instances of the referencing table.


    Ken Sheridan, Stafford, England


    Tuesday, May 31, 2016 4:33 PM
  • 1.  Queries and relationships are not the same things.  It is the former which enforces the integrity of the database by selecting 'Enforce Referential Integrity' in the relationships dialogue.  A relationship is created in the database's relationships window, which is opened by selecting Relationships on the database Tools ribbon.  Having added the tables to the window, the relationship is created by dragging from the primary key of the referenced table to the relevant foreign key of the referencing table.  This will open the relationships dialogue.


    2.  To open the dialogue for an existing relationship right click on the line between the tables and select 'Edit Relationship'.   The dialogue also includes options to enforce the referential operations 'Cascade Update Related Fields' and  'Cascade Delete Related Records'.  The former is unnecessary if the primary key of the referenced table is an autonumber, as the values of such a column cannot be changed,  but if the primary key is not an autonumber, then in most circumstances cascade updates would be enforced.  The enforcement of cascade deletes is a matter of judgement.  It is an integral part of a well designed relational database when used correctly, but can easily lead to the inadvertent loss of substantial amounts of data if used incorrectly.

    3.  Where the primary key of a single table is referenced by two foreign key columns in a referencing table, two separate relationships are created.  In the relationships window add the referenced table once, and the referencing table twice.  The second instance of the referencing table will be given a _1 suffix.  Create the relationship by dragging from the primary key of the referenced table separately to one of the foreign key columns in each of the two instances of the referencing table.


    Ken Sheridan, Stafford, England

    Sorry, I should clarify that this is an Access Web App that is on SharePoint. Yeah it's Access, but my Ribbon doesn't have much in it.


    Tuesday, May 31, 2016 4:47 PM
  • Sorry, I should clarify that this is an Access Web App that is on SharePoint. Yeah it's Access, but my Ribbon doesn't have much in it.
    Sorry, can't help you in that case.

    Ken Sheridan, Stafford, England

    Tuesday, May 31, 2016 4:56 PM
  • Hi adhaas85,

    can you try to make a query manually?

    or you can try to build your query on local database with demo tables and data and once you make it perfect you can implement the changes in your web app

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 1, 2016 7:02 AM
    Moderator
  • So this for example?

    SELECT Clients.ClientFullName, Status.StatusName AS "ClientStatus"
    FROM Clients
    LEFT JOIN Status ON Customers.ClientStatus = Status.StatusID
    WHERE Clients.ClientStatus = 1

    Because I'm getting an error:

    Wednesday, June 1, 2016 8:50 PM
  • By 'error' are you referring to the dialogue in your image?  If so, that is merely a standard confirmation dialogue, not an error.

    However, you do have another problem which is that you are trying to restrict the SQL statement on the basis of one of the JOIN columns in the LEFT OUTER JOIN.  This is logically inconsistent as it would rule out the return of rows with a NULL ClientStatus foreign key, which is precisely what the LEFT OUTER JOIN is intended to return.  Change the WHERE clause to:

    WHERE Clients.ClientStatus = 1 OR Clients.ClientStatus IS NULL


    Ken Sheridan, Stafford, England

    Wednesday, June 1, 2016 11:13 PM
  • In this particular scenario, if Clients.ClientStatus IS NULL, then I don't want it to show up. Should I use a regular JOIN then?
    Wednesday, June 1, 2016 11:24 PM
  • In this particular scenario, if Clients.ClientStatus IS NULL, then I don't want it to show up. Should I use a regular JOIN then?

    Yes, it would be an INNER JOIN.

    Ken Sheridan, Stafford, England

    Wednesday, June 1, 2016 11:28 PM
  • With this query:

    SELECT Clients.ClientFullName, Status.StatusName AS "ClientStatus"
    FROM Clients
    JOIN Status ON Customers.ClientStatus = Status.StatusID
    WHERE Clients.ClientStatus = 1

    I get:

    I don't see where the issue is.

    Thursday, June 2, 2016 11:26 PM
  • Hi adhaas85,

    is there Customers Table available in your database?

    because in the above pics I did not see this table

    in your original post There are only 5 tables and there is no customer table.

    please confirm

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 3, 2016 7:21 AM
    Moderator
  • SELECT Clients.ClientFullName, Status.StatusName AS "ClientStatus"
    FROM Clients
    JOIN Status ON Customers.ClientStatus = Status.StatusID
    WHERE Clients.ClientStatus = 1

    Hi adhaas,

    There are a couple of remarks to make on the above query.

    1. Instead of just JOIN you should use INNER JOIN, eventually LEFT JOIN of RIGHT JOIN depending on the type of join.

    2. You use an alias "Clientatus" for StatusName, with a different meaning from ClientStatus that is used as field in Customers, and also as field in Clients. With a different meaning, use a different name.

    3. What is the difference of ClientStatus in Customers and in Clients?

    4. For clearity I prefer to name a foreign key in the same as correspondng (primay) key, so:  ON Customers.StatusID = Status.StatusID.

    5. A single field alias, that has no duplicate fields in the different tables, is in my opinion not necessary.

    6. I use to have a "rigid" naming system, organized around an item. An item corresponds with an entity in the database. The table is named Item_tbl, the primary key (autonumber) is called Item_id. A foreign key referring to an Item1_tbl, is named Item1_id. In this way I can construct all relations between all entities or items completely automatic. I prefer to use a suffix behind Item, because that is more in harmony with my natural language.

    Imb.




    • Edited by Imb-hb Friday, June 3, 2016 7:26 AM typo
    Friday, June 3, 2016 7:23 AM
  • With this query:

    SELECT Clients.ClientFullName, Status.StatusName AS "ClientStatus"
    FROM Clients
    JOIN Status ON Customers.ClientStatus = Status.StatusID
    WHERE Clients.ClientStatus = 1

    I get:

    I don't see where the issue is.

    Three issues:

    1.  The query joins Clients and Status, but the join is on Customers.ClientStatus, not Clients.ClientStatus.

    2.  If you want to return all rows from Clients regardless of a match in Status the join type should be a LEFT JOIN.  If you want to return rows from Clients only where there is a match in Status the join type should be an INNER JOIN.

    3.  The alias "ClientStatus" should not be delimited with quotes characters.


    Ken Sheridan, Stafford, England

    Friday, June 3, 2016 10:51 AM
  • OK

    1. I thought just putting "JOIN" implied it was an "INNER JOIN", so now I know.

    2. Yeah, wasn't sure how to do this. "Clients.ClientStatus" = "Status.StatusID", but I don't want to display the number value. I want to display the corresponding "Status.StatusName" for that "Status.StatusID".

    3. "Customers" is my mistake, looking at too many examples trying to solve this. It should have been "Clients" not "Customers".

    4. I renamed all the IDs so they have the table name leading in front so it's not "ID" for everything. Naming it more literally would probably help too.

    5. You are absolutely correct. In the end I'll take this out probably. If I can solve this issue however, I can solve the same problem with the other tables who's values I want to display.

    6. Good idea.
    • Edited by ahaas.TP Friday, June 3, 2016 8:17 PM
    Friday, June 3, 2016 8:17 PM
  • 1. Yeah, that was my mistake totally. I've made that correction.

    2. OK, someone else mentioned that. I thought just putting JOIN implied INNER JOIN.

    3. OK, I remove the " around ClientStatus, but I still get the same error:

    SELECT Clients.ClientFullName, Status.StatusName AS ClientStatus
    FROM Clients
    INNER JOIN Status ON Clients.ClientStatus = Status.StatusID
    WHERE Clients.ClientStatus = 1

    Friday, June 3, 2016 8:22 PM
  • You are using the name of one  column as the alias for another column.  An alias is unnecessary here, so take it out:

    SELECT Clients.ClientFullName, Status.StatusName
    FROM Clients INNER JOIN Status
    ON Clients.ClientStatus = Status.StatusID
    WHERE Clients.ClientStatus = 1;

    Ken Sheridan, Stafford, England

    Friday, June 3, 2016 10:19 PM
  • Hi adhaas85,

    is your issue solved or you are still getting an error?

    if your issue is solved then I would recommend you to mark the suggestion as an answer that help you to solve your issue.

    if your are still have an issue please let us know so that we can provide you further help.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 7, 2016 7:51 AM
    Moderator
  • Same error with that SQL:

    SELECT Clients.ClientFullName, Status.StatusName
    FROM Clients
    INNER JOIN Status ON Clients.ClientStatus = Status.StatusID
    WHERE Clients.ClientStatus = 1;

    I just don't get it.

    Friday, June 10, 2016 9:17 PM
  • Without being able to debug your query I don't think there is anything more I can usefully say.

    Ken Sheridan, Stafford, England

    Friday, June 10, 2016 9:53 PM
  • OK, it looks like this box isn't expecting a query, but an expression.
    Friday, June 10, 2016 10:06 PM
  • So oddly, I figured this out without using a query or expression.

    Now the only issue I'm having is that if there is no value specified (null) for a column I'm displaying, it does not return the record.

    Example:

    Client.FullName, Status.StatusName

    Status.SatusName is based on Client.ClientStatus = Status.StatusID, that works without issue.

    However, if I have one record with Client.ClientStatus with a value of 1, but I have another record with Client.ClientStatus null, it won't return that record.

    Friday, June 10, 2016 11:09 PM
  • Status.SatusName is based on Client.ClientStatus = Status.StatusID, that works without issue.

    However, if I have one record with Client.ClientStatus with a value of 1, but I have another record with Client.ClientStatus null, it won't return that record.

    Hi adhaas,

    In that case change the INNER JOIN to a LEFT JOIN.

    Imb.

    Saturday, June 11, 2016 5:27 AM
  • There is no where to specify an INNER JOIN or LEFT JOIN. This isn't a query. The only thing I can do is make an expression.

    Monday, June 13, 2016 4:13 PM
  • Hi adhaas85,

    but in your all previous replies you used queries so now why you can't use it?

    you also did not mentioned that how you solved your issue.

    if you try to mention then I think our community members try to understand that and based on that they give you some suggestions.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 14, 2016 6:01 AM
    Moderator
  • Yes, I tried to use queries because in my mind, that made sense. Apparently that's now how Access Web App works. I found on some other forums that the field I was putting my SQL in is expecting an "Expression", hence the name "Expression Builder":

    So I didn't use that at all. Instead, when creating the datasheet view for 'Clients', when adding a column from another table (Status) that has a relationship with the main table (Clients) you can define the relationship. In my case, 'Status.StatusID' = 'Client.ClientStatus', but I want to pull the 'Status.StatusName'.

    So I add 'Status.StatusName' as a column and I get this prompt:

    From here, in the first drop-down on the left, you select the related column in 'Status' which in this case is 'StatusID'. In the first drop-down on the right, you select the related column in 'Clients' which in this case is 'ClientStatus'. Then you select "One record in 'Status' matches many records in 'Clients'" and click "OK".

    This however does not solve the issue of having null values. If any of these relationships have a null value, then the entire record is redacted. I resolved this by adding an 'N/A' value to all my tables relating to 'Clients'. In the form, I set the "Default Value" to '=7' or whatever record number matches 'N/A' for that table.

    • Marked as answer by ahaas.TP Tuesday, June 21, 2016 2:44 PM
    Tuesday, June 14, 2016 5:49 PM
  • Hi adhaas85,

    I think you need to raise this issue in new thread.

    because whole thread is based upon query all the suggestions are related to it but at the end your requirements are changed.

    so it is better to ask this question in new thread.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 21, 2016 3:58 AM
    Moderator