Revenue Tracking DB - Need to figure out the table structure.

[Sebastian]

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

 

[Klatuu]

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.

 

[Sebastian]

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]

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.

 

[Sebastian]

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]

Post the SQL code of your query and I can show you how to modify it to put
the calculated date in it.
If you are not familiar with how the see the SQL code, open the query in the
query builder. In the upper left hand corner, there is a small button that
will have an icon that looks like a datasheet. (should be the fartherest to
the left. It has a small down arrow next to it. Click on the arrow and
select SQL view. Then Copy/Past the code into the post.

 

[Sebastian]

Here is the SQL for the main query in my DB
SELECT tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate] AS amountUS, Year([tbl_periods.Period])
AS [Year]
FROM tbl_periods INNER JOIN ((tbl_rates INNER JOIN tbl_countries ON
tbl_rates.currency = tbl_countries.currency) INNER JOIN tbl_invoices ON
tbl_countries.countryName = tbl_invoices.countryName) ON
tbl_periods.invoiceDate = tbl_invoices.invoiceDate
GROUP BY tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate], Year([tbl_periods.Period]),
tbl_periods.Period
HAVING (((tbl_periods.Period)=[tbl_rates]![Period]));

 

[Klatuu]

The query as posted is a Totals query. It will not update anything nor can
it be updated. Also, what is carried in the Period fields? Is a the
LastFriday date, or is it just an number that carries the period like 1, 2,
3, 4, etc?

--
Dave Hargis, Microsoft Access MVP

Here is the SQL for the main query in my DB
SELECT tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate] AS amountUS, Year([tbl_periods.Period])
AS [Year]
FROM tbl_periods INNER JOIN ((tbl_rates INNER JOIN tbl_countries ON
tbl_rates.currency = tbl_countries.currency) INNER JOIN tbl_invoices ON
tbl_countries.countryName = tbl_invoices.countryName) ON
tbl_periods.invoiceDate = tbl_invoices.invoiceDate
GROUP BY tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate], Year([tbl_periods.Period]),
tbl_periods.Period
HAVING (((tbl_periods.Period)=[tbl_rates]![Period]));

Post the SQL code of your query and I can show you how to modify it to put
the calculated date in it.
If you are not familiar with how the see the SQL code, open the query in the
query builder. In the upper left hand corner, there is a small button that
will have an icon that looks like a datasheet. (should be the fartherest to
the left. It has a small down arrow next to it. Click on the arrow and
select SQL view. Then Copy/Past the code into the post.

 

[Sebastian]

Dave...
I'm going to go from scratch here... that way I don't mis-represent what I
have or need...

Tables:
tbl_invoices:
countryName
invoiceNumber
invoiceDate
amountLocal
activity

tbl_rates
Period
Currency
Rate

tbl_periods
date ----> Every single date from 1/1/2005 to 12/31/2007
Period ----> An asigned date based on the value in date ---> If date falls
before the last Friday of January 2005 then period = 1/1/2005.... if date
falls after that Friday then period is 2/1/05 and so on.

The tbl_periods is what I would be looking to eliminate.

So I guess the function that you provide would be Part 1 of a 2 part process
where Access figures out if the date is before or after the last Friday of
the corresponding month... and it then asigns the corresponding value based
on that finding.

Thanks
Sebastian

The query as posted is a Totals query. It will not update anything nor can
it be updated. Also, what is carried in the Period fields? Is a the
LastFriday date, or is it just an number that carries the period like 1, 2,
3, 4, etc?

--
Dave Hargis, Microsoft Access MVP

Here is the SQL for the main query in my DB
SELECT tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate] AS amountUS, Year([tbl_periods.Period])
AS [Year]
FROM tbl_periods INNER JOIN ((tbl_rates INNER JOIN tbl_countries ON
tbl_rates.currency = tbl_countries.currency) INNER JOIN tbl_invoices ON
tbl_countries.countryName = tbl_invoices.countryName) ON
tbl_periods.invoiceDate = tbl_invoices.invoiceDate
GROUP BY tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate], Year([tbl_periods.Period]),
tbl_periods.Period
HAVING (((tbl_periods.Period)=[tbl_rates]![Period]));

