> Hi,
>
> I'm new to access and i can't figure out how to do these based on two tables
> below:
>
> Table1(Quarters)
> Fields:
> QtrType
> Grade
> Rental
>
> Table2(Staff)
> Fields:
> StaffNo
> Name
> QtrType
> Grade
> Rental
>
> During Staff data entry, I would like to be able to populate Staff.Rental
> with the value of Quarters.Rental WHERE "Staff.QtrType=Quarters.QtrType AND
> Staff.Grade=Quarters.Grade"
>
> Grateful for any input.
>
> Garu
>
>

"Philip Herlihy" <bounceb***@you.com> wrote in message
news:e7ECt%23A7JHA.3860@TK2MSFTNGP05.phx.gbl...
> gar***@brunet.bn wrote:
>> Hi,
>>
>> I'm new to access and i can't figure out how to do these based on two
>> tables below:
>>
>> Table1(Quarters)
>> Fields:
>> QtrType
>> Grade
>> Rental
>>
>> Table2(Staff)
>> Fields:
>> StaffNo
>> Name
>> QtrType
>> Grade
>> Rental
>>
>> During Staff data entry, I would like to be able to populate Staff.Rental
>> with the value of Quarters.Rental WHERE "Staff.QtrType=Quarters.QtrType
>> AND Staff.Grade=Quarters.Grade"
>>
>> Grateful for any input.
>>
>> Garu
>
> Not sure your data tables are right - the duplication of QtrType, Grade
> and Rental ring alarm bells.  I guess you have an individual who will be
> allocated to a particular accommodation depending on his rank.  That
> accommodation will have a particular grade, and grades have (presumably) a
> specific rental.
>
> So, an individual has a property (either via a link through rank, or
> directly) which identifies the grade of accommodation he can use. Grades
> have a property of the rental value (and possibly the depth of carpet,
> etc).  Accommodation will have properties of location, number of beds
> (etc) and grade.
>
> That suggests to me there should be a table for Staff, one for Grades, and
> one for Accommodation.  If Rental depends also on QtrType (which I guess
> might involve number of beds, etc) then the Accomodation table would also
> have a property signifying QtrType, and the Grades table (which I might
> rename Rental!) would have fields for Grade, Type and Rental, with Grade
> and Type as a composite key (or at least a unique index).
>
> As is often the case, such a rearrangement often seems unintuitive, but I
> think that will be much easier to work with.
>
> What do you think?
>
> Phil, London

>Hi Phil,
>
>Thanks for your suggestions. I have revised the tables based on your
>suggestion I now have the following tables:
>
>Table1(Quarters)
>ID (autonumber)
>QtyType (one-to-many relationship with Rental.QtrType)
>Location
>Remarks
>
>Table2(Rental)
>ID (autonumber)
>QtrType
>Grade
>Rental
>
>Table3(Staff)
>ID (autonumber)
>StaffNo
>StaffName
>QtrType
>Grade
>
>Assuming I have the following data:
>      Quarters:
>      ID QtrType Location
>      1 I F-20
>      2 II F-21
>      3 III F-22
>
>      Grade:
>      ID Qtr Type Grade Rental
>      1 I A 500
>      2 I B 450
>      3 II A 300
>      4 II B 350
>      5 III A 300
>      6 III B 250
>
>      Staff:
>      ID StaffNo Name Qtr Type Grade
>      1 S001 John I A
>      2 S002 Paul I B
>      3 S003 Willy II A
>      4 S004 James II B
>      5 S005 Peter III A
>      6 S006 Jude III B
>
>1) How can I relate Staff with Grade to be able to get the Rental based on
>QtrType & Grade values?
>2) Do I have to create a Rental field on Staff table to assigned the Rental
>value from the related QtrType & Grade?
>
>Awaiting your reply...
>
>Thanks,
>Garu
>
>>> Hi,
>>>
>[quoted text clipped - 48 lines]
>>
>> Phil, London

