Without Matching Query Limit ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,
The Without Matching query wizard works fine until you select more than 13
columns to compare. My guess is that the 13 " AND " 's in the SQL left join
statement are the problem.
Unfortunely, my tables have 16 fields to compare.

Any sugestions ?
 
Stuart said:
The Without Matching query wizard works fine until you select more than 13
columns to compare. My guess is that the 13 " AND " 's in the SQL left join
statement are the problem.
Unfortunely, my tables have 16 fields to compare.
Click to expand...


Don't use the wizard ;-)

Seriously, after you do what you can with the wizard, open
the query in SQL view and type the rest of your conditions
following the style of what the wizard did for each field.
 
Stuart

Perhaps if you describe a bit more about the underlying data (it all starts
with the data) and the reason you are matching on so many fields, the folks
here in the newsgroup may be able to provide more specific suggestions.

If this were mine, I'd use the wizard to build the first 13, then modify the
query to get the rest (subject to the preceding question).

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks Guys,

I did start with the wizard and hand rolled from there. If I use more than
13 columns in my JOIN, it gives me incorrect data.

Each month I create a new table for export, that my users "work" for that
month.

They do not want to see the records from the previous month, as they've
already been "worked".

I could write vba to loop through each record and compare but I thought I
might try SQL to accomplish it.

Basically, I'm comparing 2 tables, give me the records that are not in both
tables.

thanks !
 
You might try moving some of the comparisons to the where clause. I think
that with that many fields involved, you are going to have a tough time.

Another possible solution would be to combine fields into a calculated field
and use that for part of the comparison.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
I just tried both without success...

Odd that it just stops @ 13 comparisons...

Oh, well. VBA to the rescue !

Thanks anyway !
 
Odd that it just stops @ 13 comparisons...
Click to expand...

Works for me e.g. using Northwind:

SELECT *
INTO Orders2
FROM Orders
WHERE OrderID < 10660;

SELECT *
FROM Orders AS T1
LEFT JOIN Orders2 AS T2
ON T1.OrderID = T2.OrderID
AND T1.CustomerID = T2.CustomerID
AND T1.EmployeeID = T2.EmployeeID
AND T1.OrderDate = T2.OrderDate
AND T1.RequiredDate = T2.RequiredDate
AND T1.ShippedDate = T2.ShippedDate
AND T1.ShipVia = T2.ShipVia
AND T1.Freight = T2.Freight
AND T1.ShipName = T2.ShipName
AND T1.ShipAddress = T2.ShipAddress
AND T1.ShipCity = T2.ShipCity
AND T1.ShipRegion = T2.ShipRegion
AND T1.ShipPostalCode = T2.ShipPostalCode
AND T1.ShipCountry = T2.ShipCountry
WHERE T2.OrderID IS NULL
AND T2.CustomerID IS NULL
AND T2.EmployeeID IS NULL
AND T2.OrderDate IS NULL
AND T2.RequiredDate IS NULL
AND T2.ShippedDate IS NULL
AND T2.ShipVia IS NULL
AND T2.Freight IS NULL
AND T2.ShipName IS NULL
AND T2.ShipAddress IS NULL
AND T2.ShipCity IS NULL
AND T2.ShipRegion IS NULL
AND T2.ShipPostalCode IS NULL
AND T2.ShipCountry IS NULL;

DROP TABLE Orders2;

Jamie.

--
 
I don't think that 13 is a hard and fast limit. I just tried an eleven (11)
field LEFT join and got errors using that, while 10 seemed to work fine.

SPECULATION:
10 fields max in an index - could that be related and a join be limited to
ten fields. I don't know.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Hi Jamie,

Thanks for your attention to this...

I did not have all the WHERE specifications on my original SQL.
I tried your example, applied to my tables and it didn't work.

My guess is it has to do with my tables not having a primary key.
Unfortunately, this is probably one the few cases where a primary key doesn't
work in the data.

good try!
 
Agreed.

John Spencer said:
I don't think that 13 is a hard and fast limit. I just tried an eleven (11)
field LEFT join and got errors using that, while 10 seemed to work fine.

