Totally Lost...Please Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm new at this so you'll have to bare with me. What I have is a db of
manufacturiong companies, however there are many duplicate records of
companies only to display additional contact people; ABC CO. has 3 possible
contact people so the list ABC Co three times with the same information
except with each record comes a different contact name(all the rest of the
information is the same-co. name, address, phone number etc.) The end result
I am looking for is to create a list with one record showing company name,
address, phone number and all available contacts. I can create another table
that has company name, contact1, contact2 and contact3 but is there a way to
filter or something and have it automagically accupy the new table or create
some sort of formula or do I have to manually accupy the list. I'm not sure
how many "companies" are in the list total, each company has 1,2 or 3
contacts so the list of 3500 records could actually break down to only half
that number. At any rate to do it manually would be far too time consuming.
 
You should have two tables. One to house a record for each company, and one
to store a record for each contact. A company coulf have one contact
record, two, five, ten, or none. This is called a one-to-many relationship.
If you will explore the Northwinds sample database, you will find several
examples of this type of relationship. For example, each company can have
multiple invoice records. Each invoice record can have multiple detail
lines on the invoice.

Your current method means you are typing the same address for multiple
records. My goodness, what if a company moves into a new building, or what
if you make a typo in one record? What a mess.


Table1
CompanyNumber
Company Name
Address
Phone
Etc...


Table2
CompanyNumber
ContactName
DirectLine
Etc...



Hope that helps,
Rick B



Veronica said:
I'm new at this so you'll have to bare with me. What I have is a db of
manufacturiong companies, however there are many duplicate records of
companies only to display additional contact people; ABC CO. has 3 possible
contact people so the list ABC Co three times with the same information
except with each record comes a different contact name(all the rest of the
information is the same-co. name, address, phone number etc.) The end result
I am looking for is to create a list with one record showing company name,
address, phone number and all available contacts. I can create another table
that has company name, contact1, contact2 and contact3 but is there a way to
filter or something and have it automagically accupy the new table or create
some sort of formula or do I have to manually accupy the list. I'm not sure
how many "companies" are in the list total, each company has 1,2 or 3
contacts so the list of 3500 records could actually break down to only half
that number. At any rate to do it manually would be far too time
Click to expand...
consuming.
 
Veronica,

At a quick glance, you need 2 tables:

tblCompany
a field to uniquely identify the company
CoName
CoAddress1
CoAddress2
CoState
CoZip
CoPhone
etc.

tblContacts
the field that uniquely identifies the company
ContName
any other information just for the contact

Example of data:

tblCompany
1 Company A 123 Peach St Suite 100 Chicago IL 12345
2 Company B 456 Ash Ave Kansas City MO 67890

tblContacts
1 Joe Smith
1 Susan Jones
1 Andy Johnson
2 Bob Anderson
2 Alice Jones

With the tables set up like this, you can have as many contacts per company as
you want to enter.

Then, if you want a report, you can create a query by using both tables joined
on the unique identifier in order to get:

Company A Joe Smith
Susan Jones
Andy Johnson

Company B Bob Anderson
Alice Jones

HTH,
Debbie


| I'm new at this so you'll have to bare with me. What I have is a db of
| manufacturiong companies, however there are many duplicate records of
| companies only to display additional contact people; ABC CO. has 3 possible
| contact people so the list ABC Co three times with the same information
| except with each record comes a different contact name(all the rest of the
| information is the same-co. name, address, phone number etc.)
|
| The end result
| I am looking for is to create a list with one record showing company name,
| address, phone number and all available contacts.
|
| I can create another table
| that has company name, contact1, contact2 and contact3 but is there a way to
| filter or something and have it automagically accupy the new table or create
| some sort of formula or do I have to manually accupy the list. I'm not sure
| how many "companies" are in the list total, each company has 1,2 or 3
| contacts so the list of 3500 records could actually break down to only half
| that number. At any rate to do it manually would be far too time consuming.
 
In tblContacts, you probably need:

ContLName
ContFName
ContMName (if you need a middle name/initial)


