Ajax,
I tried that.
So here's the full SQL WITHIN Access and it SOMEWHAT works IN Access, where the InvNo = LTS0001446, from the sample results shown is NOT showing.
Code:
SELECT dbo_v010LearnToSkateMembersPaymentsDetails.[InvNo], dbo_v010LearnToSkateMembersPaymentsDetails.[USFSANo], dbo_v010LearnToSkateMembersPaymentsDetails.[Registration Fee], dbo_v010LearnToSkateMembersPaymentsDetails.[Shipping/Processing], dbo_v010LearnToSkateMembersPaymentsDetails.[ItemNo], dbo_v010LearnToSkateMembersPaymentsDetails.[CustNo], dbo_v010LearnToSkateMembersPaymentsDetails.[ClassID]
FROM dbo_v010LearnToSkateMembersPaymentsDetails
WHERE (((dbo_v010LearnToSkateMembersPaymentsDetails.[InvNo]) In (SELECT [InvNo] FROM [dbo_v010LearnToSkateMembersPaymentsDetails] As Tmp WHERE ClassID <> 'ADMINFEE' GROUP BY [InvNo] HAVING Count(*)>1 )))
ORDER BY dbo_v010LearnToSkateMembersPaymentsDetails.[InvNo];
InvNo |
USFSANo |
Registration Fee |
Shipping/Processing |
ItemNo |
CustNo |
ClassID |
LTS0001445 |
1619953 |
12 |
0 |
LTS-NMEM |
ORG00002838 |
DUES-LTSMB |
LTS0001445 |
1916 |
0 |
0.25 |
FRGHT-LTSMB |
ORG00002838 |
SHIPPING |
LTS0001445 |
1916 |
0 |
0.25 |
FRGHT-LTSMB |
ORG00002838 |
SHIPPING |
LTS0001451 |
1619966 |
12 |
0 |
LTS-NMEM |
PER01592434 |
DUES-LTSMB |
LTS0001451 |
1619966 |
12 |
0 |
LTS-NMEM |
PER01592434 |
DUES-LTSMB |
LTS0001451 |
1619966 |
12 |
0 |
LTS-NMEM |
PER01592434 |
DUES-LTSMB |
HOWEVER, it's incorrect result AND in SQL View, it is STILL producing the InvNo = LTS0001446
I do NOT fully understand the difference in Access vs on SQL Server, it's the SAME syntax and yet it produces the correct result in Access and NOT on SQL Server
On SQL Server
Code:
SELECT InvNo, USFSANo, [Registration Fee], [Shipping/Processing], ItemNo, CustNo, ClassID
FROM dbo.v010LearnToSkateMembersPaymentsDetails
WHERE (InvNo IN
(SELECT dbo.v010LearnToSkateMembersPaymentsDetails.InvNo
FROM dbo.v010LearnToSkateMembersPaymentsDetails AS Tmp
WHERE (ClassID <> 'ADMINFEE')
GROUP BY InvNo
HAVING (COUNT(*) > 1)))
LTS0001445 |
1619953 |
12.00000 |
0.00000 |
ORG00002838 |
DUES-LTSMB |
LTS0001445 |
1916 |
0.00000 |
0.25000 |
ORG00002838 |
SHIPPING |
LTS0001445 |
1916 |
0.00000 |
1.00000 |
ORG00002838 |
ADMINFEE |
LTS0001446
|
2753 |
0.00000 |
0.25000 |
ORG00004402 |
SHIPPING |
LTS0001446 |
2753 |
0.00000 |
1.00000 |
ORG00004402 |
ADMINFEE |
LTS0001451 |
1619966 |
12.00000 |
0.00000 |
PER01592434 |
DUES-LTSMB |
LTS0001451 |
1619966 |
0.00000 |
0.25000 |
PER01592434 |
SHIPPING |
LTS0001451 |
1619966 |
0.00000 |
1.00000 |
PER01592434 |
ADMINFEE |
|
|
|
|
|
Orange, does this help?
GOAL:
Trying to EXCLUDE all invoices where it ONLY has ADMINFEE and /or SHIPPING ... WITHOUT a Registration Fee on the Invoice. So in the sample result above with the LTS0001446 in RED, these types of invoices should be excluded.