Poor table design problem

[dbl]

Hi I have run out of fields in my main table, there is to much data to use
the Access table splitter. It just keeps crashing with error message not
enough memory which I have spent hours on to get no where.

I still need to add about 34 more fields to capture all of the new data we
requirements. Is it possible to link 2 tables together by the CutomerID
auto number field?

Any ideas on how I should go forward would be very much appreciated.

Thanks Bob

 

[Gina Whipp]

DBL,

The quick and dirty answer is 'Yes' BUT you might want to consider a
COMPLETE redesign. Sounds like you've commited 'spreadsheet' with Access.

To answer your question, set up the second table with the CustomerID as a
Foreign Key and link it in the realtionships window.

 

[Dale Fye]

I'm with Gina. I've been working with relational databases for over 10 years
now, and have rarely encountered a situation where I needed more that 30
fields in a normalized table.

Generally, this is an indication that you are storing some sort of data
"years, days of the week, activities, or something like this" as column
headers in your database, rather than storing them as data in a single
column. This is usually the result of importing a spreadsheet into Access or
trying to use Access in a manner similar to a spreadsheet (as Gina indicates).

If you give us an example of some of the fields that might fall into this
category, we might be able to help you write the queries necessary to get
your data normalized.

HTH
Dale

 

[dbl]

Dale the data didn't come from a spreadsheet its a case of I have kept
adding new fields to the point where I have got myself into big trouble.

The area's I would like to split into different tables are:

TPInsurer
TPInsuerAddress
TPInsurerAddressCont
TPInsurerCity
TPInsurerCounty
TPInsurerPosalCode

DriversAddress
DriverAddressCont
DriverAddressCity
DriverAddressCounty
DriverPostalCode

Then I have the same for Employer, plus Employer different address
which all need splitting out.

Plus I now have an additional 34 fields to add each one is asking a
different question and needs some sort of input.

Every time I try to link tables it locks up and I cannot enter any more
data. What am I doing wrong?

Thanks for your help Bob

 

[Dale Fye]

This is a perfect example of "committing Spreadsheet". Knowing the purpose
of this database might help in defining a better table structure.

Each of these sets of data belongs in its own table (probably not entirely
true, but close).

First, make a copy of your database and set it aside. You should always do
this before starting to do a major overhaul (I do it before I make even minor
changes).

To start, I would create a table of Insurers (tbl_Insurers) that contains
all of the Insurer fields as well as a InsurerID (Autonumber data type) and
an InsurerAddressID field (Long data type). I would then write a query that
populates this table with data from your main table. Something like:

INSERT INTO tbl_Insurers(InsurerName, Street1, Street2, City, County,
PostalCode)
SELECT TPInsurer, TPInsurerAddress, TPInsurrerAddressCont,
TPInsurerCity, TPInsurerCounty, TPInsurerPostalCode
FROM YourTable
GROUP BY TPInsurer, TPInsurerAddress, TPInsurrerAddressCont,
TPInsurerCity, TPInsurerCounty, TPInsurerPostalCode

The InsurerID field will fill in automatically, and the InsurerAddressID
field will still be blank.

Next, I would add a field (TPInsurerID) to the main table. This should be a
long integer data type.

Then, I would create a query that updates this TP_InsurererID field from
your new tbl_Insurers. That query would look something like:

UPDATE YourTable
SET TPInsurerID = tbl_Insurer.InsurerID
FROM YourTable
INNER JOIN tbl_Insurers
ON YourTable.TPInsurer = tbl_Insurers.InsurerName
AND YourTable.TPInsurerAddress = tbl_Insurers.Street
AND YourTable.TPInsurerCity = tbl_Insurers.City
AND YourTable.TPInsurerCounty = tbl_Insurers.County
AND YourTable.TPInsurerPostalCode = tbl_Insurers.PostalCode

Then I would review my TPInsurerID field to make sure it was filled in in
every record, and I would spot check to make sure that the record
corresponding to that InsurerID in tbl_Insurers has the same address as the
record in your main table. Once that is confirmed, I would delete all the
TPInsurer fields (except TPInsurerID) from the main table.

I would then repeat that step for Drivers, Employers, ....

Eventually, I would create an addresses table(tbl_Addresses), and merge the
address data from all of those other tables into that one table, assigning an
AddressID to each of the Insurer, Driver, and Employer records.

** What are your 34 additional fields supposed to do? If they are questions
that need to be answered, then I would create a Questions table (QuestionID,
Questiontext), and a QuestionResponses table(QuestionRespID, QuestionID,
Response). If you need to ask certain questions based on some criteria, then
you might want to add another field to the questions table that defines the
criteria, and if the questions need to be asked in a specific order, then you
would also want a Sequence field to define the sequence of the questions.

