Results 1 to 5 of 5
  1. #1
    Aladdin is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2019

    I can't show all the data points when creating a query from several tables

    Good day to all,

    I'm working on data concerning road traffic accidents for a certain period. The attributes (variables) of each data point (each accident) can be found in 5 different tables, and the final table I want to achieve would have each data point (accident ID) together with its variables from all the tables, and to do so I have linked my tables using the relationship shown in the attached screenshot. I have created a bridge table to account for the duplicated values (as each accident has many components such as vehicle 1 and vehicle 2 and therefore the Accident ID would be duplicated in tables) and then using a one-to-many relationship, I linked the tables through the variable "ID_Accidente" and the join type I chose was "Include ALL records from 'Table Name' and only those records from 'BridgeTable'(which contains the non duplicated accidents ID's) where the joined fields are equal, but what I keep getting in the query is only the accidents ID's for which there is a value in each row (for example, not all accidents include pedestrians so data points with no pedestrians won't show in the final query). Can anyone suggest a solution so I can show each accident ID with all the attributes from all the tables?

    Thank you in advance!
    Click image for larger version. 

Name:	Acess Relationships.JPG 
Views:	11 
Size:	89.2 KB 
ID:	39899

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    If I understand the problem correctly,
    You also want to modify the linkages shown to "Include all records from IDPivotTable and only those from Drivers_2016" and so on.
    You probably want to do this just in a query because you want to maintain the database's relational integrity in the Relationships diagram as shown.

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    I agree with Davegri - select the other option

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Anchorage, Alaska, USA
    You might consider reworking your design. It looks like you have linked the tables on text fields instead of number fields.
    See Microsoft Access Tables: Primary Key Tips and Techniques

    I didn't add all of the fields in your image, but consider this:
    Click image for larger version. 

Name:	Aladdin1.png 
Views:	9 
Size:	117.5 KB 
ID:	39904

    I tend to have a left to right arrangement for viewing relationships; it is easier for me to see the flow of data.
    Click image for larger version. 

Name:	Aladdin2.png 
Views:	9 
Size:	119.2 KB 
ID:	39905

    A field with the suffix of "ID_PK" is a primary key field that is an Autonumber type.
    A field with the suffix of "ID_FK" is a foreign key field that is a Long Integer type.

    Also notice that the YEAR has been moved into the table "tblAccidentes" - now you can have multiple years of accidents in each table - just filter on the year.

    Good luck with your project.....
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    Aladdin is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2019
    Thank you all for your very beneficial comments and replies, I highly appreciate it!

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

Similar Threads

  1. One tables data wont show in Query
    By NickWren in forum Access
    Replies: 5
    Last Post: 07-24-2018, 09:53 AM
  2. Replies: 3
    Last Post: 10-28-2014, 12:23 AM
  3. Replies: 2
    Last Post: 09-27-2014, 09:48 AM
  4. Replies: 7
    Last Post: 12-30-2012, 03:59 AM
  5. Replies: 27
    Last Post: 08-14-2012, 09:05 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 - Senior Forums