most current

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I should've been able too figure this out on my own, but I can't seem to get
any of the criteria I've seen here to work right. Anyways this should be a
simple one for the pros. I've got a monster table and I need to run a query
to return only the most recent records. It reads something like this:

AutoNumber BuildingNumber Date [fields 3-240]
[AutoNumber] a1 6/6/06 [each
field's value]
[AutoNumber] a1 12/6/06 [each
field's value]
[AutoNumber] a2 6/6/06 [each
field's value]
[AutoNumber] a2 9/18/06 [each
field's value]
etc. for about 1800 more

Any ideas on the criteria I could use? Do I need to make multiple tables to
do it?
 
Assumption that you want the LATEST Date for each building

Solution
Build and save a query that gets the building number and the latest date
(use Max)
Use that query and your table in another query, joining the table and query
on building number and dates

In SQL view that would look like

Query 1:
SELECT BuildingNumber, Max([Date]) as LatestDate
FROM YourTable
GROUP BY BuildingNumber

Query 2:
SELECT YourTable.*
FROM YourTable INNER JOIN Query1
ON YourTable.BuildingNumber = Query1.BuildingNumber
AND YourTable.[Date] = Query1.LatestDate


All in one query: (only possible if your table and field names do NOT
contain spaces)
SELECT YourTable.*
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate
 
Wow, that works great, but now it looks like my records are locked out from
editing on the form I'm using.

John Spencer said:
Assumption that you want the LATEST Date for each building

Solution
Build and save a query that gets the building number and the latest date
(use Max)
Use that query and your table in another query, joining the table and query
on building number and dates

In SQL view that would look like

Query 1:
SELECT BuildingNumber, Max([Date]) as LatestDate
FROM YourTable
GROUP BY BuildingNumber

Query 2:
SELECT YourTable.*
FROM YourTable INNER JOIN Query1
ON YourTable.BuildingNumber = Query1.BuildingNumber
AND YourTable.[Date] = Query1.LatestDate


All in one query: (only possible if your table and field names do NOT
contain spaces)
SELECT YourTable.*
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate

MikeLostinWoods said:
I should've been able too figure this out on my own, but I can't seem to
get
any of the criteria I've seen here to work right. Anyways this should be
a
simple one for the pros. I've got a monster table and I need to run a
query
to return only the most recent records. It reads something like this:

AutoNumber BuildingNumber Date [fields
3-240]
[AutoNumber] a1 6/6/06 [each
field's value]
[AutoNumber] a1 12/6/06 [each
field's value]
[AutoNumber] a2 6/6/06 [each
field's value]
[AutoNumber] a2 9/18/06 [each
field's value]
etc. for about 1800 more

Any ideas on the criteria I could use? Do I need to make multiple tables
to
do it?
Click to expand...
Click to expand...
 
True, if you want to edit some values then you will have to write the query
differently. I think the following MIGHT work for you

SELECT X.*
FROM YOURTable as X
WHERE X.AutoNumber IN
(SELECT YourTable.AutoNumber
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate)

MikeLostinWoods said:
Wow, that works great, but now it looks like my records are locked out
from
editing on the form I'm using.

John Spencer said:
Assumption that you want the LATEST Date for each building

Solution
Build and save a query that gets the building number and the latest date
(use Max)
Use that query and your table in another query, joining the table and
query
on building number and dates

In SQL view that would look like

Query 1:
SELECT BuildingNumber, Max([Date]) as LatestDate
FROM YourTable
GROUP BY BuildingNumber

Query 2:
SELECT YourTable.*
FROM YourTable INNER JOIN Query1
ON YourTable.BuildingNumber = Query1.BuildingNumber
AND YourTable.[Date] = Query1.LatestDate


All in one query: (only possible if your table and field names do NOT
contain spaces)
SELECT YourTable.*
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate

MikeLostinWoods said:
I should've been able too figure this out on my own, but I can't seem to
get
any of the criteria I've seen here to work right. Anyways this should
be
a
simple one for the pros. I've got a monster table and I need to run a
query
to return only the most recent records. It reads something like this:

AutoNumber BuildingNumber Date [fields
3-240]
[AutoNumber] a1 6/6/06
[each
field's value]
[AutoNumber] a1 12/6/06 [each
field's value]
[AutoNumber] a2 6/6/06
[each
field's value]
[AutoNumber] a2 9/18/06 [each
field's value]
etc. for about 1800 more

Any ideas on the criteria I could use? Do I need to make multiple
tables
to
do it?
Click to expand...
Click to expand...
Click to expand...
 
Nope, no luck there. It's odd. The query itself displays the information
I'm looking for, but now, on the form, it only returns one record (a new
one). The fields all display default values, and can be edited. The combo
box I'm using for accessing a specific record does return the building
numbers correctly, but doesn't call up any other information.

John Spencer said:
True, if you want to edit some values then you will have to write the query
differently. I think the following MIGHT work for you

SELECT X.*
FROM YOURTable as X
WHERE X.AutoNumber IN
(SELECT YourTable.AutoNumber
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate)

MikeLostinWoods said:
Wow, that works great, but now it looks like my records are locked out
from
editing on the form I'm using.

John Spencer said:
Assumption that you want the LATEST Date for each building

Solution
Build and save a query that gets the building number and the latest date
(use Max)
Use that query and your table in another query, joining the table and
query
on building number and dates

In SQL view that would look like

Query 1:
SELECT BuildingNumber, Max([Date]) as LatestDate
FROM YourTable
GROUP BY BuildingNumber

Query 2:
SELECT YourTable.*
FROM YourTable INNER JOIN Query1
ON YourTable.BuildingNumber = Query1.BuildingNumber
AND YourTable.[Date] = Query1.LatestDate


All in one query: (only possible if your table and field names do NOT
contain spaces)
SELECT YourTable.*
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate

message I should've been able too figure this out on my own, but I can't seem to
get
any of the criteria I've seen here to work right. Anyways this should
be
a
simple one for the pros. I've got a monster table and I need to run a
query
to return only the most recent records. It reads something like this:

AutoNumber BuildingNumber Date [fields
3-240]
[AutoNumber] a1 6/6/06
[each
field's value]
[AutoNumber] a1 12/6/06 [each
field's value]
[AutoNumber] a2 6/6/06
[each
field's value]
[AutoNumber] a2 9/18/06 [each
field's value]
etc. for about 1800 more

Any ideas on the criteria I could use? Do I need to make multiple
tables
to
do it?
Click to expand...
Click to expand...
Click to expand...
 
So to trouble shoot let's break this down into steps.

Step one: Does the following return any data? Is it correct?

SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber

Step Two: Does the following return any data? And if so are the Autonumbers
valid ones for what you are trying to get?
SELECT YourTable.AutoNumber
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate)

Step Three: IF the above worked, then this should work unless things have
gotten too complex.
SELECT X.*
FROM YOURTable as X
WHERE X.AutoNumber IN
(SELECT YourTable.AutoNumber
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate)

If the query in step 2 works, but step 3 fails then try saving the query in
step 2 and using that in the where clause of step 3

SELECT *
FROM YourTable
WHERE YourTable.Autonumber =
(SELECT Autonumber FROM TheSavedQuery)

John Spencer said:
True, if you want to edit some values then you will have to write the
query differently. I think the following MIGHT work for you

SELECT X.*
FROM YOURTable as X
WHERE X.AutoNumber IN
(SELECT YourTable.AutoNumber
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate)

MikeLostinWoods said:
Wow, that works great, but now it looks like my records are locked out
from
editing on the form I'm using.

John Spencer said:
Assumption that you want the LATEST Date for each building

Solution
Build and save a query that gets the building number and the latest date
(use Max)
Use that query and your table in another query, joining the table and
query
on building number and dates

In SQL view that would look like

Query 1:
SELECT BuildingNumber, Max([Date]) as LatestDate
FROM YourTable
GROUP BY BuildingNumber

Query 2:
SELECT YourTable.*
FROM YourTable INNER JOIN Query1
ON YourTable.BuildingNumber = Query1.BuildingNumber
AND YourTable.[Date] = Query1.LatestDate


All in one query: (only possible if your table and field names do NOT
contain spaces)
SELECT YourTable.*
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate

message I should've been able too figure this out on my own, but I can't seem
to
get
any of the criteria I've seen here to work right. Anyways this should
be
a
simple one for the pros. I've got a monster table and I need to run a
query
to return only the most recent records. It reads something like this:

AutoNumber BuildingNumber Date [fields
3-240]
[AutoNumber] a1 6/6/06 [each
field's value]
[AutoNumber] a1 12/6/06
[each
field's value]
[AutoNumber] a2 6/6/06 [each
field's value]
[AutoNumber] a2 9/18/06
[each
field's value]
etc. for about 1800 more

Any ideas on the criteria I could use? Do I need to make multiple
tables
to
do it?
Click to expand...
Click to expand...
Click to expand...
 
in table view:
Query1 returns correct Building and Dates.
Query2 returns correct AutoNumber
Query3 returns all current records with all fields correctly
Query3a displays a message stating it'll only return one record and doesn't
open.

Both Query3 and 3a when assigned as the Record Source on the Form Property
only return 1 (new) record with default data.

Here is the SQL for 3.1 Did I make any errors?
SELECT *
FROM Inspections
WHERE Inspections.Autonumber =
(SELECT Autonumber FROM Query2) IN
(SELECT Inspections.AutoNumber
FROM Inspections INNER JOIN
(SELECT Inspections.BuildingNumber, Max(Inspections.Date) as LatestDate
FROM Inspections
GROUP BY BuildingNumber) as Temp
So to trouble shoot let's break this down into steps.

Step one: Does the following return any data? Is it correct?

SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber

Step Two: Does the following return any data? And if so are the Autonumbers
valid ones for what you are trying to get?
SELECT YourTable.AutoNumber
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate)

Step Three: IF the above worked, then this should work unless things have
gotten too complex.
SELECT X.*
FROM YOURTable as X
WHERE X.AutoNumber IN
(SELECT YourTable.AutoNumber
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate)

If the query in step 2 works, but step 3 fails then try saving the query in
step 2 and using that in the where clause of step 3

SELECT *
FROM YourTable
WHERE YourTable.Autonumber =
(SELECT Autonumber FROM TheSavedQuery)

John Spencer said:
True, if you want to edit some values then you will have to write the
query differently. I think the following MIGHT work for you

SELECT X.*
FROM YOURTable as X
WHERE X.AutoNumber IN
(SELECT YourTable.AutoNumber
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate)

MikeLostinWoods said:
Wow, that works great, but now it looks like my records are locked out
from
editing on the form I'm using.

:

Assumption that you want the LATEST Date for each building

Solution
Build and save a query that gets the building number and the latest date
(use Max)
Use that query and your table in another query, joining the table and
query
on building number and dates

In SQL view that would look like

Query 1:
SELECT BuildingNumber, Max([Date]) as LatestDate
FROM YourTable
GROUP BY BuildingNumber

Query 2:
SELECT YourTable.*
FROM YourTable INNER JOIN Query1
ON YourTable.BuildingNumber = Query1.BuildingNumber
AND YourTable.[Date] = Query1.LatestDate


All in one query: (only possible if your table and field names do NOT
contain spaces)
SELECT YourTable.*
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate

message I should've been able too figure this out on my own, but I can't seem
to
get
any of the criteria I've seen here to work right. Anyways this should
be
a
simple one for the pros. I've got a monster table and I need to run a
query
to return only the most recent records. It reads something like this:

AutoNumber BuildingNumber Date [fields
3-240]
[AutoNumber] a1 6/6/06 [each
field's value]
[AutoNumber] a1 12/6/06
[each
field's value]
[AutoNumber] a2 6/6/06 [each
field's value]
[AutoNumber] a2 9/18/06
[each
field's value]
etc. for about 1800 more

Any ideas on the criteria I could use? Do I need to make multiple
tables
to
do it?
Click to expand...
Click to expand...
Click to expand...
 
I think what you want is as follows. That assumes that query2 is correctly
returning ALL the autonumbers for the records you are interested in.

SELECT *
FROM Inspections
WHERE Inspections.Autonumber =
(SELECT Autonumber FROM Query2)

If the above query returns the correct records or query3 returns the correct
records, then check the form settings to make sure you haven't set its
DataEntry property to Yes.

MikeLostinWoods said:
in table view:
Query1 returns correct Building and Dates.
Query2 returns correct AutoNumber
Query3 returns all current records with all fields correctly
Query3a displays a message stating it'll only return one record and
doesn't
open.

Both Query3 and 3a when assigned as the Record Source on the Form Property
only return 1 (new) record with default data.

Here is the SQL for 3.1 Did I make any errors?
SELECT *
FROM Inspections
WHERE Inspections.Autonumber =
(SELECT Autonumber FROM Query2) IN
(SELECT Inspections.AutoNumber
FROM Inspections INNER JOIN
(SELECT Inspections.BuildingNumber, Max(Inspections.Date) as
LatestDate
FROM Inspections
GROUP BY BuildingNumber) as Temp
So to trouble shoot let's break this down into steps.

Step one: Does the following return any data? Is it correct?

SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber

Step Two: Does the following return any data? And if so are the
Autonumbers
valid ones for what you are trying to get?
SELECT YourTable.AutoNumber
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as
LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate)

Step Three: IF the above worked, then this should work unless things have
gotten too complex.
SELECT X.*
FROM YOURTable as X
WHERE X.AutoNumber IN
(SELECT YourTable.AutoNumber
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as
LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate)

If the query in step 2 works, but step 3 fails then try saving the query
in
step 2 and using that in the where clause of step 3

SELECT *
FROM YourTable
WHERE YourTable.Autonumber =
(SELECT Autonumber FROM TheSavedQuery)

John Spencer said:
True, if you want to edit some values then you will have to write the
query differently. I think the following MIGHT work for you

SELECT X.*
FROM YOURTable as X
WHERE X.AutoNumber IN
(SELECT YourTable.AutoNumber
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as
LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate)

message Wow, that works great, but now it looks like my records are locked out
from
editing on the form I'm using.

:

Assumption that you want the LATEST Date for each building

Solution
Build and save a query that gets the building number and the latest
date
(use Max)
Use that query and your table in another query, joining the table and
query
on building number and dates

In SQL view that would look like

Query 1:
SELECT BuildingNumber, Max([Date]) as LatestDate
FROM YourTable
GROUP BY BuildingNumber

Query 2:
SELECT YourTable.*
FROM YourTable INNER JOIN Query1
ON YourTable.BuildingNumber = Query1.BuildingNumber
AND YourTable.[Date] = Query1.LatestDate


All in one query: (only possible if your table and field names do
NOT
contain spaces)
SELECT YourTable.*
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as
LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate

in
message I should've been able too figure this out on my own, but I can't
seem
to
get
any of the criteria I've seen here to work right. Anyways this
should
be
a
simple one for the pros. I've got a monster table and I need to
run a
query
to return only the most recent records. It reads something like
this:

AutoNumber BuildingNumber Date
[fields
3-240]
[AutoNumber] a1 6/6/06 [each
field's value]
[AutoNumber] a1 12/6/06
[each
field's value]
[AutoNumber] a2 6/6/06 [each
field's value]
[AutoNumber] a2 9/18/06
[each
field's value]
etc. for about 1800 more

Any ideas on the criteria I could use? Do I need to make multiple
tables
to
do it?
Click to expand...
Click to expand...
Click to expand...
 
That did the trick! Data entry? Who put that there? Everything's running
smooth now . Thanks!

John Spencer said:
I think what you want is as follows. That assumes that query2 is correctly
returning ALL the autonumbers for the records you are interested in.

SELECT *
FROM Inspections
WHERE Inspections.Autonumber =
(SELECT Autonumber FROM Query2)

If the above query returns the correct records or query3 returns the correct
records, then check the form settings to make sure you haven't set its
DataEntry property to Yes.

MikeLostinWoods said:
in table view:
Query1 returns correct Building and Dates.
Query2 returns correct AutoNumber
Query3 returns all current records with all fields correctly
Query3a displays a message stating it'll only return one record and
doesn't
open.

Both Query3 and 3a when assigned as the Record Source on the Form Property
only return 1 (new) record with default data.

Here is the SQL for 3.1 Did I make any errors?
SELECT *
FROM Inspections
WHERE Inspections.Autonumber =
(SELECT Autonumber FROM Query2) IN
(SELECT Inspections.AutoNumber
FROM Inspections INNER JOIN
(SELECT Inspections.BuildingNumber, Max(Inspections.Date) as
LatestDate
FROM Inspections
GROUP BY BuildingNumber) as Temp
So to trouble shoot let's break this down into steps.

Step one: Does the following return any data? Is it correct?

SELECT YourTable.BuildingNumber, Max(YourTable.Date) as LatestDate
FROM YourTable
GROUP BY BuildingNumber

Step Two: Does the following return any data? And if so are the
Autonumbers
valid ones for what you are trying to get?
SELECT YourTable.AutoNumber
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as
LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate)

Step Three: IF the above worked, then this should work unless things have
gotten too complex.
SELECT X.*
FROM YOURTable as X
WHERE X.AutoNumber IN
(SELECT YourTable.AutoNumber
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as
LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate)

If the query in step 2 works, but step 3 fails then try saving the query
in
step 2 and using that in the where clause of step 3

SELECT *
FROM YourTable
WHERE YourTable.Autonumber =
(SELECT Autonumber FROM TheSavedQuery)

True, if you want to edit some values then you will have to write the
query differently. I think the following MIGHT work for you

SELECT X.*
FROM YOURTable as X
WHERE X.AutoNumber IN
(SELECT YourTable.AutoNumber
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as
LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate)

message Wow, that works great, but now it looks like my records are locked out
from
editing on the form I'm using.

:

Assumption that you want the LATEST Date for each building

Solution
Build and save a query that gets the building number and the latest
date
(use Max)
Use that query and your table in another query, joining the table and
query
on building number and dates

In SQL view that would look like

Query 1:
SELECT BuildingNumber, Max([Date]) as LatestDate
FROM YourTable
GROUP BY BuildingNumber

Query 2:
SELECT YourTable.*
FROM YourTable INNER JOIN Query1
ON YourTable.BuildingNumber = Query1.BuildingNumber
AND YourTable.[Date] = Query1.LatestDate


All in one query: (only possible if your table and field names do
NOT
contain spaces)
SELECT YourTable.*
FROM YourTable INNER JOIN
(SELECT YourTable.BuildingNumber, Max(YourTable.Date) as
LatestDate
FROM YourTable
GROUP BY BuildingNumber) as Temp
ON YourTable.BuildingNumber = Temp.BuildingNumber
AND YourTable.[Date] = Temp.LatestDate

in
message I should've been able too figure this out on my own, but I can't
seem
to
get
any of the criteria I've seen here to work right. Anyways this
should
be
a
simple one for the pros. I've got a monster table and I need to
run a
query
to return only the most recent records. It reads something like
this:

AutoNumber BuildingNumber Date
[fields
3-240]
[AutoNumber] a1 6/6/06 [each
field's value]
[AutoNumber] a1 12/6/06
[each
field's value]
[AutoNumber] a2 6/6/06 [each
field's value]
[AutoNumber] a2 9/18/06
[each
field's value]
etc. for about 1800 more

Any ideas on the criteria I could use? Do I need to make multiple
tables
to
do it?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top