SPECULATION:
10 fields max in an index - could that be related and a join be limited to
ten fields. I don't know.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Click to expand...
 
I did not have all the WHERE specifications on my original SQL.
I tried your example, applied to my tables and it didn't work.
Click to expand...

May I ask what you mean by "didn't work"? Are you getting a failure/
error message? The first stage of troubleshooting is to reproduce the
problem and I haven't yet been able to reproduce it :) Post your
tables' structures, SQL DLL is preferred but not essential.

Reading your OP, I think I'd use a NOT EXISTS construct anyhow --
worth a try?. Northwind has so many nullable columns to code around
e.g.

SELECT *
FROM Orders AS T1
WHERE NOT EXISTS (
SELECT *
FROM Orders2 AS T2
WHERE T1.OrderID = T2.OrderID
AND IIF(T1.CustomerID IS NULL, -55, T1.CustomerID)
= IIF(T2.CustomerID IS NULL, -55, T2.CustomerID)
AND IIF(T1.EmployeeID IS NULL, -55, T1.EmployeeID)
= IIF(T2.EmployeeID IS NULL, -55, T2.EmployeeID)
AND IIF(T1.OrderDate IS NULL, -55, T1.OrderDate)
= IIF(T2.OrderDate IS NULL, -55, T2.OrderDate)
AND IIF(T1.RequiredDate IS NULL, -55, T1.RequiredDate)
= IIF(T2.RequiredDate IS NULL, -55, T2.RequiredDate)
AND IIF(T1.ShippedDate IS NULL, -55, T1.ShippedDate)
= IIF(T2.ShippedDate IS NULL, -55, T2.ShippedDate)
AND IIF(T1.ShipVia IS NULL, -55, T1.ShipVia)
= IIF(T2.ShipVia IS NULL, -55, T2.ShipVia)
AND IIF(T1.Freight IS NULL, -55, T1.Freight)
= IIF(T2.Freight IS NULL, -55, T2.Freight)
AND IIF(T1.ShipName IS NULL, -55, T1.ShipName)
= IIF(T2.ShipName IS NULL, -55, T2.ShipName)
AND IIF(T1.ShipAddress IS NULL, -55, T1.ShipAddress)
= IIF(T2.ShipAddress IS NULL, -55, T2.ShipAddress)
AND IIF(T1.ShipCity IS NULL, -55, T1.ShipCity)
= IIF(T2.ShipCity IS NULL, -55, T2.ShipCity)
AND IIF(T1.ShipRegion IS NULL, -55, T1.ShipRegion)
= IIF(T2.ShipRegion IS NULL, -55, T2.ShipRegion)
AND IIF(T1.ShipPostalCode IS NULL, -55, T1.ShipPostalCode)
= IIF(T2.ShipPostalCode IS NULL, -55, T2.ShipPostalCode)
AND IIF(T1.ShipCountry IS NULL, -55, T1.ShipCountry)
= IIF(T2.ShipCountry IS NULL, -55, T2.ShipCountry)
);
My guess is it has to do with my tables not having a primary key.
Unfortunately, this is probably one the few cases where a primary key doesn't
work in the data.
Click to expand...

I doubt it. SQL tables don't require a PK nor do they require a FK to
exist to be able to JOIN in a query. If you were hitting some kind of
limit I'd expect (demand!) a failure message.

Jamie.

--
 
I don't think that 13 is a hard and fast limit. I just tried an eleven (11)
field LEFT join and got errors using that, while 10 seemed to work fine.
Click to expand...

Out of interest, what were the errors? (surely the engine returns only
one error per SQL statement?)
SPECULATION:
10 fields max in an index - could that be related and a join be limited to
ten fields. I don't know.
Click to expand...

That would be the same as saying only 10 predicates in a WHERE clause,
which sounds wrong to me. I don't think index limit has anything to do
with it either because I can use 14 columns in a JOIN (upthread) and
the following example uses a JOIN which well exceeds the character
limit for both a table index and a table row:

