Problem with "Latest Date" Query

[AdminGirl]

I have a table with multiple columns. The primary key is the column titled
"Property Address". Each property contains it's own row. There is another
group of columns in the same table that contain dates, titled: ltr_dt_1
through ltr_dt_10. Every time a letter is mailed to one of the properties,
the date of the letter is entered in those columns, beginning with (1), so
that each instance is kept. For some of the properties only the first couple
of columns are filled, for others, most of the columns are filled.

I need to write a query that will pull the "Property Address" into one
column and combine all (10) of the date columns into (1) column that only
shows the latest date that a letter was sent to each property.

I used the expression builder and got the query to pull all the dates into
one column, but I cannot figure out how to get it to show only the latest
date instead of all of the dates.

Someone please help!!

 

[Jeff Boyce]

The reason why you are finding so much difficulty accomplishing this is
because you've tried to use Access as if it were a spreadsheet. It's not.

Whenever (in a relational database) you find yourself thinking about
ltr_dt_1, ltr_dt_2, ... and so on, you have repeating fields. So what, you
ask?

So you are attempting to show a "one-to-many" relationship (one "property"
can have one-to-many letters) as you would with a spreadsheet, by adding
columns. Yes, you can do it this way, but you are creating considerable
extra work for both you and Access.

Instead, consider adding a table that holds two fields:
PropertyID (whatever you are using as a unique property identifier)
Ltr_dt (I assume your "letter date")

Now, when you need to find the most recent (don't use "LAST", Access counts
differently) letter date for a particular property, it is a simple query
against that new table.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Bob Barrows]

I have a table with multiple columns. The primary key is the column
titled "Property Address". Each property contains it's own row.
There is another group of columns in the same table that contain
dates, titled: ltr_dt_1 through ltr_dt_10. Every time a letter is
mailed to one of the properties, the date of the letter is entered in
those columns, beginning with (1), so that each instance is kept.
For some of the properties only the first couple of columns are
filled, for others, most of the columns are filled.

I need to write a query that will pull the "Property Address" into one
column and combine all (10) of the date columns into (1) column that
only shows the latest date that a letter was sent to each property.

I used the expression builder and got the query to pull all the dates
into one column, but I cannot figure out how to get it to show only
the latest date instead of all of the dates.

Someone please help!!

As Jeff said, consider redesigning this database. The Ltr_dt should be
in a single column in a separate table. The only modification I would
suggest to his suggested design is to add a letter-identifying column.

This union query (you need to switch the query builder to sql view to
enter this) will illustrate what the table needs to look like:

select [property address], 1 as LetterID, ltr_dt_1 as ltr_dt from table
union all
select [property address], 2, ltr_dt_2 from table
....
union all
select [property address], 10, ltr_dt_10 from table

Save the above query as Letters (for example) and create a new query
with this sql:
select [property address], max(ltr_dt) as LatestLetter
from letters
group by [property address]

 

[AdminGirl]

Jeff:

Thanks for answering. Is there any other way to do it the way I asked?

My problem is that the database was created years ago and is used by
numerous people. They enter the "Latest" information into fields on certain
forms, and the forms populate various tables. My Manager wants me to set up
a query that pulls up the information I mentioned before & have it
automatically update every time someone enters new information into the forms
already created.

Then he wants me to create an Excel spreadsheet that pulls this information
from the database & automatically updates every time the Excel spreadsheet is
opened.

I know how to create a merge that pulls from a table or query in a database
into Excel, but how do I get the query to only pull the most recent date
(from whichever column it comes from) for each property (row) and have it
automatically update in the database, so I can pull it into Excel?

Any chance you can help me?

 

[Jeff Boyce]

Bob offers a "union query" in his response as a way to "see" all the dates
in the ltr_dt_# fields.

See if this approach gives you the work around...

Just because someone else created it and folks adapted to using it "as is"
doesn't mean you can't offer a smarter, more usable solution.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[AdminGirl]

Bob:

