Autolookup between 3 tables

[Tray]

I have a problem, which I'm sure should be easy to resolve - but I've spent
so long looking at it that I just can't work it out now.

Part of my database is to record payments made from different employers.
Each employer pays an amount according to their individual rate (which
changes each year).

So far, I have setup 3 tables:

Employer:
employerRef (Primary Key)
employerName
employerType

Payment:
paymentID (Primary Key)
employerRef (Foreign Key)
paymentDate
paymentAmount

Rate:
employerRef (Compound Key)
paymentDate (Compound Key)
rate

Firstly, I'm not sure whether this was the best way to set it up (with the
compound key in Rate). And I can't decide how the relationships between the
Rate table and the others should be setup.

When a user inputs a new payment, I want them to select the employerRef
first and for the form to then display the employerName (for reference). This
part I managed to do by binding the form to an autolookup query with the
Employer and Payment tables.

When the user then selects the paymentYear, I want the form to display the
corresponding rate for that particular employer in that particular year. Any
guidance for how I should do this? I've played around with queries but can't
get it to work.

 

[vbasean]

Here's a concept:
Three Tables
1) Employees: same as yours

2) Payments:
PaymentID (Auto number)
EmployeeID (Number, Foriegn Key)
PaymentDate (Date)
PaymentYear (text 4 characters)
Payment (Currency)

3) Employee_Rates
EmployeeID (Number, foriegn key) combo key
EmployeeYear (Text 4 charachters) combo key
Rate (Currency)

Now, as for fixing the payment amount look up, you'll need
an unbound textbox for showing that year's amount
a combo box for selecting the year

you can put a value list in for the combo box of 4 digit years OR create a
Look up table with a list of years. For this example, call it "ComboYear"

now for the unbound textbox
=DLookup("[Employee_Rates]", "[Rate]", "[EmployeeID] = " & Me.EmployeeID & "
AND [EmployeeYear] = '" & Me.ComboYear & "'"

 

[vbasean]

I guess I could elaborate more:
Createing an Employee form
main form for Employee data
1) Sub form for Payments
2) Sub form for Rates
based off the three table structure
Each form is bound respectively off each table

going back to the year look up table, add that and every 'Year' field can be
a lookup field to that table (It's a direct pass of info as far as look up is
concerned so it's safe)

the payment form is straight forward, a datasheet form of payments

the Employee Rate form will include the unbound text box I mentioned
earlier, place this in the details section of the form

the combo box with the year can be anywhere but I would prefer to place it
in the form header. Instead of datasheet, I'd set the form to 'Continuous'

same settings in the text box and combo box as below.
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

Here's a concept:
Three Tables
1) Employees: same as yours

2) Payments:
PaymentID (Auto number)
EmployeeID (Number, Foriegn Key)
PaymentDate (Date)
PaymentYear (text 4 characters)
Payment (Currency)

3) Employee_Rates
EmployeeID (Number, foriegn key) combo key
EmployeeYear (Text 4 charachters) combo key
Rate (Currency)

Now, as for fixing the payment amount look up, you'll need
an unbound textbox for showing that year's amount
a combo box for selecting the year

you can put a value list in for the combo box of 4 digit years OR create a
Look up table with a list of years. For this example, call it "ComboYear"

now for the unbound textbox
=DLookup("[Employee_Rates]", "[Rate]", "[EmployeeID] = " & Me.EmployeeID & "
AND [EmployeeYear] = '" & Me.ComboYear & "'"




--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

I have a problem, which I'm sure should be easy to resolve - but I've spent
so long looking at it that I just can't work it out now.

Part of my database is to record payments made from different employers.
Each employer pays an amount according to their individual rate (which
changes each year).

So far, I have setup 3 tables:

Employer:
employerRef (Primary Key)
employerName
employerType

Payment:
paymentID (Primary Key)
employerRef (Foreign Key)
paymentDate
paymentAmount

Rate:
employerRef (Compound Key)
paymentDate (Compound Key)
rate

Firstly, I'm not sure whether this was the best way to set it up (with the
compound key in Rate). And I can't decide how the relationships between the
Rate table and the others should be setup.

When a user inputs a new payment, I want them to select the employerRef
first and for the form to then display the employerName (for reference). This
part I managed to do by binding the form to an autolookup query with the
Employer and Payment tables.

When the user then selects the paymentYear, I want the form to display the
corresponding rate for that particular employer in that particular year. Any
guidance for how I should do this? I've played around with queries but can't
get it to work.

 

[vbasean]

I'm really flip flop today,

It's the rate form that's straight forward as a datasheet

The payment form is the one with the continuous form and the textbox for
lookup value

--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

I guess I could elaborate more:
Createing an Employee form
main form for Employee data
1) Sub form for Payments
2) Sub form for Rates
based off the three table structure
Each form is bound respectively off each table

