Connections query to link animals together

[chronister]

Hello all.... Hope everyone is having a good night.



I am trying to wrap my head around a query. I have the following table w/ sample data....

connectionID | animal1ID | animal2ID | Notes

1 30 269 Lucy and Mia were surrendered together.

The animal1ID and animal2ID fields reference my Animals table with the following (important) fields

animalID | [Animal Name]

I have a subform on my main animals page called connections. I am wanting to run a query and find the other animals that the current one is connected to. For instance, (Mia is id 30, and Lucy is ID 269) when on Mia's page the animalID on the form is 30, I want the query to return 269 Lucy [tblConnections.Notes].

The animal may be listed in either animal1ID or animal2ID. Some animals may be linked to more than one animal such as a litter of cats, or multiple animals that are surrendered together.

I have tried a combinations of joins and WHERE IN statements and cannot find the right combination here.... any help would be greatly appreciated.

[chronister]

I have come up with this and it works outside of the form (in a stand alone query and prompts for the Forms![Master Data]![animalID] and works just fine, but when the query is in the subform, it is giving a prompt for animal1ID ... I can enter the test number of 56 (one of the animals I am using for testing) and it works just fine..... but it is not pulling that properly... what am I doing wrong?

SELECT Animals.animalID, Animals.[Animal Name], tblConnections.Notes FROM Animals INNER JOIN tblConnections ON Animals.animalID = tblConnections.animal2ID WHERE (((tblConnections.animal1ID)=Forms![Master Data]!animalID)) UNION ALL SELECT Animals.animalID, Animals.[Animal Name], tblConnections.Notes FROM Animals INNER JOIN tblConnections ON Animals.animalID = tblConnections.animal1ID WHERE (((tblConnections.animal2ID)=Forms![Master Data]!animalID));


Thanks in advance

[amrut]

Use the expression builder in query to get correct syntax to refer to subform. Like Forms!YourMainformName!YourSubformName.Form!Contro lName

[chronister]

I got it now. Thanks for help folks. I realized I had not linked the master field correctly and once I did that, it started functioning properly. Thanks.