HELP! Forms, Subforms, Relationships, Entering Data, I'm missing something

[rkgelle]

Hi all - I am hoping I found the right place for some help. I am TOTALLY STUCK, but so close to getting this database up and running....ahhhhhh!

Purpose of Database and background information:
I have a business that sells a product
We sell to companies
MOST of the companies have divisions
Each company and division has Main Contacts and "additional contacts"
Sometimes a contact will be "assigned" to more than one company because they are responsible for more than one division

So:
one company can have many contacts
one contact can have many companies = many to many relationship

I have created several tables, but here are the ones concerning this scenerio:
1. Company (Companies and their divisions are recorded in this one table)
2. Contacts
3. LINKtoCompCntcts (which consists of the following fields: LinkID, CompID, CntctsID)

Table 3 was created for the sole purpose of establishing the many to many relationship.

PROBLEM:
I am now ready to make a form for the users to easily enter data. I have read about forms and subforms, but am STUCK.

HERE IS WHAT I AM TRYING TO DO:
I want to create a form for a user to add a new Company
On that form I want them to be able to select the contacts that "belong" to that company. AND if the contact is not in the database, I want them to be able to Enter a new contact from that same form.

MY THOUGHTS:
After I got confused I then thought - maybe I should just make the "Contacts" a "look-up" field in the company table and allow multiple selections...but then, that still didn't solve the problem of if the contact isn't already in the database, how would they enter a NEW contact.

MY DILEMMA:
I think my biggest dilemma is not understanding this totally.
If I have a Company table and a Contact table and they are linked through the LINKcompcontact table. How do I set it up appropriately in the form and When using a form, how does the program know that this NEW contact is linked to this NEW company?

PLEASE HELP!!! I hope you can understand what I am trying to say. At this point, I truly don't really even care if I completely understand it all - I just need to get it up and running. I am wanting to take a class for Access, but that is more than a month away and I really can't wait that long. I have no programming experience, but am open to learning anything. I'm a quick learn...usually!

THANKS IN ADVANCE FOR YOUR HELP!! I LOOK FORWARD TO HEARING FROM YOU!

P.S. I am quite experienced with the computer, but not a programmer and would consider myself a beginner user of Access. I am self-taught by using research via the web. Last week, I didn't even know what tables were for.

 

[John Big Booty]

Firstly welcome to the forum.

From the description of you tables it sounds as if you will to create a query to collect the relevant data to populate your Sub-form.

If you are able to post a copy of your DB, I will be able to be a little more specific.

 

[rkgelle]

Thank you! Thank you! Thank you!

I would be happy to post my DB for you. I have tables and that's about it. Have at it!

I have a glimmer of hope!

I have attached the database and another file that had the same name, but I'm not sure what it is or what it is for - so I thought I better send both

Looking forward to your input!

I can't thank you enough for your time!

 

[John Big Booty]

Are you able to save the DB back to 2003 format as I don't have access to 2007 here, and I'm not sure when I will get a chance to play with this file on my 2007 machine.

 

[rkgelle]

Hi -
I will try - when I do it says that I have fields allowing multiple value selection - I think I know where they all are so I will attempt to get rid of those this evening and send it off to you. Thanks!

 

[Scooterbug]

Just a quick observation...

I have created several tables, but here are the ones concerning this scenerio:
1. Company (Companies and their divisions are recorded in this one table)
2. Contacts
3. LINKtoCompCntcts (which consists of the following fields: LinkID, CompID, CntctsID)

Can a company have multiple divisions? If so, I would have a separate table for the divisions.

tblDivision
DivisionID (Primary Key)
DivisionDesc
CompanyID (Foreign Key)

 

[rkgelle]

Interesting that you suggested that - I was actually thinking about that because a company can have multiple divisions.

I think I avoided it because I get so lost in the "entry" of everything.

 

[rkgelle]

Interesting that you suggested that - I was actually thinking about that because a company can have multiple divisions.

I think I avoided it because I get so lost in the "entry" of everything.

 

[Scooterbug]

Always a good idea to grab pencil and paper and map out your tables as the first step. Whenever you ask yourself "Can there be multiple of (whatever)?" it's a good bet that you will need a separate table to record that.

Once that is done, a Form and subform will most likely be the answer to how to do data entry for that information.

 

[rkgelle]

Hi There -

Just wanted to let you know that I am still working on getting it converted to 2003 - it keeps giving me an error. I'll keep you posted. Thanks for your willingness to help and patience!

 

[rkgelle]

Always a good idea to grab pencil and paper and map out your tables as the first step. Whenever you ask yourself "Can there be multiple of (whatever)?" it's a good bet that you will need a separate table to record that.

Once that is done, a Form and subform will most likely be the answer to how to do data entry for that information.

Scooterbug -

I actually did do a pencil map out of tables prior to starting!! The reason I did not separate company and division is because I need to know (or enter) the same information about them and didn't want to duplicate that into both tables.

Would you suggest that then I actually then make it into 3 tables: (example below)

Comptble
CompanyID
CompanyName
CompyDesc

Divsntble
DivsnID
DivisionName
CompID
DivisionDescription

CompDivisionInfotble
CompID (foreign key)
DivisionID (foreign key)
Info field A
Info field B
Etc

thanks for your input!

 

[Scooterbug]

I would imagine that you would only need two tables.

tblCompany
CompanyID
Company Info

tblDivision
DivisionID (PK)
CompanyID (FK)
Other Division Info

You will only need a third table if you use the tblDivision as a Lookup Table. That is if you have a set list of division that will be common amongst all companies. (ie: Legal, Human Resources, Production, etc). If that is the case, then the third table will be a junction table:

tblCompanyDivJunction
JunctionID (PK)
CompanyID (FK)
DivisionID (FK)
Other data specific to that Division