CREATE TABLE Test
(
col01 VARCHAR(255) DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'
NOT NULL,
col02 VARCHAR(255) DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'
NOT NULL,
col03 VARCHAR(255) DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'
NOT NULL,
col04 VARCHAR(255) DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'
NOT NULL,
col05 VARCHAR(255) DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'
NOT NULL,
col06 VARCHAR(255) DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'
NOT NULL,
col07 VARCHAR(255) DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'
NOT NULL)
;
CREATE TABLE Test2
(
col01 VARCHAR(255) DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'
NOT NULL,
col02 VARCHAR(255) DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'
NOT NULL,
col03 VARCHAR(255) DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'
NOT NULL,
col04 VARCHAR(255) DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'
NOT NULL,
col05 VARCHAR(255) DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'
NOT NULL,
col06 VARCHAR(255) DEFAULT
'123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345'
NOT NULL,
col07 VARCHAR(255) DEFAULT
'12345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234A'
NOT NULL
)
;
INSERT INTO Test (col01) VALUES
('123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345')
;
INSERT INTO Test2 (col01) VALUES
('123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345')
;
SELECT *
FROM Test AS T1
LEFT JOIN Test2 AS T2
ON T1.Col01 = T2.Col01
AND T1.col02 = T2.col02
AND T1.col03 = T2.col03
AND T1.col04 = T2.col04
AND T1.col05 = T2.col05
AND T1.col06 = T2.col06
AND T1.col07 = T2.col07
;

Jamie.

--
 
The error was that although records were returned I was seeing "#DELETED#"
in the field I returned from one of the tables and I was getting more
records than expected. I should have gotten back 3 records (from my
sample), but I was getting back several hundred. So although the join
appeared to work I was getting erroneous results.

Access 2003 using SQL server as the backend. I probably should have
investigated further, but time constraints on my time to do this won't allow
me at present.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
The error was that although records were returned I was seeing "#DELETED#"
in the field I returned from one of the tables and I was getting more
records than expected. I should have gotten back 3 records (from my
sample), but I was getting back several hundred. So although the join
appeared to work I was getting erroneous results.

Access 2003 using SQL server as the backend. I probably should have
investigated further, but time constraints on my time to do this won't allow
me at present.
Click to expand...

Is the OP using SQL Server? I guess this makes it harder to isolate
the problem (Jet? SQL Server? driver? ODBC? DAO?) I've been testing
with mdb + ADO 2.8 + Jet 4.0 ole db provider and haven't been able to
reproduce a similar problem.

Jamie.

--
 
Hi All,

No error message, just wrong results.

I've paired it down to 2 records, and I get both records back when both
records are they same(copied t1 to t2).

