"DMax" Attendance Query Problems

[DCXtreme]

Hi there, I have created a gym database, within it you can push a button each time a member attends and the date they attended is stored in a table "Attendance", multiple values are stored in this table because i want to see each time the member attends.

The problem comes when I went to create a query to pull out members who have not attended for 2 weeks or more. I am not quite sure how to do this, I think it is by putting a DMax criteria on the "AttendDate" field in the attendance table when i create a query but I am quite lost!

Any help greatly appreciated.

Jon

 

[DCXtreme]

Nobody has any idea how to do this?

 

[databasedonr]

I don't think you need to do DMAX, just write a query like this:

SELECT tblMember.MemberID, tblMember.AttendDate
FROM tblMember
WHERE (((tblMember.AttendDate)>Date()-14));

Assuming tblMember is where your data is, MemberID uniquely identifies your members, and AttendDate is the date they came in. This runs against the current date (so if you run this every day ... ). It would be simple to include names, etc. etc. into the query to bring back other relevant info.

 

[DCXtreme]

I don't think you need to do DMAX, just write a query like this:

SELECT tblMember.MemberID, tblMember.AttendDate
FROM tblMember
WHERE (((tblMember.AttendDate)>Date()-14));

Assuming tblMember is where your data is, MemberID uniquely identifies your members, and AttendDate is the date they came in. This runs against the current date (so if you run this every day ... ). It would be simple to include names, etc. etc. into the query to bring back other relevant info.

The problem is that the attendance table records *every* date the member came in, therefore I need to select the more current date they attended and compare this to todays date using the query you wrote above.

 

[Fear Naught]

Why not try the following (plagerising the SQL from "databasedonr")

SELECT tblMember.MemberID, MAX(tblMember.AttendDate)
FROM tblMember
WHERE (((tblMember.AttendDate)>Date()-14))
GROUP BY tblMember.Memberid;

 

[DCXtreme]

Why not try the following (plagerising the SQL from "databasedonr")

SELECT tblMember.MemberID, MAX(tblMember.AttendDate)
FROM tblMember
WHERE (((tblMember.AttendDate)>Date()-14))
GROUP BY tblMember.Memberid;

Do i put this in the query builder under criteria or somewhere else? Sorry I am really bad when it comes to building queries!

in reply to DatabaseDonr I already have the relevant info (name, address etc) on the query im just trying to figure out this last thing!

 

[Fear Naught]

Yes build a query using that SQL and then create a report based on the query.

 

[DCXtreme]

I cant seem to get this to work.

P.S My members table is "Customers" and my attendance table (where the AttendDate is stored, with MembershipNo as a FKey) is "Attendance"

I've tried changing your code while changing your table names for my own but that doesnt work, infact I get a syntax error when I put it in the criteria field of "AttendDate".

I'm completly stumped on this, Sorry!

Any further help would be greatly appreciated, my experience with Queries is very limited!

Edit:

SELECT Attendance.membershipNo, MAX(Attendance.AttendDate)
FROM Attendance
WHERE (((Attendance.AttendDate)>Date()-14))
GROUP BY Attendance.membershipNo;

Doesnt work for me either I still get the same syntax error

 

[databasedonr]

You need a join in your query to have the two tables work together. It will look something like this:

SELECT Customer.membershipNo, MAX(Attendance.AttendDate) As MaxDate
FROM Customer INNER JOIN Attendance ON Customer.membershipno = Attendance.membershipno
HAVING (((Max(Attendance.AttendDate))>Date()-14));

No guarantees, but that should be pretty close.

 

[Fear Naught]

This won't work databasedonr becasue you missed the GROUP BY clause. The SQL should therefore be

SELECT Customer.membershipNo, MAX(Attendance.AttendDate) As MaxDate
FROM Customer INNER JOIN Attendance ON Customer.membershipno = Attendance.membershipno
GROUP BY Customer.membershipNo
HAVING (((Max(Attendance.AttendDate))>Date()-14));

 

[databasedonr]

Fear Naught, how right you are - a slip of the key (or something like that) when copying over. Hopefully, this works for DCXtreme

 

[DCXtreme]

I know that code "should" work but I keep getting a syntax error after I put it in.

Can you explain exactly where I have to enter it? Sorry about this.

 

[Fear Naught]

Paste it into the SQL page of a query.

 

[DCXtreme]

I did that, with a UNION SQL page and it didnt work

I am REALLY stumped here on something that should be simple

 

[Fear Naught]

Why would you want a UNION query?? Do things simple and build on them. I always build queries etc in steps and make sure each step returns the result I want and then continue to build upon that. That at least gives me an idea where the error is (if indeed there is one)

 

[DCXtreme]

Well I dont think I have done anything wrong with the query. I've attached the database now, its the AWOL members query, I've built it step by step and it doesnt seem to be working!

 

[Fear Naught]

You had the criteria set to select on those with the last attend date to exactly 14 days ago and as not record met that criteria the query returned no data. I have now changed it so that it shows all that last ettended 14 days or more ago. This now returns one row (which looking at your data is correct) The SQL is:

SELECT Customer.membershipNo, Customer.firstName, Customer.lastName, Customer.address, Customer.county, Customer.postcode, Customer.ActiveMember, Max(Attendance.AttendDate) AS MaxOfAttendDate
FROM Customer INNER JOIN Attendance ON Customer.membershipNo = Attendance.MembershipNo
GROUP BY Customer.membershipNo, Customer.firstName, Customer.lastName, Customer.address, Customer.county, Customer.postcode, Customer.ActiveMember
HAVING (((Customer.ActiveMember)="yes") AND ((Max(Attendance.AttendDate))<=Date()-14));

Note difference in the HAVING clause which has changed from:

......=Date()-14));

to:

.....<=Date()-14));

HTH

 

[DCXtreme]

Thanks alot worked perfectly!