Apply Month End date based on transaction date

  • Thread starter Thread starter Robbro
  • Start date Start date
R

Robbro

Our month end dates really dont have any form or formula I could come up
with, so I made a table of the next years month end and month beg dates. Our
material prices change monthly. I need some way to assign transactions to
months for the purposes of matching our monthly material prices to them. For
example any sales transactions between 1/25/2010 and 2/20/2010 would take our
Feb mat prices. I tried setting up a query and using DSum to sum any month
end date from my ME table for which the transaction date was between the
Month Beg and Month End date but apparently once its set to use the ME table
I cant get it to see my transaction date in my sales table.
 
Robbro -

If your new table has unique identifiers that are also in your pricing
table, then your approach should work. For example, if your new
CompanyMonths table looks like this (I am making up names - use your table,
field, control names):

CoMonth StartDate EndDate
2010Jan 12/26/2009 1/24/2010
2010Feb 1/25/2010 2/20/2010

And your MaterialPricing table looks like this:

MaterialID CoMonth Price
123 2010Jan 25.65
123 2010Feb 25.75
124 2010Jan 2.57
124 2010Feb 2.68

Then to price out any transaction, you would use the transaction date and
MaterialID to determine the price. If you are using a query, something like
this:

Price = SELECT Price from MaterialPricing INNER JOIN CompanyMonths
ON MaterialPricing.CoMonth = CompanyMonths.CoMonth
WHERE MaterialID = <MaterialID>
AND <TransactionDate> Between CompanyMonths.StartDate and CompanyMonths.EndDat

If you are calculating it on a form, something like this:
Me.CoMonth = DLookup("CoMonth","CompanyMonths","[StartDate] <= #" &
<TransactionDate> & "# AND [EndDate] >= #" & <TransactionDate> & "#")
Me.Price = DLookup("Price","MaterialPricing","[MaterialID] = " &
<MaterialID> & " AND [CoMonth] = '" & Me.CoMonth & "'")
 
Ah, getting your CoMonth field is what Im trying to do:

Curently I have sales transactions with just transaction dates such as
transaction date
1/19/2010
1/20/2010
1/21/2010
and so on

I need a way to assign the company months to these dates so I can get
transaction date month end
1/19/2010 1/20/2010
1/20/2010 1/20/2010
1/21/2010 2/23/2010

just as examples

I fumbled around and finally got something to kinda work:
MonEnd: CDate(Sum(IIf(DateDiff("d",[Invoice
Date],[monthends]![monthbegin])<=0 And DateDiff("d",[Invoice
Date],[monthends]![monthend])>=0,[monthends]![monthend],0)))
as an expression in a query made specifically for this.... however I fear it
may be fragile and ineffecient for something that is going to be applied to
thousands and thousands of transactions.

Daryl S said:
Robbro -

If your new table has unique identifiers that are also in your pricing
table, then your approach should work. For example, if your new
CompanyMonths table looks like this (I am making up names - use your table,
field, control names):

CoMonth StartDate EndDate
2010Jan 12/26/2009 1/24/2010
2010Feb 1/25/2010 2/20/2010

And your MaterialPricing table looks like this:

MaterialID CoMonth Price
123 2010Jan 25.65
123 2010Feb 25.75
124 2010Jan 2.57
124 2010Feb 2.68

Then to price out any transaction, you would use the transaction date and
MaterialID to determine the price. If you are using a query, something like
this:

Price = SELECT Price from MaterialPricing INNER JOIN CompanyMonths
ON MaterialPricing.CoMonth = CompanyMonths.CoMonth
WHERE MaterialID = <MaterialID>
AND <TransactionDate> Between CompanyMonths.StartDate and CompanyMonths.EndDat

If you are calculating it on a form, something like this:
Me.CoMonth = DLookup("CoMonth","CompanyMonths","[StartDate] <= #" &
<TransactionDate> & "# AND [EndDate] >= #" & <TransactionDate> & "#")
Me.Price = DLookup("Price","MaterialPricing","[MaterialID] = " &
<MaterialID> & " AND [CoMonth] = '" & Me.CoMonth & "'")

