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

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

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

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

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

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

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