Post the SQL code of your query and I can show you how to modify it to put
the calculated date in it.
If you are not familiar with how the see the SQL code, open the query in the
query builder. In the upper left hand corner, there is a small button that
will have an icon that looks like a datasheet. (should be the fartherest to
the left. It has a small down arrow next to it. Click on the arrow and
select SQL view. Then Copy/Past the code into the post.
--
Dave Hargis, Microsoft Access MVP


:

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

:

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


:

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


:

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

 

[Sebastian]

Oh... and I forgot to add one last little detail, but I guess you would have
figured it out....
I would be looking to implement the function so that it affects
tbl_invoices....
So maybe an Update Query that would look at the invoiceDate in tbl_invoices
and then populate a column Period on that table with the appropriate
information?

Is that how I should go?

Thanks
Sebastian

Dave...
I'm going to go from scratch here... that way I don't mis-represent what I
have or need...

Tables:
tbl_invoices:
countryName
invoiceNumber
invoiceDate
amountLocal
activity

tbl_rates
Period
Currency
Rate

tbl_periods
date ----> Every single date from 1/1/2005 to 12/31/2007
Period ----> An asigned date based on the value in date ---> If date falls
before the last Friday of January 2005 then period = 1/1/2005.... if date
falls after that Friday then period is 2/1/05 and so on.

The tbl_periods is what I would be looking to eliminate.

So I guess the function that you provide would be Part 1 of a 2 part process
where Access figures out if the date is before or after the last Friday of
the corresponding month... and it then asigns the corresponding value based
on that finding.

Thanks
Sebastian

The query as posted is a Totals query. It will not update anything nor can
it be updated. Also, what is carried in the Period fields? Is a the
LastFriday date, or is it just an number that carries the period like 1, 2,
3, 4, etc?

--
Dave Hargis, Microsoft Access MVP

Here is the SQL for the main query in my DB
SELECT tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate] AS amountUS, Year([tbl_periods.Period])
AS [Year]
FROM tbl_periods INNER JOIN ((tbl_rates INNER JOIN tbl_countries ON
tbl_rates.currency = tbl_countries.currency) INNER JOIN tbl_invoices ON
tbl_countries.countryName = tbl_invoices.countryName) ON
tbl_periods.invoiceDate = tbl_invoices.invoiceDate
GROUP BY tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate], Year([tbl_periods.Period]),
tbl_periods.Period
HAVING (((tbl_periods.Period)=[tbl_rates]![Period]));


:

Post the SQL code of your query and I can show you how to modify it to put
the calculated date in it.
If you are not familiar with how the see the SQL code, open the query in the
query builder. In the upper left hand corner, there is a small button that
will have an icon that looks like a datasheet. (should be the fartherest to
the left. It has a small down arrow next to it. Click on the arrow and
select SQL view. Then Copy/Past the code into the post.
--
Dave Hargis, Microsoft Access MVP


:

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

:

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


:

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


:

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

 

[Klatuu]

You can use the code I posted previously to determine the end date of a
period. Here is another function that will return the start date of a period.

Public Function PeriodStart(InvDate As Date) As Date
Dim dtmPeriodEnd As Date

dtmPeriodEnd = LastFriday(InvDate)
PeriodStart = LastFriday(DateSerial(Year(dtmPeriodEnd),
Month(dtmPeriodEnd) - 1, 1))

End Function

So to determine the period an invoice is in, you can use filtering like:

[invoicedate] BETWEEN PeiodStart([invoicedate]) AND LastFriday([invoicedate])

Now, I would use this sort of filtering rather than stroring a period value
which would be a calculated value. Calculated values should not be stored in
tables. But if you need to determine a period number and assuming that the
period ending in January of a year would be period 1 even thought it began in
December of the previous year, you can use Month(LastFriday([invoicedate])).
For example, using today's date, it would return 7.
--
Dave Hargis, Microsoft Access MVP

Oh... and I forgot to add one last little detail, but I guess you would have
figured it out....
I would be looking to implement the function so that it affects
tbl_invoices....
So maybe an Update Query that would look at the invoiceDate in tbl_invoices
and then populate a column Period on that table with the appropriate
information?

Is that how I should go?

Thanks
Sebastian

Dave...
I'm going to go from scratch here... that way I don't mis-represent what I
have or need...

Tables:
tbl_invoices:
countryName
invoiceNumber
invoiceDate
amountLocal
activity

tbl_rates
Period
Currency
Rate

tbl_periods
date ----> Every single date from 1/1/2005 to 12/31/2007
Period ----> An asigned date based on the value in date ---> If date falls
before the last Friday of January 2005 then period = 1/1/2005.... if date
falls after that Friday then period is 2/1/05 and so on.

The tbl_periods is what I would be looking to eliminate.

So I guess the function that you provide would be Part 1 of a 2 part process
where Access figures out if the date is before or after the last Friday of
the corresponding month... and it then asigns the corresponding value based
on that finding.

Thanks
Sebastian

The query as posted is a Totals query. It will not update anything nor can
it be updated. Also, what is carried in the Period fields? Is a the
LastFriday date, or is it just an number that carries the period like 1, 2,
3, 4, etc?

--
Dave Hargis, Microsoft Access MVP


:

Here is the SQL for the main query in my DB
SELECT tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate] AS amountUS, Year([tbl_periods.Period])
AS [Year]
FROM tbl_periods INNER JOIN ((tbl_rates INNER JOIN tbl_countries ON
tbl_rates.currency = tbl_countries.currency) INNER JOIN tbl_invoices ON
tbl_countries.countryName = tbl_invoices.countryName) ON
tbl_periods.invoiceDate = tbl_invoices.invoiceDate
GROUP BY tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate], Year([tbl_periods.Period]),
tbl_periods.Period
HAVING (((tbl_periods.Period)=[tbl_rates]![Period]));