I don't know if you saw my response to Jeff's suggestion or not. I see how
your response will work, except I wouldn't need to "group by" on the
"Property Address," would I? It should just pull up the Max(ltr_dt) for the
"Property," right? I will try it right now.

Hopefully, my final question is will the Max(ltr_dt) in the new query
automatically update when a new date is entered into the Forms mentioned in
my return email to Jeff?

Thanks so much for your help!!!!!!

I have a table with multiple columns. The primary key is the column
titled "Property Address". Each property contains it's own row.
There is another group of columns in the same table that contain
dates, titled: ltr_dt_1 through ltr_dt_10. Every time a letter is
mailed to one of the properties, the date of the letter is entered in
those columns, beginning with (1), so that each instance is kept.
For some of the properties only the first couple of columns are
filled, for others, most of the columns are filled.

I need to write a query that will pull the "Property Address" into one
column and combine all (10) of the date columns into (1) column that
only shows the latest date that a letter was sent to each property.

I used the expression builder and got the query to pull all the dates
into one column, but I cannot figure out how to get it to show only
the latest date instead of all of the dates.

Someone please help!!

As Jeff said, consider redesigning this database. The Ltr_dt should be
in a single column in a separate table. The only modification I would
suggest to his suggested design is to add a letter-identifying column.

This union query (you need to switch the query builder to sql view to
enter this) will illustrate what the table needs to look like:

select [property address], 1 as LetterID, ltr_dt_1 as ltr_dt from table
union all
select [property address], 2, ltr_dt_2 from table
....
union all
select [property address], 10, ltr_dt_10 from table

Save the above query as Letters (for example) and create a new query
with this sql:
select [property address], max(ltr_dt) as LatestLetter
from letters
group by [property address]

 

[AdminGirl]

Bob & Jeff:

Sorry, I forgot another question. If no letters were sent to a particular
property, I want the date column to remain blank. Would you let me know how
to do that, as well.

Thanks again for your help. I have never worked in the SQL View before. I
hope I don't mess something up.

I have a table with multiple columns. The primary key is the column
titled "Property Address". Each property contains it's own row.
There is another group of columns in the same table that contain
dates, titled: ltr_dt_1 through ltr_dt_10. Every time a letter is
mailed to one of the properties, the date of the letter is entered in
those columns, beginning with (1), so that each instance is kept.
For some of the properties only the first couple of columns are
filled, for others, most of the columns are filled.

I need to write a query that will pull the "Property Address" into one
column and combine all (10) of the date columns into (1) column that
only shows the latest date that a letter was sent to each property.

I used the expression builder and got the query to pull all the dates
into one column, but I cannot figure out how to get it to show only
the latest date instead of all of the dates.

Someone please help!!

As Jeff said, consider redesigning this database. The Ltr_dt should be
in a single column in a separate table. The only modification I would
suggest to his suggested design is to add a letter-identifying column.

This union query (you need to switch the query builder to sql view to
enter this) will illustrate what the table needs to look like:

select [property address], 1 as LetterID, ltr_dt_1 as ltr_dt from table
union all
select [property address], 2, ltr_dt_2 from table
....
union all
select [property address], 10, ltr_dt_10 from table

Save the above query as Letters (for example) and create a new query
with this sql:
select [property address], max(ltr_dt) as LatestLetter
from letters
group by [property address]

 

[Bob Barrows]

Bob:

I don't know if you saw my response to Jeff's suggestion or not. I
see how your response will work, except I wouldn't need to "group by"
on the "Property Address," would I? It should just pull up the
Max(ltr_dt) for the "Property," right? I will try it right now.

Each Property has a latest date doesn't it? If you want the latest date
for each property, then yes, you need to group by property.
If you just want to see the latest date in the entire table, then yes,
remove the group by.

Hopefully, my final question is will the Max(ltr_dt) in the new query
automatically update when a new date is entered into the Forms
mentioned in my return email to Jeff?

Yes, why wouldn't it? A query retrieves data that is entered in the
source tables. It doesn't store data.