HTH
Dale


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Dale Fye]

Here is a hyperlink that you should use as an Access and realtional database
tutorial.

http://www.allenbrowne.com/casu-22.html


--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Bob]

Dale I have managed to get as far as making a table called
tblEmployerCheckList, I tried to do the Insurer details one first but could
not get it to work. So I tried again with a different one to see if it would
work, which gave me the following:

I now have the following fields in a the new table
EmployerID Auto Number
EmployerAddressID Number Long Integer
Employer Text
EmployerAddress Text
EmployerAddress1 Text
EmployerAddress2 Text
EmployerTown Text
EmployerCounty Text
EmployerPostalCode Text

The table has 4015 records out of the 29035 records, is this correct?

I then entered the field EmployerID (Number Long Integer) to my table
tblTelephoneCheckList

Then started on the query as follows:

UPDATE tblTelephoneCheckList
SET EmployerID =tblEmployerCheckList.EmployerID
FROM tblTelephoneCheckList
INNER JOIN tblEmployerCheckList
ON tblTelephoneCheckList.Employer=tblEmployerCheckList.Employer
AND
tblTelephoneCheckList.EmployerAddress=tblEmployerCheckList.EmployerAddress
AND
tblTelephoneCheckList.EmployerAddress1=tblEmployerCheckList.EmployerAddress1
AND
tblTelephoneCheckList.EmployerAddress2=tblEmployerCheckList.EmployerAddress2
AND tblTelephoneCheckList.EmployerTown=tblEmployerCheckList.EmployerTown
AND tblTelephoneCheckList.EmployerCounty=tblEmployerCheckList.EmployerCounty
AND
tblTelephoneCheckList.EmployerPostalCode=tblEmployerCheckList.EmployerPostalCode

I then end up with the following error message Missing Operator can you see
where I am going wrong?

Plus where does the field EmployerAddressID (InsurerAddressID in your
code)come into it?

When adding a new record how does it link it all together?

Thanks for your help it is very much appreciated.

Bob

 

[Evi]

I *think* you need a semi-colon at the very end, to show that you've reached
the end of the Sql statement


Evi

 

[Bob]

Evi that doesn't work but thanks for trying

I *think* you need a semi-colon at the very end, to show that you've
reached
the end of the Sql statement


Evi

 

[John W. Vinson]

UPDATE tblTelephoneCheckList
SET EmployerID =tblEmployerCheckList.EmployerID
FROM tblTelephoneCheckList
INNER JOIN tblEmployerCheckList
ON tblTelephoneCheckList.Employer=tblEmployerCheckList.Employer
AND
tblTelephoneCheckList.EmployerAddress=tblEmployerCheckList.EmployerAddress
AND
tblTelephoneCheckList.EmployerAddress1=tblEmployerCheckList.EmployerAddress1
AND
tblTelephoneCheckList.EmployerAddress2=tblEmployerCheckList.EmployerAddress2
AND tblTelephoneCheckList.EmployerTown=tblEmployerCheckList.EmployerTown
AND tblTelephoneCheckList.EmployerCounty=tblEmployerCheckList.EmployerCounty
AND
tblTelephoneCheckList.EmployerPostalCode=tblEmployerCheckList.EmployerPostalCode

I then end up with the following error message Missing Operator can you see
where I am going wrong?

I'd enclose all the tablenames and all the fieldnames in square brackets. In
general I suspect it's optional (since you wisely didn't put blanks or special
characters in the names), but I thik it's obligatory in the SET clause;
otherwise it may try to update employerID to the *text string*
tblEmployerChecklist.EmployerID. Try changing it to

[tblEmployerChecklist].[EmployerID]

to make it clear to JET that you mean the field's contents, not its name.

 

[Evi]

The joins from TblTelephoneCheckList to the Employer Table may have to be
Outer
joins so that all the records from the Telephone table are displayed.
Put EmployerID from the Telephone table into the query grid, turn it into a
select query and check that there are some records there.
If not, try using a FindDuplicates query on TblTelephoneChecklist on the
Employer fields to see how *few* fields you can select without finding any
duplicates. Join just on those fields

Evi

 

[dbl]

No that still comes up with the same error message

Bob

UPDATE tblTelephoneCheckList
SET EmployerID =tblEmployerCheckList.EmployerID
FROM tblTelephoneCheckList
INNER JOIN tblEmployerCheckList
ON tblTelephoneCheckList.Employer=tblEmployerCheckList.Employer
AND
tblTelephoneCheckList.EmployerAddress=tblEmployerCheckList.EmployerAddress
AND
tblTelephoneCheckList.EmployerAddress1=tblEmployerCheckList.EmployerAddress1
AND
tblTelephoneCheckList.EmployerAddress2=tblEmployerCheckList.EmployerAddress2
AND tblTelephoneCheckList.EmployerTown=tblEmployerCheckList.EmployerTown
AND
tblTelephoneCheckList.EmployerCounty=tblEmployerCheckList.EmployerCounty
AND
tblTelephoneCheckList.EmployerPostalCode=tblEmployerCheckList.EmployerPostalCode