:

Post the SQL code of your query and I can show you how to modify it to put
the calculated date in it.
If you are not familiar with how the see the SQL code, open the query in the
query builder. In the upper left hand corner, there is a small button that
will have an icon that looks like a datasheet. (should be the fartherest to
the left. It has a small down arrow next to it. Click on the arrow and
select SQL view. Then Copy/Past the code into the post.
--
Dave Hargis, Microsoft Access MVP


:

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

:

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


:

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


:

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

 

[Sebastian]

Dave...

Thank you very much for your help on this. I've read through your
explanations and most of it makes sense.
I'm still a bit confused as to how to implement the two functions..... I
think I'm supposed to save the code you've provided as functions that I can
then call from a query or a form... Am I correct?

As for the Period... I understand the part where I'm not supposed to store
calculated values in a table... This does raise another question for me
though...
I use that "Period" value (currently on a table that I maintain manually) to
determine the exchange rate that will be used to convert Local Currency to
USD... If I don't have that value anywhere... how can I then make the
matching from the tbl_periods to the tbl_rates where the link between the two
is the period column?

Thanks...
Sebastian

You can use the code I posted previously to determine the end date of a
period. Here is another function that will return the start date of a period.

Public Function PeriodStart(InvDate As Date) As Date
Dim dtmPeriodEnd As Date

dtmPeriodEnd = LastFriday(InvDate)
PeriodStart = LastFriday(DateSerial(Year(dtmPeriodEnd),
Month(dtmPeriodEnd) - 1, 1))

End Function

So to determine the period an invoice is in, you can use filtering like:

[invoicedate] BETWEEN PeiodStart([invoicedate]) AND LastFriday([invoicedate])

Now, I would use this sort of filtering rather than stroring a period value
which would be a calculated value. Calculated values should not be stored in
tables. But if you need to determine a period number and assuming that the
period ending in January of a year would be period 1 even thought it began in
December of the previous year, you can use Month(LastFriday([invoicedate])).
For example, using today's date, it would return 7.
--
Dave Hargis, Microsoft Access MVP

Oh... and I forgot to add one last little detail, but I guess you would have
figured it out....
I would be looking to implement the function so that it affects
tbl_invoices....
So maybe an Update Query that would look at the invoiceDate in tbl_invoices
and then populate a column Period on that table with the appropriate
information?

Is that how I should go?

Thanks
Sebastian

Dave...
I'm going to go from scratch here... that way I don't mis-represent what I
have or need...

Tables:
tbl_invoices:
countryName
invoiceNumber
invoiceDate
amountLocal
activity

tbl_rates
Period
Currency
Rate

tbl_periods
date ----> Every single date from 1/1/2005 to 12/31/2007
Period ----> An asigned date based on the value in date ---> If date falls
before the last Friday of January 2005 then period = 1/1/2005.... if date
falls after that Friday then period is 2/1/05 and so on.

The tbl_periods is what I would be looking to eliminate.

So I guess the function that you provide would be Part 1 of a 2 part process
where Access figures out if the date is before or after the last Friday of
the corresponding month... and it then asigns the corresponding value based
on that finding.

Thanks
Sebastian



:

The query as posted is a Totals query. It will not update anything nor can
it be updated. Also, what is carried in the Period fields? Is a the
LastFriday date, or is it just an number that carries the period like 1, 2,
3, 4, etc?

--
Dave Hargis, Microsoft Access MVP


:

Here is the SQL for the main query in my DB
SELECT tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate] AS amountUS, Year([tbl_periods.Period])
AS [Year]
FROM tbl_periods INNER JOIN ((tbl_rates INNER JOIN tbl_countries ON
tbl_rates.currency = tbl_countries.currency) INNER JOIN tbl_invoices ON
tbl_countries.countryName = tbl_invoices.countryName) ON
tbl_periods.invoiceDate = tbl_invoices.invoiceDate
GROUP BY tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate], Year([tbl_periods.Period]),
tbl_periods.Period
HAVING (((tbl_periods.Period)=[tbl_rates]![Period]));