And again, I second Jeff's advice: this database needs to be redesigned
if you want to be able to respond in a timely manner to new requests for
information from the database. The change can be transparent to the
users. Just point the forms at the new tables.

 

[Bob Barrows]

You are going to have to clarify the result that you want. Do you want
the latest letter date per property or the latest date for all
properties? Your previous message makes this very unclear. If the
latter, this question makes no sense. If the prior, your question in
your previous message makes no sense.

What might help is if you show us a few rows of sample data followed by
the rows of resulting data you want the query to retrieve from the
sample data.

Bob & Jeff:

Sorry, I forgot another question. If no letters were sent to a
particular property, I want the date column to remain blank. Would
you let me know how to do that, as well.

Thanks again for your help. I have never worked in the SQL View
before. I hope I don't mess something up.

I have a table with multiple columns. The primary key is the column
titled "Property Address". Each property contains it's own row.
There is another group of columns in the same table that contain
dates, titled: ltr_dt_1 through ltr_dt_10. Every time a letter is
mailed to one of the properties, the date of the letter is entered
in those columns, beginning with (1), so that each instance is kept.
For some of the properties only the first couple of columns are
filled, for others, most of the columns are filled.

I need to write a query that will pull the "Property Address" into
one column and combine all (10) of the date columns into (1) column
that only shows the latest date that a letter was sent to each
property.

I used the expression builder and got the query to pull all the
dates into one column, but I cannot figure out how to get it to
show only the latest date instead of all of the dates.

Someone please help!!

As Jeff said, consider redesigning this database. The Ltr_dt should
be in a single column in a separate table. The only modification I
would suggest to his suggested design is to add a letter-identifying
column.

This union query (you need to switch the query builder to sql view to
enter this) will illustrate what the table needs to look like:

select [property address], 1 as LetterID, ltr_dt_1 as ltr_dt from
table union all
select [property address], 2, ltr_dt_2 from table
....
union all
select [property address], 10, ltr_dt_10 from table

Save the above query as Letters (for example) and create a new query
with this sql:
select [property address], max(ltr_dt) as LatestLetter
from letters
group by [property address]

 

[Jeff Boyce]

Which "date column to remain blank"?

If you redesign the database to use the relationally-oriented tools Access
offers, you'd be able to run a query to see all properties without any
letters. To do it with the data structure as you've described will be ...
some effort.

How many time/ways do you want to face the "pay now or pay later" issue?
Until your data structure fits better with Access' features/functions, you
will have to continue "paying later"...

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Bob & Jeff:

Sorry, I forgot another question. If no letters were sent to a particular
property, I want the date column to remain blank. Would you let me know
how
to do that, as well.

Thanks again for your help. I have never worked in the SQL View before.
I
hope I don't mess something up.

I have a table with multiple columns. The primary key is the column
titled "Property Address". Each property contains it's own row.
There is another group of columns in the same table that contain
dates, titled: ltr_dt_1 through ltr_dt_10. Every time a letter is
mailed to one of the properties, the date of the letter is entered in
those columns, beginning with (1), so that each instance is kept.
For some of the properties only the first couple of columns are
filled, for others, most of the columns are filled.

I need to write a query that will pull the "Property Address" into one
column and combine all (10) of the date columns into (1) column that
only shows the latest date that a letter was sent to each property.

I used the expression builder and got the query to pull all the dates
into one column, but I cannot figure out how to get it to show only
the latest date instead of all of the dates.

Someone please help!!

As Jeff said, consider redesigning this database. The Ltr_dt should be
in a single column in a separate table. The only modification I would
suggest to his suggested design is to add a letter-identifying column.

This union query (you need to switch the query builder to sql view to
enter this) will illustrate what the table needs to look like:

select [property address], 1 as LetterID, ltr_dt_1 as ltr_dt from table
union all
select [property address], 2, ltr_dt_2 from table
....
union all
select [property address], 10, ltr_dt_10 from table

Save the above query as Letters (for example) and create a new query
with this sql:
select [property address], max(ltr_dt) as LatestLetter
from letters
group by [property address]