> Garu:
>
> Join the Rental table to the Staff table in a query on the QtrType  and Grade
> columns (fields).  In query design view you do this by dragging from QtrType
> in one to QtrType in the other and then by dragging from Grade in one to
> Grade in the other so you end up with two join lines.  In SQL view the query
> would look something like this:
>
> SELECT StaffName, Rental
> FROM Rental INER JOIN Staff
> ON Rental.QtrType  = Staff.QtrType
> AND Rental.Grade = Staff.Grade;
>
> You don't need to add a Rental column to the Staff table, and to do so would
> be wrong as it introduces redundancy which would leave the table open to
> inconsistent data.  Your tables are structured in such a way as to enable you
> to do what you want already, as QtrType  and Grade are what's known as a
> 'candidate key' of the rental table, each row of the table always having
> distinct values in these columns in combination.  So the combination of
> QtrType  and Grade in the Staff table is in effect a composite foreign key
> referencing the composite candidate key of Rental.
>
> The QtrType column is also a candidate key of the Quarters table, as is
> StaffNo of the Staff table.  You could in fact delete the autonumber primary
> keys of all these tables without loss and make the candidate keys the primary
> keys of these tables, but keeping the autonumber keys does no harm provided
> that you index the candidate keys uniquely.
>
> To create a unique index on two columns select indexes from the View menu.
> Enter a suitable index name in one row of the left column, then enter the
> column names on two rows of the Filed Name column.  With the first row (the
> one with the index name) selected enter Yes as the 'Unique' property. (it may
> differ in the Access 2007 interface).
>  
> Ken Sheridan
> Stafford, England
>
> Garu wrote:
>> Hi Phil,
>>
>> Thanks for your suggestions. I have revised the tables based on your
>> suggestion I now have the following tables:
>>
>> Table1(Quarters)
>> ID (autonumber)
>> QtyType (one-to-many relationship with Rental.QtrType)
>> Location
>> Remarks
>>
>> Table2(Rental)
>> ID (autonumber)
>> QtrType
>> Grade
>> Rental
>>
>> Table3(Staff)
>> ID (autonumber)
>> StaffNo
>> StaffName
>> QtrType
>> Grade
>>
>> Assuming I have the following data:
>>      Quarters:
>>      ID QtrType Location
>>      1 I F-20
>>      2 II F-21
>>      3 III F-22
>>
>>      Grade:
>>      ID Qtr Type Grade Rental
>>      1 I A 500
>>      2 I B 450
>>      3 II A 300
>>      4 II B 350
>>      5 III A 300
>>      6 III B 250
>>
>>      Staff:
>>      ID StaffNo Name Qtr Type Grade
>>      1 S001 John I A
>>      2 S002 Paul I B
>>      3 S003 Willy II A
>>      4 S004 James II B
>>      5 S005 Peter III A
>>      6 S006 Jude III B
>>
>> 1) How can I relate Staff with Grade to be able to get the Rental based on
>> QtrType & Grade values?
>> 2) Do I have to create a Rental field on Staff table to assigned the Rental
>> value from the related QtrType & Grade?
>>
>> Awaiting your reply...
>>
>> Thanks,
>> Garu
>>
>>>> Hi,
>>>>
>> [quoted text clipped - 48 lines]
>>> Phil, London
>

> Garu:
>
> Join the Rental table to the Staff table in a query on the QtrType  and
> Grade
> columns (fields).  In query design view you do this by dragging from
> QtrType
> in one to QtrType in the other and then by dragging from Grade in one to
> Grade in the other so you end up with two join lines.  In SQL view the
> query
> would look something like this:
>
> SELECT StaffName, Rental
> FROM Rental INER JOIN Staff
> ON Rental.QtrType  = Staff.QtrType
> AND Rental.Grade = Staff.Grade;
>
> You don't need to add a Rental column to the Staff table, and to do so
> would
> be wrong as it introduces redundancy which would leave the table open to
> inconsistent data.  Your tables are structured in such a way as to enable
> you
> to do what you want already, as QtrType  and Grade are what's known as a
> 'candidate key' of the rental table, each row of the table always having
> distinct values in these columns in combination.  So the combination of
> QtrType  and Grade in the Staff table is in effect a composite foreign key
> referencing the composite candidate key of Rental.
>
> The QtrType column is also a candidate key of the Quarters table, as is
> StaffNo of the Staff table.  You could in fact delete the autonumber
> primary
> keys of all these tables without loss and make the candidate keys the
> primary
> keys of these tables, but keeping the autonumber keys does no harm
> provided
> that you index the candidate keys uniquely.
>
> To create a unique index on two columns select indexes from the View menu.
> Enter a suitable index name in one row of the left column, then enter the
> column names on two rows of the Filed Name column.  With the first row
> (the
> one with the index name) selected enter Yes as the 'Unique' property. (it
> may
> differ in the Access 2007 interface).
>
> Ken Sheridan
> Stafford, England
>
> Garu wrote:
>>Hi Phil,
>>
>>Thanks for your suggestions. I have revised the tables based on your
>>suggestion I now have the following tables:
>>
>>Table1(Quarters)
>>ID (autonumber)
>>QtyType (one-to-many relationship with Rental.QtrType)
>>Location
>>Remarks
>>
>>Table2(Rental)
>>ID (autonumber)
>>QtrType
>>Grade
>>Rental
>>
>>Table3(Staff)
>>ID (autonumber)
>>StaffNo
>>StaffName
>>QtrType
>>Grade
>>
>>Assuming I have the following data:
>>      Quarters:
>>      ID QtrType Location
>>      1 I F-20
>>      2 II F-21
>>      3 III F-22
>>
>>      Grade:
>>      ID Qtr Type Grade Rental
>>      1 I A 500
>>      2 I B 450
>>      3 II A 300
>>      4 II B 350
>>      5 III A 300
>>      6 III B 250
>>
>>      Staff:
>>      ID StaffNo Name Qtr Type Grade
>>      1 S001 John I A
>>      2 S002 Paul I B
>>      3 S003 Willy II A
>>      4 S004 James II B
>>      5 S005 Peter III A
>>      6 S006 Jude III B
>>
>>1) How can I relate Staff with Grade to be able to get the Rental based on
>>QtrType & Grade values?
>>2) Do I have to create a Rental field on Staff table to assigned the
>>Rental
>>value from the related QtrType & Grade?
>>
>>Awaiting your reply...
>>
>>Thanks,
>>Garu
>>
>>>> Hi,
>>>>
>>[quoted text clipped - 48 lines]
>>>
>>> Phil, London
>
> --
> Message posted via http://www.accessmonster.com
>

