Eliminate records based on 2 fields (1 Viewer)

[lwarren1968]

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!

Date	GROSS AMT1	FEE AMT1	NET AMT1	Expense	Type	Time	GROSS AMT2
4/1/2020	159.98	-4.94	155.04		Express Checkout Payment	4:06:12	159.98
4/1/2020	159.98	-4.94	155.04		Express Checkout Payment	4:15:22	159.98
4/1/2020				-5096.04	General Withdrawal	5:17:36	0
4/1/2020	31.02	-1.2	29.82		Express Checkout Payment	7:10:51	31.02
4/1/2020	-31.02	0	-31.02		Payment Refund	7:14:09	-31.02
4/1/2020	22.98	-0.97	22.01		Express Checkout Payment	7:45:27	22.98
4/1/2020	24.03	-1	23.03		Express Checkout Payment	10:05:17	24.03
4/1/2020	-24.03	0	-24.03		Payment Refund	10:07:05	-24.03
4/1/2020	50.97	-1.78	49.19		Express Checkout Payment	10:30:09	50.97
4/1/2020	14.97	-0.73	14.24		Express Checkout Payment	11:18:44	14.97
4/1/2020	34.98	-1.31	33.67		Express Checkout Payment	12:57:37	34.98
4/13/2020				-28.99	eBay Auction Payment	10:37:09	0
4/13/2020	117.98	-3.72	114.26		Express Checkout Payment	11:23:02	117.98
4/13/2020	227.85	-6.91	220.94		Express Checkout Payment	11:27:01	227.85
4/14/2020	369.98	-11.03	358.95		Express Checkout Payment	6:13:32	369.98
4/14/2020	65.98	-2.21	63.77		Express Checkout Payment	6:34:50	65.98
4/14/2020	66.32	-2.22	64.1		Website Payment	7:28:57	66.32
4/14/2020	59.05	-2.01	57.04		Express Checkout Payment	8:35:28	59.05
4/14/2020	12.83	-0.67	12.16		Express Checkout Payment	10:18:30	12.83
4/14/2020	25.19	-1.03	24.16		Website Payment	14:50:27	25.19
4/14/2020	20.97	-0.91	20.06		Express Checkout Payment	19:50:51	20.97
4/15/2020				-8164.01	General Withdrawal	7:57:13	0
4/15/2020				-136	PreApproved Payment Bill User Payment	9:33:06	0
4/15/2020				-185.45	Website Payment	15:25:11	0
4/15/2020				-74.95	PreApproved Payment Bill User Payment	17:47:57	0
4/15/2020	25.68	-1.04	24.64		Express Checkout Payment	18:48:48	25.68

 

[bastanu]

Can you highlight in your pic the records you want to remove?
Cheers,

 

[lwarren1968]

See attached. I am typically manually deleting those records highlighted.

 

[mdnuts]

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]

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]

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]

Basically, yes. I have not seen an instance where there was a General Withdrawal other then the to/from date.

 

[lwarren1968]

Basically, yes. I have not seen an instance where there was a General Withdrawal other then the to/from date. I think the 1st instance and last or next instance make most sense.

 

[mdnuts]

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.

 

[lwarren1968]

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.

 

[lwarren1968]

Can I put this in a query instead of a report?

 

[mdnuts]

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.