Results 1 to 9 of 9
  1. #1
    qvqv is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    18

    Many to Many Query problem

    Help!

    I have 3 tables, 1 is a list of people tblPerson, a table called AddressT which is a list of addresses the person lived at, there is a link table called Addresses_JunctionT.

    The AddressT table has addresses, and more than one person in the tblPerson can have stayed at the address.

    Click image for larger version. 

Name:	ManyQuery Forum.png 
Views:	16 
Size:	11.0 KB 
ID:	44168

    My query is

    select p.* from tbl_Person p
    inner join Addresses_JunctionT aj on aj.PersonID = p.PersonID
    inner join AddressT at on at.AddressID = aj.AddressID
    where p.PersonID = “1”



    Running this query Access reports a missing operator.

    Having spent hours on this I cannot find a solution.
    Any help appreciated

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    What do you want the query to show?
    EDIT: HINT- try it without the PersonID in AddressT
    Last edited by davegri; 02-07-2021 at 12:51 PM. Reason: See EDIT

  3. #3
    qvqv is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    18
    Thanks for such a quick response.

    I want it to show all the address that a person as stayed at.

    That was the intention of "where p.PersonID = “1” PersonID is a primary key

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Delete the PersonID from AddressT then

    SELECT tbl_Person.PersonId, tbl_Person.Full_Name, AddressT.Address
    FROM tbl_Person INNER JOIN (AddressT INNER JOIN Addresses_JunctionT ON AddressT.AddressID = Addresses_JunctionT.AddressID) ON tbl_Person.PersonId = Addresses_JunctionT.PersonID
    WHERE (((tbl_Person.PersonId)=1));

  5. #5
    qvqv is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    18
    Thanks davegri

    I am a very very new beginer.

    I tired it without the PersonID in AddressT table - no difference.

    I am getting so frustrated now

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Always put your code within code tags (# on forum toolbar) because a) it's always easier to read b)it stops the forum from inserting spaces at the 50 character points and c)it often helps with spotting issues if you copy/paste and don't type here.

    If your code really contains those special quotes it could be the problem - “1” Also could be that 1 is a number and you're treating it as text with those quotes. Moke123 eliminated them, probably assuming ID was a number but it may not be.

    I don't see the need for a junction table here. Wouldn't this work?
    Ignore the dots - I forgot to put table names in the first row.

    tblPerson . . . . . . . . tblAddresses
    PersonID Fname AddressID PersonIDfk Address
    1 Joe 1 1 12 Main St.
    2 Sam 2 1 25 John St
    3 Mary 3 2 5 Maple Ave
    4 3 55 Warren Ave


    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Here is a working example with proper keys, types, etc.

    qvqvMTM-davegri-v01.zip

    And the query results

    Click image for larger version. 

Name:	qvqv.png 
Views:	17 
Size:	6.9 KB 
ID:	44170

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Without the junction table
    PersonId Full_Name Address
    1 Rob Banks 111 Maple
    1 Rob Banks 444 Ash
    2 Rose Bush 222 Oak
    3 Mallard Drake 333 Pine
    4 Reme Fasolatido 444 Ash

    more than one person in the tblPerson can have stayed at the address.
    Two different people have lived at 444 Ash.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    qvqv is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    18
    Thank you everyone for al your help and support and davegri for the working example file.

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

Similar Threads

  1. Update Query Problem (Not an Updateable Query)
    By McArthurGDM in forum Queries
    Replies: 6
    Last Post: 02-19-2015, 11:25 AM
  2. Query problem with c#
    By udigold1 in forum Queries
    Replies: 1
    Last Post: 12-19-2010, 08:34 PM
  3. query problem i have a problem wi
    By maxx3 in forum Queries
    Replies: 0
    Last Post: 06-29-2009, 02:29 AM
  4. query problem
    By maxx3 in forum Queries
    Replies: 35
    Last Post: 05-29-2009, 04:07 PM
  5. Problem in Query
    By Bruno Trindade in forum Queries
    Replies: 4
    Last Post: 03-28-2009, 04:10 PM

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