Max query results

[MaRSMAN]

Table is Communion
fldCommunionID autonumber
fldCommunionDate date
fldCommunicant number relaated to tblSouls fldSoulID
I would like a query to show the groupby communicant and then select each
communicants max date and update that communionDate to a fldLastCommuned in
the tablequerySouls Extended
would this work best as a mudular VBA code I am not to good at doing VBA
code yet
Thanks in advance to ALL who help me out on this

 

[pietlinden]

Table is Communion
fldCommunionID  autonumber
fldCommunionDate    date
fldCommunicant    number       relaated to tblSouls  fldSoulID
I would like a query to show the groupby communicant and then select each
communicants max date and update that communionDate to a fldLastCommuned in
the tablequerySouls Extended
would this work best as a mudular VBA code I am not to good at doing VBA
code yet
Thanks in advance to ALL who help me out on this

Either I'm missing something or your question does not make sense.
Unless you're storing the "LastCommuned" date somewhere (WHY??? You
can use a SQL statement to calculate it for you on the fly!)

If you just calculate on the fly, you don't need any code at all.
What did you need to do with this? If you're going to do a mail merge
or similar, you can just create another query and include both the
ChurchMember table (or whatever you call it) and this totals query and
include other values. Michel Walsh has an article on this at
www.mvps.org/access

 

[MaRSMAN]

SQL is what i want
The first part is to get the max communion date for each churchmember from
thecommunion table the next step is to take the max communion date from
each church member and update the fld Lastcommuned that is located in the
churchmember table.
Hope this clarifies better also please note I am using Access 2007
An Example woould help me out

 

[PieterLinden via AccessMonster.com]

If you can calculate LastCommuned, why store it at all? You would be better
off just calculating it on the fly. Storing it is just asking for trouble...

Assuming you have a structure like

CREATE TABLE Communion(
ChurchMemberID INT NOT NULL,
CommunionDate DATE NOT NULL,
PRIMARY KEY (ChurchMemberID, CommunionDate)
)

doing the following will give you the last CommunionDate for each member.

SELECT ChurchMemberID, Max([CommunionDate])
FROM tblMember LEFT JOIN tblCommunion ON tblMember.MemberID = tblCommunion.
ChurchMemberID
ORDER BY ChurchMemberID
GROUP BY ChurchMemberID;

Then you can join that query back to the Members table on ChurchMemberID, and
get the member's name, address, and all that ...

(Hey, John... tell him why storing derived/calculated values is a bad idea...)