Hi,

I'm relatively new when using Access so bear with me.

I have an Excel file with 4 spreadsheets, each with multiple columns

Tbl.Schools
SchoolName
SchoolCity
SchoolPop
etc.

Tbl.Counts
SchoolName
EquipmentLoc (where in large playground is equipment located...N, S,
E,W)
EquipmentType (is it a slide, jungle gym, etc...there are about 60
different possibilities)
Count1 (60 sec count of how many children were on a piece of equipment
at t1)
Count2 (60 sec count of how many children were on same piece equipment
at t2)
Count3
Date
etc.

Tbl.Depth
SchoolName
EquipmentLoc (same as above)
EquipmentType (same as above)
EquipmentPos (what position on equipment is depth being measured. i.e
left side monkey bars or right)
Depth1 (measure how deep surface is below certain pieces equipment)
DateDepth1
Depth2
DateDepth2
Depth3
DateDepth3
etc

Tbl.Height
SchoolName
EquipmentLoc
EquipmentType
EquipmentPos
Height (measure how far distance is between certain pieces equipment
and ground)
DateHeight

I am having trouble grasping how to divide these four spreadsheets into
access tables in order that the data is properly normalized in 3NF,
while still allowing for proper relationships to be made in order to
ensure referential integrity between tables.

I find myself in a situation where I'm having to put SchoolName,
EquipmentLoc, EquipmentType and EquipmentPos in multiple tables; but if
these data appear in multiple locations in the db it implies it is not
properly normalized...right?

How should I arrange the data into tables so that they are properly
normalized? Any help in setting up these tables would be greatly
appreciated.

Chris

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'd do it like this.
Counter is an AutoNumber data type.
Unique is a unique index (no duplicates).
You can change the length of each VARCHAR data type to suit your data.

CREATE TABLE Schools (
school_id COUNTER NOT NULL UNIQUE ,
school_name VARCHAR(30) NOT NULL PRIMARY KEY ,
school_city VARCHAR(35) NOT NULL ,
... etc. ...
)
It might be better to have the Primary Key as the school_name and
school_city, 'cuz there may be more than one "WalMart Elementary School"
;-) in different cities.

School_population should be in another table 'cuz it changes every year.
It would normally be a calculated column in a query that counts the
number of students. Since this db doesn't look like it will track each
individual student you could probably just use a table like this:

CREATE TABLE SchoolPopulations (
school_id LONG NOT NULL
REFERENCES Schools (school_id)
ON DELETE CASCADE ,
pop_begin_date DATE NOT NULL ,
pop_end_date DATE NOT NULL ,
school_population INT NOT NULL ,
CONSTRAINT PK_Pops PRIMARY KEY (school_id, pop_begin_date)
)

A table for the types of equipment:

CREATE TABLE EquipmentTypes (
equip_type COUNTER NOT NULL UNIQUE ,
equip_desc VARCHAR(30) NOT NULL PRIMARY KEY ,
)

Now a table that indicates which pieces of equipment go w/ each school:

