Query -

[Guest]

Hi I am going to illustrate what i am trying to do in an example. I have the
following results from a query:

Payment# Account # Payment Date Payment Amount
1 1000 4/2/2007 $500
2 1000 4/4/2007 $300

What I would like to do is just keep the first payment the most previous
date, in this case Payment# 1, any help is greatly appreciated!

Thanks a lot!

 

[John W. Vinson]

Hi I am going to illustrate what i am trying to do in an example. I have the
following results from a query:

Payment# Account # Payment Date Payment Amount
1 1000 4/2/2007 $500
2 1000 4/4/2007 $300

What I would like to do is just keep the first payment the most previous
date, in this case Payment# 1, any help is greatly appreciated!

I'm sorry, I don't understand what you're asking. "keep the first payment the
most previous date"? Do you want to permanently and irrevokably delete Payment
2 just because it's not the oldest?

John W. Vinson [MVP]

 

[Guest]

sorry , yes i need to keep only the first record which has the most recent
payment, can a query provide the result however without deleting the second
record from the database? I really hope i can do that, thanks for your help!

 

[John W. Vinson]

sorry , yes i need to keep only the first record which has the most recent
payment, can a query provide the result however without deleting the second
record from the database? I really hope i can do that, thanks for your help!

Again:

Do you want your entire database table to contain one and only one record,
the most recent date; and permanently discard all prior history?

Or - what I'd recommend! - do you want to *keep* the history and see only the
most recent payment?

If the latter, then you can use a Subquery; put a criterion on [Payment Date]
of