I then end up with the following error message Missing Operator can you
see
where I am going wrong?

I'd enclose all the tablenames and all the fieldnames in square brackets.
In
general I suspect it's optional (since you wisely didn't put blanks or
special
characters in the names), but I thik it's obligatory in the SET clause;
otherwise it may try to update employerID to the *text string*
tblEmployerChecklist.EmployerID. Try changing it to

[tblEmployerChecklist].[EmployerID]

to make it clear to JET that you mean the field's contents, not its name.

 

[John W. Vinson]

No that still comes up with the same error message

Probably choking when one or more of the fields is null. For example, if there
is no value in Address2 then the join will fail. In addition, I doubt that you
have a unique index on the combination of seven fields so the update probably
won't succeed anyway!

It's going to be very slow, but since (I hope!!) this is a one-time task, try
using DLookUp:

UPDATE tblTelephoneCheckList
SET EmployerID =DLookUp("EmployerID", "tblEmployerCheckList",
"NZ([Employer],'~') ="" & NZ(tblEmployerCheckList.Employer,'~') & """
AND NZ([EmployerAddress],'~') = """ &
NZ(tblEmployerCheckList.EmployerAddress,'~') & """
AND NZ([EmployerAddress1],'~') = """ &
NZ(tblEmployerCheckList.EmployerAddress1,'~') & """
AND NZ([EmployerAddress2],'~') = """ &
NZ(tblEmployerCheckList.EmployerAddress2,'~') & """
AND NZ([EmployerTown],'~') = """ & NZ(tblEmployerCheckList.EmployerTown,'~') &
"""
AND NZ([EmployerCounty],'~') = """ &
NZ(tblEmployerCheckList.EmployerCounty,'~') & """
AND NZ([EmployerPostalCode],'~') = """ &
NZ(tblEmployerCheckList.EmployerPostalCode,'~') & """")
WHERE EmployeeID IS NULL;

This will look up the new EmployeeID based on the entries in the seven fields;
if there is no entry it will compare '~' with '~' instead.

 

[Bob]

John that comes up with an syntax error in string in query expression
highlighting the following area

""" &
NZ(tblEmployerCheckList.EmployerPostalCode,'~') & """")
WHERE EmployeeID IS NULL

I have checked all the table and field names everything is ok there.

Thanks Bob

No that still comes up with the same error message

Probably choking when one or more of the fields is null. For example, if
there
is no value in Address2 then the join will fail. In addition, I doubt that
you
have a unique index on the combination of seven fields so the update
probably
won't succeed anyway!

It's going to be very slow, but since (I hope!!) this is a one-time task,
try
using DLookUp:

UPDATE tblTelephoneCheckList
SET EmployerID =DLookUp("EmployerID", "tblEmployerCheckList",
"NZ([Employer],'~') ="" & NZ(tblEmployerCheckList.Employer,'~') & """
AND NZ([EmployerAddress],'~') = """ &
NZ(tblEmployerCheckList.EmployerAddress,'~') & """
AND NZ([EmployerAddress1],'~') = """ &
NZ(tblEmployerCheckList.EmployerAddress1,'~') & """
AND NZ([EmployerAddress2],'~') = """ &
NZ(tblEmployerCheckList.EmployerAddress2,'~') & """
AND NZ([EmployerTown],'~') = """ &
NZ(tblEmployerCheckList.EmployerTown,'~') &
"""
AND NZ([EmployerCounty],'~') = """ &
NZ(tblEmployerCheckList.EmployerCounty,'~') & """
AND NZ([EmployerPostalCode],'~') = """ &
NZ(tblEmployerCheckList.EmployerPostalCode,'~') & """")
WHERE EmployeeID IS NULL;

This will look up the new EmployeeID based on the entries in the seven
fields;
if there is no entry it will compare '~' with '~' instead.

 

[John W. Vinson]

John that comes up with an syntax error in string in query expression
highlighting the following area

""" &
NZ(tblEmployerCheckList.EmployerPostalCode,'~') & """")
WHERE EmployeeID IS NULL

I have checked all the table and field names everything is ok there.

Thanks Bob

I've probably miscounted or mistyped some quotemarks or parentheses. I can't
see your database and am working in my Agent newsreader. Try stepping through
the logic of the expression - can you see what I'm proposing and how it should
be working? The third argument of the DLookUp is a big complex expression, but
it's made up of individual components, one for each field.