Revenue Tracking DB - Need to figure out the table structure.
in [Tables]
|
From: Sebastian on 27 Jun 2008 13:33 Here's the scenario... I manage the billing for a single client. This client is billed in a number of countries worldwide (30 Countries in total). Billing is done in multiple currencies (depending on the Country where we bill). This is the information that I have: Invoicing Data: Country Invoice Number Invoice Date Invoice Amount (In Local Currency) Invoice Description (Type of Activity being billed -- ie: Maintenance, Installation, Storage, ect) Geographical Data Country Region (Countries are divided between Americas and Europe-Middle East-Africa) Currency (Currency used at the country) Exchange Rate Data Each month I get a report from corporate that provides me with the "Official" exchange rates for that month, for all currencies that we bill in. What I need: A reporting tool that will allow me to generate Revenue reports based on Country - Region - Activity - Period This reports go to managment in US, so they need to be in USD as opposed to Local Currency. My challenges: 1. Figuring out the "Period": The company that I work for has established "cut-off" dates for billing each month (If any inovice is raised after Friday of the month... that invoice is recognized in the following "Period"). For this particular challenge, I have a table with two colums Date - Period. The "Date" column is just a list of each single calendar date starting 1/1/2005 through 12/31/2007. The "Period" column I populated manually based on the company's rules. i.e: From Dates 1/1/2005 to 1/28/2005 the Period is 1/1/05 ---> 1/28/2005 is the last Friday of that month. From Dates 1/29/2005 to 2/25/2005 the Period is 2/1/05 And so on. The problem that I have is that I each year I have to add the dates for that year and then go through the list entering the Period for each date. Is there a simpler way? Maybe a calculated field that would simply look at the invoice date and figure out the Period without having to look it up on my "Manually maintained" table? 2. Calculating the USD equivalent of the Invoice amounts: The exchange rate used to convert Local Currency to USD is based on the Period where the invoice is recognized. I have 11 different currencies and the exchange rate changes every month. I currently have a table like this Period Currency Rate 1/1/05 EUR 0.73444 1/1/05 DKK 5.66666 1/1/05 SEK 6.86666 1/1/05 NOK 6.23333 1/1/05 CHF 1.16666 And so on.... So for each month... I have to make 11 entries that will have the same Period information.... Is this an efficient way of managing this part of the data? Or is there a better way of handling this? That's it... Thanks in advance. Sebastian
From: Klatuu on 27 Jun 2008 15:10 Here is a function that will return the last Friday of the month of the date entered: Function LastFriday(ByVal dtmBaseDate As Date) As Date 'Finds the date of the Last Friday of the month for the date entered Dim intCurrMonth As Integer Dim intNextMonth As Integer Dim intNextYear As Integer Dim blnAllDone As Boolean ' Find the Current and Next Months intCurrMonth = Month(dtmBaseDate) intNextYear = Year(dtmBaseDate) + 1 intNextMonth = intCurrMonth + 1 ' Find the Friday for the date passed dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate), dtmBaseDate) If Month(dtmBaseDate) = intNextMonth Then 'The Friday for the date entered is in the next month 'so subtract a week and it will be the last Friday LastFriday = DateAdd("ww", -1, dtmBaseDate) Exit Function End If If Month(dtmBaseDate) <> intCurrMonth Then 'The Friday for the date entered is in the previous month 'so add a week to get back to current month 'Used <> instead of < because in Jan(1) you can end up in Dec(12) dtmBaseDate = DateAdd("ww", 1, dtmBaseDate) End If blnAllDone = False Do Until blnAllDone dtmBaseDate = DateAdd("ww", 1, dtmBaseDate) blnAllDone = Month(dtmBaseDate) = intNextMonth Or _ Year(dtmBaseDate) = intNextYear Loop 'Until blnAllDone LastFriday = DateAdd("ww", -1, dtmBaseDate) End Function To get the first date of the next period: =DateAdd("d", 1, LastFriday(SomeDate)) As to your table for showing converion rates by period, I don't know of a better way to do it. -- Dave Hargis, Microsoft Access MVP "Sebastian" wrote: > Here's the scenario... > I manage the billing for a single client. > This client is billed in a number of countries worldwide (30 Countries in > total). Billing is done in multiple currencies (depending on the Country > where we bill). > This is the information that I have: > > Invoicing Data: > Country > Invoice Number > Invoice Date > Invoice Amount (In Local Currency) > Invoice Description (Type of Activity being billed -- ie: Maintenance, > Installation, Storage, ect) > > Geographical Data > Country > Region (Countries are divided between Americas and Europe-Middle East-Africa) > Currency (Currency used at the country) > > Exchange Rate Data > Each month I get a report from corporate that provides me with the > "Official" exchange rates for that month, for all currencies that we bill in. > > What I need: > A reporting tool that will allow me to generate Revenue reports based on > Country - Region - Activity - Period > This reports go to managment in US, so they need to be in USD as opposed to > Local Currency. > > My challenges: > 1. Figuring out the "Period": The company that I work for has established > "cut-off" dates for billing each month (If any inovice is raised after Friday > of the month... that invoice is recognized in the following "Period"). > For this particular challenge, I have a table with two colums Date - Period. > The "Date" column is just a list of each single calendar date starting > 1/1/2005 through 12/31/2007. > The "Period" column I populated manually based on the company's rules. i.e: > From Dates 1/1/2005 to 1/28/2005 the Period is 1/1/05 ---> 1/28/2005 is the > last Friday of that month. > From Dates 1/29/2005 to 2/25/2005 the Period is 2/1/05 > And so on. > The problem that I have is that I each year I have to add the dates for that > year and then go through the list entering the Period for each date. > Is there a simpler way? Maybe a calculated field that would simply look at > the invoice date and figure out the Period without having to look it up on my > "Manually maintained" table? > > 2. Calculating the USD equivalent of the Invoice amounts: > The exchange rate used to convert Local Currency to USD is based on the > Period where the invoice is recognized. > I have 11 different currencies and the exchange rate changes every month. > I currently have a table like this > Period Currency Rate > 1/1/05 EUR 0.73444 > 1/1/05 DKK 5.66666 > 1/1/05 SEK 6.86666 > 1/1/05 NOK 6.23333 > 1/1/05 CHF 1.16666 > And so on.... > So for each month... I have to make 11 entries that will have the same > Period information.... > Is this an efficient way of managing this part of the data? Or is there a > better way of handling this? > > That's it... > > Thanks in advance. > Sebastian
From: Sebastian on 30 Jun 2008 11:39 Thank you for the response Dave... But I'm what you could call at a complete loss on what to do with this function. I have no experience with VBA... So if you could... how would I go about implementing this function so that I end up with a table of some form that contains a column "Period" with the calculation from the function? Thanks Sebastian "Klatuu" wrote: > Here is a function that will return the last Friday of the month of the date > entered: > > Function LastFriday(ByVal dtmBaseDate As Date) As Date > 'Finds the date of the Last Friday of the month for the date entered > Dim intCurrMonth As Integer > Dim intNextMonth As Integer > Dim intNextYear As Integer > Dim blnAllDone As Boolean > ' Find the Current and Next Months > intCurrMonth = Month(dtmBaseDate) > intNextYear = Year(dtmBaseDate) + 1 > intNextMonth = intCurrMonth + 1 > ' Find the Friday for the date passed > dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate), > dtmBaseDate) > If Month(dtmBaseDate) = intNextMonth Then > 'The Friday for the date entered is in the next month > 'so subtract a week and it will be the last Friday > LastFriday = DateAdd("ww", -1, dtmBaseDate) > Exit Function > End If > If Month(dtmBaseDate) <> intCurrMonth Then > 'The Friday for the date entered is in the previous month > 'so add a week to get back to current month > 'Used <> instead of < because in Jan(1) you can end up in Dec(12) > dtmBaseDate = DateAdd("ww", 1, dtmBaseDate) > End If > blnAllDone = False > Do Until blnAllDone > dtmBaseDate = DateAdd("ww", 1, dtmBaseDate) > blnAllDone = Month(dtmBaseDate) = intNextMonth Or _ > Year(dtmBaseDate) = intNextYear > Loop 'Until blnAllDone > LastFriday = DateAdd("ww", -1, dtmBaseDate) > End Function > > To get the first date of the next period: > =DateAdd("d", 1, LastFriday(SomeDate)) > As to your table for showing converion rates by period, I don't know of a > better way to do it. > -- > Dave Hargis, Microsoft Access MVP > > > "Sebastian" wrote: > > > Here's the scenario... > > I manage the billing for a single client. > > This client is billed in a number of countries worldwide (30 Countries in > > total). Billing is done in multiple currencies (depending on the Country > > where we bill). > > This is the information that I have: > > > > Invoicing Data: > > Country > > Invoice Number > > Invoice Date > > Invoice Amount (In Local Currency) > > Invoice Description (Type of Activity being billed -- ie: Maintenance, > > Installation, Storage, ect) > > > > Geographical Data > > Country > > Region (Countries are divided between Americas and Europe-Middle East-Africa) > > Currency (Currency used at the country) > > > > Exchange Rate Data > > Each month I get a report from corporate that provides me with the > > "Official" exchange rates for that month, for all currencies that we bill in. > > > > What I need: > > A reporting tool that will allow me to generate Revenue reports based on > > Country - Region - Activity - Period > > This reports go to managment in US, so they need to be in USD as opposed to > > Local Currency. > > > > My challenges: > > 1. Figuring out the "Period": The company that I work for has established > > "cut-off" dates for billing each month (If any inovice is raised after Friday > > of the month... that invoice is recognized in the following "Period"). > > For this particular challenge, I have a table with two colums Date - Period. > > The "Date" column is just a list of each single calendar date starting > > 1/1/2005 through 12/31/2007. > > The "Period" column I populated manually based on the company's rules. i.e: > > From Dates 1/1/2005 to 1/28/2005 the Period is 1/1/05 ---> 1/28/2005 is the > > last Friday of that month. > > From Dates 1/29/2005 to 2/25/2005 the Period is 2/1/05 > > And so on. > > The problem that I have is that I each year I have to add the dates for that > > year and then go through the list entering the Period for each date. > > Is there a simpler way? Maybe a calculated field that would simply look at > > the invoice date and figure out the Period without having to look it up on my > > "Manually maintained" table? > > > > 2. Calculating the USD equivalent of the Invoice amounts: > > The exchange rate used to convert Local Currency to USD is based on the > > Period where the invoice is recognized. > > I have 11 different currencies and the exchange rate changes every month. > > I currently have a table like this > > Period Currency Rate > > 1/1/05 EUR 0.73444 > > 1/1/05 DKK 5.66666 > > 1/1/05 SEK 6.86666 > > 1/1/05 NOK 6.23333 > > 1/1/05 CHF 1.16666 > > And so on.... > > So for each month... I have to make 11 entries that will have the same > > Period information.... > > Is this an efficient way of managing this part of the data? Or is there a > > better way of handling this? > > > > That's it... > > > > Thanks in advance. > > Sebastian
From: Klatuu on 30 Jun 2008 11:50 Put the function in a standard module. As to using it to populate a field in a table, since I don't know how you intend to populate a record, I can't give you a specific answer. If you can describe how you will populate the record, maybe I can offer a suggestion. -- Dave Hargis, Microsoft Access MVP "Klatuu" wrote: > Here is a function that will return the last Friday of the month of the date > entered: > > Function LastFriday(ByVal dtmBaseDate As Date) As Date > 'Finds the date of the Last Friday of the month for the date entered > Dim intCurrMonth As Integer > Dim intNextMonth As Integer > Dim intNextYear As Integer > Dim blnAllDone As Boolean > ' Find the Current and Next Months > intCurrMonth = Month(dtmBaseDate) > intNextYear = Year(dtmBaseDate) + 1 > intNextMonth = intCurrMonth + 1 > ' Find the Friday for the date passed > dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate), > dtmBaseDate) > If Month(dtmBaseDate) = intNextMonth Then > 'The Friday for the date entered is in the next month > 'so subtract a week and it will be the last Friday > LastFriday = DateAdd("ww", -1, dtmBaseDate) > Exit Function > End If > If Month(dtmBaseDate) <> intCurrMonth Then > 'The Friday for the date entered is in the previous month > 'so add a week to get back to current month > 'Used <> instead of < because in Jan(1) you can end up in Dec(12) > dtmBaseDate = DateAdd("ww", 1, dtmBaseDate) > End If > blnAllDone = False > Do Until blnAllDone > dtmBaseDate = DateAdd("ww", 1, dtmBaseDate) > blnAllDone = Month(dtmBaseDate) = intNextMonth Or _ > Year(dtmBaseDate) = intNextYear > Loop 'Until blnAllDone > LastFriday = DateAdd("ww", -1, dtmBaseDate) > End Function > > To get the first date of the next period: > =DateAdd("d", 1, LastFriday(SomeDate)) > As to your table for showing converion rates by period, I don't know of a > better way to do it. > -- > Dave Hargis, Microsoft Access MVP > > > "Sebastian" wrote: > > > Here's the scenario... > > I manage the billing for a single client. > > This client is billed in a number of countries worldwide (30 Countries in > > total). Billing is done in multiple currencies (depending on the Country > > where we bill). > > This is the information that I have: > > > > Invoicing Data: > > Country > > Invoice Number > > Invoice Date > > Invoice Amount (In Local Currency) > > Invoice Description (Type of Activity being billed -- ie: Maintenance, > > Installation, Storage, ect) > > > > Geographical Data > > Country > > Region (Countries are divided between Americas and Europe-Middle East-Africa) > > Currency (Currency used at the country) > > > > Exchange Rate Data > > Each month I get a report from corporate that provides me with the > > "Official" exchange rates for that month, for all currencies that we bill in. > > > > What I need: > > A reporting tool that will allow me to generate Revenue reports based on > > Country - Region - Activity - Period > > This reports go to managment in US, so they need to be in USD as opposed to > > Local Currency. > > > > My challenges: > > 1. Figuring out the "Period": The company that I work for has established > > "cut-off" dates for billing each month (If any inovice is raised after Friday > > of the month... that invoice is recognized in the following "Period"). > > For this particular challenge, I have a table with two colums Date - Period. > > The "Date" column is just a list of each single calendar date starting > > 1/1/2005 through 12/31/2007. > > The "Period" column I populated manually based on the company's rules. i.e: > > From Dates 1/1/2005 to 1/28/2005 the Period is 1/1/05 ---> 1/28/2005 is the > > last Friday of that month. > > From Dates 1/29/2005 to 2/25/2005 the Period is 2/1/05 > > And so on. > > The problem that I have is that I each year I have to add the dates for that > > year and then go through the list entering the Period for each date. > > Is there a simpler way? Maybe a calculated field that would simply look at > > the invoice date and figure out the Period without having to look it up on my > > "Manually maintained" table? > > > > 2. Calculating the USD equivalent of the Invoice amounts: > > The exchange rate used to convert Local Currency to USD is based on the > > Period where the invoice is recognized. > > I have 11 different currencies and the exchange rate changes every month. > > I currently have a table like this > > Period Currency Rate > > 1/1/05 EUR 0.73444 > > 1/1/05 DKK 5.66666 > > 1/1/05 SEK 6.86666 > > 1/1/05 NOK 6.23333 > > 1/1/05 CHF 1.16666 > > And so on.... > > So for each month... I have to make 11 entries that will have the same > > Period information.... > > Is this an efficient way of managing this part of the data? Or is there a > > better way of handling this? > > > > That's it... > > > > Thanks in advance. > > Sebastian
From: Sebastian on 30 Jun 2008 12:02
The way I have the DB setup now... I have a query that compares the "Invoice Date" on my tbl_invoices table to the "Date" on my tbl_periods table... The function you've provided would allow me to eliminate that tbl_periods table and obtain the "Period" information as a calculation. I need to end up with a table that will have the following columns: Country Region Invoice Date Description Invoice Amount (LC) Invoice Amount (USD) Period ---> This is where the result from that calculation would need to go... Is that something that can be done? Thanks Sebastian "Klatuu" wrote: > Put the function in a standard module. > > As to using it to populate a field in a table, since I don't know how you > intend to populate a record, I can't give you a specific answer. If you can > describe how you will populate the record, maybe I can offer a suggestion. > -- > Dave Hargis, Microsoft Access MVP > > > "Klatuu" wrote: > > > Here is a function that will return the last Friday of the month of the date > > entered: > > > > Function LastFriday(ByVal dtmBaseDate As Date) As Date > > 'Finds the date of the Last Friday of the month for the date entered > > Dim intCurrMonth As Integer > > Dim intNextMonth As Integer > > Dim intNextYear As Integer > > Dim blnAllDone As Boolean > > ' Find the Current and Next Months > > intCurrMonth = Month(dtmBaseDate) > > intNextYear = Year(dtmBaseDate) + 1 > > intNextMonth = intCurrMonth + 1 > > ' Find the Friday for the date passed > > dtmBaseDate = DateAdd("d", vbFriday - DatePart("w", dtmBaseDate), > > dtmBaseDate) > > If Month(dtmBaseDate) = intNextMonth Then > > 'The Friday for the date entered is in the next month > > 'so subtract a week and it will be the last Friday > > LastFriday = DateAdd("ww", -1, dtmBaseDate) > > Exit Function > > End If > > If Month(dtmBaseDate) <> intCurrMonth Then > > 'The Friday for the date entered is in the previous month > > 'so add a week to get back to current month > > 'Used <> instead of < because in Jan(1) you can end up in Dec(12) > > dtmBaseDate = DateAdd("ww", 1, dtmBaseDate) > > End If > > blnAllDone = False > > Do Until blnAllDone > > dtmBaseDate = DateAdd("ww", 1, dtmBaseDate) > > blnAllDone = Month(dtmBaseDate) = intNextMonth Or _ > > Year(dtmBaseDate) = intNextYear > > Loop 'Until blnAllDone > > LastFriday = DateAdd("ww", -1, dtmBaseDate) > > End Function > > > > To get the first date of the next period: > > =DateAdd("d", 1, LastFriday(SomeDate)) > > As to your table for showing converion rates by period, I don't know of a > > better way to do it. > > -- > > Dave Hargis, Microsoft Access MVP > > > > > > "Sebastian" wrote: > > > > > Here's the scenario... > > > I manage the billing for a single client. > > > This client is billed in a number of countries worldwide (30 Countries in > > > total). Billing is done in multiple currencies (depending on the Country > > > where we bill). > > > This is the information that I have: > > > > > > Invoicing Data: > > > Country > > > Invoice Number > > > Invoice Date > > > Invoice Amount (In Local Currency) > > > Invoice Description (Type of Activity being billed -- ie: Maintenance, > > > Installation, Storage, ect) > > > > > > Geographical Data > > > Country > > > Region (Countries are divided between Americas and Europe-Middle East-Africa) > > > Currency (Currency used at the country) > > > > > > Exchange Rate Data > > > Each month I get a report from corporate that provides me with the > > > "Official" exchange rates for that month, for all currencies that we bill in. > > > > > > What I need: > > > A reporting tool that will allow me to generate Revenue reports based on > > > Country - Region - Activity - Period > > > This reports go to managment in US, so they need to be in USD as opposed to > > > Local Currency. > > > > > > My challenges: > > > 1. Figuring out the "Period": The company that I work for has established > > > "cut-off" dates for billing each month (If any inovice is raised after Friday > > > of the month... that invoice is recognized in the following "Period"). > > > For this particular challenge, I have a table with two colums Date - Period. > > > The "Date" column is just a list of each single calendar date starting > > > 1/1/2005 through 12/31/2007. > > > The "Period" column I populated manually based on the company's rules. i.e: > > > From Dates 1/1/2005 to 1/28/2005 the Period is 1/1/05 ---> 1/28/2005 is the > > > last Friday of that month. > > > From Dates 1/29/2005 to 2/25/2005 the Period is 2/1/05 > > > And so on. > > > The problem that I have is that I each year I have to add the dates for that > > > year and then go through the list entering the Period for each date. > > > Is there a simpler way? Maybe a calculated field that would simply look at > > > the invoice date and figure out the Period without having to look it up on my > > > "Manually maintained" table? > > > > > > 2. Calculating the USD equivalent of the Invoice amounts: > > > The exchange rate used to convert Local Currency to USD is based on the > > > Period where the invoice is recognized. > > > I have 11 different currencies and the exchange rate changes every month. > > > I currently have a table like this > > > Period Currency Rate > > > 1/1/05 EUR 0.73444 > > > 1/1/05 DKK 5.66666 > > > 1/1/05 SEK 6.86666 > > > 1/1/05 NOK 6.23333 > > > 1/1/05 CHF 1.16666 > > > And so on.... > > > So for each month... I have to make 11 entries that will have the same > > > Period information.... > > > Is this an efficient way of managing this part of the data? Or is there a > > > better way of handling this? > > > > > > That's it... > > > > > > Thanks in advance. > > > Sebastian |