SQL help omitting records (1 Viewer)

lwarren1968

Registered User.
Local time
Yesterday, 23:42
Joined
Jan 18, 2013
Messages
77
Sorry, I was testing your sample and then got pulled away for another project. That being said, Yes you did indicate the draw back being it would only work with 2. I do however need it to work if there is only 1 or 2.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:42
Joined
Oct 29, 2018
Messages
21,358
Sorry, I was testing your sample and then got pulled away for another project. That being said, Yes you did indicate the draw back being it would only work with 2. I do however need it to work if there is only 1 or 2.
Hi. Did you see my last post? I asked which records you want the query to return if only 1 withdrawal is present.

Here's a sample query that will return the "after withdrawal" records. Just to show you it's possible.

Code:
SELECT Sample.*
FROM Sample
WHERE ((DCount("*","Sample","Customer='General Withdrawal'")>1 And ([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'")))
OR ((DCount("*","Sample","Customer='General Withdrawal'")=1 And ([Sample].[Date]+CDate([Sample].[Time]))>DLookup("Date + CDate([Time])","Sample","Customer='General Withdrawal'")));
 

bastanu

AWF VIP
Local time
Yesterday, 23:42
Joined
Apr 13, 2010
Messages
1,401
And in addition to theDBGuy's questions, are the withdrawals always happening in the start and\or end dates? If that is the case it would be easier to identify the right boundaries.

Cheers,
 

bastanu

AWF VIP
Local time
Yesterday, 23:42
Joined
Apr 13, 2010
Messages
1,401
Please review the attached updated file, should work now with 0,1 or 2 withdrawals.

Cheers,
 

Attachments

  • Database21_Updated.zip
    45.1 KB · Views: 88

Users who are viewing this thread

Top Bottom