Unmatched records - what if the source table has a null value?

[peskywinnets]

So I'm having some diffs grasping something that is probably fundamental to getting to grips with databases!

I wish to establish some unmatched records...customer address information to be precise.

So, a new customer gets appended to an incoming 'scratch' table. Towards normalising the data, I firstly need to check if this customer exists in the customer table, so I've set up three queries...

Query 1 pulls out Unique records (to avoid duplicates) from the scratch table
Query 2 checks to see which of the above are unmatched in the Customer Table
Query 3 Actually appends the above the unmatched to the customers table

Is my approach correct?

Ok, assuming it is ....sometimes the customer doesn't put an entry for 'address line 2' ...but many do & because of this I therefore ought to find 'unmatched' to include 'address line 2' as well ....but if some customers haven't entered anything (null) then how can I find unmatched entries in the customer table against the value?

 

[sneuberg]

You can put functions the join so you could use Is Null to put in the case where the addresses are null. Something like

((Table1.[CustomerAddess]=Table2.[CustomerAddess]) Or (Table1.[CustomerAddess] Is Null AND Table2.[CustomerAddess] Is Null)) AND (Table1.[CustomerName]=Table2.[CustomerName])

So in this the both the condition of the Addresses being equal or both being null are considered. Note that the Ored expressions are in parenthesis as Or has a low precedence than And. Also if you put a function in the join you can only view the query in the SQL view, Access can't display this in the grid view.

 

[sneuberg]

Another way to do this would be to create queries for the tables involved and use the queries in place of the tables. These queries would have an expression for the address which would substitute a string for the null addresses. In the example expression below I substitute "NoAddress" for the null Addresses.

CusAddr: IIf([Table2]![CustomerAddess] Is Null,"NoAddress",[Table2]![CustomerAddess])

The advantage to this is that you can create your joins in the query designer design (grid) view as there are no function in the join. The disadvantage is having to remove the substitute string in the final result.

 

[Galaxiom]

Best practice is to use

WHERE [field] Is Null

rather than the function

IsNull([field])

Performance isn't much different when a field has no index but Is Null is significantly better on an indexed field because the engine cannot use the index with IsNull().

 

[peskywinnets]

Thanks....some good stuff to ponder there!

 

[peskywinnets]

So just revisiting my source data (scratch tables temporarily 'holding' incoming data), it seems wrt Amazon vs Ebay data....for Amazon if there's no ShippingAddress2 data entered by the customer, then Amazon use a null for the field, whereas Ebay if there's no ShippingAddress2 entered by the customer, then Ebay use a zero length field.

Now having googled it seems I can join tables on zero length fields...so in conclusion would it not just be better to massage the incoming Amazon data to change any nulls to "" ? (then I can join tables freely)

 

[sneuberg]

In effect that would be doing the same thing as I suggested in post #3 except that you would be using "" instead of "NoAddress". I'm fairly certain that would work but personally I wouldn't want empty strings in my data as you can't see if it is null or an empty string. If someone inherits this system from you and wants to query for records without addresses they probably wouldn't think to use "" rather than Is Null as a criteria.

This is just my opinion. I've actually read some arguments for the use of empty strings in data. The proponent asserted that Null means "there is none" while an empty string indicates the value is unknown. If you go with using empty strings I suggest documenting it somehow, perhaps in the field's description.

 

[peskywinnets]

Thanks.

being relatively new to databases, I'm still grasping what the best practises are.... so "" (zero length fields) are frowned upon? (to my untrained eye, if you can join/match on a "", then I think it's better to use 'em!)

Yes, I agree, in effect my proposed use of "" is the same as your "NO ADDRESS" as suggested earlier ....but with two small benefits...I don't have to remove the "NO ADDRESS" again when using the data on a customer Invoice (if I don't do this, the data will show up as "NO ADDRESS" on his invoice *unless* I massage it out again) & secondly, over time my "" will use less database space vs "NO ADDRESS"!

Point taken about the documentation (though I'm doing this for my own business...& it's just me & my wife, so I doubt this will bite anyone down the line)

 

[jdraw]

Pesky,

Identify any/all "flags/indicators" you use. Keep a current list in your documentation.
Create test data scenarios and test/confirm your code.

You may find some things in conflict as you move forward (add complexity) with your database/processes. Having a current test package will save you time and frustration.

Good luck

Some additional info on Is Null and IsNull()
.

 

[peskywinnets]

Pesky,

Identify any/all "flags/indicators" you use. Keep a current list in your documentation.
Create test data scenarios and test/confirm your code.

You may find some things in conflict as you move forward (add complexity) with your database/processes. Having a current test package will save you time and frustration.

Good luck

Some additional info on Is Null and IsNull()
.

I've always been a bit rubbish at this aspect! I'll take those comments on board though

I now have 8 weeks left before my current subscription runs out (i.e. with my current 3rd party 'order management system' who I'm using at present & that I'm now trying to replace urgently with my own Access deployment), so I'm on track....the worst part is now behind me. I now have all my Ebay/Amazon sales data in access, normalised, formatted (it was an unbelievable amount of time spent coming up with new functions to 'clean' up the supplied address data from Ebay & Amazon).

I now just have to work out how to put a slick GUI on top of all this data (to reflect the 3rd party system I'm using at the moment)....I've a lot of learning to do with forms in particular.....hey ho, onwards & upwards....

Thanks for all your help...I certainly wouldn't be able to do all of this without the excellent input/help you're all graciously providing

 

[jdraw]

Pesky,

We want you to succeed. Make it structurally correct. You can always make it prettier. But if your foundation (tables and relationships must match your requirements) is not correct -- all the lipstick in the world won't cure that issue.

Continued good luck.