| Veronica,
|
| At a quick glance, you need 2 tables:
|
| tblCompany
| a field to uniquely identify the company
| CoName
| CoAddress1
| CoAddress2
| CoState
| CoZip
| CoPhone
| etc.
|
| tblContacts
| the field that uniquely identifies the company
| ContName
| any other information just for the contact
|
| Example of data:
|
| tblCompany
| 1 Company A 123 Peach St Suite 100 Chicago IL 12345
| 2 Company B 456 Ash Ave Kansas City MO
67890
|
| tblContacts
| 1 Joe Smith
| 1 Susan Jones
| 1 Andy Johnson
| 2 Bob Anderson
| 2 Alice Jones
|
| With the tables set up like this, you can have as many contacts per company as
| you want to enter.
|
| Then, if you want a report, you can create a query by using both tables joined
| on the unique identifier in order to get:
|
| Company A Joe Smith
| Susan Jones
| Andy Johnson
|
| Company B Bob Anderson
| Alice Jones
|
| HTH,
| Debbie
|
|
| || I'm new at this so you'll have to bare with me. What I have is a db of
|| manufacturiong companies, however there are many duplicate records of
|| companies only to display additional contact people; ABC CO. has 3 possible
|| contact people so the list ABC Co three times with the same information
|| except with each record comes a different contact name(all the rest of the
|| information is the same-co. name, address, phone number etc.)
||
|| The end result
|| I am looking for is to create a list with one record showing company name,
|| address, phone number and all available contacts.
||
|| I can create another table
|| that has company name, contact1, contact2 and contact3 but is there a way to
|| filter or something and have it automagically accupy the new table or create
|| some sort of formula or do I have to manually accupy the list. I'm not sure
|| how many "companies" are in the list total, each company has 1,2 or 3
|| contacts so the list of 3500 records could actually break down to only half
|| that number. At any rate to do it manually would be far too time consuming.
|
|
|
 
Thank you for your reply Debbie,

So basically what I need to do first is manually asign each record a
number(primary key) I was hoping there was another way as I have 3500 records
and sometimes more in the lists I have to break down. I did try to let
access asign the primary key but ofcourse it gave every record a number.
Because there are duplicate records due to the additional contacts I am
assuming this will not work. This is what it looks like if access gives it
the PK.

1.Beck and Associates, 12 belmont Ave, Winchester, Ca. 92567,
909-876-1234,Joe Smith, IT Director
2.Beck and Associates, 12 belmont Ave, Winchester, Ca., 92576,
909-876-1234,Kim Camble, Controller
3.Beck and Associates, 12 belmont Ave, Winchester, Ca., 92576, 909-876-1234,
Cory Harris, President

The numbers 1,2 and 3 are the primary keys access assigned.
This is what I need;
Beck And Associates, 12 belmont Ave, Winchester, Ca., 92567, 909-876-1234
Joe Smith, IT Director
Kim Camble, Controller
Cory Harris, President

In order for this to happen automagically access would have to not asign a
primary key to the duplicate record or to recognize it is a duplicate record
and assign the same primary key as did to the first. Is this possible????
 
See my answers inline.

| Thank you for your reply Debbie,
|
| So basically what I need to do first is manually asign each record a
| number(primary key) I was hoping there was another way as I have 3500 records
| and sometimes more in the lists I have to break down. I did try to let
| access asign the primary key but ofcourse it gave every record a number.
| Because there are duplicate records due to the additional contacts I am
| assuming this will not work. This is what it looks like if access gives it
| the PK.
|
| 1.Beck and Associates, 12 belmont Ave, Winchester, Ca. 92567,
| 909-876-1234,Joe Smith, IT Director
| 2.Beck and Associates, 12 belmont Ave, Winchester, Ca., 92576,
| 909-876-1234,Kim Camble, Controller
| 3.Beck and Associates, 12 belmont Ave, Winchester, Ca., 92576, 909-876-1234,
| Cory Harris, President
|

Yes, AutoNumber will give every record a unique number. That's why you need to
put your information in separate tables like my example below.

