Emmet and James, I had never used UNION SELECT before so I simply did not think of it. So some study time is coming to learn more about it. From Emmit's example I see how it works. James, you are right about identifying the tables. That was the purpose of the EventName column. UNION SELECT looks much simpler. Thanks very much to both of you.
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 ________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Emmitt Dove Sent: Wednesday, February 06, 2008 7:30 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Appending unrelated data and scrolling edits Tom, Rather than go through all that work to build temp tables, why not just create a UNION SELECT view with aliases to join the like data for your scrolling region? Example: CREATE VIEW masterview (columnA, columnB, columnC) AS + SELECT firstcolumn, secondcolumn, thirdcolumn + FROM firsttable + WHERE condition + UNION ALL + SELECT fourthcolumn, fifthcolumn, sixthcolumn + FROM secondtable + WHERE condition2 + UNION ALL + SELECT seventhcolumn, eighthcolumn, ninethcolumn + FROM thirdtable + WHERE condition3 This only requires that the columns being matched have the same data type. Your scrolling region then references the aliases specified in the CREATE VIEW. Emmitt Dove Manager, DairyPak Business Systems Evergreen Packaging, Inc. [EMAIL PROTECTED] [EMAIL PROTECTED] (203) 643-8022 From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Frederick Sent: Wednesday, February 06, 2008 7:53 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Appending unrelated data and scrolling edits 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