CREATE TABLE SchoolEquipment (
equip_id COUNTER NOT NULL UNIQUE ,
school_id LONG NOT NULL
REFERENCES Schools (school_id)
ON DELETE CASCADE,
equip_type LONG NOT NULL
REFERENCES EquipmentTypes (equip_type) ,
equip_posn CHAR(4) NOT NULL
CHECK (equip_posn IN ('N', 'S', 'E', 'W', 'NE', 'NW', 'SE', 'SW') ,
CONSTRAINT PK_SchEqp PRIMARY KEY (school_id, equip_type, equip_posn)
)

Access doesn't like CHECK expressions in CREATE TABLE statements, this
is just an example. You can use this for the column's Validation Rule
property. Just use the "IN ('N', 'S', etc....)" expression in the
property.

For the equipment history I'm not quite sure, 'cuz you don't state the
purpose of this db; and I'm not sure what the Count per "t1", "t2" is.
Is t1, t2, etc. a time?

Here's a table that show the physical environment of the equipment per
date. "Meas" is short for measurement.

CREATE TABLE EquipEnviron (
equip_id LONG NOT NULL
REFERENCES SchoolEquipment (equip_id)
ON DELETE CASCADE ,
meas_date DATE NOT NULL ,
meas_posn CHAR(1) NOT NULL
CHECK (meas_posn IN ('L','R') ,
equip_height DOUBLE NOT NULL ,
equip_depth DOUBLE NOT NULL ,
CONSTRAINT PK_EqEnviron PRIMARY KEY (equip_id, meas_date)
)

meas_posn holds 'L' (Left) or 'R' (Right).

Put an Index on the meas_date.

I use Double data type for the height and depth so you can use decimal
values.


Here's the table for the count of children usage of equipment. The
column use_date contains both the date and the time. That way you can
include the time of the t1, t2, t3, etc. values. One of the drawbacks
of using a combined DateTime column is querying for a specific date.
You can't use something like this:

WHERE use_date = #1/1/2006#

You'd have to use this:

WHERE use_date BETWEEN #1/1/2006 00:00# AND #1/1/2006 23:59#

which is the expression for one day, w/ times from midnight to 1 minute
before midnight of the next day; IOW 23 hrs and 59 minutes in one day.

Some people would do something like this:

WHERE DatePart(use_date) = #1/1/2006#

but, this is slower than the BETWEEN 'cuz each value in use_date has to
be parsed by the DatePart() function, a complete table scan, instead of
the query using the use_date index, which is faster.

CREATE TABLE EquipUse (
equip_id LONG NOT NULL
REFERENCES SchoolEquipment (equip_id)
ON DELETE CASCADE ,
use_date DATE NOT NULL ,
child_count INT NOT NULL ,
CONSTRAINT PK_EquipUse PRIMARY KEY (equip_id, use_date)
)

Put an index on the use_date.

The ON DELETE CASCADE means if the "parent" table data row is deleted,
then the "child" table row is also deleted. IOW, if the
SchoolEquipment.equip_id '222' was deleted, all rows in any tables that
had the same equip_id number would be deleted.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only on this newsgroup. I DO NOT respond to emails **
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRK60MoechKqOuFEgEQIVtACgn3tGYME4zRD4p2GJAFGiXO rC4gUAoJQQ
c8Jfh/hPIwuNbrYkiPBYho4u
=zoRW
-----END PGP SIGNATURE-----


Meaney wrote:

Looks like you need to stick EquipmentLoc and EquipmentType into
tblSchools, and assign each school a School ID. This way you only have
to update the school-related data once. Then put the ID in the other
tables.

Meaney wrote:

"fi.or.jp.de" <fi.or.jp.de (AT) gmail (DOT) com> wrote


You can also try posting your question to comp.databases.ms-access or
microsoft.public.access.

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Lots of ways to achieve that - the simplest (and not automated) is to:
- select the range of your interest
- open the target table in MsAccess
- Edit>Paste Append
Done.

Another way is to create a linked table in access (New Table > Linked
Table) and then link it to the Excel file of your interest.

It all really depends on what exactly you need to achieve.

On Jan 6, 1:46*am, "Clif McIrvin" <clare.nom... (AT) gmail (DOT) com> wrote:

On 06/01/2011 09:02:18, AB wrote:
With 10,000 lines in the Excel sheet, there must be a hell of a lot of
duplication (without any details, it is is impossible to say). So for example
if your Excel sheet has a Product, quantity & date bought, quantity and date
sold and you have just recorded the tranactions day by day, you will need to
create 2 Access tables, 1 for product and 1 for bought & sold. Creating these
tables can be done as Austris suggests. If you set the Product descriptipn as
indexed(No Duplicates) then each product will only occur once. The fun bit
comes when you have to go through all the product descriptions and manually
realise that "gren pepper" is a typo for "green pepper" and you have to
transfer the transactions for "gren pepper" to "green pepper" before you can
delete the former. Excel is for processing figures - Access is for storing &
manipupulating data.

Phil

Meanwhile, here is a good explanation of linking Excel and Access
programmatically:
http://www.excelguru.ca/node/18

I built a similar interface for users, and after this experience
recommend limiting the number of rows a user may edit/insert at a
time.

Additionally, if both insert and update functionality are required, I
can share my approach to that -- perhaps not beautiful, but
functional.

jn


On Jan 6, 11:22*am, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:

Neat code, Jeff.

A question, though - is there any particular/technical reason why you
use "rst.Open INSERT..." as oppose to "cnt.Execute INSERT..."?
Wouldn't it do the same thing just with one variable less?



On Jan 7, 12:10*pm, Jeff <jnorvi... (AT) gmail (DOT) com> wrote:

Ah, I shouldn't get credit for the neatness of Ken's code -- that's
Ken Puls's website. (About Ken: http://www.excelguru.ca/node/16)

However, reviewing my own spaghetti code I did use Execute (saw Ken's
page later):
SQL = 'long insert or update query
CMD.CommandText = SQL
CMD.CommandType = adCmdUnknown
'code to highlight row to be inserted...
CMD.Execute

....and I'm handling ADO error codes using an On Error handler.

While I can't remember the specific reason for specifying CommandType,
it had something to do with not knowing the exact context of all SQL
statements the interface could build -- I don't update every field in
each table, for example, and the SQL string may be either an insert or
update (if a particular "key" column is populated, for example). One
of the proper gurus on this list can go into detail there.

Cheers,
Jeff

On Jan 7, 3:05*pm, AB <austris.bahanovs... (AT) gmail (DOT) com> wrote:

Thanks for taking the time to respond!
Cudos then goes to Ken.

A.

On Jan 8, 9:04*am, Jeff Norville <jnorvi... (AT) gmail (DOT) com> wrote: