Results 1 to 7 of 7
  1. #1
    Seindie is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    4

    Question My query does not show all results because it wants to display only results that have a result in bo


    Dear all,

    I have built a query, see attached picture.
    Click image for larger version. 

Name:	query.PNG 
Views:	10 
Size:	11.4 KB 
ID:	39528

    The query does not show all results/records that it should show.
    I think that the reason is that the 2 last columns, DamageQuotation and DamageCost, are not always both filled out in the database behind. As I analyse the results I can see that when for example DamageQuotation is empty and DamageCost has a value, the result will not show the record because not both fields have a value. Same if DamageQuotation has a value, but DamageCost has not, it will not appear in the result.

    If both columns have a value, all records are shown.

    What is the way to solve this, and make sure that all records are shown, even if either DamageQuotation or DamageCost is empty?

    thank you so much for your help!
    Sandy
    Last edited by Seindie; 08-21-2019 at 06:07 AM. Reason: I forgot to update my title

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    if there is nothing in any criteria line, you get all records.
    but its possible when you look at the data sheet grid, see if the FILTER is on. (on the top icon bar)
    If pressed in, click it again to turn if off.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    Probably you have either Cartesian or inner join! You need to use left join instead to join tbl_DamaeQuotation and tbl_DamageCost with tbl_Damage!

    As it looks like you have SQL Database as BE, then probably the best solution would be to create a view in SQL DB, and link it into your Access FE! Multy-table queries are much complicated to create and have more limitations in Access compared with queries in SQL DB.

    Also, in FE, you can rename linked tables and views to get rid of those "dbo_"'s Access does add by default. I.e. when in SQL DB you have tbl_Damage, and by default the linked table has name dbo_tbl_Damage, then you can rename it as tbl_Damage.

  4. #4
    Seindie is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    4
    Hey! Thanks for the reply, I checked and filter is not on.

  5. #5
    Seindie is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    4
    Hey Arvil, I'm sorry that sounds chinese I'm a self-instructed Access user and no idea what you write there

  6. #6
    Seindie is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    4
    Hey found a work around!
    thanks for the input!
    Sandy

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    For Cartesian query the query string will be like
    Code:
    Select a.fieldA1, b.fielfdB1 FROM tblA a, tblB b
    The query combines every row in tblA with every row in tblB.

    For query using inner join the query string will be like
    Code:
    Select a.fieldA1, b.fielfdB1 FROM tblA a inner join tblB b ON a.AID = b.BID
    The query returns values from record where join condition is true for both tables - i.e. when you have some AID value n table tblA, but don't have same value n tblB, then for this ID no info is returned.

    For query using left join the query string will be like
    Code:
    Select a.fieldA1, b.fielfdB1 FROM tblA a left join tblB b ON a.AID = b.BID
    The query returns results for every record in tblA combined with every record with matching ID in tblB, and with Null values for those tblB fields where no matching ID was found.

    Someone here may explain what to do to create a left join query using wizard - I am quite illiterate about this!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query result illustrates results on main form
    By Dave_D in forum Programming
    Replies: 1
    Last Post: 12-01-2016, 08:31 AM
  2. Replies: 3
    Last Post: 03-18-2016, 10:07 AM
  3. Replies: 3
    Last Post: 09-12-2013, 02:18 PM
  4. Replies: 3
    Last Post: 05-02-2013, 10:36 AM
  5. Replies: 6
    Last Post: 05-14-2012, 07:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums