Results 1 to 10 of 10
  1. #1
    sprtrmp is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    Greenville, SC
    Posts
    102

    Not all records visible on subform

    Howdy everyone

    I have a form which a user inputs information regarding overtime periods that they work. Only some of the fields are required (start time, end time, etc) and the other fields can be left blank. This form spreads the information it collects over 8 different tables that all have one-to-one relationships (with referential integrity enforced) with the table containing the "main details" of that overtime period.

    If the user doesn't have any information that pertains to the data stored on one (or more) of the tables, then no record is created for that table.

    Then, I have a second form with a main form and a subform that shows all of the overtime periods for a specific date. If a record does not contain information on ALL of the tables, then that record does not show up on the subform.

    Again, not all of the information is REQUIRED because not all of it applies to EVERY overtime period. I need to get the subform to show ALL of the records, even if there is no applicable data stored on one or more of the tables. But I don't want to create a bunch of blank records on table if there is nothing applicable to that table.

    What am I doing wrong?



    Thanks in advance for the help

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Use Left Join on all the tables from your Main table. On the linking line between each table, double click and select you want to see all records from the Main table and only those form linking table that are equal.

  3. #3
    sprtrmp is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    Greenville, SC
    Posts
    102
    That didn't fix it. there are no records that are equal on some of the tables. in the case where there are no corresponding records on those tables, I am just looking for the controls on the form to be blank... if that means anything

    I suppose a better way to explain it is to say that I want whatever IS there to show up, regardless of whether or not there is a corresponding record on every other table.

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    That is what Left Join should do. It should always show the data in fields from the Main table and only show data in fields from Table2 if there is a corresponding record in Table2 that matches the record in Main. If not corresponding Table2 record, those fields would be blank but the Main table fields would be populated.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Can you post your dB or a screen print of your relationship window?

    This form spreads the information it collects over 8 different tables that all have one-to-one relationships (with referential integrity enforced) with the table containing the "main details" of that overtime period.
    My first thought is that you have a structure problem.
    One-to-One relationships are extremely rare. But you have 8 one-to-one relationships in one dB???

    From https://support.office.com/en-us/art...9-E06C4E272C45
    "A one-to-one relationship
    • In a one-to-one relationship, each record in the first table can have only one matching record in the second table, and each record in the second table can have only one matching record in the first table. This type of relationship is not common because, most often, the information related in this way is stored in the same table. You might use a one-to-one relationship to divide a table with many fields, to isolate part of a table for security reasons, or to store information that applies only to a subset of the main table. When you do identify such a relationship, both tables must share a common field."

    (Emphasis is mine)


    I have never had a dB with a one-to-one relationship...

  6. #6
    sprtrmp is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    Greenville, SC
    Posts
    102
    Click image for larger version. 

Name:	Untitled.jpg 
Views:	17 
Size:	109.2 KB 
ID:	25298Here is a pic of the relationships. The problem I am having is with tblAFTPDetails and the others that are related to it.

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Is there a reason all those User data tables cannot all be in tblUser such as DOB and Address, etc.? If there could only be 1 value for that related info, then it should all go in the tblUser table.

    I would combine all those fields into tblUser, then put Left Join from tblUser to FTPDetails table and see what you get.

  8. #8
    sprtrmp is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Location
    Greenville, SC
    Posts
    102
    I was just trying to break the tables down into very bite-sized chunks. seemed to me that combining it all onto one table made it a little bit bulky. be it seems as though a redesign is possibly in order.

  9. #9
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    No you don't want to have to link to multiple tables if you don't have to for various reasons.

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    After looking at your relationship picture, I think you are definitely in need of a structure redesign. You should not have any one-to-one relationships......

    Use paper & pencil or white board to design/redesign your table structures. Try adding data to the tables... If it works on paper, THEN create the tables in Access.
    I also noticed that there are tables where I would have date field(s) that you don't have.

    IMO, if you don't redesign your table structure now, you will continue to (always) have problems like you are having now.....

    I'm just saying..........

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

Similar Threads

  1. Replies: 18
    Last Post: 11-20-2013, 05:45 PM
  2. Image visible if listed in a subform
    By nigelbloomy in forum Forms
    Replies: 4
    Last Post: 12-20-2012, 04:40 PM
  3. Macro for visible field in subform
    By g=2012 in forum Access
    Replies: 7
    Last Post: 09-11-2012, 02:23 PM
  4. Visible property in subform columns
    By MDB in forum Forms
    Replies: 3
    Last Post: 09-03-2011, 06:46 PM
  5. Replies: 2
    Last Post: 01-06-2011, 04:38 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