SQL help omitting records

[lwarren1968]

Attached is a payment file that I need help with please. Data is retrieved via a to/from date. In this case its from 09/03/2021 to 09/15/2021. I do this on a bi-weekly bases. What I need to do is eliminate the General Withdrawal record if one exists at the beginning of my date range along with all records prior to that General Withdraw record and then eliminate the General Withdrawal record if one exist at the end date range along with those records after that General Withdraw. There is a time stamp that I'm sure can play a part in omitting. I'm just not sure how to add to my query. Below is the SQL I am currently using.

Currently I am just deleting manually once exported to excel, but I'd like to automate if possible. Any help would be greatly appreciated!

SELECT PAYPAL_PYMNT_Query.Date, PAYPAL_PYMNT_Query.CUSTOMER1 AS CUSTOMER, PAYPAL_PYMNT_Query.[GROSS AMT], PAYPAL_PYMNT_Query.[FEE AMT], PAYPAL_PYMNT_Query.[NET AMT], PAYPAL_PYMNT_Query.EXPENSES
FROM PAYPAL_PYMNT_Query;

 

[theDBguy]

I don't see an attachment... just FYI.

 

[lwarren1968]

I don't see an attachment... just FYI.

Sorry, I didn't realize I could attach an excel file. Here you go. Thanks.

 

[plog]

It would be best if we helped closer to the beginning of the issue and not 3 steps in. We need data prior to it hitting your query that doesn't work. Can you provide us with a sample database with the tables at the root of PAYPAL_PYMNT_Query?

 

[lwarren1968]

It would be best if we helped closer to the beginning of the issue and not 3 steps in. We need data prior to it hitting your query that doesn't work. Can you provide us with a sample database with the tables at the root of PAYPAL_PYMNT_Query?

 

[lwarren1968]

That is the data. I'm confused with your question? I literally go out to PayPal enter a to/from date and that is the data I receive with the exception of omitting/masking the name of those who purchased. It comes in a text file that I link to MS Access and export what I need. There is other data, but it is not relative to what I am using the data for.

 

[theDBguy]

That is the data. I'm confused with your question? I literally go out to PayPal enter a to/from date and that is the data I receive with the exception of omitting/masking the name of those who purchased. It comes in a text file that I link to MS Access and export what I need. There is other data, but it is not relative to what I am using the data for.

Hi. When I imported your sample file into an Access table, this is what I got.

When I run my query, this is what I got.

Here's the SQL for the above query.

SELECT Sample.*
FROM Sample
WHERE Sample.[Date]+CDate(Sample.[Time])>DMin("Date + CDate([Time])","Sample","Customer='General Withdrawal'")
AND Sample.[Date]+CDate(Sample.[Time])<DMax("Date + CDate([Time])","Sample","Customer='General Withdrawal'")

However, the above query only works if there are two "General Withdrawal" in your data. It would require some logic tests to see if any are present or missing. So, it might be easier to simply add them in, if they are missing, so you can run this query.

Hope that helps...

 

[plog]

I misunderstood, I thought this data was in an Access table. But that raises another question--How would Access make this process simpler? You have to double click to open Access to process this, at best you double click a macro to export your finished file, then you click to close Access.

Your "manual" process requires a double click to open the file, Ctrl+F and some typing to find the "General Withdrawal" line, then you select and delete and close and save the file. Access saves you at best 5 seconds per finished file.

That aside, I understnad your rules for 2 "General Withdrawal" Records, but what if only 1 General Withdrawal record? What if 3?

 

[lwarren1968]

I misunderstood, I thought this data was in an Access table. But that raises another question--How would Access make this process simpler? You have to double click to open Access to process this, at best you double click a macro to export your finished file, then you click to close Access.

Your "manual" process requires a double click to open the file, Ctrl+F and some typing to find the "General Withdrawal" line, then you select and delete and close and save the file. Access saves you at best 5 seconds per finished file.

That aside, I understnad your rules for 2 "General Withdrawal" Records, but what if only 1 General Withdrawal record? What if 3?

 

[plog]

This below method will work on your Linked Table to text file if your data contains 2 "General Withdrawal" records:

SELECT CDate([Date] & " " & [Time]) AS PaymentDate, PAYPAL_PYMT_Query.CUSTOMER, PAYPAL_PYMT_Query.[GROSS AMT] AS Gross, PAYPAL_PYMT_Query.[FEE AMT] AS Fee, PAYPAL_PYMT_Query.[NET AMT] AS Net, PAYPAL_PYMT_Query.EXPENSES, PAYPAL_PYMT_Query.Date
FROM PAYPAL_PYMT_Query
WHERE (((PAYPAL_PYMT_Query.Date) Is Not Null));

Paste that into a query object and call it 'sub1'. It cleans up your field names (removes spaces), combines your Date & Time fields and skips over records without any date (linked text files tend to include a lot of blank records). Next save this query:

