Pull Most Recent Date

[Warren Phillips]

Hi:

I have a datebase with 3 fields (Job#, Check Date, Check Amount). There
will be thousands of records for each Job#

I am trying to determine if there is a way to do the following:

Get a list that gives me 1 record for each Job#.....that includes the most
recent Check Date for that Job#

Something would have to look at a date entered as part of the QUERY code and
then compare each record to this date...and only return the record where the
Date field is "closest" to this value.

Management wants to know the most recent check date that was generated by AP
for several thousand jobs....across an entire year.

 

[Maxz]

SELECT tblxxxx.Job, Last(tbxxxx.Date) AS LastOfDate, tblxxxx.CQAmount
FROM tblxxxx
GROUP BY tblxxxx.Job, tblxxxx.CQAmount;

Generic Groupby function. Give it a try

 

[KARL DEWEY]

I hear tell the 'last' pulls the last record, not the latest date so try this

 

[Warren Phillips]

This sounds like exactly what I need. I must be missing something though in
translating this to my QUERY. Can you double check

Below is the version of your code....ie....with "MY" field names
______________________________________________________________

SELECT Job#, Max(Check Date) AS LastOfDate, Check Amount FROM jcppst GROUP
BY Job#, Check Amount

______________________________________________________________

I am getting a syntax error.

Can you also confirm that I would just put this code in the "CRITERIA"
section of the QUERY GRID.....OR......do I put this code into a MODULE (I am
less familiar with this process)

IF...the code needs to be within an actual QUERY Grid, do I pull the fields
being used (Job#, Check Date, Check Amount" into the QUERY grid and just add
the code as a 4th "Field Name"

Table name is jcppst

Thank you very much for the help on this
Warren Phillips

 

[Lord Kelvan]

the syntax error is due to the # or the spaces in yoru field names you
need to use []

SELECT [Job#], Max([Check Date]) AS LastOfDate, [Check Amount]
FROM jcppst
GROUP BY [Job#], [Check Amount]

try that

regards
kelvan

 

[Warren Phillips]

Can anyone offer a suggestion on "where" I put this code.

I have tried a MODULE....and I have tried the CRITERIA and FIELD GRIDs
within a QUERY. I get syntax errors each time I try to get our of the QUERY.

IF....the code needs to go into a MODULE....I am less familiar with this.

I am using Access 2007 and could really use some help on how to do this

Thanks

Warren

 

[Lord Kelvan]

o sorry

i have no idea how to get to it in access 2007 because it is different
but look around for SQL view when you are building a query then paste
it in there

Regards
Kelvan

 

[pietlinden]

This sounds like exactly what I need.  I must be missing something though in
translating this to my QUERY.  Can you double check

Below is the version of your code....ie....with "MY" field names
______________________________________________________________

SELECT Job#, Max(Check Date) AS LastOfDate, Check Amount FROM jcppst GROUP
BY Job#, Check Amount

______________________________________________________________

I am getting a syntax error.  

I must be going blind. Any fields with spaces in them must be in
square brackets.


SELECT Job#, Max([Check Date]) AS LastOfDate, [Check Amount] FROM
jcppst GROUP
BY Job#, [Check Amount]

 

[pietlinden]

Can anyone offer a suggestion on "where" I put this code.

I have tried a MODULE....and I have tried the CRITERIA and FIELD GRIDs
within a QUERY.  I get syntax errors each time I try to get our of the QUERY.

IF....the code needs to go into a MODULE....I am less familiar with this.

Create a new query.
Click the Design button on the top left. (Can't remember where they
put anything in A2007, so you may have to look for it.)
from the dropdown, choose SQL.

Then you'll get a SQL window and you can paste it in.

 

[DH]

I tried adapting this code for a table listing contacts associated with a
particular order (fk_distrib_id). I must be doing something wrong - I get
multiple records from earliest to latest. I'm trying to identify which
contact, for a particular fk_distrib_id is the most recently entered:

SELECT tb_Contacts.fk_Distrib_id, Max(tb_Contacts.date_entered) AS
LastOfDate, tb_Contacts.contact_name
FROM tb_Contacts
GROUP BY tb_Contacts.fk_Distrib_id, tb_Contacts.contact_name;

Any help appreciated!

DH

 

[KARL DEWEY]

Are the ID and names different or the same?

 

[DH]

There are multiple instances of the same ID in this Contacts table (as the
fk_distrib_ID is the foreign key linking to a master table, we'll call TB_
Distribution) and there may be some of the same names as well - this is an
audit log of changing contacts for a given Distribution record.

So I need a query that will look at the Contacts table and find the contact
closest to the current date where contacts.type = "external", and return that
value only for a particular Distribution (where fk_distrib_id is a value
passed from the main form to the subform.)

DH

 

[KARL DEWEY]

Save this query as LastContact ---
SELECT tb_Contacts.fk_Distrib_id, Max(tb_Contacts.date_entered) AS LastOfDate
FROM tb_Contacts
GROUP BY tb_Contacts.fk_Distrib_id;

SELECT tb_Contacts.fk_Distrib_id, tb_Contacts.contact_name,
LastContact.LastOfDate
FROM tb_Contacts INNER JOIN LastContact ON tb_Contacts.date_entered =
LastContact.LastOfDate;