--
Daryl S


Robbro said:
Our month end dates really dont have any form or formula I could come up
with, so I made a table of the next years month end and month beg dates. Our
material prices change monthly. I need some way to assign transactions to
months for the purposes of matching our monthly material prices to them. For
example any sales transactions between 1/25/2010 and 2/20/2010 would take our
Feb mat prices. I tried setting up a query and using DSum to sum any month
end date from my ME table for which the transaction date was between the
Month Beg and Month End date but apparently once its set to use the ME table
I cant get it to see my transaction date in my sales table.
Click to expand...
Click to expand...
 
Robbro -

How do you distiguish the different prices for the months? That is, for any
product, what field tells you the pricing for January 2010 versus February
2010? If you have a field in your 'pricing' table that does this (my
CoMonth), then that is the field you want to add to the CompanyMonths table
along with the MonthBegin and MonthEnd dates. If that field is a date field
(your MonEnd looks like it is using the monthend date), then it might be
easier:

MonEnd: (Select [monthend] from [monthends] WHERE [Invoice Date] Between
[monthends]![monthbegin] AND [monthends]![monthend])

--
Daryl S


Robbro said:
Ah, getting your CoMonth field is what Im trying to do:

Curently I have sales transactions with just transaction dates such as
transaction date
1/19/2010
1/20/2010
1/21/2010
and so on

I need a way to assign the company months to these dates so I can get
transaction date month end
1/19/2010 1/20/2010
1/20/2010 1/20/2010
1/21/2010 2/23/2010

just as examples

I fumbled around and finally got something to kinda work:
MonEnd: CDate(Sum(IIf(DateDiff("d",[Invoice
Date],[monthends]![monthbegin])<=0 And DateDiff("d",[Invoice
Date],[monthends]![monthend])>=0,[monthends]![monthend],0)))
as an expression in a query made specifically for this.... however I fear it
may be fragile and ineffecient for something that is going to be applied to
thousands and thousands of transactions.

Daryl S said:
Robbro -

If your new table has unique identifiers that are also in your pricing
table, then your approach should work. For example, if your new
CompanyMonths table looks like this (I am making up names - use your table,
field, control names):

CoMonth StartDate EndDate
2010Jan 12/26/2009 1/24/2010
2010Feb 1/25/2010 2/20/2010

And your MaterialPricing table looks like this:

MaterialID CoMonth Price
123 2010Jan 25.65
123 2010Feb 25.75
124 2010Jan 2.57
124 2010Feb 2.68

Then to price out any transaction, you would use the transaction date and
MaterialID to determine the price. If you are using a query, something like
this:

Price = SELECT Price from MaterialPricing INNER JOIN CompanyMonths
ON MaterialPricing.CoMonth = CompanyMonths.CoMonth
WHERE MaterialID = <MaterialID>
AND <TransactionDate> Between CompanyMonths.StartDate and CompanyMonths.EndDat

If you are calculating it on a form, something like this:
Me.CoMonth = DLookup("CoMonth","CompanyMonths","[StartDate] <= #" &
<TransactionDate> & "# AND [EndDate] >= #" & <TransactionDate> & "#")
Me.Price = DLookup("Price","MaterialPricing","[MaterialID] = " &
<MaterialID> & " AND [CoMonth] = '" & Me.CoMonth & "'")

--
Daryl S


Robbro said:
Our month end dates really dont have any form or formula I could come up
with, so I made a table of the next years month end and month beg dates. Our
material prices change monthly. I need some way to assign transactions to
months for the purposes of matching our monthly material prices to them. For
example any sales transactions between 1/25/2010 and 2/20/2010 would take our
Feb mat prices. I tried setting up a query and using DSum to sum any month
end date from my ME table for which the transaction date was between the
Month Beg and Month End date but apparently once its set to use the ME table
I cant get it to see my transaction date in my sales table.
Click to expand...
Click to expand...
Click to expand...
 