:

Post the SQL code of your query and I can show you how to modify it to put
the calculated date in it.
If you are not familiar with how the see the SQL code, open the query in the
query builder. In the upper left hand corner, there is a small button that
will have an icon that looks like a datasheet. (should be the fartherest to
the left. It has a small down arrow next to it. Click on the arrow and
select SQL view. Then Copy/Past the code into the post.
--
Dave Hargis, Microsoft Access MVP


:

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

:

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


:

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


:

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

 

[Klatuu]

See answers below:
--
Dave Hargis, Microsoft Access MVP

Dave...

Thank you very much for your help on this. I've read through your
explanations and most of it makes sense.
I'm still a bit confused as to how to implement the two functions..... I
think I'm supposed to save the code you've provided as functions that I can
then call from a query or a form... Am I correct?

That is correct. You should put them in a standard module so they can be
seen from any form, report, or query and used throughout your application.

As for the Period... I understand the part where I'm not supposed to store
calculated values in a table... This does raise another question for me
though...
I use that "Period" value (currently on a table that I maintain manually) to
determine the exchange rate that will be used to convert Local Currency to
USD... If I don't have that value anywhere... how can I then make the
matching from the tbl_periods to the tbl_rates where the link between the two
is the period column?

Well, to stay true to form, you would use a query that would calculate the
period as I described in my last post. This would then be a calculated
field. You can then relate the rate table to that calculated field in the
query; however, if it makes more sense in your application, you can populate
the period field in the invoice table using the same technique. Now, you
also have to be aware that if the invoice date can ever be changed in a
record, you must be very careful to also update the period field. This
gotcha is one of the reasons that database normalization rules state you
should not carry calculated values. It is possible to change the value of
the field they were calculated on and not change the calculated value. As I
said, you could populate the period field, but I would advise using
calculated fields in queries to accomplish that task.

Thanks...
Sebastian

You can use the code I posted previously to determine the end date of a
period. Here is another function that will return the start date of a period.

Public Function PeriodStart(InvDate As Date) As Date
Dim dtmPeriodEnd As Date

dtmPeriodEnd = LastFriday(InvDate)
PeriodStart = LastFriday(DateSerial(Year(dtmPeriodEnd),
Month(dtmPeriodEnd) - 1, 1))

End Function

So to determine the period an invoice is in, you can use filtering like:

[invoicedate] BETWEEN PeiodStart([invoicedate]) AND LastFriday([invoicedate])

Now, I would use this sort of filtering rather than stroring a period value
which would be a calculated value. Calculated values should not be stored in
tables. But if you need to determine a period number and assuming that the
period ending in January of a year would be period 1 even thought it began in
December of the previous year, you can use Month(LastFriday([invoicedate])).
For example, using today's date, it would return 7.
--
Dave Hargis, Microsoft Access MVP

Oh... and I forgot to add one last little detail, but I guess you would have
figured it out....
I would be looking to implement the function so that it affects
tbl_invoices....
So maybe an Update Query that would look at the invoiceDate in tbl_invoices
and then populate a column Period on that table with the appropriate
information?

Is that how I should go?

Thanks
Sebastian

:

Dave...
I'm going to go from scratch here... that way I don't mis-represent what I
have or need...

Tables:
tbl_invoices:
countryName
invoiceNumber
invoiceDate
amountLocal
activity

tbl_rates
Period
Currency
Rate

tbl_periods
date ----> Every single date from 1/1/2005 to 12/31/2007
Period ----> An asigned date based on the value in date ---> If date falls
before the last Friday of January 2005 then period = 1/1/2005.... if date
falls after that Friday then period is 2/1/05 and so on.

The tbl_periods is what I would be looking to eliminate.

So I guess the function that you provide would be Part 1 of a 2 part process
where Access figures out if the date is before or after the last Friday of
the corresponding month... and it then asigns the corresponding value based
on that finding.

Thanks
Sebastian



:

The query as posted is a Totals query. It will not update anything nor can
it be updated. Also, what is carried in the Period fields? Is a the
LastFriday date, or is it just an number that carries the period like 1, 2,
3, 4, etc?

--
Dave Hargis, Microsoft Access MVP


:

Here is the SQL for the main query in my DB
SELECT tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate] AS amountUS, Year([tbl_periods.Period])
AS [Year]
FROM tbl_periods INNER JOIN ((tbl_rates INNER JOIN tbl_countries ON
tbl_rates.currency = tbl_countries.currency) INNER JOIN tbl_invoices ON
tbl_countries.countryName = tbl_invoices.countryName) ON
tbl_periods.invoiceDate = tbl_invoices.invoiceDate
GROUP BY tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate], Year([tbl_periods.Period]),
tbl_periods.Period
HAVING (((tbl_periods.Period)=[tbl_rates]![Period]));


