Eliminate records based on 2 fields (1 Viewer)

lwarren1968

Registered User.
Local time
Today, 05:14
Joined
Jan 18, 2013
Messages
48
I have a file that I originally retrieved based on a from/to date. That being said I need a query to eliminate the [Type] = "General Withdrawal" record and those with a [Time] before and after the [Type] = "General Withdrawal". There are 2 General Withdrawal's - 1 for the beginning date and 1 for the ending date. I hope this makes sense. Any help would be appreciated!



DateGROSS AMT1FEE AMT1NET AMT1ExpenseTypeTimeGROSS AMT2
4/1/2020159.98-4.94155.04Express Checkout Payment4:06:12159.98
4/1/2020159.98-4.94155.04Express Checkout Payment4:15:22159.98
4/1/2020-5096.04General Withdrawal5:17:360
4/1/202031.02-1.229.82Express Checkout Payment7:10:5131.02
4/1/2020-31.020-31.02Payment Refund7:14:09-31.02
4/1/202022.98-0.9722.01Express Checkout Payment7:45:2722.98
4/1/202024.03-123.03Express Checkout Payment10:05:1724.03
4/1/2020-24.030-24.03Payment Refund10:07:05-24.03
4/1/202050.97-1.7849.19Express Checkout Payment10:30:0950.97
4/1/202014.97-0.7314.24Express Checkout Payment11:18:4414.97
4/1/202034.98-1.3133.67Express Checkout Payment12:57:3734.98
4/13/2020-28.99eBay Auction Payment10:37:090
4/13/2020117.98-3.72114.26Express Checkout Payment11:23:02117.98
4/13/2020227.85-6.91220.94Express Checkout Payment11:27:01227.85
4/14/2020369.98-11.03358.95Express Checkout Payment6:13:32369.98
4/14/202065.98-2.2163.77Express Checkout Payment6:34:5065.98
4/14/202066.32-2.2264.1Website Payment7:28:5766.32
4/14/202059.05-2.0157.04Express Checkout Payment8:35:2859.05
4/14/202012.83-0.6712.16Express Checkout Payment10:18:3012.83
4/14/202025.19-1.0324.16Website Payment14:50:2725.19
4/14/202020.97-0.9120.06Express Checkout Payment19:50:5120.97
4/15/2020-8164.01General Withdrawal7:57:130
4/15/2020-136PreApproved Payment Bill User Payment9:33:060
4/15/2020-185.45Website Payment15:25:110
4/15/2020-74.95PreApproved Payment Bill User Payment17:47:570
4/15/202025.68-1.0424.64Express Checkout Payment18:48:4825.68
 

bastanu

Registered User.
Local time
Today, 05:14
Joined
Apr 13, 2010
Messages
454
Can you highlight in your pic the records you want to remove?
Cheers,
 

lwarren1968

Registered User.
Local time
Today, 05:14
Joined
Jan 18, 2013
Messages
48
See attached. I am typically manually deleting those records highlighted.
 

Attachments

  • sample.pdf
    67.5 KB · Views: 17

mdnuts

Registered User.
Local time
Today, 08:14
Joined
May 28, 2014
Messages
120
The two examples seem to differ enough to not know the full scope. Can you narrow down the criteria for what is to be removed. Same Date, Time within x minutes, any other Types that would precede/follow within the parameters?

In the first set you have it removing two express checkout payments plus the General Withdrawal on the same date less 1 hour, however others on the same date are not removed greater than 1 hour.

in the second set you have it removing General Withdrawal plus 4 other rows with times much greater than one hour.
 

lwarren1968

Registered User.
Local time
Today, 05:14
Joined
Jan 18, 2013
Messages
48
Sorry, I don't mean to confuse. FYI, the samples are the same accept I actually highlighted the records in the PDF, which I am currently manually removing. See below. Does that make more sense.

Beginning date 04/01/20

[Type] = "General Withdrawal" with a [Time] equal to or prior to the time listed in the “General Withdrawal” record regardless of [Type]

Ending date 04/15/20

[Type] = "General Withdrawal" with a [Time] equal to or after the time listed in the “General Withdrawal” record regardless of [Type]
 

mdnuts

Registered User.
Local time
Today, 08:14
Joined
May 28, 2014
Messages
120
so you're looking to find the first instance of "General Withdrawal" and remove everything on the same date prior to it. Then looking for the next (or last?) instance of General Withdrawal and removing everything after it on that same date?

is it the next instance of General Withdrawal or the last?
 

lwarren1968

Registered User.
Local time
Today, 05:14
Joined
Jan 18, 2013
Messages
48
Basically, yes. I have not seen an instance where there was a General Withdrawal other then the to/from date.
 

mdnuts

Registered User.
Local time
Today, 08:14
Joined
May 28, 2014
Messages
120
Try something like this.
i have a copy of tblRegistry in the event it's not functioning the way you want - then tblRegistry can be deleted and a copy of "tblRegistry_bku" can be made and saved as tblRegistry

if you open the report - click the button on the upper right, then just refresh the report or look at the table. you would of course need to alter the code to match your database. the code is attached to the button on the report.

In essence what it does is it loops through, finds "General Withdrawal" and if it is the first instance, remove any records for the same date and time earlier and equal to General withdrawal.

Looping then finds the next instance and removes any records for the same date and time equal to and greater than the General Withdrawal date.

I also have it stop at the second instance - if you need to find the final instance instead it'd need to be altered to look through all the rows first to find the last instance and act from there.
 

Attachments

  • genwith.zip
    104.8 KB · Views: 16

lwarren1968

Registered User.
Local time
Today, 05:14
Joined
Jan 18, 2013
Messages
48
Try something like this.
i have a copy of tblRegistry in the event it's not functioning the way you want - then tblRegistry can be deleted and a copy of "tblRegistry_bku" can be made and saved as tblRegistry

if you open the report - click the button on the upper right, then just refresh the report or look at the table. you would of course need to alter the code to match your database. the code is attached to the button on the report.

In essence what it does is it loops through, finds "General Withdrawal" and if it is the first instance, remove any records for the same date and time earlier and equal to General withdrawal.

Looping then finds the next instance and removes any records for the same date and time equal to and greater than the General Withdrawal date.

I also have it stop at the second instance - if you need to find the final instance instead it'd need to be altered to look through all the rows first to find the last instance and act from there.
 

mdnuts

Registered User.
Local time
Today, 08:14
Joined
May 28, 2014
Messages
120
however you are going to launch it, is where I'd put it. if it's a form or report. it would be the same wherever you'd launch a query from.

Personally i haven't put VBA in an access query, I couldn't say how it'd react.
 

Users who are viewing this thread

Top Bottom