Data field integrity

[triann_a]

All,

I am an inexperienced access user that knows more than enough to label me DA
and I'm not talking about a Database Administrator.

My question/problem is how I can ensure that the data imported in a data
table field is correct. Simple right?

Issue: I work with 500 - 600 records daily that are download load to Excel,
manipulated to update data fields to meet the reporting needs and imported in
to Access.

This effort involves a tremendous manual manipulation as there are 6
separate fields that need to be changed to meet reporting requirements and I
am sure that there must be an easier way within Access to accomplish this.

Each record contain information on student activities such as courses
completed test scores, locations, dates ECT and the data is gathered from a
number of sources.

One example would be course title.

I have recently been successful in having inventory system developed that
will uniquely identify each course by Course ID and then by a unique Course
Title. Each data reporting source would be required to use that specified
Course ID. My need is to be able to ensure that based on the unique Course ID
that the corresponding Course Title can be confirmed/auto filled into the
Course title field by it’s unique course title. The goal is to confirm one
record/score per student per course completed.

My initial thought is to create a lookup table based on the Course ID that
would update the Course Title field based on the Course ID table.

How do I do it?

I know these questions are basic, but I would be thankful for any help
offered.

Regards,

Trianna

 

[John Spencer (MVP)]

Is Course ID unique or is Course Title unique or are they both unique?

Assuming that Course Id is unique, you would only enter the course id in your
table of information. When you need to see the course title, you would use a
query that links from your table of information to the table of courses.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Maarkr]

I may suggest that you setup your excel table to do everything for you before
importing in access...have a lookup in Excel for your CourseID and
CourseNames. If later on, you added or changed courses, you could change it
only in Excel and not have to remember to do it again in Access. I would
also try to setup an Excel macro to fix your data issues before importing.
Look in the Excel discussion groups to learn how to fix each of your ss
issues, building a macro that works for each step, then learn how to combine
the macro code so one macro performs many steps. You may learn how to
dramatically reduce your workload. Also, try the macros on a COPY of your
data until you're sure it is foolproof.

 

[Ken Sheridan]

Ensuring the consistency of the data in Excel is only a part of the solution.
Access is a relational database application, so there is then the question
of decomposing the data as imported from Excel into a set of correctly
normalized related tables in Access.

FWIW the following set of queries and explanatory notes are taken from a
demo file I produced some time ago to show how imported data can be
decomposed into normalized tables. The demo uses a simple international
contacts lists so there are countries, regions and cities involved in the
addresses. Also each contact can have more than one employer:

The query below inserts rows into the Countries table with unique values of
the Country column imported from Excel into the table MasterTable. This is
necessary before rows can be inserted into the Regions table as that table
references the Countries table in a many-to-one relationship.

INSERT INTO Countries (Country)
SELECT DISTINCT Country
FROM MasterTable;

Having inserted rows into the Countries table rows can now be inserted into
the Regions table with the query below. This joins the MasterTable to the
newly filled Countries table on the Country columns and inserts unique values
from the Region column of the MasterTable and the CountryID column of the
Countries table into the Regions table.

INSERT INTO Regions (Region, CountryID)
SELECT DISTINCT Region, CountryID
FROM MasterTable INNER JOIN Countries
ON MasterTable.Country=Countries.Country;

Having inserted rows into the Regions table rows can now be inserted into
the Cities table with the query below. This joins the MasterTable to the
newly filled Regions table on the Region columns. The Countries table is
joined to the MasterTable on the Country columns and to the Regions table on
the CountryID columns, thus taking account of any regions of the same name in
different countries. The query inserts unique values from the City column of
the MasterTable and the RegionID column of the Regions table into the Cities
table.

INSERT INTO Cities (City, RegionID)
SELECT DISTINCT MasterTable.City, Regions.RegionID
FROM Countries INNER JOIN (MasterTable INNER JOIN Regions
ON MasterTable.Region=Regions.Region)
ON (MasterTable.Country=Countries.Country)
AND (Countries.CountryID=Regions.CountryID);

The previous queries inserted rows into the Countries, Regions and Cities
tables. Following the insertion of data into the last of these, Cities, it is
now possible to insert rows into the Contacts table as this only needs to
reference the Cities table, the relevant Region and Country being referenced
via the relationships between these three tables. The query below does this
by joining the MasterTable to both the Cities table, on the City columns, and
to the Regions table, on the Region columns. The Cities table is also joined
to Regions on RegionID and the Countries table is joined to the MasterTable
on Country and the Regions table on Country ID. This is to take account of
the possibility of two cities having the same name, but being in different
regions, which themselves could theoretically have the same name but be in
different countries, so that the correct CityID value is inserted into
Contacts.

For simplicity it is assumed that contacts at the same address have unique
names. This might not always be the case, particularly with commercial
premises (the developer of this demo once worked with two Maggie Taylors in
the same building!). In such cases, however, there is likely to be some
distinguishing value such as Job Title or Department which could be used.

INSERT INTO Contacts ( FirstName, LastName, Address, CityID )
SELECT DISTINCT MasterTable.FirstName, MasterTable.LastName,
MasterTable.Address, Cities.CityID
FROM Countries INNER JOIN ((MasterTable INNER JOIN Cities ON
MasterTable.City = Cities.City)
INNER JOIN Regions ON (Regions.RegionID = Cities.RegionID)
AND (MasterTable.Region = Regions.Region))
ON (Countries.CountryID = Regions.CountryID)
AND (Countries.Country = MasterTable.Country);

The query below inserts rows into the Employers table with unique values of
the Employer column imported from Excel into the table MasterTable. This is
necessary before rows can be inserted into the ContactEmployers table as that
table references the Employers table.

INSERT INTO Employers (Employer)
SELECT DISTINCT Employer
FROM MasterTable;

Having inserted rows into the Contacts and Employers table it is now
possible to insert rows into the ContactEmployers table which models the
many-to-many relationship between Contacts and Employers. The query below
does this by joining the MasterTable to Contacts on the Address, LastName and
Firstname columns and to the Employers table on the Employer columns. The
ContactID values from Contacts and EmployerID values from Employers are
inserted into the two columns of ContactEmployers.

INSERT INTO ContactEmployers (ContactID, EmployerID)
SELECT Contacts.ContactID, Employers.EmployerID
FROM (Contacts INNER JOIN MasterTable
ON (Contacts.Address=MasterTable.Address)
AND (Contacts.LastName=MasterTable.LastName)
AND (Contacts.FirstName=MasterTable.FirstName))
INNER JOIN Employers ON MasterTable.Employer=Employers.Employer;

The above does assume that the imported data is completely consistent, which
I think is what you were saying in your reply. If Trianna, or anyone else
for that matter, would like a copy of the demo itself, which is made up of an
Excel file from which the data is imported and the Access file which
decomposes it using the above queries they can mail me at:

kenwsheridan<at>yahoo<dot>co<dot>uk

Ken Sheridan
Stafford, England

 

[Trianna]

Ken,

Thank you for the information. It is helpful.

I have accepted your offer of the Excel and Access demo files and have sent
you a seperate email as requested.

Hope I got that address right!

Again, thank you very much.

Trianna