Select Query

[Greg]

I am new at the use of access. I am using Access 2000 on a Windows 2000
system.
My question is (if possible). Select records with the greatest date field.
Example:

Table Invoice:

Telephone Number(Account Number)
Invoice Date(MM/DD/YR
Current Charges
Local Service charges

 

[tina]

turn your query into a Totals query. Group By the telephone number and
choose the Last option for the invoice date. if you search on "totals query"
in Access Help, you should find an explanation of how to set it up. take a
look, and post back if you run into problems.

hth

 

[Gary Walter]

"Greg"wrote:

I am new at the use of access. I am using Access 2000 on a Windows 2000
system.
My question is (if possible). Select records with the greatest date field.
Example:

Table Invoice:

Telephone Number(Account Number)
Invoice Date(MM/DD/YR
Current Charges
Local Service charges
.
.
.
Toll Charges


Example with values.

111-111-1111,01/01/2006,$25.00,$48.00,...........
111-111-1111,02/01/2006,$39.00,$28.00,...........
222-222-2222,02/02/2006,$77.00,$44,00,...........
222-222-2222,03/01/2006,$44.44,$55.55,...........
333-333-3333,01/02/2006,$44.00,$66.66,...........
333-333-3333,02/01/2006,$44.00,$66.66,...........
333-333-3333,02/22/2006,$55.55,$66.66
333-333-3333,03/03/2006,$44.00,$66.66,...........

The results I am trying to get to are

111-111-1111,02/01/2006,$39.00,$28.00,...........
222-222-2222,03/01/2006,$44.44,$55.55,...........
333-333-3333,03/03/2006,$44.00,$66.66,...........

Primary Key is Telephone Number and Invoice Date.

PMFBI

In addition to Tina's sage advice, one thing that may
help is to mentally slow down the decision making of
whether to return a record in your result, i.e.,

look at first record in table...
for all the records in the table with this telenum,
is this the latest date?
if so, return it...

look at next record in table...
for all the records in the table with this telenum,
is this the latest date?
if so, return it...

etc......

so your WHERE clause (the criteria for whether
you will return each specific record) looks like
it will need to be a subquery that finds the latest
date for the telenum you are looking at.

for example, if I'm looking at a record where telenum
is '111-111-1111'... a subquery to find latest date

SELECT
Max(t.[Invoice Date])
FROM
[Table Invoice] As t
WHERE
t.[Telephone Number] = "111-111-1111"

if we were to stick this subquery into a WHERE
clause

SELECT
M.[Telephone Number],
M.[Invoice Date],
M.[Current Charges],
M.[Local Service charges],
M.[Toll Charges]
FROM
[Table Invoice] As M
WHERE
M.[Invoice Date] =
(SELECT
Max(t.[Invoice Date])
FROM
[Table Invoice] As t
WHERE
t.[Telephone Number] = "111-111-1111")

it should return the record for the latest date
of telenum = '111-111-1111'

to make this subquery work for all telenum's
in original query, we "correlate" back to the
original query by replacing the actual value
('111-111-1111') in the subquery with the
main query's field name (M.[Telephone Number]).

SELECT
M.[Telephone Number],
M.[Invoice Date],
M.[Current Charges],
M.[Local Service charges],
M.[Toll Charges]
FROM
[Table Invoice] As M
WHERE
M.[Invoice Date] =
(SELECT
Max(t.[Invoice Date])
FROM
[Table Invoice] As t
WHERE
t.[Telephone Number] = M.[Telephone Number]);

to start from scratch in query design,

add your table..

right-mouse click on table and choose "Properties"

change its "Alias" to "M"
(this could be anything you want,
to me, "M" stands for "Main query")

double-click on head of table to select all fields

drag and drop this selection down to a field row
in grid

under column in grid for [Invoice Date],
in the "Criteria" row, type out the correlated
subquery all as one line, starting and ending
it with parentheses...

(SELECT
Max(t.[Invoice Date])
FROM
[Table Invoice] As t
WHERE
t.[Telephone Number] = M.[Telephone Number])

again, in the "slowed-down" process of deciding
whether to return a record from the main query,
the subquery will "correlate" back to the main query
to get the current telenum, use that telenum to determine
the latest (max) date out of all records for that telenum,
then the record will be returned if the date for the
current record is equal to the latest (max) date for
that telenum....

sometimes that "slow-down" thinking helps to
understand SQL....

 

[John Spencer]

Two query solution:
First query, finds the latest (Max) date for each telephone number
Using the query grid
-- Add your table to the query
-- Add Telephone Number and Invoice Date
-- Select View: Totals from the menu
-- Change GROUP BY in the Total row to MAX under Invoice date.
-- Save this as qLastInvoiceDate

Second query, use the wizard to build a query based on the Invoice table and
the qLastInvoiceDate query. Join the two on Telephone Number AND Invoice
Date and MaxOfInvoiceDate.

You can build this second query if you want instead of using the wizard,
-- start a new query
-- Add Invoice Table to the query grid
-- Add qLastInvoiceDate (from queries) to the query grid
-- set up relationship by dragging from Telephone Number to Telephone Number
-- set second relationship by dragging from InvoiceDate to MaxOfInvoiceDate
-- Add the fields you want to see from Invoice Table.
-- Save query.