"Philip Herlihy" <bounceb***@you.com> wrote in message
news:u$AMYab7JHA.2388@TK2MSFTNGP06.phx.gbl...
> Garu wrote:
>> This is exciting... I've done the relationship between Staff and Rental
>> table, created a query and I seem to get the corresponding rental value
>> based on the combination of data assigned to QtrType and Grade.
>>
>> My next problem is during Staff data entry, i would like the user to be
>> able to see the Rental value upon populating QtrType and Grade.  I
>> created a form and use the above Query in the form but I don't seem to
>> add nor change any data in Form View.  Grateful for another instruction
>> in getting the Staff data entry going and thanks so much for your time.
>>
>> Garu
>>
> Looking at the Rental value issue first; if Rental isn't tied to an
> individual Staff-member record, which means it would always change for
> everyone at the same time, then all you have to do is add the Rental table
> to your query and you should be able to display the value in your form.
>
> If, however, individuals start at the going rate but then stay on that
> rate even when the rental for new people has gone up, then you only need
> to look up the going rate when a new staff record is added, and that value
> is stored with the staff record (not in the Rental table, which only
> supplies the "starting" rate).  In that case the lookup process is
> independent of the main connection ("Record Source") which links the form
> to the underlying query, and you have to have a separate bit of code to
> launch this separate query.  The value of the field is "bound" in this
> case to the field in the Form's main query, but the value that is supplied
> at data entry time is coming from another route.  One good way of doing
> this (usually done where there are several options to choose from) is to
> use a Combo or List box, which has a "Row Source" property.  The combo or
> list box takes care of the business of invoking that lookup, whereas if
> you used a simple text box you'd need to use an Event Procedure in VBA to
> force a lookup using the DLOOKUP command, which will need to refer to the
> existing values of QtrType and Grade. (If I had more time today I'd have
> tried harder to make this more comprehensible - sorry!).
>
> Can't see an obvious reason why you can't change records in your Form.
> Working with a copy of your data (I trust), can you change records in the
> underlying query?
>
> That's all from me today, I'm afraid - meetings!
>
> Phil

> Im clear on the Rental issue now, i decided not to put it as property of
> Staff table since changes on Rental will affect everyone at the same time.
> So what i have on my query is as follows:
>
> Tables: (QtrType and Grade Joined)
> 1)Staff
> 2)Rental
>
> On my field list I got:
> -Staff.StaffNo
> -Staff.QtrType
> -Staff.Grade
> -Rental.Rental (which I renamed to RentalRate to avoid confusion, so it is
> now Rental.RentalRate)
>
> In datasheet view, the query dislays the right RentalRate for individual
> staff based on combination of Staff.QtrType and Staff.Grade which I have
> entered from Staff table for testing purposes.  My problem
> is... I can't seem to amend existing Staff records and also unable to add
> new Staff record using the above query (in datasheet view) and I can't
> figure out why.
>
> Please help
>
> Thanks,
> Garry
>

