Forum Discussion
Basic query question - query not returning results from 2 tables
I have attached a picture of the query I'm attempting to run. Now, it's been a few years since I've used Access. But, I don't remember it being this difficult! I'm probably doing something really stupid. If anyone can look at my picture and tell me what I'm doing wrong, it would be much appreciated.
6 Replies
Not sure if your issue got resolved. But when I look at the relationship that you drew between the two tables, I was puzzled. If you want to return the name of the person from the top table with the class from the bottom table, then shouldn't the tables be linked by ID. I assume that Susan in Care Givers has a corresponding entry in Classes per pathway with the matching ID of 1. From your example in Classes per Pathway I cannot tell whether Susan has more than one entry in Classes per Pathway, but I would assume not since the ID field in both tables has a primary key and usually primary keys do not allow duplicates. Therefore Susan in Care Givers should just have one entry in Classes per pathway and the query should be "easy". I can't easily construct the SQL in my head for you but it would be something like select first name, last name, class where Care Givers ID matches Classes per pathway ID.
Hope this is understandable.
If you want to post a sample Care Givers and Classes per pathway table of five records each, I could try to write the query for you. You won't have to include all the fields of your original tables, just first name and last name of Care Givers and the corresponding linked to ID Class in Classes per pathway.
I could make some sample tables myself, but I am not sure about your entries in Classes per pathway. If you want to just show me ID 1-5 of both tables, I will construct the tables on my end and test the query that I suggested.
Wes Jan
Wes_Jan Thank you so much for looking at my issue. After deleting my original table and creating a new one, the query ran fine. I've since added several tables, queries, forms, and reports. They are all working as expected. I've created several Access databases before, but it's been several years. I thought maybe it was something I was doing. It was frustrating for it to come down to some type of bug.
- Switch you query to SQL View and copy/paste the SQL Statement here please.
SELECT [Care Givers].[First Name], [Care Givers].[Last Name], [Classes per pathway].ClassFROM [Care Givers] INNER JOIN [Classes per pathway] ON [Care Givers].Pathway = [Classes per pathway].Pathway;
For one thing, there is a missing space in front of the FROM
Could you have defined different data type for the Pathway fields?
Take a look at the attached and compare it with your version.
On a side note, you really should avoid spaces and special characters in field and object names. You may like to browse: https://www.devhut.net/2017/04/20/access-best-practices-and-troubleshooting-steps/
