Join when matching field are blank (1 Viewer)

[tanyamc]

I have two tables that have 4 columns in common. One of the columns can be blank for some entries. My query will not return these as matches.

Example
Table 1 and Table two have columns that include the following matching information for a given Item

Location
Name
Type
Purpose

For some Items, the Type is (correctly) blank. My query returns the records that match between the tables, except for the Items that have blank Type.

How can I get it to see the blanks as matches? Thanks.

 

[MajP]

You can do something like this concatenating a ""

SELECT [tblb].[userid] & ""    AS NewUserID, 
       [tblb].[firstname] & "" AS NewFirst, 
       [tblb].[lastname] & ""  AS NewLast, 
       tblb2.firstname, 
       tblb2.lastname, 
       tblb.uniqueid           AS TblBID, 
       tblb2.uniqueid          AS TblB2ID 
FROM   tblb, 
       tblb2 
WHERE  ( ( ( [tblb].[userid] & "" ) = [tblb2].[userid] & "" ) 
         AND ( ( [tblb].[firstname] & "" ) = [tblb2].[firstname] & "" ) 
         AND ( ( [tblb].[lastname] & "" ) = [tblb2].[lastname] & "" ) );

You may be interested in the application I built in this thread
https://www.access-programmers.co.uk/forums/showthread.php?t=303677
It is a long thread but around page 4 I demonstrate an approach for doing this with many fields. The application is still a little rough but gets the job done.

 

[isladogs]

As well as the link provided by MajP, have a look at this extended article about Synchronising Data on my website http://www.mendipdatasystems.co.uk/synchronise-data-1/4594514001

 

[The_Doc_Man]

The problem is also one of clarity of language. When you say "blank" ...

Two blank fields can be different if one has two blanks while the other has three blanks. They would compare as "not equal."

Two EMPTY fields should be the same because empty implies a length of zero.

It CAN happen for a table (and more often for a JOIN query) that sometimes a field will be NULL. Two null fields are NEVER equal to each other. In this context, I am not talking about the ASCII NUL character, either. THAT, you could compare.

So... when you say you can't compare blanks, how uniform are these blanks?

 

[tanyamc]

The type cell can be correctly empty. All other cells will have data. I can filter for the rows using (Blanks) so they do not contain a space. I have to match type for the other items that have multiple types because the type affects my process for which I need the report.


To continue my example, I have a table of insured items. I have a query of items purchased. Not all item purchased are insured. I only want to show the items that were purchased and insured. A specific type of an item, if applicable, may not be insured. The type is not applicable to all items. These are the items that are not showing up on my query when I match the table and query.

 

[MajP]

query 1: join items purchased to insured items
query 2: return purchased items of the type that do not need to be insured
query 3: Select * from query1 UNION Select * from query2

 

[tanyamc]

So for query 1, I just removed the Type from my joins, and it returned the ones with blank types along with lots of items that are not insured. I am a newbie, so please bear with me. I don't know how to query 2.

I have a table of insurable items by category and type. (TI)
I have a query of purchased items category and type. (QP)
I have added a query (Q1) that lists all purchased items in categories that are insurable, so it includes types that are not insurable.

What is the difference in comparing the TI and QP and Q1 and TI since types are still blank?

PS There are non-insurable items that don't have a type as well, if that matters.

 

[MajP]

Is T1 and QP list of specific items or a list of types of items? If it is specific items then a query of QP to T1 joined by item id returns the list of purchased and insured items. If you query QP and return all items that do not require insurance you have a list all items not requiring insurance but it will include those that do have insurance anyways. The nice thing about a union query is that it will not duplicate items. So if an item is insured but not required to be insured it will be returned in your first query. It will also be in the second query that returns all items that do not need insurance. When you union them it will no duplicate that item, but only add it once.

 

[tanyamc]

T1 is all possible items that can be insured in that location by type and purpose.
QP is all items that have been purchased at that location by type and purpose, along with additional data about the purchase (i.e. amount and customer).
They don't have unique identifiers, although each record is unique.

