Tom,
This first thing that occurs to me is how in the combined table
do I identify what table it comes from. I am assuming that ZID
is not unique to each table. So I would think you need an
additional column in combined items to identify source table.
Thus the key to retrieve data would be tableid,ZID,lineid.
Under standard SQL I believe your data is not in third normal
form. It seems that your separate tables should have been
implemented as one table with a columnd that identifies event
type.
To solve you problem consider creating a vie to join the data.
In doing so check ou the UNION ALL phrase of the SELECT command.
Jim Bentley
--- Tom Frederick <[EMAIL PROTECTED]> wrote:
> I have received a system request that I like, but I am not
> sure what
> problems I will run into. I have separate tables for unrelated
> Events
> like behavior, seizures, or Occ Med injury. Right now, a table
> may have
> up to 120 columns of data with virtually no shared columns and
> has its
> own form. Each table has an add/edit/browse menu that selects
> a person's
> records, scrolls by date to allow the user to pick a specific
> event to
> review with the right form. Works very well. The suggestion
> has come to
> blend these unrelated, separate tables/events into one
> summarized
> format, sort by date, and allow the user to select an event
> with the
> correct form to review the real data.
>
>
>
> Each table has an integer column called "ZID" which ties back
> to an
> autonum integer "ZID" column in the identification table. All
> other
> column names are specific for each table such as "BehavDate",
> "OMDate",
> "SeizDate", etc, but if "ZID" works why wouldn't other shared
> names also
> work? Each line in each table also gets an autonum integer ID
> specific
> to its table. If I had common columns in each table ( say
> "ZID", "Date",
> "Time", "LineID", "EventName", and "BriefDescription", each
> with the
> appropriate type/length), I should be able to:
>
> 1. Project these columns based on "ZID" and a Date range
> to a temp
> table,
>
> 2. Append those temp tables into a master temp table
> because the
> column name/types would match (and not have to change column
> names from
> different tables),
>
> 3. Use ORDER to scroll the master temp table records by
> "Date" and
>
>
> 4. Then by selecting a scroll line, use EDIT USING in a
> bit button
> EEP, with WHERE commands for "EventName" and "LineID", to go
> to the
> specific row in a specific table using the required form.
>
> This type of system would avoid going to several menus and
> visually show
> behavior trend summaries that would not be obvious by using
> multiple
> menus.
>
>
>
> It sounds feasible and seems clearer the more I think about
> it. Still,
> for me, it is a major jump on Razzak's "Keep Learning" mantra.
> What
> problems will I run into or is there just a much simpler
> method to
> accomplish this task? I have looked at the sales transaction
> examples in
> SAT and the sample databases. It seems that as long as columns
> like
> "Date" or "LineID" do not require distinct values, I should be
> able to
> append a series of columns with other identically named
> columns from
> other tables as long as the types/names match and, through
> scrolling,
> let the user pick exactly what they want. Sounds like a simple
> process.
> Feels like a John Wayne movie where the scene is "too quiet"
> before the
> guns start blazing.
>
>
>
> Tom Frederick
>
> Elm City Center
>
> 1314 W Walnut
>
> Jacksonville, IL 62650
>
> Off - 217-245-9504
>
> Fax - 217-245-2350
>
> Email - [EMAIL PROTECTED]
>
> Web - www.elmcity.org
>
>
>
>
Jim Bentley
American Celiac Society
[EMAIL PROTECTED]
tel: 1-504-737-3293
____________________________________________________________________________________
Be a better friend, newshound, and
know-it-all with Yahoo! Mobile. Try it now.
http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