> Who, me?
>
> It could be that the join columns (QtrType and Grade) being returned in
> the
> query are those from the Rental table not the Staff table.  We had a post
> a
> few days back where this was causing a query to be non-updatable.  In that
> case the key was one column, but I don't think the multi-column key should
> be
> a problem provided the columns being returned are those from the
> referencing
> table, not the referenced table.
>
> Ken Sheridan
> Stafford, England
>
> Philip Herlihy wrote:
>>> Im clear on the Rental issue now, i decided not to put it as property of
>>> Staff table since changes on Rental will affect everyone at the same
>>> time.
>>[quoted text clipped - 22 lines]
>>> Thanks,
>>> Garry
>>
>>I wonder if it's the use of a composite key that's blocking changes.  As
>>I've said, I've never been comfortable with composite keys for some
>>reason, and I'm certainly no expert in their implications.  One for Ken?
>>
>>Might be worth posting the SQL text of your query here.
>>
>>Phil
>
> --
> Message posted via http://www.accessmonster.com
>

>I can't see why you should get that behaviour, nor can I reproduce it.   To
> reiterate, the form's RecordSource should be:
>
> SELECT Staff.StaffNo, Staff.QtrType, Staff.Grade, Rental.RentalRate
> FROM Rental INNER JOIN Staff ON (Rental.Grade = Staff.Grade)
> AND (Rental.QtrType = Staff.QtrType);
>
> There can be other columns from the Staff table such as the staff names in
> the query's SELECT clause of course, but the QtrType and Grade columns
> from
> Rental should not be in the query's SELECT clause.
>
> The primary key of Staff should be StaffNo and the primary key of Rental a
> composite one of QtrType and Grade.
>
> The relationship between Staff and Rental should be on the two columns
> QtrType and Grade and referential integrity enforced.
>
> If all that is the case the rental rate should appear in the control on
> the
> form bound to that column as soon as the QtrType  and Grade have been
> entered.
> You must of course move focus off whichever is the last of these to be
> entered for it to update and the rental rate to be shown.
>
> Ken Sheridan
> Stafford, England
>
> Garu wrote:
>>Recordset Type property to "Dynaset (Inconsistent Update) allowed me to
>>amend and add records but one thing is the Rental.RentalRate field does
>>not
>>display its value automatically even after entering QtrType and Grade, I
>>have to close the form and reopen again then the Rental.RentalRate will
>>display its corresponding value.
>>
>>Garu
>>
>>> Who, me?
>>>
>>[quoted text clipped - 26 lines]
>>>>
>>>>Phil
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200906/1
>

> Hi Phil,
>
> Thanks for your suggestions. I have revised the tables based on your
> suggestion I now have the following tables:
>
> Table1(Quarters)
> ID (autonumber)
> QtyType (one-to-many relationship with Rental.QtrType)
> Location
> Remarks
>
> Table2(Rental)
> ID (autonumber)
> QtrType
> Grade
> Rental
>
> Table3(Staff)
> ID (autonumber)
> StaffNo
> StaffName
> QtrType
> Grade
>
> Assuming I have the following data:
>       Quarters:
>       ID QtrType Location
>       1 I F-20
>       2 II F-21
>       3 III F-22
>
>       Grade:
>       ID Qtr Type Grade Rental
>       1 I A 500
>       2 I B 450
>       3 II A 300
>       4 II B 350
>       5 III A 300
>       6 III B 250
>
>       Staff:
>       ID StaffNo Name Qtr Type Grade
>       1 S001 John I A
>       2 S002 Paul I B
>       3 S003 Willy II A
>       4 S004 James II B
>       5 S005 Peter III A
>       6 S006 Jude III B
>
>
> 1) How can I relate Staff with Grade to be able to get the Rental based on
> QtrType & Grade values?
> 2) Do I have to create a Rental field on Staff table to assigned the Rental
> value from the related QtrType & Grade?
>

