Updating more recods than exist in the table?

[peskywinnets]

Updating more records than exist in the table?

Ok, I'm confused.

Towards normalizing data, I've updated a Customer Table with customer name & address information....I now want to copy the CustomerID back to the original table.

Here's my query...



...when I go to run it, Access informs that it's about to update 1625 records - BUT there's actually only a total of 1593 rows in the target table being updated?

 

[jdraw]

Pesky,

Tell us about each of these tables --plain English.
My first reaction is that you have hidden entities, but readers and you need to know what these tables represent in terms of your business.

Also UPDATE queries can be very unforgiving. Use test data; even copy your table to ensure you have a backup just in case. Test and confirm, then apply to your production/operational system.

Good luck.

 

[peskywinnets]

Actually I've just discovered a much worse problem...so I'd like to park the above for a second (the above is more of a puzzlement, than causing problems ....I think!)

Ok, to this latest (serious) problem

I have a 'incoming data' which goes into scratch table called AmazonOrders.

Towards normalizing this data, I 'pull out' all the customer name & address & add this to the customers table, here's the sequence....

1. Frm my source/scratch table (AmazonOrders) I select 'unique records' based on name, shippingaddress1,shippingaddress2 etc via a select query

2. I then do an unmatched query (against the customers table) based on the output of the query above.

3. I then do an append to the Customers table of the unmatched

...it mainly works, but there are clearly some records which are problematic (from step 3).

After the first run of the append query (which adds the bulk of 1400 customers), if I then immediately re-run the append query, it informs it's going to add 39 records to the customers table (there shouldn't be any as the append query has just been run!), so I run the append query & customers table 'no of records' increments by 39), if I run it immediately again, it says it's going to add 39 records ....rinse, repeat.

....this is illogical(!) ...because once those 39 customer records have been added, it shouldn't add again?

Here's now I'm establishing the unmatched...



Or if you prefer in SQL...

