Must I use multiple tables?

[Rolfee]

Hi.

I suspect that this post may receive a few groans, but I have searched and searched and cannot find an answer that is conclusive.

I have an Excel spreadsheet 'database' of 10,000 end-user records that I have converted to an Access table. Application is a telemarketing/contact management and reporting tool

Of these 10,000 records, virtually all of them are multiple contacts from the same company. (i.e. approx 4,500 company records with multiple contacts).

I am trying to create a database form that will view Contacts in a separate subform, on the same screen, to the company record so that, for example, a change of head office address only needs effecting once. (This approach, I believe, will also help further down the track when I have to run reports.)

So, to the question. Do I have to split the freshly converted, 10k line, table in to two tables - Company and Contact - or is there a way of avoiding all that work of deciding which fields to include in which table, identifying them, sleceting them and cutting/copying them across to a new Contacts table? - only to discover further down the track that I got something wrong.

Thanks in advance for your help. Also, any links to tutorial vids, previous posts, etc., will be greatly appreciated.

Cheers!

Rolfee

 

[jdraw]

Hi.

I suspect that this post may receive a few groans, but I have searched and searched and cannot find an answer that is conclusive.

I have an Excel spreadsheet 'database' of 10,000 end-user records that I have converted to an Access table. Application is a telemarketing/contact management and reporting tool

Of these 10,000 records, virtually all of them are multiple contacts from the same company. (i.e. approx 4,500 company records with multiple contacts).

I am trying to create a database form that will view Contacts in a separate subform, on the same screen, to the company record so that, for example, a change of head office address only needs effecting once. (This approach, I believe, will also help further down the track when I have to run reports.)

So, to the question. Do I have to split the freshly converted, 10k line, table in to two tables - Company and Contact - or is there a way of avoiding all that work of deciding which fields to include in which table, identifying them, sleceting them and cutting/copying them across to a new Contacts table? - only to discover further down the track that I got something wrong.

Thanks in advance for your help. Also, any links to tutorial vids, previous posts, etc., will be greatly appreciated.

Cheers!

Rolfee

Please look at the article at this address. It will give you some info on your particular question of database design.
http://r937.com/relational.html

 

[gemma-the-husky]

as you surmise you need a company table and a contact table. (You may also need other tables - eg a given company may have multiple contact persons, so you may prefer to have a company table, a contact persons table, and a contact occurrences table)

Access appears to be a series of spreadsheets, where the spreadsheets enjoy a cetain degree of relatedness. However this appearance is really superficial. It is in fact far more than this as the database manager (access) can limit and manage the data you can enter in a row, in a way which is really hard to do in excel. it can also cope with many more rows than excel, and handle the data far far quicker.

There is a real learning curve associated with access. Indeed, as you consider your data in this application, and begin to use access, and read about normalization and other things, you will learn many ways to accomplish the same thing. It is quite hard to "dive in" and achieve good results without a clear overview of what access is, and what it isnt.

for example, a real difference is the concept of an intrinsic order for your data. In truth Access has no concept of order - it really deals with sets of data corresponding to particular parameters, and you impose whatever order you require to this set. Its like rapid and simple use of the data/sort/filter facilities you get with excel.

In your case, each row of your spreadsheet represents a different contact event, but not every row represents a different company. so you need to split this into a contact table, and a company table which contains the unique companies involved in those contacts.

now in terms of your current problem, there is a database splitter tool which will try to separate your tables automatically, into the contacts and company table. alternatively you can do it by hand. if you do it by hand you may get a better result, and you will learn more about access.

Hope this helps.

Access is a fantastic product, and its worth the effort to learn it.

 

[Rolfee]

Hi. Thanks for the feedback, advice and reference material. Apologies for delay in replying, I have been busy building the database with forms/sub-froms. All looking good, except for two things that I cannot seem to suss out and I hope that someone has a quick answer for me.

Firstly, I have the main form set up for 'Companies' and the sub-from set up for 'Contacts', and have a relationship set up between a 'Mastercode' field in each form/sub-form. My objective in doing it this way is so that I can quickly tab through the companies for (e.g.) Company1, with four contacts, straight to Company2 with five contacts, with a single click - not four clicks to transition from Company1 to Company2. I hope that makes sense as I am not experienced enough with Access to be able to articulate it better. I have attached a screenshot in the hope that helps.

Secondly, why does my subform always present as a table by default and requires me to right-clicxk on it, and select 'Subform - Form' to get it to present as a from? Surely, there is a way to lock the subform in form view?...

Thanks for considering my question/request for help.

Rgds,

Rolfee