SQL View not returning correct records

Bob Vitter 181 Reputation points
2023-05-15T21:05:48.9466667+00:00

I created a simple view that returns data from 2 tables: a master table with the PK an Int, Identity Specification and the detail table with the FK an Int

The tables are Inter Joined PF to FK

When I display the view in Access it displays the data from 1 detail record for each of the linked detail records

The SQL statement appears exactly the same as the SQL resulting from an Access query linking the tables - the Acess query works correctly

SQL 2019 Express

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,690 questions
Access
Access
A family of Microsoft relational database management systems designed for ease of use.
302 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Bob Vitter 181 Reputation points
    2023-05-15T23:09:36.76+00:00

    The view displays correctly in SSMS but not in Access

    Inner Join View.PNG

  2. Bob Vitter 181 Reputation points
    2023-05-16T15:26:05.5433333+00:00

    Sorry for the newbie question - I only use SQLServer as a backend to Access databases

    In researching answers to the questions posted I discovered that I needed to include the PK for the many-side table

    With that the view works correctly in Access

    0 comments