:

Post the SQL code of your query and I can show you how to modify it to put
the calculated date in it.
If you are not familiar with how the see the SQL code, open the query in the
query builder. In the upper left hand corner, there is a small button that
will have an icon that looks like a datasheet. (should be the fartherest to
the left. It has a small down arrow next to it. Click on the arrow and
select SQL view. Then Copy/Past the code into the post.
--
Dave Hargis, Microsoft Access MVP


:

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

:

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


:

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


:

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

 

[Sebastian]

Dave...

Thanks again for all your help...

I've added the LastFriday function to a standard module and tested the
Month(LastFriday(invoiceDate)) expression... it works like a charm...

I'm off to the races now...
Thank you very much....

Sebastian

See answers below:
--
Dave Hargis, Microsoft Access MVP

Dave...

Thank you very much for your help on this. I've read through your
explanations and most of it makes sense.
I'm still a bit confused as to how to implement the two functions..... I
think I'm supposed to save the code you've provided as functions that I can
then call from a query or a form... Am I correct?

That is correct. You should put them in a standard module so they can be
seen from any form, report, or query and used throughout your application.

As for the Period... I understand the part where I'm not supposed to store
calculated values in a table... This does raise another question for me
though...
I use that "Period" value (currently on a table that I maintain manually) to
determine the exchange rate that will be used to convert Local Currency to
USD... If I don't have that value anywhere... how can I then make the
matching from the tbl_periods to the tbl_rates where the link between the two
is the period column?

Well, to stay true to form, you would use a query that would calculate the
period as I described in my last post. This would then be a calculated
field. You can then relate the rate table to that calculated field in the
query; however, if it makes more sense in your application, you can populate
the period field in the invoice table using the same technique. Now, you
also have to be aware that if the invoice date can ever be changed in a
record, you must be very careful to also update the period field. This
gotcha is one of the reasons that database normalization rules state you
should not carry calculated values. It is possible to change the value of
the field they were calculated on and not change the calculated value. As I
said, you could populate the period field, but I would advise using
calculated fields in queries to accomplish that task.

Thanks...
Sebastian

You can use the code I posted previously to determine the end date of a
period. Here is another function that will return the start date of a period.

Public Function PeriodStart(InvDate As Date) As Date
Dim dtmPeriodEnd As Date

dtmPeriodEnd = LastFriday(InvDate)
PeriodStart = LastFriday(DateSerial(Year(dtmPeriodEnd),
Month(dtmPeriodEnd) - 1, 1))

End Function

So to determine the period an invoice is in, you can use filtering like:

[invoicedate] BETWEEN PeiodStart([invoicedate]) AND LastFriday([invoicedate])

Now, I would use this sort of filtering rather than stroring a period value
which would be a calculated value. Calculated values should not be stored in
tables. But if you need to determine a period number and assuming that the
period ending in January of a year would be period 1 even thought it began in
December of the previous year, you can use Month(LastFriday([invoicedate])).
For example, using today's date, it would return 7.
--
Dave Hargis, Microsoft Access MVP


:

Oh... and I forgot to add one last little detail, but I guess you would have
figured it out....
I would be looking to implement the function so that it affects
tbl_invoices....
So maybe an Update Query that would look at the invoiceDate in tbl_invoices
and then populate a column Period on that table with the appropriate
information?

Is that how I should go?

Thanks
Sebastian

:

Dave...
I'm going to go from scratch here... that way I don't mis-represent what I
have or need...

Tables:
tbl_invoices:
countryName
invoiceNumber
invoiceDate
amountLocal
activity

tbl_rates
Period
Currency
Rate

tbl_periods
date ----> Every single date from 1/1/2005 to 12/31/2007
Period ----> An asigned date based on the value in date ---> If date falls
before the last Friday of January 2005 then period = 1/1/2005.... if date
falls after that Friday then period is 2/1/05 and so on.

The tbl_periods is what I would be looking to eliminate.

So I guess the function that you provide would be Part 1 of a 2 part process
where Access figures out if the date is before or after the last Friday of
the corresponding month... and it then asigns the corresponding value based
on that finding.

Thanks
Sebastian



:

The query as posted is a Totals query. It will not update anything nor can
it be updated. Also, what is carried in the Period fields? Is a the
LastFriday date, or is it just an number that carries the period like 1, 2,
3, 4, etc?

--
Dave Hargis, Microsoft Access MVP


:

Here is the SQL for the main query in my DB
SELECT tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate] AS amountUS, Year([tbl_periods.Period])
AS [Year]
FROM tbl_periods INNER JOIN ((tbl_rates INNER JOIN tbl_countries ON
tbl_rates.currency = tbl_countries.currency) INNER JOIN tbl_invoices ON
tbl_countries.countryName = tbl_invoices.countryName) ON
tbl_periods.invoiceDate = tbl_invoices.invoiceDate
GROUP BY tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate], Year([tbl_periods.Period]),
tbl_periods.Period
HAVING (((tbl_periods.Period)=[tbl_rates]![Period]));


