Relationships Question(s)

[Exwarrior187]

This Forum has been great since I joined recently!



That being said I come back to the well of knowledge for another drink.

I have created a DB that has two separate tables that were currently working in. One for Existing Customers named 'North Florida' and another for Potential Customers named 'North Florida Prospects'.

These two tables are identical with the exception of some data types and validations. We find ourselves in need of keeping track of more information on these customers entered into both of these tables, ie the creation of two new tables 'Follow Up' and 'Revenue'. So Relationships and Database Normalization comes to mind, two subjects that I'm vaguely familiar with.

I've been trying to figure it out through a series of web searches and do-it-yourself attempts but I'm worried that I'm on the brink of bringing my entire house of cards down. Attached is a picture of the Relationships I've created and the way it would seem to work to me thus far.

Ultimately what I'm after is that when we get a Qualified Sales Lead from my first two original tables, I want it to be created in my two new tables with the 5-6 data points replicating from the Master tables 'North Florida' and 'North Florida Prospects'.

Many thanks in advance, I'm just looking to get on the right track here.

[TheShabz]

Woah. Too many joins! Two tables should only be joined by one field. Before you start messing with it, I really recommend reading up on Normalization. As you described them, The North Florida table and the Prospects table can be merged into one table, with an extra field named isProspect that holds a yes/no value. Further, this new table should then be broken up. For example. "Customer Type" should have its own table where you would have a foreign key relating to it. Products should have their own table. Calls should have their own table.

Further, I'd remove all spaces and special characters from the field names as it can make things difficult for you later on.

"Normalization" and "relational database" are two terms I think you would benefit from reading up on.

[Exwarrior187]

Thanks for the reply! I did a little digging and found an article that was written in verbage that I understood.

http://www.databasejournal.com/sqlet...malization.htm

Now to put it into use! The Database Analyzer wants to take my existing tables apart and place it in 10 tables, to me that seems like it's going to be a sizeable task to run queries against.

[TheShabz]

As far as the number of tables, it really all depends on the functionality you are looking for. the Database Analyzer isn't the be-all end-all of normalization. Give it a shot on your own and post back your Relationship Window and we can keep adjusting until we get to where it needs to be.

[Exwarrior187]

I'll be doing that believe me. First I need to take your first piece of advice and combine those two tables once I can get them to myself, which could take a day or two. Then once I've gotten them combined and identified as Customer/Prospect I'll start looking at how to best carve up the data.

[orange]

Here is a video on normalization.
Hope it helps
http://www.youtube.com/watch?v=uO80f...eature=related

[Exwarrior187]

Ok TheShabz:

I've combined both tables and Identified the records as Prospects and Non-Prospects.

Which method should I persue in order to make sure that when we label a record as having sales that 3-4 fields are duplicated into a table I want to use to hold the Sales data?

Many thanks once again.

[TheShabz]

This is where Forms and VBA come in to play. upon entering in sales information onto the form, with VBA you can write queries that will take the form information and update whatever tables you wish.

[Exwarrior187]

Shabz:

I have a button on our form with an OnClick event that runs a series of validations for the information stored in the form. What I'm missing is another Sub that would copy my 4 fields from the North Florida table into the Revenue table. They are named identically and share a relationship on the ID field.

[Exwarrior187]

I was able to find the code linked below and tweaked it to my needs (changed table names etc.) Thanks for the advice all!

http://bytes.com/topic/access/answer...m-fields-table