AnsweredAssumed Answered

Table Occurrences Driving Me Crazy!!!

Question asked by jhowlin on Apr 12, 2012
Latest reply on Apr 13, 2012 by jhowlin

Title

Table Occurrences Driving Me Crazy!!!

Post

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.

THANK YOU!

Jason

 

 

 

 

 

Screen_Shot_2012-04-12_at_2.25.17_PM.png

Outcomes