How to get a value from a related record?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
Click to expand...

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
 
Garu said:
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?
Click to expand...

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
 
KenSheridan said:
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
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,
Click to expand...
[quoted text clipped - 48 lines]
Phil, London
Click to expand...
Click to expand...
Click to expand...


Ken's one of the _real_ experts I specifically had in mind in my earlier
post (not in this branch of the thread) and I've learned a lot from his
posts. I agree that introducing a Rental column to the Staff table
would be an error, unless you have a situation as I described in my
post, where the rental paid by an individual starts at the "standard"
rate given by the Rental table, but stays at that rate even if the
standard rate increases . Then it would be an error not to store it
with the Staff table (or in some related table describing, say, the
Tenancy). Makes your brain ache, doesn't it?

Phil
 
KenSheridan said:
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 said:
Hi Phil,
Click to expand...
[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?
Click to expand...
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
Click to expand...
Click to expand...

That quote seems familiar - which university was it?

Elegantly put - cheers, Ken!

Phil
 
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

KenSheridan via AccessMonster.com said:
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
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,
Click to expand...
[quoted text clipped - 48 lines]
Phil, London
Click to expand...
Click to expand...
Click to expand...
 
Garu said:
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
Click to expand...
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
 
KenSheridan said:
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 said:
Birmingham.
Click to expand...
[quoted text clipped - 7 lines]
Phil
Click to expand...
Me too. Psychology, 1982.

Phil
Click to expand...
Click to expand...

It's only relatively recently that I've come to understand the skill
that is in History, and why Natural History is so called. I guess that
would be the detection of threads of causality or influence from a mass
of complex, and potentially incoherent, evidence. Psychology was fun,
too. We were well-taught, and led to understand that lots of what you
see pedalled on the telly by "psychologists" is platitudinous nonsense.
I was a mature student (having been thrown out of Cardiff a few years
earlier for getting the work-life balance spectacularly wrong in what
now seems an unthinkable way). Still young enough to relish three years
of self-development in a benevolent and enriching environment. Later
did the Cambridge Computer Science Diploma, where an attempt was made
(in the room in which the atom had first been split, by someone who'd
taught Chris Date) to teach me the theoretical basics of relational
theory but that work-life balance issue was getting in the way again...
Scraped through, with scowls from my supervisor :-) I was good (and
fast) in a punt, though.

O for another go...

Best wishes,

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
 
Garu said:
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
Click to expand...

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
 
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

KenSheridan via AccessMonster.com said:
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 said:
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.
Click to expand...
[quoted text clipped - 22 lines]
Thanks,
Garry
Click to expand...

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
Click to expand...
Click to expand...
 
Got it! The problem is the Rental.ID was set as primary key. I changed the
primary key to QtrType and Grade (composite) and works well. Sorry, t'was
my fault (didn't follow instructions properly on the early posts). Million
thanks to Phill & Ken for their time and patient.

Garu

KenSheridan via AccessMonster.com said:
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
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?
Click to expand...
[quoted text clipped - 26 lines]
Phil
Click to expand...
Click to expand...
Click to expand...
 
Back
Top