Need help with my first Access file

[sduffield2]

So far I have:

- Imported my data from an Excel file into an Access Table.
- Sorted the table alphabetically
- Turned on the Subdatasheet so that the Master Field is Field1 and the
Child Field is Field2.

What I want to do is keep Field1 in this table but move all data in
Field2:Field22 to the subdatasheet.

I could move all that data into separate tables and then make a relationship
between the new table and the cell in Field1 that I want it linked to
(couldn't I?), but I would prefer to keep all the data in one table.

Perhaps of note is that my reasons for wanting the data moved to
Subdatasheets is because only a quarter of the rows in Field1 have anything
in Field2:Field22

Thank you!

 

[tina]

SubDatasheet is a misleading name. a subdatasheet is not a subcategory of
records in a single table; it is simply a way to see related records *from
another table*, in the first table's datasheet view.

an Access table looks similar to an Excel spreadsheet (rows and columns),
but it is very different. the "rows" are records, and the "columns" are
fields. (there are no "cells" in an Access table.) if your data originated
in an Excel file, it is very probably not normalized - not correctly grouped
by relationships. if you're just doing something "quick and dirty", for
yourself, to analyze and/or manipulate the data, you may not need to bother
setting up the tables and relationships correctly. BUT if you're building a
database that is going to accumulate data (either via Excel or by manual
data entry), and be used over and over, and especially if other people are
going to use the database, i strongly recommend that you invest the time in
learning the basics of both the software and the concepts of relational
database design (often referred to as data modeling, or table
normalization). there are numerous good books on the market, including

Microsoft Access <version> Bible by Prague and Irwin
(teaches you how to use the software effectively)
Database Design for Mere Mortals by Michael Hernandez
(teaches you data modeling)

and many websites that offer information and tips. here are a few that will
help you start out on the right foot.

http://www.mvps.org/access/tencommandments.htm
(as a newbie, pay special attention to #s 1, 2, 3, 7, 8 and 10.)
http://www.mvps.org/access/lookupfields.htm
http://www.mvps.org/access/general/gen0012.htm

hth

 

[sduffield2]

Thank you for the links to FAQs and advice on getting started with Access.

Now that my vocab is more expanded, maybe I can state my questions more
clearly to you:

1. Do subdatasheets assist in normalization or just make the data harder to
maintain?

2. In a drawer of 50 files 20 of the files contain other files (subfiles).
To create a normalized database, would you suggest I create a table for each
of the files that contains files? I have already created keys to identify
each record whether it be a "parent" file or a subfile. Creating new tables
for each file that contains files seems redundant and prohibitive for easy
data entry in the future.

Currently, I'm simply trying to convert a directory of files in Word to a
searchable database in Access, but I expect it to grow over time. So, of
course I need to have a better handle of what works and doesn't in Access,
but I'm hoping that over time as my needs become more complex that I can grow
with them.

Thanks for your help!

 

[tina]

since you're pursuing this same issue in another post in this newsgroup, and
also in the access.tablesdbdesign newsgroup with MVP John Nurick, i'll leave
you to it. John is an excellent resource for you, suggest you stick to the
threaded discussion with him until your dialog is finished. good luck.