MS Access help!!!!! - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HUKD, you accept our cookie and privacy policy.
Get the HUKD app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit

MS Access help!!!!!

Abz Avatar
6y, 9m agoPosted 6 years, 9 months ago
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
Abz Avatar
6y, 9m agoPosted 6 years, 9 months ago
Options

All Comments

(4) Jump to unreadPost a comment
Comments/page:
#1
Have another field listing active/non active or current /not current. Then build only the active or current contacts into your query
#2
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).
#3
5757alan
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
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
#4
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

You don't need an account to leave a comment. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!