If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Autolookup between 3 tables
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. |
#2
|
|||
|
|||
Autolookup between 3 tables
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 "Tray" wrote: 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. |
#3
|
|||
|
|||
Autolookup between 3 tables
I guess I could elaborate mo
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 "vbasean" wrote: 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 "Tray" wrote: 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. |
#4
|
|||
|
|||
Autolookup between 3 tables
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 "vbasean" wrote: I guess I could elaborate mo 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 "vbasean" wrote: 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 "Tray" wrote: 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. |
#5
|
|||
|
|||
Autolookup between 3 tables
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 "Tray" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|