=(SELECT Max([Payment Date] FROM yourtable AS X WHERE X.[Account #] =
yourtable.[Account #])


John W. Vinson [MVP]

 

[Guest]

Here is what i have:
Payment# Account# Payment Date
1 10000 5/1/2007
2 10001 5/1/2007
3 10002 5/5/2007
4 10000 5/7/2007

The result that I would like to based on the above:

Payment# Account# Payment Date
1 10000 5/1/2007
2 10001 5/1/2007
3 10002 5/5/2007
The result would be to get ridd off the last record since it s a payment
done for an account with a prior payment, so keep the prior payment and get
rid off the recent one, but at the same time keep all the other ones
(accounts with single payment) (I have a table that has 30000 rows) so i
think instead of Max i will use Min but how i can do it with 30000 rows

sorry , yes i need to keep only the first record which has the most recent
payment, can a query provide the result however without deleting the second
record from the database? I really hope i can do that, thanks for your help!

Again:

Do you want your entire database table to contain one and only one record,
the most recent date; and permanently discard all prior history?

Or - what I'd recommend! - do you want to *keep* the history and see only the
most recent payment?

If the latter, then you can use a Subquery; put a criterion on [Payment Date]
of

=(SELECT Max([Payment Date] FROM yourtable AS X WHERE X.[Account #] =
yourtable.[Account #])


John W. Vinson [MVP]

 

[John W. Vinson]

Here is what i have:
Payment# Account# Payment Date
1 10000 5/1/2007
2 10001 5/1/2007
3 10002 5/5/2007
4 10000 5/7/2007

The result that I would like to based on the above:

Payment# Account# Payment Date
1 10000 5/1/2007
2 10001 5/1/2007
3 10002 5/5/2007
The result would be to get ridd off the last record since it s a payment
done for an account with a prior payment, so keep the prior payment and get
rid off the recent one, but at the same time keep all the other ones
(accounts with single payment) (I have a table that has 30000 rows) so i
think instead of Max i will use Min but how i can do it with 30000 rows

It makes me really, really nervous to *delete all records that a payment
occurred* even if there were a prior payment. I know if I were a customer of
yours, and sent you two checks, and was later told "We only have records that
you paid once" I'd be more than a little bit annoyed. That's why I'm being so
persistant with this question:

Do you want to permanently, irrevokably, delete all payments more recent than
the oldest?

If so, you can run a Delete query:

DELETE * FROM yourtable
WHERE [Payment#] IN
(SELECT [Payment#] FROM yourtable AS X
WHERE X.[Account#] = yourtable.[Account#]
AND X.[Payment Date] > yourtable.[Payment Date])

This Subquery will look at every record in the table which has one or more
later payments for the same account, and delete that payment.

Make a backup of your database before you do this - deletion is permanent and
there is no "undelete"!

John W. Vinson [MVP]

 

[Guest]

SELECT DISTINCT [Payments from 3_8].PYMT_NO, [Payments from 3_8].ACCT_ID,
[Payments from 3_8].[Payment Date], [Payments from 3_8].[Net Cash], [Payments
from 3_8].PAYMENT_METHOD
FROM [Hotline from 3_8] INNER JOIN [Payments from 3_8] ON [Hotline from
3_8].ACCT_ID = [Payments from 3_8].ACCT_ID
WHERE ((([Payments from 3_8].[Payment Date])>=[Enter Payment Start Date]
And ([Payments from 3_8].[Payment Date])<=[Enter Payment End Date] And
([Payments from 3_8].[Payment Date])>=[ACTN_Date]
And
([Payments from 3_8].[Payment Date])=(SELECT MIN([Payments from
3_8].[Payment Date]) FROM [Payments from 3_8] As [First Payment] Where [First
Payment].[ACCT_ID]= [Payments from 3_8].[ACCT_ID])) AND (([Hotline from
3_8].ACTN_DATE)>=[Enter Hotline Start Date] And ([Hotline from
3_8].ACTN_DATE)<=[Enter Hotline End Date]));

John, here is the query I am using now, yes it is true i do not wanna delete
the recent payments so i just want to have the oldest payment for an account
plus all the other records where you have one payment per account within a
period of time.

The problen with this query is that each time i execute it, Access freezes
and can't test the query, can you please take a look and see if there is
something wrong with this query,

THANKS A LOT!

Here is what i have:
Payment# Account# Payment Date
1 10000 5/1/2007
2 10001 5/1/2007
3 10002 5/5/2007
4 10000 5/7/2007

The result that I would like to based on the above:

Payment# Account# Payment Date
1 10000 5/1/2007
2 10001 5/1/2007
3 10002 5/5/2007
The result would be to get ridd off the last record since it s a payment
done for an account with a prior payment, so keep the prior payment and get
rid off the recent one, but at the same time keep all the other ones
(accounts with single payment) (I have a table that has 30000 rows) so i
think instead of Max i will use Min but how i can do it with 30000 rows

It makes me really, really nervous to *delete all records that a payment
occurred* even if there were a prior payment. I know if I were a customer of
yours, and sent you two checks, and was later told "We only have records that
you paid once" I'd be more than a little bit annoyed. That's why I'm being so
persistant with this question:

Do you want to permanently, irrevokably, delete all payments more recent than
the oldest?

If so, you can run a Delete query:

DELETE * FROM yourtable
WHERE [Payment#] IN
(SELECT [Payment#] FROM yourtable AS X
WHERE X.[Account#] = yourtable.[Account#]
AND X.[Payment Date] > yourtable.[Payment Date])

This Subquery will look at every record in the table which has one or more
later payments for the same account, and delete that payment.

Make a backup of your database before you do this - deletion is permanent and
there is no "undelete"!

John W. Vinson [MVP]

 

[Michael Gramelspacher]

=[ACTN_Date]

And

Shouldn't this be >= [Hotline from 3_8].Actn_Date And ?

Not that this is the real problem.

 

[Guest]

ok and?

=[ACTN_Date]

And

Shouldn't this be >= [Hotline from 3_8].Actn_Date And ?

Not that this is the real problem.

 

[Guest]

yep no

=[ACTN_Date]

And

Shouldn't this be >= [Hotline from 3_8].Actn_Date And ?

Not that this is the real problem.

 

[Michael Gramelspacher]

yep no

Ok, you did not like that, so ...
(SELECT MIN([Payments from 3_8].[Payment Date])
FROM [Payments from 3_8] AS [First Payment]
WHERE [First Payment].[ACCT_ID] = [Payments from 3_8].[ACCT_ID]))

maybe should be:
(SELECT MIN([First Payment].[Payment Date]
FROM [Payments from 3_8] AS [First Payment]
WHERE [First Payment].[ACCT_ID] = [Payments from 3_8].[ACCT_ID]))