SELECT Min(sub1.PaymentDate) AS FirstGeneralWithdrawal, Max(sub1.PaymentDate) AS LastGeneralWithdrawal, Count(sub1.PaymentDate) AS CountOfPaymentDate
FROM sub1
WHERE (((sub1.CUSTOMER)="General Withdrawal"));

As 'sub2'. It finds the date/time of your 2 "General Withdrawal" records, so you can use those date/times to exclude the necessary records. Then finally this query will produce the results you want:

SELECT sub1.PaymentDate, sub1.CUSTOMER, sub1.Gross, sub1.Fee, sub1.Net, sub1.EXPENSES
FROM sub1, sub2
WHERE (((sub1.PaymentDate)>[FirstGeneralWithdrawal] AND (sub1.PaymentDate)<[LastGeneralWithdrawal]));

If you only have 1 its gonna fail, if you have 3 or more it might work.

 

[bastanu]

Please have a look at the attached sample.
Cheers,
Vlad

 

[lwarren1968]

Okay, I initiate the General withdraw manually on a by weekly bases. It is not an auto process. That being said there could be sales prior to me withdrawing the funds that are captured in the prior General Withdraw and already accounted for hence me omitting and the same for the end Withdraw. I can only select a date to/from via PayPal, hence I get all sales for the to/from date range regardless. So, yes there is a possibility of not have a withdraw at the end, but typically I'll have one at the beginning because of sales overlapping into the day. That why I say "if one exist".

 

[lwarren1968]

Please have a look at the attached sample.
Cheers,
Vlad

This works perfectly regardless of there being a General Withdraw or not. Thank you.

 

[lwarren1968]

I don't see an attachment... just FYI.

thank you.

 

[lwarren1968]

Hi. When I imported your sample file into an Access table, this is what I got.

View attachment 94452

When I run my query, this is what I got.

View attachment 94453

Here's the SQL for the above query.

SELECT Sample.*
FROM Sample
WHERE Sample.[Date]+CDate(Sample.[Time])>DMin("Date + CDate([Time])","Sample","Customer='General Withdrawal'")
AND Sample.[Date]+CDate(Sample.[Time])<DMax("Date + CDate([Time])","Sample","Customer='General Withdrawal'")

However, the above query only works if there are two "General Withdrawal" in your data. It would require some logic tests to see if any are present or missing. So, it might be easier to simply add them in, if they are missing, so you can run this query.

Hope that helps...

Thank you.

 

[theDBguy]

This works perfectly regardless of there being a General Withdraw or not. Thank you.

Hi. I guess I misunderstood your requirements. It looks like this just eliminates those records with "General Withdrawal" in them. I thought you also wanted to remove all the records before and after each "General Withdrawal?"

Have you tried my suggestion? Just curious...

 

[theDBguy]

Just to clarify, here's what I mean. Hope you can see the difference.

Basically, 125 records versus 132 records in the final result. Which one did you want to get?

But, if I misunderstood, then please ignore me. Cheers!

 

[lwarren1968]

Just to clarify, here's what I mean. Hope you can see the difference.

View attachment 94455

View attachment 94456

Basically, 125 records versus 132 records in the final result. Which one did you want to get?

But, if I misunderstood, then please ignore me. Cheers!

Yes,

Hi. When I imported your sample file into an Access table, this is what I got.

View attachment 94452

When I run my query, this is what I got.

View attachment 94453

Here's the SQL for the above query.

SELECT Sample.*
FROM Sample
WHERE Sample.[Date]+CDate(Sample.[Time])>DMin("Date + CDate([Time])","Sample","Customer='General Withdrawal'")
AND Sample.[Date]+CDate(Sample.[Time])<DMax("Date + CDate([Time])","Sample","Customer='General Withdrawal'")

However, the above query only works if there are two "General Withdrawal" in your data. It would require some logic tests to see if any are present or missing. So, it might be easier to simply add them in, if they are missing, so you can run this query.

Hope that helps...

Yes, it works perfectly and actually your method is more of what I wanted. Simplified, however it does only work if there are 2 General Withdrawal. If I remove the 1st one from the orig. file and those records before leaving on one it removes the one remaining Withdrawal record, but not those records after to Withdraw?

 

[theDBguy]

Yes,

Yes, it works perfectly and actually your method is more of what I wanted. Simplified, however it does only work if there are 2 General Withdrawal. If I remove the 1st one from the orig. file and those records before leaving on one it removes the one remaining Withdrawal record, but not those records after to Withdraw?

Hi. I did mention that was the drawback with the simplified query I suggested and offered a way to work around it. I also mentioned that handling special situations would need a more complex query. I didn't hear back from you which way you wanted to go.

 

[theDBguy]

Hi. I did mention that was the drawback with the simplified query I suggested and offered a way to work around it. I also mentioned that handling special situations would need a more complex query. I didn't hear back from you which way you wanted to go.

Also, we'll probably need more information about your process. For example, let's say your imported/linked table has 100 records in it with only one "General Withdrawal" in the middle (let's say record #50). In that case, which records would you want returned? Records 1 through 49 or records 51 through 100?