Groups

    MS Access help!!!!!

    Afternoon All,

    I'm hoping some-one on here can help me with some Microsoft Access issues.

    I am trying to create a query from 2 tables. One table holds organisation details and the second table holds contact details...i have create a one-to-many relationship...as one centre will have many contacts....

    now what I'm trying to do is set up a query to create a mailing list, but the problem i'm having is that if an organisation has more the one person in the same job it is returning both contacts. But i want it to only return the latest contact (from a field called Date Added). I've tried using the max and last function....but it just doesnt seem to work!

    Any ideas anyone...

    Rep left for any help

    4 Comments

    Have another field listing active/non active or current /not current. Then build only the active or current contacts into your query

    What's the query you got so far? You probably will need to use a subquery to to find out the last person that got added using the max(date) or max(id).

    Original Poster

    5757alan;8185526

    Have another field listing active/non active or current /not current. … Have another field listing active/non active or current /not current. Then build only the active or current contacts into your query



    well the problem is from the user perspective....once a new contact is added it doesnt necessarily mean that the older contact is "inactive"....and if I add in an extra field then chances are user wont update those fields when adding in new contacts...so when running mailing lists i will still be getting duplicates...

    Spriggan;8187050

    What's the query you got so far? You probably will need to use a subquery … What's the query you got so far? You probably will need to use a subquery to to find out the last person that got added using the max(date) or max(id).



    ok so the first query selects the fields from a form (a range of drop down menu's)...

    then the next sub query will use the first query to link to the contacts table...which is also linked to main form to allow the user to select the job title they want to target....

    u see when i add use the max function it will give me the latest date a contact was added....but not by the organisation...just in general...

    hope that makes sense

    You need to do a "Group By" when using the MAX aggregate function so it groups by each organisation. You just need to make sure you include the organisation name and the max(date) in your selection. Thinking about it you might not even need the subquery.
    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. Your dream gaming set up?2658
      2. Labour MP Naz Shah retweeted calling for abused white girls to shut their m…1332
      3. Surprise! The HUKD Summer Flamedeer Hunt 2017 **OFFICIAL THREAD** (trading …277960
      4. word association Game72944191

      See more discussions