Stuck! Again!

[David F-B]

Hi All!

As readers of this group may know, fo some time now I have been
struggling with our existing office database. The person who set it
up thought along Excel lines and we have just added more and more
fields as needed. To date we have 72 fields.

Don't get me wrong, it works ok and I have built forms and queries
which work on it. The thing is that every time I look in books for
help to do stuff with the database, they all talk about multiple
tables and breaking stuff up. So, this is what I am trying to do.

Over the months of this work I have received many messages of support
and very useful advice. For which, many thanks.

However, I have again hit a snag. I have gone to the library and got
books on Access. As ever, they are worse than useless. They always
start off from scratch with their database (going into detail about
queries and stuff I've got the hang of) and they always use examples
that would obviously make straight forward databases (stock purchases,
etc) and are completely useless to me. I am trying to hack up an
existing contacts database without losing existing
functionallity/flexibity and without having to retype 3000 entries!
If it causes me a lot of extra work then there is no benefit to doing
this.

I aim to tackle the assorted problems one by one. But at the moment I
am concentrating on separating out the address details from the
people.

I am doing this a) so that I don't have to type Zippo Co.'s full
details every time, b) so that there is less chance of me making a
mistake entering the data, c) so that if Mr Smith moves from Zippo
company to Alpha company I don't have to delete the existing address
for Mr Smith and then find and type in all the Zippo address. That's
what I'm doing at present.

OK, so far I've gone through the existing table, isolated the
addresses then phyiscally copied them all to a new table
(automatically deleteing duplicates in the process). Fine so far. I
came out with about 940 individual companies and addresses.

This is where I have stuffed up: I thought that I could use the
existing company names as the link between the new company table and
the existing main table (which is now minus the addresses but still
retains the company name). However, my CompanyAddress table has
things like

Company Name Alpha Co
Address1 21 Long Street, Manchester

but also

Company Name Alpha Co
Address1 37 Smith Street, London

So if an individual's entry in the Main Table has

PersonName John Smith
Company Name Alpha Co

how will the system know which Alpha Co (north or South) Mr Smith
should be linked with?

I'm thinking now that the only thing to do is assign each company (all
900 odd) with some kind of reference code/number, then find and type
the relevant code into each of the 3000 entries. (Except that some
people do not have company details, so what do I do there?) Is there
no easier way of doing this? It sems a lot of work to save the effort
of retyping addresses!

As ever, any help would be appreciated.

Cheers
David F-B

 

[David F-B]

Another problem with splitting the addresses into a new table has just
occured to me.

I have a region code for each entry. I use this region code to tell
me

What companies are in each region?

What members (people) are in each region?

If I split the addresses from the main table, how do I do both these
queries?

If I stick the region code with Companies table, how can I use it to
tell me where the members are?

If I stick the region code in People how can it tell me where the
companies are?

Some people are not in companies.

If I get a query to link People and Companies table together, can I
use that as a stepping stone towards finding all the people or
compaies in a region?

The only other way I can think of is to have yet another table and
stick the region code in that, together with copies of the company
codes and people codes. But this seems like a lot more work.

I'm sure this all sounds so obvious and simple to you guys. But it is
doing my head in. Trying to rebuild a database you use every day is
like trying to build a car while you are hurtling down the motorway!

Thanks for all help given.
David

 

[Lynn Trapp]

David,
You are definitely on the right track and, while it may look like a lot of
effort, it will save you time and heartache in the future to keep in the
path you are on. Your instinct is right that you need some kind of reference
number to link the tables together. What you are building is called a
one-to-many relationship and you need to find either a combination of fields
in the main table that uniquely identify each record or, else, use some
arbitrary number (possibly using an Autonumber field). That number will need
to be in the main table and in every record of the new table (Foreign Key)
that references each unique record in the main table.

 

[Arvin Meyer]

I'm thinking now that the only thing to do is assign each company (all
900 odd) with some kind of reference code/number, then find and type
the relevant code into each of the 3000 entries. (Except that some
people do not have company details, so what do I do there?) Is there
no easier way of doing this? It sems a lot of work to save the effort
of retyping addresses!

Yes, you need a CompanyID. There are several ways you can do it, but by far
the easiest and most foolproof would be to create an Autonumber called
CompanyID in the Design View of the Company table. That part is easy, the
more difficult part is matching them up.

No, you do not need to have a detail record for each record in the company
table. Yes, you can have multiple addresses for the same company. Make sure
it is the same company with multiple addresses, as opposed to 2 companies
with the same name and different addresses.

