M$ Access Query - Query

[tips]

I have a M$ Access Database.

There are 2 main tables....one contains a persons name and address details.....the other is linked to this and contains various dates that the person attended.

I want to get a list of names and address for anyone who hasn't attended since 1st Feb 2008. Something like Where Date <= 01/02/2008 NOT > 01/02/2008

I am mega rusty with Access could anyone tell me the quickest and easiest way to do this.

THANKS

EDIT: I can get it to display the records using <#01/02/2008# I am unsure of the syntax to stop it including any that have been >#01/02/2008#

[LilacHarryQuinn]

Could do with details of both tables but it will be something on these lines :

SELECT * FROM DETAILS_TABLE OUTER JOIN DATE_TABLE ON DETAILS_TABLE.ID = DATE_TABLE.ID WHERE DATE<=#01/02/2008#

[tips]

Hi

This is the problem Using similar logic to the above (in my query I have Date <#01/02/2008#)

This does indeed show peoples data as required that have been before 01/02/2008, the problem is it will also show the person if they have been after this date. Basically I am trying to get a mailing list to send to anyone who hasn't attended in the past 3 months.

I need it to be <#01/02/2008 AND NOT BEEN > #01/02/2008#

But I don't know how to write this expression.

Edit: I have also tried Not >#01/02/2008# and this still doesn't get the desired results. PLEASE HELP!!!

[tips]

I am definately barking up the wrong tree. In the design table I am just putting
Not > #01/02/2008# all this does is remove the dates after this. I want it to NOT even display any of the persons data if they attended after this date.

Think I need to start again.

[LilacHarryQuinn]

I can't remember if Access supports this but you could try :

SELECT * FROM DETAILS_TABLE WHERE DETAILS_TABLE.ID NOT IN (SELECT DISTINCT ID FROM DATE_TABLE WHERE DATE>=#01-02-2008#)

[tips]

I haven't got a clue where I would put that. I am using the design view of the query.

[LilacHarryQuinn]

Change the view to SQL VIEW and type it in. To do this in Access you will notice, depending on how your toolbars are setup, that on the left hand-side underneath FILE there is an icon with a small arrow at the side that if you click will allow you to change views.

[SiE]

You need to create a query that picks up the max date for each student then put the criteria on that.

use the group by option in the query builder then choose max on the date attended.

[tips]

I will try that now..thanks

[tips]

I am obviously doing something completely wrong I have no idea where a "group by" option is.

I also dont know what a query builder is.

I know im going to give this PC a good trashing.

[LilacHarryQuinn]

The NOT IN query I posted before should give you the results you want tips.

[tips]

I have no idea what to do with it or where to put it. I would need a step by step dummies guide.

[LilacHarryQuinn]

Which version of Access are you using ?

[tips]

2002

[LilacHarryQuinn]

Here's how to get to SQL VIEW from DESIGN VIEW. A new window will open and you can type or cut and paste the query in, alter it to include your table and field names and then save and/or execute it.

[tips]

When I switch to SQL view I have loads of fields there that i just don't know what to do with.

SELECT [Infosheet 001].Date, [pokerresults 001].[Roll No / Name], [pokerresults 001].[House Number/Road], [pokerresults 001].District, [pokerresults 001].Town, [pokerresults 001].County, [pokerresults 001].Postcode
FROM [pokerresults 001] INNER JOIN [Infosheet 001] ON [pokerresults 001].[Roll No / Name] = [Infosheet 001].[Roll No / Name];


Thanks very much for trying to help me, Much appreciated. I think it is above my MS Access knowledge though, I give up.

[tips]

Just before I give up. Would you be up for me sending you a small copy of the database (with only a few records) to see if you could get it to do it. I could then transfer this to the bigger database ?

If not totally understand.

[LilacHarryQuinn]

Upload it as an attachment and I will have a look for you.

Just checked my options and you can email it to me through the board if you like.

[tips]

PM SENT...THANKS

[SiE]

click on the sigma sign on the query builders toolbar to get the total bar then you can choose max.