SELECT UniqueCustomersFromAmazonOrders.Email, UniqueCustomersFromAmazonOrders.BillingName, UniqueCustomersFromAmazonOrders.BillingAddress1, UniqueCustomersFromAmazonOrders.BillingAddress2, UniqueCustomersFromAmazonOrders.BillingAddress3, UniqueCustomersFromAmazonOrders.BillingCity, UniqueCustomersFromAmazonOrders.BillingRegion, UniqueCustomersFromAmazonOrders.BillingPostcode, UniqueCustomersFromAmazonOrders.BillingCountryCode, UniqueCustomersFromAmazonOrders.BillingPhone, UniqueCustomersFromAmazonOrders.ShippingName, UniqueCustomersFromAmazonOrders.ShippingAddress1, UniqueCustomersFromAmazonOrders.ShippingAddress2, UniqueCustomersFromAmazonOrders.ShippingAddress3, UniqueCustomersFromAmazonOrders.ShippingCity, UniqueCustomersFromAmazonOrders.ShippingRegion, UniqueCustomersFromAmazonOrders.ShippingPostcode, UniqueCustomersFromAmazonOrders.ShippingCountryCode, UniqueCustomersFromAmazonOrders.ShippingPhone, Customers.Email, Customers.BillingName, Customers.BillingAddress1, Customers.BillingAddress2, Customers.BillingAddress3, Customers.BillingCity, Customers.BillingRegion, Customers.BillingPostcode, Customers.BillingCountryCode, Customers.BillingPhone, Customers.ShippingName, Customers.ShippingAddress1, Customers.ShippingAddress2, Customers.ShippingAddress3, Customers.ShippingCity, Customers.ShippingRegion, Customers.ShippingPostcode, Customers.ShippingCountryCode, Customers.ShippingPhone
FROM UniqueCustomersFromAmazonOrders LEFT JOIN Customers ON (UniqueCustomersFromAmazonOrders.ShippingPhone = Customers.ShippingPhone) AND (UniqueCustomersFromAmazonOrders.ShippingCountryCode = Customers.ShippingCountryCode) AND (UniqueCustomersFromAmazonOrders.ShippingPostcode = Customers.ShippingPostcode) AND (UniqueCustomersFromAmazonOrders.ShippingRegion = Customers.ShippingRegion) AND (UniqueCustomersFromAmazonOrders.ShippingCity = Customers.ShippingCity) AND (UniqueCustomersFromAmazonOrders.ShippingAddress3 = Customers.ShippingAddress3) AND (UniqueCustomersFromAmazonOrders.ShippingAddress2 = Customers.ShippingAddress2) AND (UniqueCustomersFromAmazonOrders.ShippingAddress1 = Customers.ShippingAddress1) AND (UniqueCustomersFromAmazonOrders.BillingName = Customers.BillingName) AND (UniqueCustomersFromAmazonOrders.ShippingName = Customers.ShippingName) AND (UniqueCustomersFromAmazonOrders.BillingPhone = Customers.BillingPhone) AND (UniqueCustomersFromAmazonOrders.BillingCountryCode = Customers.BillingCountryCode) AND (UniqueCustomersFromAmazonOrders.BillingPostcode = Customers.BillingPostcode) AND (UniqueCustomersFromAmazonOrders.BillingRegion = Customers.BillingRegion) AND (UniqueCustomersFromAmazonOrders.BillingCity = Customers.BillingCity) AND (UniqueCustomersFromAmazonOrders.BillingAddress3 = Customers.BillingAddress3) AND (UniqueCustomersFromAmazonOrders.BillingAddress2 = Customers.BillingAddress2) AND (UniqueCustomersFromAmazonOrders.BillingAddress1 = Customers.BillingAddress1) AND (UniqueCustomersFromAmazonOrders.Email = Customers.Email)
WHERE (((Customers.Email) Is Null) AND ((Customers.BillingName) Is Null) AND ((Customers.BillingAddress1) Is Null) AND ((Customers.BillingAddress2) Is Null) AND ((Customers.BillingAddress3) Is Null) AND ((Customers.BillingCity) Is Null) AND ((Customers.BillingRegion) Is Null) AND ((Customers.BillingPostcode) Is Null) AND ((Customers.BillingCountryCode) Is Null) AND ((Customers.BillingPhone) Is Null) AND ((Customers.ShippingName) Is Null) AND ((Customers.ShippingAddress1) Is Null) AND ((Customers.ShippingAddress2) Is Null) AND ((Customers.ShippingAddress3) Is Null) AND ((Customers.ShippingCity) Is Null) AND ((Customers.ShippingRegion) Is Null) AND ((Customers.ShippingPostcode) Is Null) AND ((Customers.ShippingCountryCode) Is Null) AND ((Customers.ShippingPhone) Is Null));

I'm perplexed why the 'unmatched' query is showing 39 records as unmatched (against the customers table) when those same 39 records have just been appended?



EDIT/UPDATE: Ok, if I drop the joins on ShippingAddress1,ShippingAddress2 & ShippingAddress3 I don't get the 39 records constantly showing up as unmatched...



so there must be something about the shippingaddress1, shippingaddress2 & shippingaddress3 that access isn't joining properly (there are some foreign addresses with spanish, italalian & German text 'characters' in there - wondering if it's related to that? That said the shipping addess text content is the same as the billing address text content ...which has joined ok?!!). I'm confused...I think I need to take a break!

 

[jdraw]

Do you have a test database with some sample data? And a set of requirements that can act as scenarios against the test data?

It appears to me that you are doing design by trial and error --not the best strategy.

If you can show us your tables and relationships, and any descriptive info, that would be helpful to readers.

 

[peskywinnets]

It might seem that I'm a bit haphazard, but I'm being quite structured in my approach.

1. First I had to learn how to get the data (via an API)

2. Then work out the best way to store/manipulate the data in access (the incoming data is in a mess in its raw form)

3. Then normalize it

...etc, etc