It is sometimes difficult to match them up, but the alternative is to create
further problems. You can match them on name and fix those few that may have
multiple locations. When you're finished, delete the CompanyName field in
the details table. To make it easier for the multiple locations, create a
form for companies, with a subform in datasheet view (you can make it pretty
or make a better one later). On the subform, make the first control a
combobox with 2 fields, CompanyID and CompanyName. The CompanyID field is
the bound field, and its width is set to 0", so that what you see when it's
dropped down is the CompanyName. As you scroll through the detail records,
assign the correct CompanyID.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Arvin Meyer]

A query will join the 2 tables back together again to see the regions you
want. Join the 2 tables on CompanyID (see your other post)
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[David F-B]

Many thanks to all for brilliant suggestions and guidance.

In a not so brilliant moment I have managed to delete the fields I
wanted to work on (the company address stuff) from my test copy of
the database. I will have to make another copy at work tomorrow.

However, there is some good news. Progress is being made.
As I mentioned before, some of those who work in the companies on our
database are listed as members. I thought it might be better to have
members and membership details out of the main table, because not all
those on the database are members. So there are lots of empty
membership fields on the main table.

Does that sound like the right thing to do?


So anyway, I did a query gathering up all the fields for those who are
listed as members (Category = like *member), plus the (unique) ID
number and turned it into a make table query.

When the table was made I went to relationships and linked the record
ID in Memberships Table with its twin on the Main Table.

Then I produced a rather nice query which clearly showed the ID
reference number, the membership details (when paid, what paid, when
joined, when left, etc). Results match the original main database,
which still has the membership data in it cos I've not deleted that
yet. Deleting multiple fields is a problem as I only seem to be
allowed to delete one at a time. Anyone know why?

However, at this point I hit a snag: While I had my query all nicely
laid out and everything, I found I could not alter the data. This was
really the whole point of the exercise. After a bit of gnashing and
cursing I read up on Keys. I made the IDnumber field which appears in
the main table and the IDnumber field which appears in Membership
Table both Key fields. I have tested it and this has meant that I can
now delete or edit the query and add as many records or details as I
like!

Well, it probably doesn't sound like much to you lot. You're probably
thinking I'm as thick as a plank of wood but I'm dead pleased with
getting somewhere. I think I'm off for a Pint of beer!

Cheers all!

David

 

[Arvin Meyer]

I think I'm off for a Pint of beer!

Enjoy! You earned it.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

I have normalized several large tables as you are now. The first thing I would recommend is to add a new field to the original table and either use auto number or do an update to the field and create a number for each record. This is more a way back to ground 0 if needed First and foremost add the unique record ID field to master table, run a query to make a table of company names with a unique ID. Add a field to the master table and call it company ID or something and update the master table with this information. Do all this before you even start to break the table up and you will find it will go much faster and better. From there I then get my white board out and start doing a flow chart of the info to break it down into the level of normalization I think I will need to do the job. You have to have a idea of what you want the finished data base should have in it before you start or you will be starting over or redoing things to many times. Hope this helps.

Keith

 

[David F-B]

nal table and either use auto number or do an update to the field and create a number for each record. This is more a way back to ground 0 if needed First and foremost add the unique record ID field to master table, run a query to make a table of company names with a unique ID. Add a field to the master table and call it company ID or something and update the master table with this information. Do all this before you even start to break the table up and you will find it will go much faster and better. From there I then get my white board out and start doing a flow chart of the info to break it down into the level of normalization I think I will need to do the job. You have to have a idea of what you want the finished d

No worries. I have no intention of touching our office database until
I am totally sure I know what I'm doing and I can get all the same
data out of it that I do now.

Cheers
David F-B

 

[Fred Boer]

Dear David:

Good luck with your project. I'll leave the Access stuff to the experts, but
having had to do something like this myself, might I offer some additional
advice? Redoing table structures like this can take a number of steps. I see
that you are being sensible, and are working from backups. What I found
useful was to save a copy of the database after each change I made, along
with a little text file describing exactly what was done. This way, if I
found, after making a number of changes, that I had made a mistake, or
should have done it differently, I could simply backtrack a few versions,
rather than start from scratch. Also, I had documented exactly what I had
done, which was useful for a number of reasons, particularly since I had
trouble remembering exactly what I had done and why at various stages....

So... for example, you add a field to a table.. save as version1.mdb
You perform an update query to move data to the new field... save as
version2.mdb
You delete data from an unecessary field...save as version3.mdb