Getting into another can of worms here, but pricing is on a contract by
contract basis. A contract can be any lenght of time a day 3 days 35 days 1
year, anything. Any customer can have multiple contracts. Only certain
contracts get "locked" in. For those we use the contracted materials prices,
for others we use monthly consumed prices, those are the ones I'm trying to
get the month end dates for. This will also be used eventually to assign
certain financial info from the month to certain transactions too.
At any rate your solution works naturally, for some reason I never thought
of that, didnt really know you could practically make a select query as an
expression, thought it had to be a function of some kind. I'm still learnin
a lot here.
Thanks for the lesson!

Daryl S said:
Robbro -

How do you distiguish the different prices for the months? That is, for any
product, what field tells you the pricing for January 2010 versus February
2010? If you have a field in your 'pricing' table that does this (my
CoMonth), then that is the field you want to add to the CompanyMonths table
along with the MonthBegin and MonthEnd dates. If that field is a date field
(your MonEnd looks like it is using the monthend date), then it might be
easier:

MonEnd: (Select [monthend] from [monthends] WHERE [Invoice Date] Between
[monthends]![monthbegin] AND [monthends]![monthend])

--
Daryl S


Robbro said:
Ah, getting your CoMonth field is what Im trying to do:

Curently I have sales transactions with just transaction dates such as
transaction date
1/19/2010
1/20/2010
1/21/2010
and so on

I need a way to assign the company months to these dates so I can get
transaction date month end
1/19/2010 1/20/2010
1/20/2010 1/20/2010
1/21/2010 2/23/2010

just as examples

I fumbled around and finally got something to kinda work:
MonEnd: CDate(Sum(IIf(DateDiff("d",[Invoice
Date],[monthends]![monthbegin])<=0 And DateDiff("d",[Invoice
Date],[monthends]![monthend])>=0,[monthends]![monthend],0)))
as an expression in a query made specifically for this.... however I fear it
may be fragile and ineffecient for something that is going to be applied to
thousands and thousands of transactions.

Daryl S said:
Robbro -

If your new table has unique identifiers that are also in your pricing
table, then your approach should work. For example, if your new
CompanyMonths table looks like this (I am making up names - use your table,
field, control names):

CoMonth StartDate EndDate
2010Jan 12/26/2009 1/24/2010
2010Feb 1/25/2010 2/20/2010

And your MaterialPricing table looks like this:

MaterialID CoMonth Price
123 2010Jan 25.65
123 2010Feb 25.75
124 2010Jan 2.57
124 2010Feb 2.68

Then to price out any transaction, you would use the transaction date and
MaterialID to determine the price. If you are using a query, something like
this:

Price = SELECT Price from MaterialPricing INNER JOIN CompanyMonths
ON MaterialPricing.CoMonth = CompanyMonths.CoMonth
WHERE MaterialID = <MaterialID>
AND <TransactionDate> Between CompanyMonths.StartDate and CompanyMonths.EndDat

If you are calculating it on a form, something like this:
Me.CoMonth = DLookup("CoMonth","CompanyMonths","[StartDate] <= #" &
<TransactionDate> & "# AND [EndDate] >= #" & <TransactionDate> & "#")
Me.Price = DLookup("Price","MaterialPricing","[MaterialID] = " &
<MaterialID> & " AND [CoMonth] = '" & Me.CoMonth & "'")

--
Daryl S


:

Our month end dates really dont have any form or formula I could come up
with, so I made a table of the next years month end and month beg dates. Our
material prices change monthly. I need some way to assign transactions to
months for the purposes of matching our monthly material prices to them. For
example any sales transactions between 1/25/2010 and 2/20/2010 would take our
Feb mat prices. I tried setting up a query and using DSum to sum any month
end date from my ME table for which the transaction date was between the
Month Beg and Month End date but apparently once its set to use the ME table
I cant get it to see my transaction date in my sales table.
Click to expand...
Click to expand...
Click to expand...
 
