Need Help With Criterias

  • Thread starter Thread starter Jone
  • Start date Start date
J

Jone

Hi everybody I have a table that has a few dates for every record, and I want
to make a query that will return only one date per record, and should be the
earliest date for that record.
What do I have to write in criteria?
 
Here's a generic example that uses a subquery to get the minimum date field
value for each "unique" value of the field that has multiple records with
the same value:

SELECT TableName.*
FROM TableName
WHERE TableName.DateField =
(SELECT Min(T.DateField)
FROM TableName AS T
WHERE T.DuplicateValueField =
TableName.DuplicateValueField);
 
What I posted is not VBA code; it's a generic SQL statement.

To make this happen in the query design view, add your table to the grid.
Select the fields that you want to display. If you don't select the date
field to display, add it to the grid and uncheck the Show box. Under the
date field, in the Criterion: box, type this (replace my generic names {
TableName, DateField, DuplicateValueField } with your real names):

(SELECT Min(T.DateField) FROM TableName AS T WHERE T.DuplicateValueField =
TableName.DuplicateValueField)
 
Hi I’m sorry I don’t know what to put where.
My table is called “PaymentForClasses†and the field name is called
“DatePaid†can please write the criteria
 
What are the names of all the fields in the table? Which field contains the
same value in multiple records (that is, which field identifies the parent
of the record)? We need to know this information if we're to assist you
further with writing the specific query that you seek.
 
Back
Top