Since backing up the entire database is as easy as copying a file, it isn't
difficult to do this...

And so on... Anyway, it might be something to consider..

Cheers!
Fred Boer


create a number for each record. This is more a way back to ground 0 if
needed First and foremost add the unique record ID field to master table,
run a query to make a table of company names with a unique ID. Add a field
to the master table and call it company ID or something and update the
master table with this information. Do all this before you even start to
break the table up and you will find it will go much faster and better. From
there I then get my white board out and start doing a flow chart of the info
to break it down into the level of normalization I think I will need to do
the job. You have to have a idea of what you want the finished d

 

[David F-B]

Yes, thanks! This is good advice. As yet I've not done much
different from the original table except

Set up a table containing all the member numbers and member payment
details, then use the member number to link it to the original table
(which has the exact same member numbers). Then I produced a query
combining the two tables via the member number.

I have produced a query which highlights the companies and their
addresses. I have manually gone through it to reduce duplicates and
errors that might cause a duplicate to be read as a unique record.

I have created a company reference number within the main table (but
not filled it in as yet).

Now I'm pondering the advice given on the best way to proceed. The
next stage is assigning the companycode to the company. Then I've got
to link the companies to the people.

Cheers
David

 

[David F-B]

Yes, you need a CompanyID. There are several ways you can do it, but by far
the easiest and most foolproof would be to create an Autonumber called
CompanyID in the Design View of the Company table. That part is easy, the
more difficult part is matching them up.

No, you do not need to have a detail record for each record in the company
table. Yes, you can have multiple addresses for the same company. Make sure
it is the same company with multiple addresses, as opposed to 2 companies
with the same name and different addresses.

The database contains both examples.
It contains Abig Company having addresses in London and also in
Manchester. It also contains records where Abig Company and Another
Company share the same address and postal code. There appears to be
no way of automatically removing duplicates without possibly losing
needed data.

Looking at the list of Companies it seems to me the only way to
proceed with any certainty of retaining all the existing companies is
to to manually go through each record and type in a number for each
one.

ID No Company Address Member
01 Abig Company Manchester John Smith
01 Abig Company Manchester Alex Smith
02 Abig Company Liverpool Polly Peterson
03 Another Company Edinburgh David Jones
03 Another Company Edinburgh Jock McEwen
04 BiggerCo Manchester John Davidson
04 BiggerCo Manchester Norman Person

And so on for 2,000 entries.

It is sometimes difficult to match them up,

I can forsee members and/or companies going missing here. Even if I
manage to correctly list all the companies, I've then got the problem
of linking up the people to the companies. I'm not sure I should
proceed further with this. I'm no longer certain that the benefit
outweighs the work involved.

Thank you to all who offered suggestions.

David

 

[David F-B]

OK, I'm trying to take this a little further before I abandon this.

I want at least to be able to get a list of companies which are all
unique and have no duplicates.

I did a query to search CompanyName is not null, giving me all the
companies on the database.

I then went into the query properties and selected
Unique Values as Yes.

This cut down my list from 2,000 to 1,500. But I was very puzzled to
see completely identical records there.

Or so I thought. Having had another look I see that the records are
not identical. I will clean them up tomorrow and have another stab at
this. Hopefully I shall feel more inspired tomorrow.

Once I've listed the unique companies and put an ID for each one, I
can stick them into a Companies table.

But, how do I proceed from there? I suspect I've lost one or two
recent replies to this problem. Sorry about that.

Tomorrow's another day...

David

 

[David F-B]

Is there any way to edit a query that you have selected Unique Record
for?

I have added a CompanyID field to my main table. This is the table
that will become my People table. It has both company names and
member numbers in it. If I can enter the company numbers into this
table, then when I copy the table and edit out all the other fields,
I will end up with a table with Company details and Company ID in one
table and a table with Company ID and People ID and People fields in
another. If I can do that, then all the data I need will be in the
tables I need and I won't have to spend yet more hours trying to match
the two tables together again. I hope.

So, I set up a query to list only Companies and their addresses, and
to include the new Company ID field.

I then saw that the query has the same load of duplicates as before.

I set Unique Record to yes and this got rid of most of the duplicates.
However, extremely frustratingly, it is also meant that I cannot now
edit the records by entering the office reference number. So again I
am no further on.

This really is extremely frustrating. If only the table break up
wizard thing worked in some useful fashion, rather than splitting
every three fields into another table regardless, this job would be
done and dusted by now!

David