RE: [MS_AccessPros] Opions plz, continued from discussion with John V. re: LOTS of Tables

Terence,
Did you look at my suggestion of normalizing your tables as demo'd in http://www.rogersaccesslibrary.com/forum/at-your-survey_topic3.html?

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: buffalome90210@yahoo.com
Date: Sat, 27 Aug 2011 13:05:19 +0000
Subject: [MS_AccessPros] Opions plz, continued from discussion with John V. re: LOTS of Tables

I have been working on a DB which started out small and grew LOL. It is a medical/statistical DB, and the General format was an Information Table (Patients name, address, etc), An Office_Visit Table, consisting of measurements taken during that visit. Very Straight forward.

Then it turned out that the measurements taken during each office visit were derived from tests which required either specific calculations or questioners. So what I did was to create a popup form for each test or questioners. These detailed elements of information were then stored in a separate table linked to the office visit.

Well I now have over 40 potential tables per office visit and counting.

In discussions with John V. (Which you can follow on our forum). John suggested that I consider making each test a ROW of the Visit table, thus reducing the number of table significantly.

Now be aware, these probably only 50 % of these test are actually performed during a visit.

John seemed to be uncomfortable with the large number of tables, and seemed especially concerned about DB size.

I am looking for comments suggestions, and any case experience from other member with a similar situation. I wrestle with LOTS of tables, BIG DEAL, "thats what a DB is for". Or should I take a more conservative approach and make the various test data as rows to the Visit ?

Pit Falls ? Pros Cons ?
terence
TY in advance.

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/