| The numbers 1,2 and 3 are the primary keys access assigned.
| This is what I need;
| Beck And Associates, 12 belmont Ave, Winchester, Ca., 92567, 909-876-1234
| Joe Smith, IT Director
| Kim Camble, Controller
| Cory Harris, President
|
| In order for this to happen automagically access would have to not asign a
| primary key to the duplicate record or to recognize it is a duplicate record
| and assign the same primary key as did to the first. Is this possible????

Yes, this is possible. I just did a test and your tblContacts would need to
look like this:

Contact ID
Company ID
ContLName
ContFName
ContMName
any other information just for the contact

You can create a form based on the tblCompany, but don't show the CompanyID (you
don't care what number Access assigns as a unique identifier. You could call
this something like frmCompany

Then create a form based on the tblContacts, but don't show the ContactID or the
CompanyID. You could call this something like frmContacts.

Open frmCompany.
Make the Detail section a little taller.
On the Toolbar, make sure the Control Wizards button is ON.
On the Toolbar, click the Subform/Subreport button.
Click in the blank area of the Detail section. The Wizard will start.
Click "Use an existing form."
Select frmContacts and click Next.
Select "Choose from list" and click Next.
Leave the name the same and click Finish.
The company information is at the top of the form, the contacts are in the
subform.
When you want to add a contact for a company, you need to view that company
first, and then add a contact. The contact will automatically pick up the
CompanyID and create a unique ContactID.

Your tblContact woul now look like this:

ContactID CompanyID ContFName ContLName
1 1 Joe Smith
2 1 Susan Jones
3 1 Andy Johnson
4 2 Bob Anderson
5 2 Alice Jones
6 3 Sam Malone

Does that help?

|
|
| "DebbieG" wrote:
|
| > Veronica,
| >
| > At a quick glance, you need 2 tables:
| >
| > tblCompany
| > a field to uniquely identify the company
| > CoName
| > CoAddress1
| > CoAddress2
| > CoState
| > CoZip
| > CoPhone
| > etc.
| >
| > tblContacts
| > the field that uniquely identifies the company
| > ContName
| > any other information just for the contact
| >
| > Example of data:
| >
| > tblCompany
| > 1 Company A 123 Peach St Suite 100 Chicago IL
12345
| > 2 Company B 456 Ash Ave Kansas City MO
67890
| >
| > tblContacts
| > 1 Joe Smith
| > 1 Susan Jones
| > 1 Andy Johnson
| > 2 Bob Anderson
| > 2 Alice Jones
| >
| > With the tables set up like this, you can have as many contacts per company
as
| > you want to enter.
| >
| > Then, if you want a report, you can create a query by using both tables
joined
| > on the unique identifier in order to get:
| >
| > Company A Joe Smith
| > Susan Jones
| > Andy Johnson
| >
| > Company B Bob Anderson
| > Alice Jones
| >
| > HTH,
| > Debbie
| >
| >
| > | > | I'm new at this so you'll have to bare with me. What I have is a db of
| > | manufacturiong companies, however there are many duplicate records of
| > | companies only to display additional contact people; ABC CO. has 3
possible
| > | contact people so the list ABC Co three times with the same information
| > | except with each record comes a different contact name(all the rest of the
| > | information is the same-co. name, address, phone number etc.)
| > |
| > | The end result
| > | I am looking for is to create a list with one record showing company name,
| > | address, phone number and all available contacts.
| > |
| > | I can create another table
| > | that has company name, contact1, contact2 and contact3 but is there a way
to
| > | filter or something and have it automagically accupy the new table or
create
| > | some sort of formula or do I have to manually accupy the list. I'm not
sure
| > | how many "companies" are in the list total, each company has 1,2 or 3
| > | contacts so the list of 3500 records could actually break down to only
half
| > | that number. At any rate to do it manually would be far too time
consuming.
| >
| >
| >
 
Just one quick thought... in A2K the command Tools, Analyze, Table is
designed to take this kind of table and split it into two. I've never tried
it in anger so can't comment how reliable it is... but might be worth a go.


