Here goes an example that may cut through some of the fog.
TABLE 1 - EmpMaster (EmpID, EmpFirstName, EmpLastName, EmpPhone)
TABLE 2 - EmpDetail (EmpDetailID, EmpID, EmpPhone, EmpEffectiveDate)
The second table being a dated effective table, which allows for keeping
history of the employee phone numbers. If the second table is included in a
form, the only record that shows will be the one containing the current phone
number, which is included in the header record (bad design, I know, but...)
If, in the form, we create a scrolling region based on the following view, the
employee phone number history will appear, with all records visible, and
editable.
CREATE VIEW vwEmpForm (EmpDetailID, EmpID, EmpDetailPhone, EmpEffectiveDate)
AS SELECT EmpDetailID, EmpID, EmpPhone, EmpEffectiveDate
FROM EmpDetail
Note that the only change is that the EmpPhone column has been renamed
EmpDetailPhone, so the form will not attempt to match the phone numbers.
--- Bill Niehaus <[EMAIL PROTECTED]> wrote:
> Thanks for the suggestion Albert. I can get the second table (a single
> table view) to display in the form as well as the first table. So far
> the displaying of data is independent of each other. Now I need to
> review how to get data from the second table to match the corresponding
> record in the first table, so the user can see/edit information from
> both tables about one masterID at a time.
>
> Bill
>
>
>
> >>> [EMAIL PROTECTED] 8/10/2004 11:13:40 AM >>>
> To clarify, my suggestion is to replace the second table in the form
> with a
> view that refers to only that second table. If the view has reference
> to more
> than one, and only one, table, the view is not editable - it will
> display fine,
> but the user cannot update, delete or insert through the view.
>
> The object of the view in this case is solely to rename the non-linking
> columns
> that appear in both tables in the form so that the form will pick up
> the second
> table using only the linking column.
>
>
> --- Charles Parks <[EMAIL PROTECTED]> wrote:
>
> > That would be a two table view. If you take off all the columns with
> a
> > t2. prefix and the table rawdata that will make it a one table view.
>
> >
> > -----Original Message-----
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bill
> > Niehaus
> > Posted At: Monday, August 09, 2004 2:07 PM
> > Posted To: RB7-L
> > Conversation: [RBG7-L] - Re: RBase 7 for windows - two table form
> > Subject: [RBG7-L] - Re: RBase 7 for windows - two table form
> >
> >
> > I get the following error when trying to edit using the view:
> > Invalid cursor state (2645)
> >
> > Albert said I need to make sure I use a single table view. I'm not
> > familiar with a single table view.
> > The following is my view:
> >
> > CREATE VIEW fieldRawdata +
> > (TrialF, PlotF, EntryF, RepF, +
> > LandF, RangeF, FieldRowF, UserNameF, ChangeDateF, + CreateDateF,
> > UpdatedF, seqPlotNumF, + RandNumF, harvSeqF, harvRoundF, harvDirectF,
> +
> > uniqueFieldNumF, harvestNoteF, harvestF, trialPlotF, FieldNoteF, +
> inwt,
> > outwt, sugar, sodium, potass, amino, + foot1, foot2, foot3, foot4,
> > bolters, + fieldwt1, fieldwt2, vigor1, vigor2, stand1, stand2, +
> stand3,
> > stand4, stand5, stand6, username, changeDate, + createDateField,
> > fieldNoteRaw, generic1, generic2, generic3, + generic4, numGaps,
> > runDate, runTime, harvDate, land, + rangeRaw, fieldColumn, disease1,
> > disease2, disease3, + disease4, disease5, disease6, disease7, +
> > disease8, disease9, sample) + AS SELECT t1.trial, t1.plotNum,
> t1.entry,
> > t1.repNum, + t1.landNum, t1.rangeNum, t1.fieldRowNum, t1.userName,
> > t1.changeDateField, + t1.createDateField, t1.updateDateField,
> > t1.seqPlotNum, + t1.randNum, t1.harvSequence, t1.harvRound,
> > t1.harvDirection, + t1.uniqueFieldNum, t1.harvestNote, t1.harvest,
> > t1.trialPlot, t1.FieldNote, + t2.inwt, t2.outwt, t2.sugar,
> t2.sodium,
> > t2.potassium, t2.aminoNit, + t2.footage1, t2.footage2, t2.footage3,
> > t2.footage4, t2.bolters, + t2.fieldwt1, t2.fieldwt2, t2.vigor1,
> > t2.vigor2, t2.stand1, t2.stand2,
> > +
> > t2.stand3, t2.stand4, t2.stand5, t2.stand6, t2.username,
> t2.changeDate,
> > +
> > t2.createDate, t2.fieldNoteRaw, t2.generic1, t2.generic2,
> t2.generic3,
> > +
> > t2.generic4, t2.numGaps, t2.runDate, t2.runTime, t2.harvDate,
> t2.land,
> > +
> > t2.range, t2.fieldColumn, t2.diseaserate1, t2.diseaserate2,
> > t2.diseaseRate3, + t2.diseaseRate4, t2.diseaseRate5,
> t2.diseaseRate6,
> > t2.diseaseRate7, + t2.diseaseRate8, t2.diseaseRate9, t2.sample +
> FROM
> > field t1, rawdata t2 WHERE + t2.trialPlot = t1.trialPlot RETURN
> >
> > >>> [EMAIL PROTECTED] 8/9/2004 10:39:49 AM >>>
> > When you have a linking column and also other non-linking columns,
> and
> > you wish to slave table2 to table1 in a single form, create a view
> that
> > renames all common columns except the linking one for one of the
> tables.
> > Make sure this is a single table view. Now use the view in your
> form,
> > and the slave table will appear correctly. As it is a single table
> view,
> > it is editable.
> >
> >
> > Bill Niehaus <[EMAIL PROTECTED]> wrote:The tables have more
> than
> > one column in common and may not be identical between the two
> tables.
> >
> > Bill N.
> >
> >
> > >>> [EMAIL PROTECTED] 8/9/2004 6:45:41 AM >>>
> > At 10:34 PM 8/8/2004 -0500, Bill Niehaus wrote:
> >
> > >I have never used a single form for editing data from two tables
> with
> > a
> > >common column. I looked through some examples (e.g. OneToManyDBGrid
> > in
> > >RRBYW) but have not been able to get the second table to show up.
> > >
> > >Are there some general procedures that would help set up such a
> form?
> >
> >
> > Bill,
> >
> > 01. Do both tables have more than one matching column(s)?
> >
> > 02. If there are more than one matching column, and if the column
> values
> > are different, the slave table data is not going to show up.
> >
> > 03. For more examples, take a look a the following forms in RRBYW8:
> >
> > Database: RRBYW8
> > Forms: CustContAndSales
> > CustomerContacts
> > Products
> > SalesTransactions
> >
> > Hope that helps!
> >
> > Very Best R:egards,
> >
> > Razzak.
> >
> >
> >
> >
> > =====
> > Albert Berry
> > Management Consultant
> > RR2 - 1252 Ponderosa Drive
> > Sparwood BC, V0B 2G2
> > Canada
> > (250) 425-5806
> > (250) 425-7259
> > (708) 575-3952 (fax)
> > [EMAIL PROTECTED]
> >
> >
>
>
> =====
> Albert Berry
> Management Consultant
> RR2 - 1252 Ponderosa Drive
> Sparwood BC, V0B 2G2
> Canada
> (250) 425-5806
> (250) 425-7259
> (708) 575-3952 (fax)
> [EMAIL PROTECTED]
>
>
=====
Albert Berry
Management Consultant
RR2 - 1252 Ponderosa Drive
Sparwood BC, V0B 2G2
Canada
(250) 425-5806
(250) 425-7259
(708) 575-3952 (fax)
[EMAIL PROTECTED]
