In MS Access 2007 on the query tab:
What I'm trying to do is compare the value in one field to the next value in the same field.
So the query would look something like:

Field: address
table: customer
update to:
criteria: address + 1

So I'm comparing the address to the next address in the table.
Obviously that doesn't work. Anyone could clue me in on how
to do that?

I'm not able to program in visual basic but just use the
query tab section.

Thanks!
Dean :)

Does your table have a primary key? (autonumber perhaps/hopefully)

I can use auto numbers but maybe not primary key unless I have
the auto numbers the primary key.

Dean

So I'm comparing the address to the next address in the table.

So,

Did you know that the next address is not always going to be the same as before? In other words, unless you have either a primary key defined that increments upwards, always or you have a date/time stamp, you are not guaranteed that the records will always come out in the same order. Without a numeric primary key (and that isn't always guaranteed either) or explicit sort in a query, you are not guaranteed to have your records in any particular order.

Do you have such a mechanism in place?

Second, what purpose does it serve to compare the addresses?

I'm trying to compare the address to find duplicates. So once I find
duplicates I can mark that record as a duplicate so I can deal with
it later. Bascially here is what I'm doing:

First I sort in ascending order address and then in decending order
the date the rental property was bought, ie:

field names: mailing address date rental property bought
123 main st 12/31/2008
123 main st 12/25/2008
123 main st 01/01/2007

So what my query will do after I sorted as above it will
compare the address field to the next address field and
if the same, it will mark delete and move on to the next one.

So in the end I will have one address of the Owner with
his oldest rental property. THis way I don't send him mail
on all of his rental properties but just one letter referring
to his oldest rental property (and hoping that one will have
the most equity).

I can do this in excel but it would be nice and more convenient
to do it in Access.

Dean

Forgot this editor does not recognize my spacing.

There are two key fields: "address" and say "date rental property bought"
so look at the previous post and the 123 main is under the address
field and the date refers to the other field.

I assume you figured that out but wanted to clarify.

So, one more question. Will there ever be a situation like this?


123 main st 12/31/2008
123 main 12/25/2008
123 main st 01/01/2007


Where your address is not exactly the same as the others.

Good question. What I've done in the past is run it thru my address correction software so then it wouldn't be a problem. Unless I'm just
lazy and don't care of a few cases such as that.

So No and maybe? :)

I'm confused.

why wouldn't you just do a "Totals" query with the Group By on the Customer field and then set a field to get the minimum date?

Am I missing something here?

I'm confused.

why wouldn't you just do a "Totals" query with the Group By on the Customer field and then set a field to get the minimum date?

Am I missing something here?
That would be MAXimum date (remember dates get larger as you get the latest).

And yes, that would be a way to do it.

"Totals" query with the Group By on the Customer field and then set a field to get the minimum date?

Could you explain that? Not sure what a "totals" query or Group By means.

Dean

What version of Access are you using?

Edit:
Sorry, forgot you already mentioned it is 07

With a new query in Design View, you should see up in the toolbar or ribbon area, a "Totals" button with the Greek "Equation" symbol.

By default, when you drag a field into the design grid, it will be set to "Group By".
Drag the customer field in and drop it (note where it says "Group By")

Then drag you date field in and drop it.
where it too says "Group By" change that to MIN for the oldest or max for the newest date in the field.

With a new query in Design View, you should see up in the toolbar or ribbon area, a "Totals" button with the Greek "Equation" symbol.

By default, when you drag a field into the design grid, it will be set to "Group By".
Drag the customer field in and drop it (note where it says "Group By")

Then drag you date field in and drop it.
where it too says "Group By" change that to MIN for the oldest or max for the newest date in the field.
And for a visual on what the button kind of looks like:
http://downloads.btlarson.com/AWF/screenshots/sigma.png

I am trying to create a query very similar to what the OP described. When I follow the instructions for the Group By query, the Design View still shows each row/line item, not just the oldest date. Is that what it is supposed to show? then do I somehow filter the information in the report?

What I've found sometimes is that you can create a separate query to get the latest date and then link that back to the original table in another query to select those that match with that date.

InDFW,
I'm not sure from your post, how you're getting to where you are, but:

YOu do not need to add all fields to a Group By query, only the field(s) necessary to filter to the desired end result, per group.

You should not set all fields to Group By.
In the working example for the OP, only customer is set to Group By.
The date Field is set to MIN instead.

What I've found sometimes is that you can create a separate query to get the latest date and then link that back to the original table in another query to select those that match with that date.

I hate when people write the "forgive me in advance for dumb question", but please fogive the stupid question...

What would be the criteria/speicific info to query for latest date if I did it this way?

I hate when people write the "forgive me in advance for dumb question", but please fogive the stupid question...

What would be the criteria/speicific info to query for latest date if I did it this way?


In the examples from this thread, you need no criteria at all.

InDFW,
I'm not sure from your post, how you're getting to where you are, but:

YOu do not need to add all fields to a Group By query, only the field(s) necessary to filter to the desired end result, per group.

You should not set all fields to Group By.
In the working example for the OP, only customer is set to Group By.
The date Field is set to MIN instead.

this is what i'm doing - only using the necessary fields.
do i put "MAX" (for the oldest date) i the Group By cell, or in the Criteria cell?

nevermind - i just read the previous post. ty! trying it now...

Change the "Group By" to "MAX" or "MIN" in the date field

OMG! THANK Y'ALL SO MUCH!!!
i've been trying to do this for so long - my company is gonna LOOOVE me! wish i could buy y'all a drink!!!

i've always lurked and found the answers i need, but i'm sure i'll be posting more cuz the boss always wants more more more!!! job security :)

I need to create a report with the max date, Title (in OPs example, this was her Address field), and also include several other fields. When i add other fields, it stops grouping correctly.

I tried creating a query from the query and adding additional fields, and grabbed the fields I needed along with the fields from the new Group By query, but it gives me all of the dates, not just the oldest ones.

Does that explain my problem clearly? If not, would it help if I gave you my specific field categories?

I need to create a report with the max date, Title (in OPs example, this was her Address field), and also include several other fields. When i add other fields, it stops grouping correctly.

I tried creating a query from the query and adding additional fields, and grabbed the fields I needed along with the fields from the new Group By query, but it gives me all of the dates, not just the oldest ones.

Does that explain my problem clearly? If not, would it help if I gave you my specific field categories?

What you need is to create a query from the query and add additional fields BUT you also need to include the date field and link both tables at the date field as well as the primary key.

What you need is to create a query from the query and add additional fields BUT you also need to include the date field and link both tables at the date field as well as the primary key.

worked like a charm.
ty again!

I got lost on that last explanation from Bob. Where I left off is adding the address ( I think Bilbo called it the customer field) and the date using the group by and min. It works with just those to fields in the query but when I add fields it's all messed up.

Thanks,
Dean

Also, if I can flag a field showing that the owner has multiple properties then
i can mail him later on those. But the oldest rental is the one I first and foremost
want to target. Using the summation and group by and min I don't thinkt that
will work.