I had thought the normalizing was done, but I've since found some records that are problematic...I've been on with this a day (& I'm now in 'panic mode' as until I sort this, I can't progress - & I'm up against time)....perhaps my brain is whacked & I need time away.

Re sample data, this is all real customer data, so I can't post on a forum....if push comes to shove I can delete 99.99% of the data & & change the 0.1%...but that's a lot of work!

Re this 'latest' problem, ok I can see where it's falling down, but don't know why!!

I have textual address data in my customers table which was appended from my scratch (AmazonOrders) table. even though the data is identical in both tables, it's not matching on ShippingAddress1, ShippingAddress2 & ShippingAddress3 when I try to match back from the Customers table to the original (AmazonOrders) table .....the same text data is also contained in BillingAddress1, BillingAddress2 & BillingAddress3 ....which does match between the two tables ....I'm completely lost as to why data taken from a source table & deposited into a target table does not match?

 

[jdraw]

Can you make a copy of the database, delete a number of records, then change the names of customers to things like Porky Pig, Captain Crunch, Heeza Payne etc, same with city/town.

It would make for more streamline understanding and communication if we had some tangible material.
Just need some structure and some sample data. I'm not interested in your application for personal gain.

You really should be working on a sample, representative database and/or copies of same.

Anyway, offering help, but need something to work with.

It sounds to me that:
You have some local representation of a Customer, and you get some "stream of data" from EBay/Amazon and have no
unique Customer identifier(s), so you are trying to map their data to yours for "processing". Also, their "Order/Invoice" info
can be broken out as multiple transactions even though it is the same Order; and from your perspective reassembling the
info is not trivial.

 

[peskywinnets]

It sounds to me that:
You have some local representation of a Customer, and you get some "stream of data" from EBay/Amazon and have no
unique Customer identifier(s), so you are trying to map their data to yours for "processing". Also, their "Order/Invoice" info
can be broken out as multiple transactions even though it is the same Order; and from your perspective reassembling the
info is not trivial.

That's pretty much it...I have two 'streams' of sales data incoming from Ebay & Amazon

They give me different 'chunks of data' (for example Ebay only provides a shipping address, whereas Amazon provides a Billing & Shipping)

I dump this raw incoming data into separate 'scratch' tables (EbayOrders & AmazonOrders).

Towards normalizing, I run queries to get unique customer records from each of these two tables, then any 'unmatched' against the customers table get appended *to* the customers table. I then need to copy back the ID field in the customers table back to the original scratch table (that's the CustomerID)

The majority of it works, but there's about 39 records out of 1400 records that have something odd about them.

Like I say I need a break, but will likely do as you've suggested...i.e. give you some sample data with the name changed slightly to protect privacy.

Thanks for the offer of help

 

[jdraw]

You're welcome.

Good luck.

Sometimes a break or diversion can prove very useful.

 

[peskywinnets]

Ok, so I had a break....back at the wheel, but as puzzled as ever, here's my query....



....if my interpretation is correct, any data that is in the left hand table (which in this case is actually the output of a query called 'UniqueCustomersfromAmazonOrders') that is not 'matched' by the same data in the right hand table (Customers), will show.

This is where it gets whacky, taking one row as an example, the customer has entered NEXT TO RIDING SCHOOL ....as text in the field ShippingAddress3 .....that particular customer's address data already exists in the Customers table ....but it won't match between the two tables!

The ShippingAdress3 data will match if I change the text in both tables to something like....

NEX TO RIDING SCHOOL

such is my brain frazzlement, i thought that perhaps it might a problem with the word NEXT at the start of the text, so i tried entering

ABCD TO RIDING SCHOOL

In both tables, but even then, it still won't match?!!

Could this be me hitting Access limits? ( i have ShippingAddress3 set as Short Text...which allows 255 characters, so I'm well within the boundaries for that text) ...or even a bug?

 

[jdraw]

That is a massive amount of fields in your join. Do you get any error/messages?
Can you not identify matches between tables using fewer fields?

What are the minimum fields required to identify/test for a record in both tables?

How do you identify unique records in your Customers table? Your diagram shows CustomerID as primary key.

 

[peskywinnets]

That is a massive amount of fields in your join. Do you get any error/messages? Can you not identify matches between tables using fewer fields?

I don't get any error messages.

The data contained in the customer table is used for things like invoices.....I have to have all of those joins because for example, a customer could place two separate orders but choose a slightly different way of entering the address data .....if I don't check against all the data that he's allowed to enter, then I could end up with the wrong info on his invoice.

How do you identify unique records in your Customers table? Your diagram shows CustomerID as primary key.

I don't identify unique records in the customer table, I identify unique record in the append query (a query that basically selects all the name & address info from the incoming /scratch table, with 'Unique Records' selected as a query property)

The heat is now off a little, most of my Amazon orders are shipped by Amazon themselves (I give them my stock, they store it & when it sells, they ship), therefore for emailing out an invoice I can get away with just using the customer billing address data for the invoice .... ...all the problems I was having was when the shipping address data joins were active too.

My gut feeling is that I pushed Access beyond its limit. I would like to get to the bottom of it though, because it would be very nice to also show the shipping address on the invoice.

 

[sneuberg]

One way to test whether a set of fields uniquely identifies records is to use the keyword DISTINCT in a select query. For example let's say the customers table has 100 records. If you do a SELECT DISTINCT Email FROM Customers and the result has 100 records then Email is sufficient. If the resulting set has less than 100 then add another field let's say

SELECT DISTINCT Email, BillingName FROM Customers

Just keep on adding fields and run the query until you get 100 records. Note that if never gets to 100 even with all of the fields except for the CustomerID then you have duplicate records.

 

[peskywinnets]

One way to test whether a set of fields uniquely identifies records is to use the keyword DISTINCT in a select query. For example let's say the customers table has 100 records. If you do a SELECT DISTINCT Email FROM Customers and the result has 100 records then Email is sufficient. If the resulting set has less than 100 then add another field let's say

SELECT DISTINCT Email, BillingName FROM Customers

Just keep on adding fields and run the query until you get 100 records. Note that if never gets to 100 even with all of the fields except for the CustomerID then you have duplicate records.

Perhaps I've not understood your drift, but this is for new data going onto the customers table.

I'm using a distinct query combined with an append query to ensure I have unique records (from the incoming scratch table) & then append 'unmatched' to the Customer table - but like I say, with all those joins enabled (my post #9), I get very weird results...once I remove the shipping address joins, it all works as it should.

 

[jdraw]

Do you get any info re a confirmation number/order number/ etc.?

Have you tried just doing that join on fewer fields?
So in your sample, did you figure how to match the records.

 

[peskywinnets]

Do you get any info re a confirmation number/order number/ etc.?

Have you tried just doing that join on fewer fields?
So in your sample, did you figure how to match the records.

Like I say, I have it working now by removing the shipping address related joins (leaving on the billing address joins - but I'm 100% sure there was nothing wrong with the data contained within the shipping address fields, e.g.. no null fields contained etc)....

This works...

 

[CJ_London]

I appreciate this doesn't address your original reason for the post, but you seem to have evolved to a solution anyway. But thought I would comment:

For normalisation you do need to decide who your customer is - is it the one with the same billing address? the one with the same shipping address? or perhaps the customer is defined by the email address? Or all of them as you have?

If the 'customer' (based on same email and billing address) one time, ships to address A and another time to address B - is that a different customer? If they change their phone number from landline to mobile - is that a different customer?

Other than producing an invoice and occasional shipping documentation - what else do you use the data for?

I would have expected you to have had a probably two maybe three append queries - one for customer detail, one or two for addresses.

Just something for you to think about.

 

[peskywinnets]

For normalisation you do need to decide who your customer is - is it the one with the same billing address? the one with the same shipping address? or perhaps the customer is defined by the email address? Or all of them as you have?.

It is driven by the customer supplied data. Like I say, a customer can place two orders, but have slightly different addressing details for each order...if I don't check all the fields & store any differences as a 'new' separate customer, then the outcome will be that the addressing info used on his invoice will not reflect what he entered. Also if I merely update the address info within the Customers table each time the same customer supplies slightly differing address info (to keep the number of customer entries down in the customers table), I'd historic addressing data for the customer? (so if a customer comes to me for an old invoice, he'd get it with the latest addressing info....not the addressing info he/she supplied at the time)

so whilst normalisation is the goal, I have to be pragmatic & let a bit more semi-duplicated data onto the customers table.

Other than producing an invoice and occasional shipping documentation - what else do you use the data for?

I would have expected you to have had a probably two maybe three append queries - one for customer detail, one or two for addresses.

I use the data to help with other things such as stock prediction/stock control & yes the (normalised) data gets appended to other tables ...so I have a table called Invoices, another table called InvoiceItems & so on...