>> Hi Phil,
>>
>[quoted text clipped - 49 lines]
>> 2) Do I have to create a Rental field on Staff table to assigned the Rental
>> value from the related QtrType & Grade?
>
>1)  Firstly, it's always a good idea to "declare" your relationships in
>the Relationships window if you haven't already done so.  Then Access
>will sometimes offer more options.  In the query builder, add the Staff
>table and also the Grade/Rental table (you've used both names).  If
>Access doesn't show the relationship already, drag the two key fields
>from the table in which they are primary key (Grade/Rental) to the one
>in which they are a foreign key (the "many" side), in this case the
>Staff table.  Then, drag the fields you want (which might be, say, Name
>from the Staff table and Rental from what I'll now call the Rental
>table) as well as the related fields QtrType and Grade (but you can
>untick the "show" checkboxes if you don't need to see those fields.
>That should, in this design, produce just one record per name, showing
>the rental that would apply.  Caveat - I seem to have a blind spot with
>composite keys, so pay close attention if any of the _real_ experts here
>comment!
>
>2)  You'd only want to do this if the rental for this particular
>assignment would stay the same if the general rule changed.  For
>example, I occasionally increase my billing rate to customers but I
>don't (couldn't!) go back and retrospectively increase the charges for
>work already done and paid for.  So, although I have a similar
>relationship which applies a charge rate for new work, that charge rate
>is then stored with the job.  So in your situation, if a particular
>rental was agreed based on the current "rule" but then the Staff member
>was entitled to stay at that rate indefinitely, the Rental then becomes
>a property of the Staff record, and you'd need to store it.
>
>HTH
>
>Phil

> Phil:
>
> Partly right.  Not the bit about me being an expert, unless you adopt the
> definition given to us by the Vice Chancellor on my first day as an
> undergraduate, which was that 'x' is unknown quantity and 'spurt' is a drip
> under pressure.
>
> The point about the rental is well made, though.  It’s a question of
> functional dependency; is it functionally dependent on the key of Rental, or
> is it also functionally dependent on the key of Staff, i.e. can the rental to
> a member of staff vary at any time from that determined by the row with their
> QtrType and Grade in the Rental table.  If the latter then its needed in both
> tables and the current rental determined by QtrType and Grade in Rental
> should be used to assign a value to a Rental column in Staff when a row is
> inserted into that table.  This is analogous to the assignment of a UnitPrice
> from the Products table to a UnitPrice in the OrderDetails table in Northwind,
> which is done by code in the OrderDetails subform's module.
>
> Ken Sheridan
> Stafford, England
>
> Philip Herlihy wrote:
>>> Hi Phil,
>>>
>> [quoted text clipped - 49 lines]
>>> 2) Do I have to create a Rental field on Staff table to assigned the Rental
>>> value from the related QtrType & Grade?
>> 1)  Firstly, it's always a good idea to "declare" your relationships in
>> the Relationships window if you haven't already done so.  Then Access
>> will sometimes offer more options.  In the query builder, add the Staff
>> table and also the Grade/Rental table (you've used both names).  If
>> Access doesn't show the relationship already, drag the two key fields
>>from the table in which they are primary key (Grade/Rental) to the one
>> in which they are a foreign key (the "many" side), in this case the
>> Staff table.  Then, drag the fields you want (which might be, say, Name
>>from the Staff table and Rental from what I'll now call the Rental
>> table) as well as the related fields QtrType and Grade (but you can
>> untick the "show" checkboxes if you don't need to see those fields.
>> That should, in this design, produce just one record per name, showing
>> the rental that would apply.  Caveat - I seem to have a blind spot with
>> composite keys, so pay close attention if any of the _real_ experts here
>> comment!
>>
>> 2)  You'd only want to do this if the rental for this particular
>> assignment would stay the same if the general rule changed.  For
>> example, I occasionally increase my billing rate to customers but I
>> don't (couldn't!) go back and retrospectively increase the charges for
>> work already done and paid for.  So, although I have a similar
>> relationship which applies a charge rate for new work, that charge rate
>> is then stored with the job.  So in your situation, if a particular
>> rental was agreed based on the current "rule" but then the Staff member
>> was entitled to stay at that rate indefinitely, the Rental then becomes
>> a property of the Staff record, and you'd need to store it.
>>
>> HTH
>>
>> Phil
>

>> Phil:
>>
>[quoted text clipped - 52 lines]
>>>
>>> Phil
>
>That quote seems familiar - which university was it?
>
>Elegantly put - cheers, Ken!
>
>Phil

> I was a bit earlier, graduated 1969; Ancient History and Archaeology.  I
> remember my time there fondly, apart from the indignity of playing for a
> rugby team named 'Mermaids'.  For the benefit of non-Brummie lurkers the
> emblem of The University of Birmingham is a mermaid.
>
> Ken Sheridan
> Stafford, England
>
> Philip Herlihy wrote:
>>> Birmingham.
>>>
>> [quoted text clipped - 7 lines]
>>>> Phil
>> Me too.  Psychology, 1982.
>>
>> Phil
>