How do I compare one field to the next?

[servent1]

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

 

[namliam]

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

 

[servent1]

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

Dean

 

[boblarson]

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?

 

[servent1]

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

 

[servent1]

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.

 

[boblarson]

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.

 

[servent1]

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?

 

[Bilbo_Baggins_Esq]

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?

 

[boblarson]

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.

 

[servent1]

"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

 

[Bilbo_Baggins_Esq]

What version of Access are you using?

Edit:
Sorry, forgot you already mentioned it is 07

 

[Bilbo_Baggins_Esq]

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.

 

[boblarson]

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:

 

[InDFW]

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?

 

[boblarson]

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.

 

[Bilbo_Baggins_Esq]

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.

 

[InDFW]

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?

 

[Bilbo_Baggins_Esq]

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]

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...