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 |
#11
|
|||
|
|||
Table History for Newbie
ahhh, thank you! do you happen to know if there is a link to a sample db or a book w/CD that has this kind of scenario already set-up? i'd like to see how someone else would setup the query and the forms.
going to think out loud here about possible setup - (if you have any suggestions, please feel free!) on the drive in to work, i was trying to imagine how i might have things set up. i'm going to stick with this mileage scenario: tblMileageTypes MileageTypeId (PK) TypeDesc (car, boat, plane, semi-truck, etc.) tblMileageRates RateID (PK) MileageTypeID (FK) Rate EffDate the form would have text boxes for TypeDesc, EffDate, and Rate, one list box that would show a list of type descriptions and another that would list the 'history' of the type (selected in the first list) i could have cmd buttons for adding a new type, or adding a new rate change, or modifying a rate the query for the expenses could then compare date of activity to the effective date and select the correct rate, right? still, if you know of a sample, i'd appreciate it much! tia rpw ----- Lynn Trapp wrote: ----- Well, I think I understand a bit better now. I think what you need to do is have a rate table that has the different commission rates and the rate effective dates. Then use that as a lookup table to get the appropriate commission rate to be stored in your sales table. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "rpw" wrote in message ... hi Lynn, thanks for the response. more below... ----- Lynn Trapp wrote: ----- I suspect the easiest way to handle this is to simply store the commission rate in your commissions table. If the commission changes it won't affect the previously created records and, thus, the salesmen will get paid on the correct commission rate for all sales. if I understand correctly, this would be true if I stored the (duplicate) commission rate in the sales table when the sale was input. mmm, not exactly what i was looking for... let me try again. (btw, sorry, I guess it should have been a separate post rather than an addendum to MadCrazyNewbie's post) I want the db user to have access to viewing the current commission rate and it's 'effective' date. and i want the user to be able to input a new rate with a new effective date with those new rates showing as 'current'. but i also want to store all of the past rates and effective dates. I'm undecided if i want a listbox to show the history of changes or not why would i want to do things this way? let's change scenarios back to the mileage expense in the case of expense reports / mileage - sometimes the report /expense is not turned in until after the change has occurred. so if the expense/sale/quote happened in april (but was not entered into the db until june) it should get the april rate and if it happened on or after the effective date in may, then it gets the newer rate the more traditional 'store the rate at the time of the transaction' approach doesn't accomodate the above scenario i was asking for a link to a sample because when i think about how to go about this my brain goes blank - usually it helps alot to look at a sample i aprreciate your involvement :-) (jeez - am i making this more clear or more confusing???) rpw |
#12
|
|||
|
|||
Table History for Newbie
I don't know of any samples that cover that specific kind of problem, but
you could easily use the 2 tables you listed below as a way to lookup the RateID and store that in the main Mileage table. The query for a combobox on your form might look something like this: Select A.RateID, B.TypeDesc, A.Rate, A.EffDate From tblMileageRates AS A Join ON a.MileageTypeID = B.MileageTypeID; WARNING! That is untested "air" code. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "rpw" wrote in message ... ahhh, thank you! do you happen to know if there is a link to a sample db or a book w/CD that has this kind of scenario already set-up? i'd like to see how someone else would setup the query and the forms. going to think out loud here about possible setup - (if you have any suggestions, please feel free!) on the drive in to work, i was trying to imagine how i might have things set up. i'm going to stick with this mileage scenario: tblMileageTypes MileageTypeId (PK) TypeDesc (car, boat, plane, semi-truck, etc.) tblMileageRates RateID (PK) MileageTypeID (FK) Rate EffDate the form would have text boxes for TypeDesc, EffDate, and Rate, one list box that would show a list of type descriptions and another that would list the 'history' of the type (selected in the first list) i could have cmd buttons for adding a new type, or adding a new rate change, or modifying a rate the query for the expenses could then compare date of activity to the effective date and select the correct rate, right? still, if you know of a sample, i'd appreciate it much! tia rpw ----- Lynn Trapp wrote: ----- Well, I think I understand a bit better now. I think what you need to do is have a rate table that has the different commission rates and the rate effective dates. Then use that as a lookup table to get the appropriate commission rate to be stored in your sales table. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "rpw" wrote in message ... hi Lynn, thanks for the response. more below... ----- Lynn Trapp wrote: ----- I suspect the easiest way to handle this is to simply store the commission rate in your commissions table. If the commission changes it won't affect the previously created records and, thus, the salesmen will get paid on the correct commission rate for all sales. if I understand correctly, this would be true if I stored the (duplicate) commission rate in the sales table when the sale was input. mmm, not exactly what i was looking for... let me try again. (btw, sorry, I guess it should have been a separate post rather than an addendum to MadCrazyNewbie's post) I want the db user to have access to viewing the current commission rate and it's 'effective' date. and i want the user to be able to input a new rate with a new effective date with those new rates showing as 'current'. but i also want to store all of the past rates and effective dates. I'm undecided if i want a listbox to show the history of changes or not why would i want to do things this way? let's change scenarios back to the mileage expense in the case of expense reports / mileage - sometimes the report /expense is not turned in until after the change has occurred. so if the expense/sale/quote happened in april (but was not entered into the db until june) it should get the april rate and if it happened on or after the effective date in may, then it gets the newer rate the more traditional 'store the rate at the time of the transaction' approach doesn't accomodate the above scenario i was asking for a link to a sample because when i think about how to go about this my brain goes blank - usually it helps alot to look at a sample i aprreciate your involvement :-) (jeez - am i making this more clear or more confusing???) rpw |
#13
|
|||
|
|||
Table History for Newbie
hey Lynn, thanks for keeping in touch! thank you for the suggestion, too!
(plz don't take this like i'm looking a gift horse in the mouth) i'm not sure i understand why to use a combobox - did you suggest that because it would minimize form real estate? (as opposed to two listboxes) if i understand the query correctly, the combo would list ALL rates. if there were 5 types, and 10 changes each, the combo would have 50 choices for the user to select from if i may ask, how are you thinking this would be used? rpw ----- Lynn Trapp wrote: ----- I don't know of any samples that cover that specific kind of problem, but you could easily use the 2 tables you listed below as a way to lookup the RateID and store that in the main Mileage table. The query for a combobox on your form might look something like this: Select A.RateID, B.TypeDesc, A.Rate, A.EffDate From tblMileageRates AS A Join ON a.MileageTypeID = B.MileageTypeID; WARNING! That is untested "air" code. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "rpw" wrote in message ... ahhh, thank you! do you happen to know if there is a link to a sample db or a book w/CD that has this kind of scenario already set-up? i'd like to see how someone else would setup the query and the forms. going to think out loud here about possible setup - (if you have any suggestions, please feel free!) on the drive in to work, i was trying to imagine how i might have things set up. i'm going to stick with this mileage scenario: tblMileageTypes MileageTypeId (PK) TypeDesc (car, boat, plane, semi-truck, etc.) tblMileageRates RateID (PK) MileageTypeID (FK) Rate EffDate the form would have text boxes for TypeDesc, EffDate, and Rate, one list box that would show a list of type descriptions and another that would list the 'history' of the type (selected in the first list) i could have cmd buttons for adding a new type, or adding a new rate change, or modifying a rate the query for the expenses could then compare date of activity to the effective date and select the correct rate, right? still, if you know of a sample, i'd appreciate it much! tia rpw ----- Lynn Trapp wrote: ----- Well, I think I understand a bit better now. I think what you need to do is have a rate table that has the different commission rates and the rate effective dates. Then use that as a lookup table to get the appropriate commission rate to be stored in your sales table. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "rpw" wrote in message ... hi Lynn, thanks for the response. more below... ----- Lynn Trapp wrote: ----- I suspect the easiest way to handle this is to simply store the commission rate in your commissions table. If the commission changes it won't affect the previously created records and, thus, the salesmen will get paid on the correct commission rate for all sales. if I understand correctly, this would be true if I stored the (duplicate) commission rate in the sales table when the sale was input. mmm, not exactly what i was looking for... let me try again. (btw, sorry, I guess it should have been a separate post rather than an addendum to MadCrazyNewbie's post) I want the db user to have access to viewing the current commission rate and it's 'effective' date. and i want the user to be able to input a new rate with a new effective date with those new rates showing as 'current'. but i also want to store all of the past rates and effective dates. I'm undecided if i want a listbox to show the history of changes or not why would i want to do things this way? let's change scenarios back to the mileage expense in the case of expense reports / mileage - sometimes the report /expense is not turned in until after the change has occurred. so if the expense/sale/quote happened in april (but was not entered into the db until june) it should get the april rate and if it happened on or after the effective date in may, then it gets the newer rate the more traditional 'store the rate at the time of the transaction' approach doesn't accomodate the above scenario i was asking for a link to a sample because when i think about how to go about this my brain goes blank - usually it helps alot to look at a sample i aprreciate your involvement :-) (jeez - am i making this more clear or more confusing???) rpw |
#14
|
|||
|
|||
Table History for Newbie
Well,
Mainly because I have a big preference for a combobox over a listbox. I try to do everything with a combobox rather than a listbox. What I have in mind is that you put a field in your main Sales table that will store the RateID for each sales record. You would have to add a RateID field to the table and then, on your data entry form, use the combobox to select the appropriate RateID. Once you have that then you can always calculate the sales commission based on the total sales times the rate identified by the RateID. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "rpw" wrote in message ... hey Lynn, thanks for keeping in touch! thank you for the suggestion, too! (plz don't take this like i'm looking a gift horse in the mouth) i'm not sure i understand why to use a combobox - did you suggest that because it would minimize form real estate? (as opposed to two listboxes) if i understand the query correctly, the combo would list ALL rates. if there were 5 types, and 10 changes each, the combo would have 50 choices for the user to select from if i may ask, how are you thinking this would be used? rpw ----- Lynn Trapp wrote: ----- I don't know of any samples that cover that specific kind of problem, but you could easily use the 2 tables you listed below as a way to lookup the RateID and store that in the main Mileage table. The query for a combobox on your form might look something like this: Select A.RateID, B.TypeDesc, A.Rate, A.EffDate From tblMileageRates AS A Join ON a.MileageTypeID = B.MileageTypeID; WARNING! That is untested "air" code. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "rpw" wrote in message ... ahhh, thank you! do you happen to know if there is a link to a sample db or a book w/CD that has this kind of scenario already set-up? i'd like to see how someone else would setup the query and the forms. going to think out loud here about possible setup - (if you have any suggestions, please feel free!) on the drive in to work, i was trying to imagine how i might have things set up. i'm going to stick with this mileage scenario: tblMileageTypes MileageTypeId (PK) TypeDesc (car, boat, plane, semi-truck, etc.) tblMileageRates RateID (PK) MileageTypeID (FK) Rate EffDate the form would have text boxes for TypeDesc, EffDate, and Rate, one list box that would show a list of type descriptions and another that would list the 'history' of the type (selected in the first list) i could have cmd buttons for adding a new type, or adding a new rate change, or modifying a rate the query for the expenses could then compare date of activity to the effective date and select the correct rate, right? still, if you know of a sample, i'd appreciate it much! tia rpw ----- Lynn Trapp wrote: ----- Well, I think I understand a bit better now. I think what you need to do is have a rate table that has the different commission rates and the rate effective dates. Then use that as a lookup table to get the appropriate commission rate to be stored in your sales table. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "rpw" wrote in message ... hi Lynn, thanks for the response. more below... ----- Lynn Trapp wrote: ----- I suspect the easiest way to handle this is to simply store the commission rate in your commissions table. If the commission changes it won't affect the previously created records and, thus, the salesmen will get paid on the correct commission rate for all sales. if I understand correctly, this would be true if I stored the (duplicate) commission rate in the sales table when the sale was input. mmm, not exactly what i was looking for... let me try again. (btw, sorry, I guess it should have been a separate post rather than an addendum to MadCrazyNewbie's post) I want the db user to have access to viewing the current commission rate and it's 'effective' date. and i want the user to be able to input a new rate with a new effective date with those new rates showing as 'current'. but i also want to store all of the past rates and effective dates. I'm undecided if i want a listbox to show the history of changes or not why would i want to do things this way? let's change scenarios back to the mileage expense in the case of expense reports / mileage - sometimes the report /expense is not turned in until after the change has occurred. so if the expense/sale/quote happened in april (but was not entered into the db until june) it should get the april rate and if it happened on or after the effective date in may, then it gets the newer rate the more traditional 'store the rate at the time of the transaction' approach doesn't accomodate the above scenario i was asking for a link to a sample because when i think about how to go about this my brain goes blank - usually it helps alot to look at a sample i aprreciate your involvement :-) (jeez - am i making this more clear or more confusing???) rpw |
#15
|
|||
|
|||
Table History for Newbie
thank you, i understand now
i didn't get it because i was thinking of something different - a rates form where the user could input rate changes listbox1 queried to hold only unique TypeDesc and listbox2 that would hold the list of changes for the selected item in listbox1 with final selection bookmarking the form's RateID record. i've seen alot of requests for comboboxes to behave this way, but i guess i must have a preference for listboxes also, i'm imagining this Rates form to have plenty of available real estate for placing the listboxes now that you've brought up the main activity table (sales, or mileage expenses), my idea for the activity input form would have the user needing only to use a combo to select a new TypeDesc (different than the default) and input a date for the activity. i'm thinking that with those two items, a query could lookup the appropriate rate if it was needed for calculations on a report probably more difficult to implement, but less dependant upon the user to make the right selection thanks for all of your input, i think that you've help me to start clearing th fog on this rpw ----- Lynn Trapp wrote: ----- Well, Mainly because I have a big preference for a combobox over a listbox. I try to do everything with a combobox rather than a listbox. What I have in mind is that you put a field in your main Sales table that will store the RateID for each sales record. You would have to add a RateID field to the table and then, on your data entry form, use the combobox to select the appropriate RateID. Once you have that then you can always calculate the sales commission based on the total sales times the rate identified by the RateID. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "rpw" wrote in message ... hey Lynn, thanks for keeping in touch! thank you for the suggestion, too! (plz don't take this like i'm looking a gift horse in the mouth) i'm not sure i understand why to use a combobox - did you suggest that because it would minimize form real estate? (as opposed to two listboxes) if i understand the query correctly, the combo would list ALL rates. if there were 5 types, and 10 changes each, the combo would have 50 choices for the user to select from if i may ask, how are you thinking this would be used? rpw ----- Lynn Trapp wrote: ----- I don't know of any samples that cover that specific kind of problem, but you could easily use the 2 tables you listed below as a way to lookup the RateID and store that in the main Mileage table. The query for a combobox on your form might look something like this: Select A.RateID, B.TypeDesc, A.Rate, A.EffDate From tblMileageRates AS A Join ON a.MileageTypeID = B.MileageTypeID; WARNING! That is untested "air" code. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "rpw" wrote in message ... ahhh, thank you! do you happen to know if there is a link to a sample db or a book w/CD that has this kind of scenario already set-up? i'd like to see how someone else would setup the query and the forms. going to think out loud here about possible setup - (if you have any suggestions, please feel free!) on the drive in to work, i was trying to imagine how i might have things set up. i'm going to stick with this mileage scenario: tblMileageTypes MileageTypeId (PK) TypeDesc (car, boat, plane, semi-truck, etc.) tblMileageRates RateID (PK) MileageTypeID (FK) Rate EffDate the form would have text boxes for TypeDesc, EffDate, and Rate, one list box that would show a list of type descriptions and another that would list the 'history' of the type (selected in the first list) i could have cmd buttons for adding a new type, or adding a new rate change, or modifying a rate the query for the expenses could then compare date of activity to the effective date and select the correct rate, right? still, if you know of a sample, i'd appreciate it much! tia rpw ----- Lynn Trapp wrote: ----- Well, I think I understand a bit better now. I think what you need to do is have a rate table that has the different commission rates and the rate effective dates. Then use that as a lookup table to get the appropriate commission rate to be stored in your sales table. -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "rpw" wrote in message ... hi Lynn, thanks for the response. more below... ----- Lynn Trapp wrote: ----- I suspect the easiest way to handle this is to simply store the commission rate in your commissions table. If the commission changes it won't affect the previously created records and, thus, the salesmen will get paid on the correct commission rate for all sales. if I understand correctly, this would be true if I stored the (duplicate) commission rate in the sales table when the sale was input. mmm, not exactly what i was looking for... let me try again. (btw, sorry, I guess it should have been a separate post rather than an addendum to MadCrazyNewbie's post) I want the db user to have access to viewing the current commission rate and it's 'effective' date. and i want the user to be able to input a new rate with a new effective date with those new rates showing as 'current'. but i also want to store all of the past rates and effective dates. I'm undecided if i want a listbox to show the history of changes or not why would i want to do things this way? let's change scenarios back to the mileage expense in the case of expense reports / mileage - sometimes the report /expense is not turned in until after the change has occurred. so if the expense/sale/quote happened in april (but was not entered into the db until june) it should get the april rate and if it happened on or after the effective date in may, then it gets the newer rate the more traditional 'store the rate at the time of the transaction' approach doesn't accomodate the above scenario i was asking for a link to a sample because when i think about how to go about this my brain goes blank - usually it helps alot to look at a sample i aprreciate your involvement :-) (jeez - am i making this more clear or more confusing???) rpw |
#16
|
|||
|
|||
Table History for Newbie
hi again,
i've put together a small demo db to test out what i've been learning from this thread and i've got tables and forms working the way i'd like but i'm having a bit of trouble with the query for the report. the rate's effective date changes and is recorded in a table (sample simplified) RateID 1 Rate .25 EffDate 4/1/04 RateID 2 Rate .30 EffDate 5/1/04 an expense is entered into a form with a MileageDate MileageExpenseID 1 MileageDate 4/2/04 MileageExpenseID 2 MileageDate 5/6/04 the actual SQL for the report is SELECT tblMileageExpense.MileageExpenseID, tblMileageExpense.MileageDate, tblMileageExpense.StartMiles, tblMileageExpense.EndMiles, tblMileageExpense.MileageTypeID, tblMileageType.TypeDesc, tblMileageRate.Rate, tblMileageRate.EffDate, tblMileageRate.EffDate FROM (tblMileageType INNER JOIN tblMileageExpense ON tblMileageType.MileageTypeID=tblMileageExpense.Mil eageTypeID) INNER JOIN tblMileageRate ON tblMileageType.MileageTypeID=tblMileageRate.Mileag eTypeID WHERE (((tblMileageRate.EffDate)=tblMileageExpense!Mile ageDate)); I get three lines printed on the report and I want to see only two - (simplified from actual report) MileageDate 4/2/04 Rate .25 EffDate 4/1/04 (MileageExpenseID1) MileageDate 5/6/04 Rate .25 EffDate 4/1/04 (MileageExpenseID2) this listing is the one i don't need??? MileageDate 5/6/04 Rate .30 EffDate 5/1/04 (MileageExpenseID2) I can't figure out how to have the query select the MileageExpenseID only one time any suggestions?? tia rpw PS - sorry, i gotta go now so i won't be able to check for responses till Monday - no internet at home if you wanna help and you've got questions, pls bear w/me until then --- i really appreciate the help everyone here gives |
#17
|
|||
|
|||
Table History for Newbie
just in case anyone has similar problem: i reposted under the subject "not DMAX or MAX" and Brian Camire was able to provide a solution. (maybe because I provided more info than i did in this post.) thanks to all who help us lost access souls
rpw |
|
Thread Tools | |
Display Modes | |
|
|