Discrepant record numbers between query and table it is based on.

[natsirtm]

I have a temporary table with all text fields for importing data to a normalized table. In order to import I need to prep the data by pulling the numeric values of "lookup" type fields (nb. not actually using MS Access lookups)

My prep query is producing 9 more records than there is rows in the import table, even though every join is a LEFT join, which should just pull all import rows and match up lookup values where they are available.

eg. of import table (all fields are text)

Location;
Group;
Name;
Wfm type;
Account Number;
WFM Status;
Date Created;
Date Held To;
processdate

Beachville Operating Centre;
Service Orders;
DAVID JONES;
;
Complete Engineering Investigation;
Completed;
4/20/2007;
;
4/27/2007

here is my prep query (the iif is to replace userid's that can't be found with UNKNOWN)

SELECT lookup_location.val AS F1location, lookup_group.val AS F2group, IIf([lookup_user.val] Is Not Null,[lookup_user.val],"UNKNOWN") AS F3user, wfm.wfmcode AS F4wfmcode, CURPW020_import.[Account Number] AS F5account, lookup_status.val AS F6status, CURPW020_import.[Date Created] AS F7datecreated, CURPW020_import.[Date Held To] AS F8dateheldto, CURPW020_import.processdate AS F9processdate
FROM ((((CURPW020_import LEFT JOIN lookup_location ON CURPW020_import.Location = lookup_location.txt) LEFT JOIN lookup_group ON CURPW020_import.Group = lookup_group.txt) LEFT JOIN lookup_user ON CURPW020_import.Name = lookup_user.txt) LEFT JOIN wfm ON CURPW020_import.[Wfm type] = wfm.wfmname) LEFT JOIN lookup_status ON CURPW020_import.[WFM Status] = lookup_status.txt;

any assistance would be much appreciated. any other info needed let me know.

 

[jal]

Extra records means redundancy somewhere, one of your tables probably has duplicate matchup-records/IDs somewhere.

On a related note, I believe that LEFT JOINs operate in sequence. Perhaps you are under the mistaken impression that each LEFT JOIN is preserving records only in the main table (the first table named in your FROM clause, i.e, the Import table). In my opinion, if you have

SELECT FROM T1 LEFT JOIN T2 LEFT JOIN T3

this results in two completely separate left-join operations. The first one left joins t1 and t2. The second one left joins the t1+t2 table to t3. This could possibly result in preserving more records than desired. What I am saying is that since the purpose of a left join is to preserve records potentially lost due to nonmatches, make sure you only use it where needed (that is, use it only on those joins suffering from insufficient matches). In other words you may only need one left join (or perhaps 2), and perhaps too many left joins could result in too many records. Unfortunately the syntax is such that the first join in a series has to be an inner join, as far as i know, if you intend to have both left joins and inner joins in your query. You may need to reorder the tables, therefore, and you may even need to use some right-joins due to the reordering.

 

[natsirtm]

The joins were good, it turned out we have multiple users with the same name.

 

[boblarson]

That would do it.