selecting newer values

[Guest]

I don't know if this is even possible. I'm using Access 2000 with Windows XP. I have a list with three fields, name, number, and date. Names are always unique, but may share a number and/or a date. I want to run a query that shows me A) Original name, and B) any newer names (based on date) in the same list with matching numbers. These would preferably be in separate columns so I could easily pick out the older names. Find Duplicates gives me the data, but not in a format that I can easily use to separate old from new. Any help would be great. Thanks.

 

[Jeff Boyce]

Are you saying you want to see Names, sorted first by Number, then
(descending) by Date? Your query can do that.

 

[Guest]

not quite. I'll probably do a better job explaining if I illustrate what I hope the final results will look like

Original List
Column A: Nam
Column B: Numbe
Column C: Dat

Query Results
Column A: Older Nam
Column B: Number for Column
Column C: Date for Column
Column D: Newer Name (associated number matches number in Column B, newer date than Column C
Column E: Date for Column

I realize if I can do this it may result in duplicate entries in Column A if there is more than one newer match. That's OK. So far the only method that has come close is by separating into two lists using an arbitrary date (for instance, everything before 2001 in one list, everything since 2001 in another) and comparing, but that still leaves everything older to sort through manually.

 

[John Spencer (MVP)]

SELECT A.[Name], A.[Number], A.[Date],
B.[Number], B.[Date]
FROM Tablename as A INNER JOIN TableName as B
On A.[Name] = B.[Name]
WHERE A.[Date] < B.[Date]

Now, I don't have any idea if that will give you an updatable query.

 

[Guest]

Thanks for the input, but now I'll show how little I know about Access. I tried to lay out the five columns I want returned from the original list in Design View. I then entered the code you laid out under Column D, replacing "TableName" with the name of my table, and tried to run the query. It said the syntax was incorrect.

Since I obviously don't know nearly enough about this, would you be able to give it to me in the simplest possible terms - exactly what code goes in which fields, do I need a duplicate of my original table, how do I lay this out in design view, should I not have replaced "TableName", and anything else I might not be thinking of

Thanks a bunch for your help. I'm in way over my head with this

----- John Spencer (MVP) wrote: ----

SELECT A.[Name], A.[Number], A.[Date]
B.[Number], B.[Date
FROM Tablename as A INNER JOIN TableName as
On A.[Name] = B.[Name
WHERE A.[Date] < B.[Date

Now, I don't have any idea if that will give you an updatable query

peza wrote

 

[Gary Walter]

Hi peza,

PMFBI

Click on "Create Query in Design View"

In the Show Table dialog box,
click on your table,
click Add,
click on Add again,
and then click Close.

You should now show 2 copies of your table
in the query designer.

Right-mouse click on the left table
and choose Properties.
In the Alias row, type in
Older
then close the Properties dialog box.

Right-mouse click on the right table
and choose Properties.
In the Alias row, type in
Newer
then close the Properties dialog box.

I believe you want to join them
on the Number field.

Click and hold down on left table's
Number field
and "drag and drop"
over on right table's Number field.

You should now have a (join) line
connecting the 2 tables going
from Older.Number to Newer.Number
fields.

Drag and Drop Name field from
Older table down in to field row
of first column of grid.

Drag and Drop Number field from
Older table down in to field row
of second column of grid.

Drag and Drop Date field from
Older table down in to field row
of third column in grid.

Drag and Drop Name field from
Newer table down in to field row
of fourth cloumn in grid.

Drag and Drop Date field from
Newer table down in to field row
of fifth cloumn in grid.

All that is left to do is set the Date
Criteria. If I understand correctly
you want to show the records (matches)
from Newer table where

Newer.Datefield > Older.Datefield

So...
In Criteria row under Newer.Datefield column,
type in

Older.Datefield

(using your actual name of the "Datefield"
instead of "Datefield")

Apologies again for butting in.

Good luck,

Gary Walter

Thanks for the input, but now I'll show how little I know about Access. I tried to

lay out the five columns I want returned from the original list in Design View. I
then entered the code you laid out under Column D, replacing "TableName" with the
name of my table, and tried to run the query. It said the syntax was incorrect.

Since I obviously don't know nearly enough about this, would you be able to give it

to me in the simplest possible terms - exactly what code goes in which fields, do I
need a duplicate of my original table, how do I lay this out in design view, should I
not have replaced "TableName", and anything else I might not be thinking of?

Thanks a bunch for your help. I'm in way over my head with this.

----- John Spencer (MVP) wrote: -----

SELECT A.[Name], A.[Number], A.[Date],
B.[Number], B.[Date]
FROM Tablename as A INNER JOIN TableName as B
On A.[Name] = B.[Name]
WHERE A.[Date] < B.[Date]

Now, I don't have any idea if that will give you an updatable query.

Column A: Name
Column B: Number
Column C: Date
Column A: Older Name
Column B: Number for Column A
Column C: Date for Column A
Column D: Newer Name (associated number matches number in Column B, newer date than Column C)
Column E: Date for Column D

if there is more than one newer match. That's OK. So far the only method that has
come close is by separating into two lists using an arbitrary date (for instance,
everything before 2001 in one list, everything since 2001 in another) and comparing,
but that still leaves everything older to sort through manually.

 

[John Spencer (MVP)]

Gary Walter,

Thanks for butting in. I was unavailable till this afternoon. The ability to
have multiple people answering questions is a good one. Sometimes a better
solution, sometimes a better explanation, sometimes a quicker response.

Peza,
I hope this answered your problem.

Hi peza,

PMFBI

Click on "Create Query in Design View"

In the Show Table dialog box,
click on your table,
click Add,
click on Add again,
and then click Close.

You should now show 2 copies of your table
in the query designer.

Right-mouse click on the left table
and choose Properties.
In the Alias row, type in
Older
then close the Properties dialog box.

Right-mouse click on the right table
and choose Properties.
In the Alias row, type in
Newer
then close the Properties dialog box.

I believe you want to join them
on the Number field.

Click and hold down on left table's
Number field
and "drag and drop"
over on right table's Number field.

You should now have a (join) line
connecting the 2 tables going
from Older.Number to Newer.Number
fields.

Drag and Drop Name field from
Older table down in to field row
of first column of grid.

Drag and Drop Number field from
Older table down in to field row
of second column of grid.

Drag and Drop Date field from
Older table down in to field row
of third column in grid.

Drag and Drop Name field from
Newer table down in to field row
of fourth cloumn in grid.

Drag and Drop Date field from
Newer table down in to field row
of fifth cloumn in grid.

All that is left to do is set the Date
Criteria. If I understand correctly
you want to show the records (matches)
from Newer table where

Newer.Datefield > Older.Datefield

So...
In Criteria row under Newer.Datefield column,
type in

Older.Datefield

(using your actual name of the "Datefield"
instead of "Datefield")

Apologies again for butting in.

Good luck,

Gary Walter

Thanks for the input, but now I'll show how little I know about Access. I tried to

lay out the five columns I want returned from the original list in Design View. I
then entered the code you laid out under Column D, replacing "TableName" with the
name of my table, and tried to run the query. It said the syntax was incorrect.

Since I obviously don't know nearly enough about this, would you be able to give it

to me in the simplest possible terms - exactly what code goes in which fields, do I
need a duplicate of my original table, how do I lay this out in design view, should I
not have replaced "TableName", and anything else I might not be thinking of?

Thanks a bunch for your help. I'm in way over my head with this.

----- John Spencer (MVP) wrote: -----

SELECT A.[Name], A.[Number], A.[Date],
B.[Number], B.[Date]
FROM Tablename as A INNER JOIN TableName as B
On A.[Name] = B.[Name]
WHERE A.[Date] < B.[Date]

Now, I don't have any idea if that will give you an updatable query.

not quite. I'll probably do a better job explaining if I illustrate what I hope the final results will look like:
Original List:
Column A: Name
Column B: Number
Column C: Date
Query Results:
Column A: Older Name
Column B: Number for Column A
Column C: Date for Column A
Column D: Newer Name (associated number matches number in Column B, newer date than Column C)
Column E: Date for Column D
I realize if I can do this it may result in duplicate entries in Column A

if there is more than one newer match. That's OK. So far the only method that has
come close is by separating into two lists using an arbitrary date (for instance,
everything before 2001 in one list, everything since 2001 in another) and comparing,
but that still leaves everything older to sort through manually.

 

[Guest]

Thanks to all for butting in. That works perfectly

----- John Spencer (MVP) wrote: ----

Gary Walter

Thanks for butting in. I was unavailable till this afternoon. The ability t
have multiple people answering questions is a good one. Sometimes a bette
solution, sometimes a better explanation, sometimes a quicker response

Peza
I hope this answered your problem

Gary Walter wrote

Hi peza
PMFB
Click on "Create Query in Design View
In the Show Table dialog box

click on your table
click Add
click on Add again
and then click Close

You should now show 2 copies of your tabl in the query designer
Right-mouse click on the left tabl

and choose Properties
In the Alias row, type i
Olde
then close the Properties dialog box

Right-mouse click on the right tabl

and choose Properties
In the Alias row, type i
Newe
then close the Properties dialog box

I believe you want to join the on the Number field
Click and hold down on left table'

Number fiel
and "drag and drop
over on right table's Number field

You should now have a (join) lin

connecting the 2 tables goin
from Older.Number to Newer.Numbe
fields

Drag and Drop Name field fro

Older table down in to field ro
of first column of grid

Drag and Drop Number field fro

Older table down in to field ro
of second column of grid

Drag and Drop Date field fro

Older table down in to field ro
of third column in grid

Drag and Drop Name field fro

Newer table down in to field ro
of fourth cloumn in grid

Drag and Drop Date field fro

Newer table down in to field ro
of fifth cloumn in grid

All that is left to do is set the Dat

Criteria. If I understand correctl
you want to show the records (matches
from Newer table wher

Newer.Datefield > Older.Datefiel
So..

In Criteria row under Newer.Datefield column
type i

(using your actual name of the "Datefield instead of "Datefield"
Apologies again for butting in
Good luck
Gary Walte
"peza" <[email protected]> wrot
Thanks for the input, but now I'll show how little I know about Access. I tried t

lay out the five columns I want returned from the original list in Design View.
then entered the code you laid out under Column D, replacing "TableName" with th
name of my table, and tried to run the query. It said the syntax was incorrectto me in the simplest possible terms - exactly what code goes in which fields, do
need a duplicate of my original table, how do I lay this out in design view, should
not have replaced "TableName", and anything else I might not be thinking of

Thanks a bunch for your help. I'm in way over my head with this
----- John Spencer (MVP) wrote: ----
SELECT A.[Name], A.[Number], A.[Date]

B.[Number], B.[Date
FROM Tablename as A INNER JOIN TableName as
On A.[Name] = B.[Name
WHERE A.[Date] < B.[Date

Now, I don't have any idea if that will give you an updatable query
peza wrote
not quite. I'll probably do a better job explaining if I illustrate what hope the final results will look like
Original List
Column A: Nam
Column B: Numbe
Column C: Dat
Query Results
Column A: Older Nam
Column B: Number for Column
Column C: Date for Column
Column D: Newer Name (associated number matches number in Column B, newer date than Column C)
Column E: Date for Column D
I realize if I can do this it may result in duplicate entries in Column A

if there is more than one newer match. That's OK. So far the only method that has
come close is by separating into two lists using an arbitrary date (for instance,
everything before 2001 in one list, everything since 2001 in another) and comparing,
but that still leaves everything older to sort through manually.