:

Post the SQL code of your query and I can show you how to modify it to put
the calculated date in it.
If you are not familiar with how the see the SQL code, open the query in the
query builder. In the upper left hand corner, there is a small button that
will have an icon that looks like a datasheet. (should be the fartherest to
the left. It has a small down arrow next to it. Click on the arrow and
select SQL view. Then Copy/Past the code into the post.
--
Dave Hargis, Microsoft Access MVP


:

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

:

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


:

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


:

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.

 

[Klatuu]

Glad I could help.
I did just have another thought you can consider. In your rate table,
rather than using a period identifier, how about using the Period End Date,
which would align with the LastFriday of the invoicedate? It would reduce
the amount of code and simplify your queries.
--
Dave Hargis, Microsoft Access MVP

Dave...

Thanks again for all your help...

I've added the LastFriday function to a standard module and tested the
Month(LastFriday(invoiceDate)) expression... it works like a charm...

I'm off to the races now...
Thank you very much....

Sebastian

See answers below:
--
Dave Hargis, Microsoft Access MVP

Dave...

Thank you very much for your help on this. I've read through your
explanations and most of it makes sense.
I'm still a bit confused as to how to implement the two functions..... I
think I'm supposed to save the code you've provided as functions that I can
then call from a query or a form... Am I correct?

That is correct. You should put them in a standard module so they can be
seen from any form, report, or query and used throughout your application.

As for the Period... I understand the part where I'm not supposed to store
calculated values in a table... This does raise another question for me
though...
I use that "Period" value (currently on a table that I maintain manually) to
determine the exchange rate that will be used to convert Local Currency to
USD... If I don't have that value anywhere... how can I then make the
matching from the tbl_periods to the tbl_rates where the link between the two
is the period column?

Well, to stay true to form, you would use a query that would calculate the
period as I described in my last post. This would then be a calculated
field. You can then relate the rate table to that calculated field in the
query; however, if it makes more sense in your application, you can populate
the period field in the invoice table using the same technique. Now, you
also have to be aware that if the invoice date can ever be changed in a
record, you must be very careful to also update the period field. This
gotcha is one of the reasons that database normalization rules state you
should not carry calculated values. It is possible to change the value of
the field they were calculated on and not change the calculated value. As I
said, you could populate the period field, but I would advise using
calculated fields in queries to accomplish that task.

Thanks...
Sebastian

:

You can use the code I posted previously to determine the end date of a
period. Here is another function that will return the start date of a period.

Public Function PeriodStart(InvDate As Date) As Date
Dim dtmPeriodEnd As Date

dtmPeriodEnd = LastFriday(InvDate)
PeriodStart = LastFriday(DateSerial(Year(dtmPeriodEnd),
Month(dtmPeriodEnd) - 1, 1))

End Function

So to determine the period an invoice is in, you can use filtering like:

[invoicedate] BETWEEN PeiodStart([invoicedate]) AND LastFriday([invoicedate])

Now, I would use this sort of filtering rather than stroring a period value
which would be a calculated value. Calculated values should not be stored in
tables. But if you need to determine a period number and assuming that the
period ending in January of a year would be period 1 even thought it began in
December of the previous year, you can use Month(LastFriday([invoicedate])).
For example, using today's date, it would return 7.
--
Dave Hargis, Microsoft Access MVP


:

Oh... and I forgot to add one last little detail, but I guess you would have
figured it out....
I would be looking to implement the function so that it affects
tbl_invoices....
So maybe an Update Query that would look at the invoiceDate in tbl_invoices
and then populate a column Period on that table with the appropriate
information?

Is that how I should go?

Thanks
Sebastian

:

Dave...
I'm going to go from scratch here... that way I don't mis-represent what I
have or need...

Tables:
tbl_invoices:
countryName
invoiceNumber
invoiceDate
amountLocal
activity

tbl_rates
Period
Currency
Rate

tbl_periods
date ----> Every single date from 1/1/2005 to 12/31/2007
Period ----> An asigned date based on the value in date ---> If date falls
before the last Friday of January 2005 then period = 1/1/2005.... if date
falls after that Friday then period is 2/1/05 and so on.

The tbl_periods is what I would be looking to eliminate.

So I guess the function that you provide would be Part 1 of a 2 part process
where Access figures out if the date is before or after the last Friday of
the corresponding month... and it then asigns the corresponding value based
on that finding.

Thanks
Sebastian



:

The query as posted is a Totals query. It will not update anything nor can
it be updated. Also, what is carried in the Period fields? Is a the
LastFriday date, or is it just an number that carries the period like 1, 2,
3, 4, etc?

--
Dave Hargis, Microsoft Access MVP


:

Here is the SQL for the main query in my DB
SELECT tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate] AS amountUS, Year([tbl_periods.Period])
AS [Year]
FROM tbl_periods INNER JOIN ((tbl_rates INNER JOIN tbl_countries ON
tbl_rates.currency = tbl_countries.currency) INNER JOIN tbl_invoices ON
tbl_countries.countryName = tbl_invoices.countryName) ON
tbl_periods.invoiceDate = tbl_invoices.invoiceDate
GROUP BY tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate], Year([tbl_periods.Period]),
tbl_periods.Period
HAVING (((tbl_periods.Period)=[tbl_rates]![Period]));