Example
T1 (Insurable Items - includes records for multiple locations)
Location Name Type Purpose
SD Item1 BLUE CUT
SD Item1 BLUE EAT
SD Item2 (BLANK) CUT
SD Item2 (BLANK) EAT
SD Item3 RED CUT


QP (Purchased Items - records for one location only)
Location Name Type Purpose Amount Customer
SD Item1 BLUE CUT 10 John
SD Item2 (BLANK) CUT 8 Joe
SD Item4 YEL CUT 6 Jim
SD Item1 BLUE SERVE 7 Jane
SD Item5 EAT 30 Julie

When I match the two files, I want to get the first two records from the QP . But my query is only returning the first record.

If I join T1 and QP without TYPE, I get all Item1 and Item2 records. Includes insured (records 1 & 2) and not insured (record 4).
Record 3 & 5 are not insurable items.

 

[MajP]

Not sure of what you want for final results. There is no mention of Purpose.

I think this is what you want. All records that match by name and purpose and match by type or type is null. That goes back to my original.

SELECT q1.location, 
       q1.name, 
       [q1].[type] & "" AS NewType, 
       q1.purpose, 
       q1.amount, 
       q1.customer 
FROM   q1, 
       t1 
WHERE  ( ( ( q1.name ) = [t1].[name] ) 
         AND ( ( [q1].[type] & "" ) = [t1].[type] & "" ) 
         AND ( ( q1.purpose ) = [t1].[purpose] ) ) 
ORDER  BY q1.name, 
          [q1].[type] & "";

Results

Location	Name	NewType	Purpose	Amount	Customer
SD	Item1	BLUE	CUT	10	John
SD	Item2		CUT	8	Joe

 

[MajP]

Or without the cartesian join

SELECT q1.location, 
       q1.name, 
       q1.type, 
       q1.purpose, 
       q1.amount, 
       q1.customer 
FROM   q1 
       INNER JOIN t1 
               ON ( q1.type & "" = t1.type & "" ) 
                  AND ( q1.name = t1.name ) 
                  AND ( q1.purpose = t1.purpose ) 
ORDER  BY q1.name, 
          q1.type;

 

[tanyamc]

Not sure of what you want for final results. There is no mention of Purpose.

Sorry, yes all 4 columns must match. I assume this is something I would copy/paste into SQL type of query? Not to be obtuse but I am really new and self taught at Access! Thanks!

 

[tanyamc]

Can you tell me about the order portion? What does that do? Thanks

 

[MajP]

When you use the query designer in the background it creates the SQL code that gets stored. You can switch view from design view to sql view. Some queries cannot be shown in design view. The above query cannot be shown in design view. You can create a new query switch to SQL view and drop the SQL code there. Then save it.

The order by creates the sort order of the query. You can go here to see how to hand write sql
https://www.w3schools.com/sql/
Or build some for yourself and then swith to the sql view to see what is created.

I use this website to take what is in SQL and format it nicely to post on this site
http://www.dpriver.com/pp/sqlformat.htm

 

[tanyamc]

Thank you.

My table names and columns have spaces in them. I'm guessing that's a problem?

I get a syntax error.

 

[MajP]

You should avoid any spaces in any names of anything in Access. For fields and table names you usually will have to wrap them in square brackets [Table Name].[Field Name]

 

[tanyamc]

I got it to work with ` marks, but now I get a syntax error in the Join. Will mess with it again on Monday I guess.

 

[tanyamc]

I noticed that two of my items are not listed the same in my linked tables. Is there a way to use a query of a linked table to replace the name so it matches/standardizes? Could I do that to get rid of the blanks by replacing them with 'none' using a query?

 

[MajP]

You can do update queries if that is what you mean.
https://www.w3schools.com/sql/sql_update.asp

 

[tanyamc]

UPDATE NAP_Crops_Dupl_Table SET NAP_Crops_Dupl_Table.[Crop Name] = IIf([Crop Name]='Sorghum Dual - Purpose',"Sorghum, Dual Purpose",[Crop Name]), NAP_Crops_Dupl_Table.[Crop Type] = IIf(IsNull([Crop Type]),"NONE",[Crop Type]);

The "NONE" portion works to get rid of my blanks, but the name correction part doesn't.

Almost there! Thanks.