Table Occurrences Driving Me Crazy!!!
Hi I have a database I use personally for marketing analysis. There are no other users and 100% data integrity and accuracy is not vital, but of course I want it to be as close to perfect as possible.
Our business system records e-commerce orders and currently there are no customer records. So every ORDER comes in as a new record with customer data attached, such as shipping and e-mail and everything associated with it, and of course the product information for that specific order attached.
I am trying to create another table occurrence of the ORDER table, look at just a found set of orders where I omit orders where the e-mail address and phone number are the same. This gives me a reasonable view of my UNIQUE CUSTOMERS. I'm calling this table occurrence of my ORDERS table "CUSTOMERS."
I then want to go back and take a given CUSTOMER and find all ORDERS where the E-MAIL address matches. This would give me a somewhat reasonable idea of orders of a given CUSTOMER. Currently I am trying to do this with a portal but am having no luck.
I understand I have a self-referential join here and am just thoroughly confused. Am I approaching this correctly? Does anyone have any insight on how I might accomplish this?
I frequently have to update this ORDERS table from an outside source, via import, so I want to avoid having to create a new CUSTOMERS table that is exported data from the ORDERS table. I tried that for some testing and was having problems: a CUSTOMERS layout I created that contained a portal into orders would only show ONE ORDER for each customer (when joined by the email field). When I tried another field, first name, it showed many different orders. I don't know if that was because of the @ symbol or something.
Anyway, I'm uploading a diagram if that helps. DISREGARD THAT ORDERSCUSTOMERS TABLE IN THE CORNER - HE'S FOR SOMETHING ELSE.