Basic query question - query not returning results from 2 tables

%3CLINGO-SUB%20id%3D%22lingo-sub-1585642%22%20slang%3D%22en-US%22%3EBasic%20query%20question%20-%20query%20not%20returning%20results%20from%202%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1585642%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20attached%20a%20picture%20of%20the%20query%20I'm%20attempting%20to%20run.%26nbsp%3B%20Now%2C%20it's%20been%20a%20few%20years%20since%20I've%20used%20Access.%26nbsp%3B%20But%2C%20I%20don't%20remember%20it%20being%20this%20difficult!%26nbsp%3B%20I'm%20probably%20doing%20something%20really%20stupid.%26nbsp%3B%20If%20anyone%20can%20look%20at%26nbsp%3B%20my%20picture%20and%20tell%20me%20what%20I'm%20doing%20wrong%2C%20it%20would%20be%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1585642%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1586651%22%20slang%3D%22en-US%22%3ERe%3A%20Basic%20query%20question%20-%20query%20not%20returning%20results%20from%202%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1586651%22%20slang%3D%22en-US%22%3ESwitch%20you%20query%20to%20SQL%20View%20and%20copy%2Fpaste%20the%20SQL%20Statement%20here%20please.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1586750%22%20slang%3D%22en-US%22%3ERe%3A%20Basic%20query%20question%20-%20query%20not%20returning%20results%20from%202%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1586750%22%20slang%3D%22en-US%22%3E%3CP%3ESELECT%20%5BCare%20Givers%5D.%5BFirst%20Name%5D%2C%20%5BCare%20Givers%5D.%5BLast%20Name%5D%2C%20%5BClasses%20per%20pathway%5D.ClassFROM%20%5BCare%20Givers%5D%20INNER%20JOIN%20%5BClasses%20per%20pathway%5D%20ON%20%5BCare%20Givers%5D.Pathway%20%3D%20%5BClasses%20per%20pathway%5D.Pathway%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F459380%22%20target%3D%22_blank%22%3E%40Daniel_Pineault%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1586876%22%20slang%3D%22en-US%22%3ERe%3A%20Basic%20query%20question%20-%20query%20not%20returning%20results%20from%202%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1586876%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20one%20thing%2C%20there%20is%20a%20missing%20space%20in%20front%20of%20the%20FROM%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20have%20defined%20different%20data%20type%20for%20the%20Pathway%20fields%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETake%20a%20look%20at%20the%20attached%20and%20compare%20it%20with%20your%20version.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20a%20side%20note%2C%20you%20really%20should%20avoid%20spaces%20and%20special%20characters%20in%20field%20and%20object%20names.%26nbsp%3B%20You%20may%20like%20to%20browse%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.devhut.net%2F2017%2F04%2F20%2Faccess-best-practices-and-troubleshooting-steps%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.devhut.net%2F2017%2F04%2F20%2Faccess-best-practices-and-troubleshooting-steps%2F%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1587102%22%20slang%3D%22en-US%22%3ERe%3A%20Basic%20query%20question%20-%20query%20not%20returning%20results%20from%202%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1587102%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Daniel%20-%20First%26nbsp%3Bof%26nbsp%3Ball%2C%26nbsp%3Bthank%26nbsp%3Byou%26nbsp%3Bfor%20the%26nbsp%3Btime%26nbsp%3Byou%26nbsp%3Btook%26nbsp%3Bto%26nbsp%3Banswer%26nbsp%3Bthis%26nbsp%3Bquestion.%26nbsp%3B%20This%26nbsp%3Bis%26nbsp%3Bthe%20most%20basic%26nbsp%3Bof%26nbsp%3Bqueries%26nbsp%3Band%26nbsp%3Bit's%20been%20driving%20me%20crazy.%26nbsp%3B%20I%20went%20line%20by%20line%20and%20compared%20your%20database%20with%20mine%20and%20EVERYTHING%20was%20identical.%26nbsp%3B%20I%20copied%20your%20elements%20into%20my%20database%20and%20it%20ran%20fine.%26nbsp%3B%20I%20copied%20your%20SQL%20into%20my%20query%20and%20it%20wouldn't%20run%20(I%20took%20into%20account%20the%20field%20name%20changes).%26nbsp%3B%20It%20should%20have%20worked.%26nbsp%3B%20That%20told%20me%20there%20was%20probably%20something%20wrong%20with%20my%20tables.%26nbsp%3BI%20rebuilt%20my%20tables%20one%20field%20at%20a%20time%20and%26nbsp%3Bdiscovered%20something%20was%20wrong%20with%20my%20CareGivers%20table.%26nbsp%3B%20I%20still%20have%20no%20clue%20what%20was%20wrong%26nbsp%3Bbecause%20the%20one%20I%20rebuilt%20is%20identical%20to%20the%26nbsp%3Boriginal%20(all%20data%20types%20were%20matching%20with%26nbsp%3Bthe%20same%20field%20properties.)%26nbsp%3B%20I'm%20going%20to%20chalk%20this%20up%20to%20some%20stupid%20bug%20with%20my%20original%20table.%26nbsp%3B%20I%20was%20stuck%20with%20my%20problem%20solving%20and%20your%20response%26nbsp%3BREALLY%26nbsp%3Bhelped.%26nbsp%3B%20THANK%20YOU!%26nbsp%3B%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F459380%22%20target%3D%22_blank%22%3E%40Daniel_Pineault%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1592083%22%20slang%3D%22en-US%22%3ERe%3A%20Basic%20query%20question%20-%20query%20not%20returning%20results%20from%202%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1592083%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F757851%22%20target%3D%22_blank%22%3E%40SVinLV%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20sure%20if%20your%20issue%20got%20resolved.%26nbsp%3B%20But%20when%20I%20look%20at%20the%20relationship%20that%20you%20drew%20between%20the%20two%20tables%2C%20I%20was%20puzzled.%26nbsp%3B%20If%20you%20want%20to%20return%20the%20name%20of%20the%20person%20from%20the%20top%20table%20with%20the%20class%20from%20the%20bottom%20table%2C%20then%20shouldn't%20the%20tables%20be%20linked%20by%20ID.%26nbsp%3B%20I%20assume%20that%20Susan%20in%20Care%20Givers%20has%20a%20corresponding%20entry%20in%20Classes%20per%20pathway%20with%20the%20matching%20ID%20of%201.%26nbsp%3B%20From%20your%20example%20in%20Classes%20per%20Pathway%20I%20cannot%20tell%20whether%20Susan%20has%20more%20than%20one%20entry%20in%20Classes%20per%20Pathway%2C%20but%20I%20would%20assume%20not%20since%20the%20ID%20field%20in%20both%20tables%20has%20a%20primary%20key%20and%20usually%20primary%20keys%20do%20not%20allow%20duplicates.%26nbsp%3B%20Therefore%20Susan%20in%20Care%20Givers%20should%20just%20have%20one%20entry%20in%20Classes%20per%20pathway%20and%20the%20query%20should%20be%20%22easy%22.%26nbsp%3B%20I%20can't%20easily%20construct%20the%20SQL%20in%20my%20head%20for%20you%20but%20it%20would%20be%20something%20like%20select%20first%20name%2C%20last%20name%2C%20class%20where%20Care%20Givers%20ID%20matches%20Classes%20per%20pathway%20ID.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20is%20understandable.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20post%20a%20sample%20Care%20Givers%20and%20Classes%20per%20pathway%20table%20of%20five%20records%20each%2C%20I%20could%20try%20to%20write%20the%20query%20for%20you.%26nbsp%3B%20You%20won't%20have%20to%20include%20all%20the%20fields%20of%20your%20original%20tables%2C%20just%20first%20name%20and%20last%20name%20of%20Care%20Givers%20and%20the%20corresponding%20linked%20to%20ID%20Class%20in%20Classes%20per%20pathway.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20could%20make%20some%20sample%20tables%20myself%2C%20but%20I%20am%20not%20sure%20about%20your%20entries%20in%20Classes%20per%20pathway.%26nbsp%3B%20If%20you%20want%20to%20just%20show%20me%20ID%201-5%20of%20both%20tables%2C%20I%20will%20construct%20the%20tables%20on%20my%20end%20and%20test%20the%20query%20that%20I%20suggested.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWes%20Jan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1594794%22%20slang%3D%22en-US%22%3ERe%3A%20Basic%20query%20question%20-%20query%20not%20returning%20results%20from%202%20tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1594794%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F702789%22%20target%3D%22_blank%22%3E%40Wes_Jan%3C%2FA%3E%26nbsp%3BThank%20you%20so%20much%20for%20looking%20at%20my%20issue.%20%26nbsp%3BAfter%20deleting%20my%20original%20table%20and%20creating%20a%20new%20one%2C%20the%20query%20ran%20fine.%20%26nbsp%3BI've%20since%20added%20several%20tables%2C%20queries%2C%20forms%2C%20and%20reports.%20%26nbsp%3BThey%20are%20all%20working%20as%20expected.%20%26nbsp%3BI've%20created%20several%20Access%20databases%20before%2C%20but%20it's%20been%20several%20years.%20%26nbsp%3BI%20thought%20maybe%20it%20was%20something%20I%20was%20doing.%20%26nbsp%3BIt%20was%20frustrating%20for%20it%20to%20come%20down%20to%20some%20type%20of%20bug.%20%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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
Highlighted
Switch you query to SQL View and copy/paste the SQL Statement here please.
Highlighted

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;

 

@Daniel_Pineault 

Highlighted

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/

Highlighted

Hi Daniel - First of all, thank you for the time you took to answer this question.  This is the most basic of queries and it's been driving me crazy.  I went line by line and compared your database with mine and EVERYTHING was identical.  I copied your elements into my database and it ran fine.  I copied your SQL into my query and it wouldn't run (I took into account the field name changes).  It should have worked.  That told me there was probably something wrong with my tables. I rebuilt my tables one field at a time and discovered something was wrong with my CareGivers table.  I still have no clue what was wrong because the one I rebuilt is identical to the original (all data types were matching with the same field properties.)  I'm going to chalk this up to some stupid bug with my original table.  I was stuck with my problem solving and your response REALLY helped.  THANK YOU!   @Daniel_Pineault 

Highlighted

@SVinLV 

 

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

Highlighted

@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.