going back to the year look up table, add that and every 'Year' field can be
a lookup field to that table (It's a direct pass of info as far as look up is
concerned so it's safe)

the payment form is straight forward, a datasheet form of payments

the Employee Rate form will include the unbound text box I mentioned
earlier, place this in the details section of the form

the combo box with the year can be anywhere but I would prefer to place it
in the form header. Instead of datasheet, I'd set the form to 'Continuous'

same settings in the text box and combo box as below.
--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

Here's a concept:
Three Tables
1) Employees: same as yours

2) Payments:
PaymentID (Auto number)
EmployeeID (Number, Foriegn Key)
PaymentDate (Date)
PaymentYear (text 4 characters)
Payment (Currency)

3) Employee_Rates
EmployeeID (Number, foriegn key) combo key
EmployeeYear (Text 4 charachters) combo key
Rate (Currency)

Now, as for fixing the payment amount look up, you'll need
an unbound textbox for showing that year's amount
a combo box for selecting the year

you can put a value list in for the combo box of 4 digit years OR create a
Look up table with a list of years. For this example, call it "ComboYear"

now for the unbound textbox
=DLookup("[Employee_Rates]", "[Rate]", "[EmployeeID] = " & Me.EmployeeID & "
AND [EmployeeYear] = '" & Me.ComboYear & "'"




--
~Your Friend Chris
http://myvbastuff.blogspot.com/
thinking out loud

I have a problem, which I'm sure should be easy to resolve - but I've spent
so long looking at it that I just can't work it out now.

Part of my database is to record payments made from different employers.
Each employer pays an amount according to their individual rate (which
changes each year).

So far, I have setup 3 tables:

Employer:
employerRef (Primary Key)
employerName
employerType

Payment:
paymentID (Primary Key)
employerRef (Foreign Key)
paymentDate
paymentAmount

Rate:
employerRef (Compound Key)
paymentDate (Compound Key)
rate

Firstly, I'm not sure whether this was the best way to set it up (with the
compound key in Rate). And I can't decide how the relationships between the
Rate table and the others should be setup.

When a user inputs a new payment, I want them to select the employerRef
first and for the form to then display the employerName (for reference). This
part I managed to do by binding the form to an autolookup query with the
Employer and Payment tables.

When the user then selects the paymentYear, I want the form to display the
corresponding rate for that particular employer in that particular year. Any
guidance for how I should do this? I've played around with queries but can't
get it to work.

 

[Ken Sheridan]

A couple of questions first:

1. Is the payment year a calendar year (i.e. 1 January – 31 December) or
an accounting year (e.g. 1 April – 31 March)?

2. Is the payment year per payment determined by the payment date, i.e.
does the payment date always fall within the payment year, or can it be
before or after it?

Looking at the Rate table first, if the payment year is a calendar year then
this should include a paymentYear column of integer number data type; if its
an accounting year which differs from the calendar year then it should
include a paymentYearStartDate column of date/time data type.

If the payment year is determined by the payment date then the payment table
does not need a paymentYear or paymentYearStartDate column, if it can fall
outside the payment year then it does.

For data entry purposes, if the payment year per payment is determined by
the payment date the user would select the employer, then enter a payment
date. To get the rate I'd suggest creating a function. If the payment year
is a non-calendar accounting year the function (NB all code is untested)
would be:

Function GetRate(varEmployerRef , varPaymentDate)

Dim strCriteria As String
Dim dtmYearStart as Date

If Not IsNull(varEmployerRef) And Not IsNull(varPaymentDate) Then
strCriteria = "employerRef = " & varEmployerRef & _
" And paymentYearStartDate <= #" & _
Format(varPaymentdate, "yyyy-mm-dd") & "#"

dtmYearStart = DMax("paymentYearStartDate","Rate",strCriteria)

strCriteria = "paymentYearStartDate = #" & _
Format(dtmYearStart, "yyyy-mm-dd") & "#"

GetRate = DLookup("rate", "Rate", strCriteria)
End If

End Function

If the payment year is a calendar year and again the payment year is
determined by the payment date then the function would be:

Function GetRate(varEmployerRef , varPaymentDate)

Dim strCriteria As String

If Not IsNull(varEmployerRef) And Not IsNull(varPaymentDate) Then
strCriteria = "paymentYear = " & Year(varPaymentDate)

GetRate = DLookup("rate", "Rate", strCriteria)
End If

End Function

In either case the ControlSource for an unbound control to show the rate
would be:

=GetRate([EmployerRef], [PaymentDate])

If the payment year is not determined by the payment date then the function
would be:

Function GetRate(varEmployerRef , varPaymentYear)

Dim strCriteria As String

If Not IsNull(varEmployerRef) And Not IsNull(varPaymentYear) Then
strCriteria = "paymentYear = " & varPaymentYear

GetRate = DLookup("rate", "Rate", strCriteria)
End If

End Function

And in this case the ControlSource for an unbound control to show the rate
would be:

=GetRate([EmployerRef], [PaymentYear])

Finally, you've used singular nouns as table names. I favour the convention
promoted by Joe Celko amongst others, of using plural or collective nouns as
table names as this better reflects the fact that tables are sets. For
column names I favour singular nouns to reflect the fact that each column
represents an attribute type. I'm not proscriptive about this however; the
choice is yours.

Ken Sheridan
Stafford, England