Great! Glad it is working...
--
Daryl S


Robbro said:
Getting into another can of worms here, but pricing is on a contract by
contract basis. A contract can be any lenght of time a day 3 days 35 days 1
year, anything. Any customer can have multiple contracts. Only certain
contracts get "locked" in. For those we use the contracted materials prices,
for others we use monthly consumed prices, those are the ones I'm trying to
get the month end dates for. This will also be used eventually to assign
certain financial info from the month to certain transactions too.
At any rate your solution works naturally, for some reason I never thought
of that, didnt really know you could practically make a select query as an
expression, thought it had to be a function of some kind. I'm still learnin
a lot here.
Thanks for the lesson!

Daryl S said:
Robbro -

How do you distiguish the different prices for the months? That is, for any
product, what field tells you the pricing for January 2010 versus February
2010? If you have a field in your 'pricing' table that does this (my
CoMonth), then that is the field you want to add to the CompanyMonths table
along with the MonthBegin and MonthEnd dates. If that field is a date field
(your MonEnd looks like it is using the monthend date), then it might be
easier:

MonEnd: (Select [monthend] from [monthends] WHERE [Invoice Date] Between
[monthends]![monthbegin] AND [monthends]![monthend])

--
Daryl S


Robbro said:
Ah, getting your CoMonth field is what Im trying to do:

Curently I have sales transactions with just transaction dates such as
transaction date
1/19/2010
1/20/2010
1/21/2010
and so on

I need a way to assign the company months to these dates so I can get
transaction date month end
1/19/2010 1/20/2010
1/20/2010 1/20/2010
1/21/2010 2/23/2010

just as examples

I fumbled around and finally got something to kinda work:
MonEnd: CDate(Sum(IIf(DateDiff("d",[Invoice
Date],[monthends]![monthbegin])<=0 And DateDiff("d",[Invoice
Date],[monthends]![monthend])>=0,[monthends]![monthend],0)))
as an expression in a query made specifically for this.... however I fear it
may be fragile and ineffecient for something that is going to be applied to
thousands and thousands of transactions.

:

Robbro -

If your new table has unique identifiers that are also in your pricing
table, then your approach should work. For example, if your new
CompanyMonths table looks like this (I am making up names - use your table,
field, control names):

CoMonth StartDate EndDate
2010Jan 12/26/2009 1/24/2010
2010Feb 1/25/2010 2/20/2010

And your MaterialPricing table looks like this:

MaterialID CoMonth Price
123 2010Jan 25.65
123 2010Feb 25.75
124 2010Jan 2.57
124 2010Feb 2.68

Then to price out any transaction, you would use the transaction date and
MaterialID to determine the price. If you are using a query, something like
this:

Price = SELECT Price from MaterialPricing INNER JOIN CompanyMonths
ON MaterialPricing.CoMonth = CompanyMonths.CoMonth
WHERE MaterialID = <MaterialID>
AND <TransactionDate> Between CompanyMonths.StartDate and CompanyMonths.EndDat

If you are calculating it on a form, something like this:
Me.CoMonth = DLookup("CoMonth","CompanyMonths","[StartDate] <= #" &
<TransactionDate> & "# AND [EndDate] >= #" & <TransactionDate> & "#")
Me.Price = DLookup("Price","MaterialPricing","[MaterialID] = " &
<MaterialID> & " AND [CoMonth] = '" & Me.CoMonth & "'")

--
Daryl S


:

Our month end dates really dont have any form or formula I could come up
with, so I made a table of the next years month end and month beg dates. Our
material prices change monthly. I need some way to assign transactions to
months for the purposes of matching our monthly material prices to them. For
example any sales transactions between 1/25/2010 and 2/20/2010 would take our
Feb mat prices. I tried setting up a query and using DSum to sum any month
end date from my ME table for which the transaction date was between the
Month Beg and Month End date but apparently once its set to use the ME table
I cant get it to see my transaction date in my sales table.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top