With this latest attempt, I get no records when they're both different.
SQL:
SELECT *
FROM tf2
LEFT JOIN tf1
ON (tf2.[Medical Record Number] = tf1.[Medical Record Number])
AND (tf2.[Registration Date] = tf1.[Registration Date])
AND (tf2.[Ins #1 Code] = tf1.[Ins #1 Code])
AND (tf2.InsConverted = tf1.InsConverted)
AND (tf2.[Ins Plan#] = tf1.[Ins Plan#])
AND (tf2.[Ins #] = tf1.[Ins #])
AND (tf2.MRNum = tf1.MRNum)
AND (tf2.[Ins Paid] = tf1.[Ins Paid])
AND (tf2.[Chg Adj] = tf1.[Chg Adj])
AND (tf2.[Claim Balance] = tf1.[Claim Balance])
AND (tf2.[From Dos] = tf1.[From Dos])
AND (tf2.[Claim Status] = tf1.[Claim Status])
AND (tf2.Charged = tf1.Charged)
AND (tf2.[Dr #] = tf1.[Dr #])
AND (tf2.[Pat Name (Last, First M)] = tf1.[Pat Name (Last, First M)])
AND (tf2.[Pat #] = tf1.[Pat #])
AND (tf2.[Claim #] = tf1.[Claim #])

WHERE (tf2.Charged is null )
AND (tf2.[Dr #] is null )
AND (tf2.[Pat Name (Last, First M)] is null )
AND (tf2.[Pat #] is null )
and (tf2.[Medical Record Number] is null )
AND (tf2.[Registration Date] is null )
AND (tf2.[Ins #1 Code] is null )
AND (tf2.InsConverted is null )
AND (tf2.[Ins Plan#] is null )
AND (tf2.[Ins #] is null )
AND (tf2.MRNum is null )
AND (tf2.[Ins Paid] is null )
AND (tf2.[Chg Adj] is null )
AND (tf2.[Claim Balance] is null )
AND (tf2.[From Dos] is null)
AND (tf2.[Claim Status] is null )
AND (tf2.[Claim #] is null )
 
No error message, just wrong results.

I've paired it down to 2 records, and I get both records back when both
records are they same(copied t1 to t2).

With this latest attempt, I get no records when they're both different.
SQL:
SELECT *
FROM tf2
LEFT JOIN tf1
ON (tf2.[Medical Record Number] = tf1.[Medical Record Number])
AND (tf2.[Registration Date] = tf1.[Registration Date])
AND (tf2.[Ins #1 Code] = tf1.[Ins #1 Code])
AND (tf2.InsConverted = tf1.InsConverted)
AND (tf2.[Ins Plan#] = tf1.[Ins Plan#])
AND (tf2.[Ins #] = tf1.[Ins #])
AND (tf2.MRNum = tf1.MRNum)
AND (tf2.[Ins Paid] = tf1.[Ins Paid])
AND (tf2.[Chg Adj] = tf1.[Chg Adj])
AND (tf2.[Claim Balance] = tf1.[Claim Balance])
AND (tf2.[From Dos] = tf1.[From Dos])
AND (tf2.[Claim Status] = tf1.[Claim Status])
AND (tf2.Charged = tf1.Charged)
AND (tf2.[Dr #] = tf1.[Dr #])
AND (tf2.[Pat Name (Last, First M)] = tf1.[Pat Name (Last, First M)])
AND (tf2.[Pat #] = tf1.[Pat #])
AND (tf2.[Claim #] = tf1.[Claim #])
WHERE (tf2.Charged is null )
AND (tf2.[Dr #] is null )
AND (tf2.[Pat Name (Last, First M)] is null )
AND (tf2.[Pat #] is null )
and (tf2.[Medical Record Number] is null )
AND (tf2.[Registration Date] is null )
AND (tf2.[Ins #1 Code] is null )
AND (tf2.InsConverted is null )
AND (tf2.[Ins Plan#] is null )
AND (tf2.[Ins #] is null )
AND (tf2.MRNum is null )
AND (tf2.[Ins Paid] is null )
AND (tf2.[Chg Adj] is null )
AND (tf2.[Claim Balance] is null )
AND (tf2.[From Dos] is null)
AND (tf2.[Claim Status] is null )
AND (tf2.[Claim #] is null )
Click to expand...

Assuming no nullable columns:

SELECT *
FROM tf2
WHERE NOT EXISTS (
SELECT *
FROM tf1
WHERE tf2.[Medical Record Number] = tf1.[Medical Record Number]
AND tf2.[Registration Date] = tf1.[Registration Date]
AND tf2.[Ins #1 Code] = tf1.[Ins #1 Code]
AND tf2.InsConverted = tf1.InsConverted
AND tf2.[Ins Plan#] = tf1.[Ins Plan#]
AND tf2.[Ins #] = tf1.[Ins #]
AND tf2.MRNum = tf1.MRNum
AND tf2.[Ins Paid] = tf1.[Ins Paid]
AND tf2.[Chg Adj] = tf1.[Chg Adj]
AND tf2.[Claim Balance] = tf1.[Claim Balance]
AND tf2.[From Dos] = tf1.[From Dos]
AND tf2.[Claim Status] = tf1.[Claim Status]
AND tf2.Charged = tf1.Charged
AND tf2.[Dr #] = tf1.[Dr #]
AND tf2.[Pat Name Last, First M] = tf1.[Pat Name Last, First M]
AND tf2.[Pat #] = tf1.[Pat #]
AND tf2.[Claim #] = tf1.[Claim #]);

Jamie.

--
 
LIGHT BULB !!!!

"Assuming no nullable columns"

That was my problem the whole time !!!

I didn't have nulls until I got out to the 13th column of my data.

I went back to the original wizard-built/hand-rolled query, got rid of the
nulls and it worked !

I got tipped off after I ran your query and got the same results I had
originally.

Since nulls are all over the data:
-Create an extra field in each table
-Insert a concatenated string of all 16 columns in the field
-Use that field as my Unmatched Query item
-Get the result set and drop that extra column

Thanks Jamie and John for all your help, I've been working on this on & off
for a week !

Jamie Collins said:
No error message, just wrong results.

I've paired it down to 2 records, and I get both records back when both
records are they same(copied t1 to t2).

With this latest attempt, I get no records when they're both different.
SQL:
SELECT *
FROM tf2
LEFT JOIN tf1
ON (tf2.[Medical Record Number] = tf1.[Medical Record Number])
AND (tf2.[Registration Date] = tf1.[Registration Date])
AND (tf2.[Ins #1 Code] = tf1.[Ins #1 Code])
AND (tf2.InsConverted = tf1.InsConverted)
AND (tf2.[Ins Plan#] = tf1.[Ins Plan#])
AND (tf2.[Ins #] = tf1.[Ins #])
AND (tf2.MRNum = tf1.MRNum)
AND (tf2.[Ins Paid] = tf1.[Ins Paid])
AND (tf2.[Chg Adj] = tf1.[Chg Adj])
AND (tf2.[Claim Balance] = tf1.[Claim Balance])
AND (tf2.[From Dos] = tf1.[From Dos])
AND (tf2.[Claim Status] = tf1.[Claim Status])
AND (tf2.Charged = tf1.Charged)
AND (tf2.[Dr #] = tf1.[Dr #])
AND (tf2.[Pat Name (Last, First M)] = tf1.[Pat Name (Last, First M)])
AND (tf2.[Pat #] = tf1.[Pat #])
AND (tf2.[Claim #] = tf1.[Claim #])
WHERE (tf2.Charged is null )
AND (tf2.[Dr #] is null )
AND (tf2.[Pat Name (Last, First M)] is null )
AND (tf2.[Pat #] is null )
and (tf2.[Medical Record Number] is null )
AND (tf2.[Registration Date] is null )
AND (tf2.[Ins #1 Code] is null )
AND (tf2.InsConverted is null )
AND (tf2.[Ins Plan#] is null )
AND (tf2.[Ins #] is null )
AND (tf2.MRNum is null )
AND (tf2.[Ins Paid] is null )
AND (tf2.[Chg Adj] is null )
AND (tf2.[Claim Balance] is null )
AND (tf2.[From Dos] is null)
AND (tf2.[Claim Status] is null )
AND (tf2.[Claim #] is null )
Click to expand...

Assuming no nullable columns:

SELECT *
FROM tf2
WHERE NOT EXISTS (
SELECT *
FROM tf1
WHERE tf2.[Medical Record Number] = tf1.[Medical Record Number]
AND tf2.[Registration Date] = tf1.[Registration Date]
AND tf2.[Ins #1 Code] = tf1.[Ins #1 Code]
AND tf2.InsConverted = tf1.InsConverted
AND tf2.[Ins Plan#] = tf1.[Ins Plan#]
AND tf2.[Ins #] = tf1.[Ins #]
AND tf2.MRNum = tf1.MRNum
AND tf2.[Ins Paid] = tf1.[Ins Paid]
AND tf2.[Chg Adj] = tf1.[Chg Adj]
AND tf2.[Claim Balance] = tf1.[Claim Balance]
AND tf2.[From Dos] = tf1.[From Dos]
AND tf2.[Claim Status] = tf1.[Claim Status]
AND tf2.Charged = tf1.Charged
AND tf2.[Dr #] = tf1.[Dr #]
AND tf2.[Pat Name Last, First M] = tf1.[Pat Name Last, First M]
AND tf2.[Pat #] = tf1.[Pat #]
AND tf2.[Claim #] = tf1.[Claim #]);

Jamie.
Click to expand...
 
Back
Top