I am stuck with something I think should be so basic to a relational database and can only assume I am missing something.
OK, I have an items form and a many 2 many relationship with a contacts form. When an Item is bought I select the contact who has bought it and this appears on the items form.
Right, I now want to query a list of items bought by people who live in Manchester.
I create a new query. I set the record source to my sold items folder. I add a rule and go to select the field I want to query on - I'm looking for City on the Contacts form. Ok, so Contacts is listed there, but that's it. I can select it and set Contacts = something. But this is nonsense I don't have a value for the contacts form, I want to set a value for a field on the contacts form. This it appears is not possible. What I would expect it to do would now show me a list of the fields on the contacts form so I can select which one I want to query on.
This is one area where you are some what limited compared to SQL databases. Would need to think about best procedure. Normally you run the query on the form that contains the criteria that you are searching on and possibly have look up fields to fill in data from other forms (customer?). Your case may be a little different with date ranges and formatting information in to a email (that makes sense to the reader). I find it easier to create a couple of test forms and play with the tools BD supplies like multiple
relations, filtered record sets, record set formatting, etc and if stuck run a looping script to brute force a result.