:

Post the SQL code of your query and I can show you how to modify it to put
the calculated date in it.
If you are not familiar with how the see the SQL code, open the query in the
query builder. In the upper left hand corner, there is a small button that
will have an icon that looks like a datasheet. (should be the fartherest to
the left. It has a small down arrow next to it. Click on the arrow and
select SQL view. Then Copy/Past the code into the post.
--
Dave Hargis, Microsoft Access MVP


:

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

:

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


:

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


:

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.

 

[Sebastian]

That would make things easier... I'll change that table... thanks for the
heads up.

Glad I could help.
I did just have another thought you can consider. In your rate table,
rather than using a period identifier, how about using the Period End Date,
which would align with the LastFriday of the invoicedate? It would reduce
the amount of code and simplify your queries.
--
Dave Hargis, Microsoft Access MVP

Dave...

Thanks again for all your help...

I've added the LastFriday function to a standard module and tested the
Month(LastFriday(invoiceDate)) expression... it works like a charm...

I'm off to the races now...
Thank you very much....

Sebastian

See answers below:
--
Dave Hargis, Microsoft Access MVP


:

Dave...

Thank you very much for your help on this. I've read through your
explanations and most of it makes sense.
I'm still a bit confused as to how to implement the two functions..... I
think I'm supposed to save the code you've provided as functions that I can
then call from a query or a form... Am I correct?

That is correct. You should put them in a standard module so they can be
seen from any form, report, or query and used throughout your application.

As for the Period... I understand the part where I'm not supposed to store
calculated values in a table... This does raise another question for me
though...
I use that "Period" value (currently on a table that I maintain manually) to
determine the exchange rate that will be used to convert Local Currency to
USD... If I don't have that value anywhere... how can I then make the
matching from the tbl_periods to the tbl_rates where the link between the two
is the period column?

Well, to stay true to form, you would use a query that would calculate the
period as I described in my last post. This would then be a calculated
field. You can then relate the rate table to that calculated field in the
query; however, if it makes more sense in your application, you can populate
the period field in the invoice table using the same technique. Now, you
also have to be aware that if the invoice date can ever be changed in a
record, you must be very careful to also update the period field. This
gotcha is one of the reasons that database normalization rules state you
should not carry calculated values. It is possible to change the value of
the field they were calculated on and not change the calculated value. As I
said, you could populate the period field, but I would advise using
calculated fields in queries to accomplish that task.

Thanks...
Sebastian

:

You can use the code I posted previously to determine the end date of a
period. Here is another function that will return the start date of a period.

Public Function PeriodStart(InvDate As Date) As Date
Dim dtmPeriodEnd As Date

dtmPeriodEnd = LastFriday(InvDate)
PeriodStart = LastFriday(DateSerial(Year(dtmPeriodEnd),
Month(dtmPeriodEnd) - 1, 1))

End Function

So to determine the period an invoice is in, you can use filtering like:

[invoicedate] BETWEEN PeiodStart([invoicedate]) AND LastFriday([invoicedate])

Now, I would use this sort of filtering rather than stroring a period value
which would be a calculated value. Calculated values should not be stored in
tables. But if you need to determine a period number and assuming that the
period ending in January of a year would be period 1 even thought it began in
December of the previous year, you can use Month(LastFriday([invoicedate])).
For example, using today's date, it would return 7.
--
Dave Hargis, Microsoft Access MVP


:

Oh... and I forgot to add one last little detail, but I guess you would have
figured it out....
I would be looking to implement the function so that it affects
tbl_invoices....
So maybe an Update Query that would look at the invoiceDate in tbl_invoices
and then populate a column Period on that table with the appropriate
information?

Is that how I should go?

Thanks
Sebastian

:

Dave...
I'm going to go from scratch here... that way I don't mis-represent what I
have or need...

Tables:
tbl_invoices:
countryName
invoiceNumber
invoiceDate
amountLocal
activity

tbl_rates
Period
Currency
Rate

tbl_periods
date ----> Every single date from 1/1/2005 to 12/31/2007
Period ----> An asigned date based on the value in date ---> If date falls
before the last Friday of January 2005 then period = 1/1/2005.... if date
falls after that Friday then period is 2/1/05 and so on.

The tbl_periods is what I would be looking to eliminate.

So I guess the function that you provide would be Part 1 of a 2 part process
where Access figures out if the date is before or after the last Friday of
the corresponding month... and it then asigns the corresponding value based
on that finding.

Thanks
Sebastian



:

The query as posted is a Totals query. It will not update anything nor can
it be updated. Also, what is carried in the Period fields? Is a the
LastFriday date, or is it just an number that carries the period like 1, 2,
3, 4, etc?

--
Dave Hargis, Microsoft Access MVP


:

Here is the SQL for the main query in my DB
SELECT tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate] AS amountUS, Year([tbl_periods.Period])
AS [Year]
FROM tbl_periods INNER JOIN ((tbl_rates INNER JOIN tbl_countries ON
tbl_rates.currency = tbl_countries.currency) INNER JOIN tbl_invoices ON
tbl_countries.countryName = tbl_invoices.countryName) ON
tbl_periods.invoiceDate = tbl_invoices.invoiceDate
GROUP BY tbl_invoices.countryName, tbl_countries.area, tbl_countries.region,
tbl_invoices.invoiceNumber, tbl_invoices.invoiceDate, tbl_invoices.activity,
tbl_rates.Period, tbl_invoices.amountLocal, tbl_rates.currency,
tbl_rates.rate, [amountLocal]/[rate], Year([tbl_periods.Period]),
tbl_periods.Period
HAVING (((tbl_periods.Period)=[tbl_rates]![Period]));


:

Post the SQL code of your query and I can show you how to modify it to put
the calculated date in it.
If you are not familiar with how the see the SQL code, open the query in the
query builder. In the upper left hand corner, there is a small button that
will have an icon that looks like a datasheet. (should be the fartherest to
the left. It has a small down arrow next to it. Click on the arrow and
select SQL view. Then Copy/Past the code into the post.
--
Dave Hargis, Microsoft Access MVP


:

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

:

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


:

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


:

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