Veronica said:
I'm new at this so you'll have to bare with me. What I have is a db of
manufacturiong companies, however there are many duplicate records of
companies only to display additional contact people; ABC CO. has 3 possible
contact people so the list ABC Co three times with the same information
except with each record comes a different contact name(all the rest of the
information is the same-co. name, address, phone number etc.) The end result
I am looking for is to create a list with one record showing company name,
address, phone number and all available contacts. I can create another table
that has company name, contact1, contact2 and contact3 but is there a way to
filter or something and have it automagically accupy the new table or create
some sort of formula or do I have to manually accupy the list. I'm not sure
how many "companies" are in the list total, each company has 1,2 or 3
contacts so the list of 3500 records could actually break down to only half
that number. At any rate to do it manually would be far too time
Click to expand...
consuming.
 
Hi Debbie,

I would first like to tell you how much I really do appreciate your help and
apolozize for being so dence. I want to make sure however that you
understant I am not building a database from scratch, I am trying to
manipulate data that already exists. My client has a list from Harris(not
sure if you are familiar). He has sent it to me on an excel spreadsheet. It
is set up as previously explained - multiple companies in a row, all
information the same except the contact names. I have imported it into
access and from there need to know how to manipulate the data so I only have
one listing of the company with its information, at the same time retain all
the contact names in the "one" listing.

I will create the two tables, but how do I get the information from one
table to the next without haveing to re-enter all the information? Also, I
did go back to the existing table and inserted a column as an "ID", tryed to
assign it as the primary key. I did give each listing of the company the
same number. I got a message saying; The changes I have requested were not
successful because they would create duplicate values in the index, primary
key or relationships. Change the data in the field/fields that contain
duplicate data, remove index to permit duplicate entries and try again.

Take a deep breath and know your purpose is being served well, sorry again
for being so dence.
 
Ladies,

PMFJI, but I have an idea that may be of interest.

Veronica, import the Excel flat table as is, and go Tools > Analyze >
Table; Access has a built-in wizard to help you normalize your data and
split the tables, I think it's worth a shot. Suggest you test that in a
separate database, after you have made a back-up copy of the original
Excel file - no such thing as one precaustion too many!

HTH,
Nikos
 
Veronica said:
I'm new at this so you'll have to bare with me. What I have is a db of
manufacturiong companies, however there are many duplicate records of
companies only to display additional contact people; ABC CO. has 3 possible
contact people so the list ABC Co three times with the same information
except with each record comes a different contact name(all the rest of the
information is the same-co. name, address, phone number etc.) The end result
I am looking for is to create a list with one record showing company name,
address, phone number and all available contacts. I can create another table
that has company name, contact1, contact2 and contact3 but is there a way to
filter or something and have it automagically accupy the new table or create
some sort of formula or do I have to manually accupy the list. I'm not sure
how many "companies" are in the list total, each company has 1,2 or 3
contacts so the list of 3500 records could actually break down to only half
that number. At any rate to do it manually would be far too time
Click to expand...
consuming.
 
Veronica,

I have a simple solution for you. Contact me at my email address below.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Veronica said:
I'm new at this so you'll have to bare with me. What I have is a db of
manufacturiong companies, however there are many duplicate records of
companies only to display additional contact people; ABC CO. has 3 possible
contact people so the list ABC Co three times with the same information
except with each record comes a different contact name(all the rest of the
information is the same-co. name, address, phone number etc.) The end result
I am looking for is to create a list with one record showing company name,
address, phone number and all available contacts. I can create another table
that has company name, contact1, contact2 and contact3 but is there a way to
filter or something and have it automagically accupy the new table or create
some sort of formula or do I have to manually accupy the list. I'm not sure
how many "companies" are in the list total, each company has 1,2 or 3
contacts so the list of 3500 records could actually break down to only half
that number. At any rate to do it manually would be far too time
Click to expand...
consuming.
 
....and if that doesn't work, then it's just a case of running a couple of
make table/grouped/update queries. Try the Tools, Analyze thing first and
let us know if it doesn't work.
 
Back
Top