historic VAT Rate lookup

  • Thread starter Thread starter Sue
  • Start date Start date
S

Sue

Hi all

I have a table holding data over the last twelve months, with a parts and
labour total per item. The table holds data for all European countries and
each record has a CountryCode field and ClaimNo as its key. These totals
include VAT, I need to calculate a NET total in a query for analysis. The
VAT rates are held in a table called [tbl VAT Rates]. This has 3 fields:
CountryCode (same as totals data table), EffectiveDate and VATRate. There
can be more than one record per country, it is keyed on Country and
EffectiveDate. I need to calculate the VAT based on a payment date in the
totals table.

For example I might have a record for country UK paid on 31/03/10, the VAT
table has two records for the UK - one Effective Date of 01/01/01 as 15% and
another of 01/02/10 with 0% (ie no VAT charged). In this example it would
need to use the 0% value to calculate the NET total of labour and parts.

Can I achieve this in a query? I have tried playing with dlookup, but the
problem is I think it needs to return the Max of EffectiveDate, which is
less than the paymentdate - and my brain is just not working!!! Can anyone
help please?
 
I would try the following UNTESTED SQL

First a query to identify the record in the VAT table that you need

SELECT CostTable.ClaimNo
, CostTable.CountryCode
, Max(VV.EffectiveDate) as Effective
FROM CostTable INNER JOIN [tbl Vat Rates] as VV
ON CostTable.CountryCode = VV.CountryCode
AND CostTable.ActivityDate >= VV.EffectiveDate
GROUP BY CostTable.ClaimNo, CostTable.CountryCode

Now use that saved query in a join

SELECT CostTable.*
, V.VATRate
FROM (CostTable INNER JOIN [SavedQueryName] as Q
ON CostTable.PrimaryKey = Q.PrimaryKey)
INNER JOIN [tbl Vat Rates] as V
ON Q.CountryCode = V.CountryCode
AND Q.Effective = V.EffectiveDate


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Hi John

Thanks very much for this!!! I have the first query working but the 2nd
errors with 'the specified field [tbl Warranty Data].Primarykey could refer
to more than one table listed in the FROM clause of your SQL statement'.
Having said that, I checked the key again on the tables (it isn't my
database I am being asked to work on) and the primary key of the Cost table
is actually keyed on a unique claim key and not the countrycode + claimno
(called [Claim_System_Key]). So it isn't the primary key I need to use in
this case.. Sorry for the confusion! Cheers, Sue


John Spencer said:
I would try the following UNTESTED SQL

First a query to identify the record in the VAT table that you need

SELECT CostTable.ClaimNo
, CostTable.CountryCode
, Max(VV.EffectiveDate) as Effective
FROM CostTable INNER JOIN [tbl Vat Rates] as VV
ON CostTable.CountryCode = VV.CountryCode
AND CostTable.ActivityDate >= VV.EffectiveDate
GROUP BY CostTable.ClaimNo, CostTable.CountryCode

Now use that saved query in a join

SELECT CostTable.*
, V.VATRate
FROM (CostTable INNER JOIN [SavedQueryName] as Q
ON CostTable.PrimaryKey = Q.PrimaryKey)
INNER JOIN [tbl Vat Rates] as V
ON Q.CountryCode = V.CountryCode
AND Q.Effective = V.EffectiveDate


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi all

I have a table holding data over the last twelve months, with a parts and
labour total per item. The table holds data for all European countries
and each record has a CountryCode field and ClaimNo as its key. These
totals include VAT, I need to calculate a NET total in a query for
analysis. The VAT rates are held in a table called [tbl VAT Rates]. This
has 3 fields: CountryCode (same as totals data table), EffectiveDate and
VATRate. There can be more than one record per country, it is keyed on
Country and EffectiveDate. I need to calculate the VAT based on a payment
date in the totals table.

For example I might have a record for country UK paid on 31/03/10, the
VAT table has two records for the UK - one Effective Date of 01/01/01 as
15% and another of 01/02/10 with 0% (ie no VAT charged). In this example
it would need to use the 0% value to calculate the NET total of labour
and parts.

Can I achieve this in a query? I have tried playing with dlookup, but the
problem is I think it needs to return the Max of EffectiveDate, which is
less than the paymentdate - and my brain is just not working!!! Can
anyone help please?
Click to expand...
Click to expand...
 
For reference, I did try changing the first query to substitute ClaimNo with
Claim_System_Key to see if this worked but I still got the sql error as
mentioned
below...

Sue said:
Hi John

Thanks very much for this!!! I have the first query working but the 2nd
errors with 'the specified field [tbl Warranty Data].Primarykey could
refer to more than one table listed in the FROM clause of your SQL
statement'. Having said that, I checked the key again on the tables (it
isn't my database I am being asked to work on) and the primary key of the
Cost table is actually keyed on a unique claim key and not the countrycode
+ claimno (called [Claim_System_Key]). So it isn't the primary key I need
to use in this case.. Sorry for the confusion! Cheers, Sue


John Spencer said:
I would try the following UNTESTED SQL

First a query to identify the record in the VAT table that you need

SELECT CostTable.ClaimNo
, CostTable.CountryCode
, Max(VV.EffectiveDate) as Effective
FROM CostTable INNER JOIN [tbl Vat Rates] as VV
ON CostTable.CountryCode = VV.CountryCode
AND CostTable.ActivityDate >= VV.EffectiveDate
GROUP BY CostTable.ClaimNo, CostTable.CountryCode

Now use that saved query in a join

SELECT CostTable.*
, V.VATRate
FROM (CostTable INNER JOIN [SavedQueryName] as Q
ON CostTable.PrimaryKey = Q.PrimaryKey)
INNER JOIN [tbl Vat Rates] as V
ON Q.CountryCode = V.CountryCode
AND Q.Effective = V.EffectiveDate


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi all

I have a table holding data over the last twelve months, with a parts
and labour total per item. The table holds data for all European
countries and each record has a CountryCode field and ClaimNo as its
key. These totals include VAT, I need to calculate a NET total in a
query for analysis. The VAT rates are held in a table called [tbl VAT
Rates]. This has 3 fields: CountryCode (same as totals data table),
EffectiveDate and VATRate. There can be more than one record per
country, it is keyed on Country and EffectiveDate. I need to calculate
the VAT based on a payment date in the totals table.

For example I might have a record for country UK paid on 31/03/10, the
VAT table has two records for the UK - one Effective Date of 01/01/01 as
15% and another of 01/02/10 with 0% (ie no VAT charged). In this example
it would need to use the 0% value to calculate the NET total of labour
and parts.

Can I achieve this in a query? I have tried playing with dlookup, but
the problem is I think it needs to return the Max of EffectiveDate,
which is less than the paymentdate - and my brain is just not working!!!
Can anyone help please?
Click to expand...
Click to expand...
Click to expand...
 
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
For reference, I did try changing the first query to substitute ClaimNo with
Claim_System_Key to see if this worked but I still got the sql error as
mentioned
below...

Sue said:
Hi John

Thanks very much for this!!! I have the first query working but the 2nd
errors with 'the specified field [tbl Warranty Data].Primarykey could
refer to more than one table listed in the FROM clause of your SQL
statement'. Having said that, I checked the key again on the tables (it
isn't my database I am being asked to work on) and the primary key of the
Cost table is actually keyed on a unique claim key and not the countrycode
+ claimno (called [Claim_System_Key]). So it isn't the primary key I need
to use in this case.. Sorry for the confusion! Cheers, Sue


John Spencer said:
I would try the following UNTESTED SQL

First a query to identify the record in the VAT table that you need

SELECT CostTable.ClaimNo
, CostTable.CountryCode
, Max(VV.EffectiveDate) as Effective
FROM CostTable INNER JOIN [tbl Vat Rates] as VV
ON CostTable.CountryCode = VV.CountryCode
AND CostTable.ActivityDate >= VV.EffectiveDate
GROUP BY CostTable.ClaimNo, CostTable.CountryCode

Now use that saved query in a join

SELECT CostTable.*
, V.VATRate
FROM (CostTable INNER JOIN [SavedQueryName] as Q
ON CostTable.PrimaryKey = Q.PrimaryKey)
INNER JOIN [tbl Vat Rates] as V
ON Q.CountryCode = V.CountryCode
AND Q.Effective = V.EffectiveDate


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Sue wrote:
Hi all

I have a table holding data over the last twelve months, with a parts
and labour total per item. The table holds data for all European
countries and each record has a CountryCode field and ClaimNo as its
key. These totals include VAT, I need to calculate a NET total in a
query for analysis. The VAT rates are held in a table called [tbl VAT
Rates]. This has 3 fields: CountryCode (same as totals data table),
EffectiveDate and VATRate. There can be more than one record per
country, it is keyed on Country and EffectiveDate. I need to calculate
the VAT based on a payment date in the totals table.

For example I might have a record for country UK paid on 31/03/10, the
VAT table has two records for the UK - one Effective Date of 01/01/01 as
15% and another of 01/02/10 with 0% (ie no VAT charged). In this example
it would need to use the 0% value to calculate the NET total of labour
and parts.

Can I achieve this in a query? I have tried playing with dlookup, but
the problem is I think it needs to return the Max of EffectiveDate,
which is less than the paymentdate - and my brain is just not working!!!
Can anyone help please?
Click to expand...
Click to expand...
Click to expand...
 
Sue, I have no idea what is failing or why.

If you need further help you need to post the SQL of the queries you are
attempting to use. That way someone has a chance of figuring out how to fix
the query so it will work.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
For reference, I did try changing the first query to substitute ClaimNo with
Claim_System_Key to see if this worked but I still got the sql error as
mentioned
below...

Sue said:
Hi John

Thanks very much for this!!! I have the first query working but the 2nd
errors with 'the specified field [tbl Warranty Data].Primarykey could
refer to more than one table listed in the FROM clause of your SQL
statement'. Having said that, I checked the key again on the tables (it
isn't my database I am being asked to work on) and the primary key of the
Cost table is actually keyed on a unique claim key and not the countrycode
+ claimno (called [Claim_System_Key]). So it isn't the primary key I need
to use in this case.. Sorry for the confusion! Cheers, Sue


John Spencer said:
I would try the following UNTESTED SQL

First a query to identify the record in the VAT table that you need

SELECT CostTable.ClaimNo
, CostTable.CountryCode
, Max(VV.EffectiveDate) as Effective
FROM CostTable INNER JOIN [tbl Vat Rates] as VV
ON CostTable.CountryCode = VV.CountryCode
AND CostTable.ActivityDate >= VV.EffectiveDate
GROUP BY CostTable.ClaimNo, CostTable.CountryCode

Now use that saved query in a join

SELECT CostTable.*
, V.VATRate
FROM (CostTable INNER JOIN [SavedQueryName] as Q
ON CostTable.PrimaryKey = Q.PrimaryKey)
INNER JOIN [tbl Vat Rates] as V
ON Q.CountryCode = V.CountryCode
AND Q.Effective = V.EffectiveDate


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Sue wrote:
Hi all

I have a table holding data over the last twelve months, with a parts
and labour total per item. The table holds data for all European
countries and each record has a CountryCode field and ClaimNo as its
key. These totals include VAT, I need to calculate a NET total in a
query for analysis. The VAT rates are held in a table called [tbl VAT
Rates]. This has 3 fields: CountryCode (same as totals data table),
EffectiveDate and VATRate. There can be more than one record per
country, it is keyed on Country and EffectiveDate. I need to calculate
the VAT based on a payment date in the totals table.

For example I might have a record for country UK paid on 31/03/10, the
VAT table has two records for the UK - one Effective Date of 01/01/01 as
15% and another of 01/02/10 with 0% (ie no VAT charged). In this example
it would need to use the 0% value to calculate the NET total of labour
and parts.

Can I achieve this in a query? I have tried playing with dlookup, but
the problem is I think it needs to return the Max of EffectiveDate,
which is less than the paymentdate - and my brain is just not working!!!
Can anyone help please?
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your help John, I will post it tomorrow when I am back at work
but it is exactly the same sql you posted really with just the table and
fieldnames changed.

John Spencer said:
Sue, I have no idea what is failing or why.

If you need further help you need to post the SQL of the queries you are
attempting to use. That way someone has a chance of figuring out how to
fix the query so it will work.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
For reference, I did try changing the first query to substitute ClaimNo
with
Claim_System_Key to see if this worked but I still got the sql error as
mentioned
below...

Hi John

Thanks very much for this!!! I have the first query working but the 2nd
errors with 'the specified field [tbl Warranty Data].Primarykey could
refer to more than one table listed in the FROM clause of your SQL
statement'. Having said that, I checked the key again on the tables (it
isn't my database I am being asked to work on) and the primary key of
the Cost table is actually keyed on a unique claim key and not the
countrycode + claimno (called [Claim_System_Key]). So it isn't the
primary key I need to use in this case.. Sorry for the confusion!
Cheers, Sue


I would try the following UNTESTED SQL

First a query to identify the record in the VAT table that you need

SELECT CostTable.ClaimNo
, CostTable.CountryCode
, Max(VV.EffectiveDate) as Effective
FROM CostTable INNER JOIN [tbl Vat Rates] as VV
ON CostTable.CountryCode = VV.CountryCode
AND CostTable.ActivityDate >= VV.EffectiveDate
GROUP BY CostTable.ClaimNo, CostTable.CountryCode

Now use that saved query in a join

SELECT CostTable.*
, V.VATRate
FROM (CostTable INNER JOIN [SavedQueryName] as Q
ON CostTable.PrimaryKey = Q.PrimaryKey)
INNER JOIN [tbl Vat Rates] as V
ON Q.CountryCode = V.CountryCode
AND Q.Effective = V.EffectiveDate


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Sue wrote:
Hi all

I have a table holding data over the last twelve months, with a parts
and labour total per item. The table holds data for all European
countries and each record has a CountryCode field and ClaimNo as its
key. These totals include VAT, I need to calculate a NET total in a
query for analysis. The VAT rates are held in a table called [tbl VAT
Rates]. This has 3 fields: CountryCode (same as totals data table),
EffectiveDate and VATRate. There can be more than one record per
country, it is keyed on Country and EffectiveDate. I need to calculate
the VAT based on a payment date in the totals table.

For example I might have a record for country UK paid on 31/03/10, the
VAT table has two records for the UK - one Effective Date of 01/01/01
as 15% and another of 01/02/10 with 0% (ie no VAT charged). In this
example it would need to use the 0% value to calculate the NET total
of labour and parts.

Can I achieve this in a query? I have tried playing with dlookup, but
the problem is I think it needs to return the Max of EffectiveDate,
which is less than the paymentdate - and my brain is just not
working!!! Can anyone help please?
Click to expand...
Click to expand...
Click to expand...
 
Try this --
SELECT CostTable.ClaimNo, CostTable.CountryCode, ActivityDate, (SELECT [tbl
Vat Rates].[VATRate] FROM [tbl Vat Rates] WHERE [tbl Vat Rates].EffectiveDate
<= CostTable.ActivityDate AND [tbl Vat Rates].[CountryCode] =
CostTable.CountryCode ORDER BY [tbl Vat Rates].EffectiveDate DESC) AS
[EffectVATRate]
FROM CostTable;
 
I think you will need the two query solution and a left join with an IIF
statement to return 0 (zero) when [tbl Vat Rates].[Franchise Code] is null.

--
Build a little, test a little.


Sue said:
I saved the query and opened it again to do more testing and this time when
scrolling through the records a MS Access error occurs. It gives me a msgbox
stating 'At most one record can be returned by this subquery' and all the
fields suddenly show #Name. The sql is the same, just changed field & table
names as necessary:

SELECT [tbl Warranty Data].[Claim No], [tbl Warranty Data].[Franchise Code],
[tbl Warranty Data].[Self Billing Date], (SELECT [tbl Vat Rates].[VAT_Rate]
FROM [tbl Vat Rates] WHERE [tbl Vat Rates].Effective_Date
<= [tbl Warranty Data].[Self Billing Date] AND [tbl Vat Rates].[Franchise
Code] =
[tbl Warranty Data].[Franchise Code] ORDER BY [tbl Vat
Rates].Effective_Date DESC) AS EffectVATRate
FROM [tbl Warranty Data];



Sue said:
Thanks Karl that's great :-). It seems to work although I haven't fully
tested it yet. Just one problem with it I have found so far - for the
dates that need to return a 0% VAT rate it displays #Error in the
EffectVatRate field?

Also, if possible I would like to display 0 by default if no vat rate
found (or I will have to enter all countries in the VAT Rate table with 0%
where they currently do not have a date record).

Thanks again... most appreciated.


KARL DEWEY said:
Try this --
SELECT CostTable.ClaimNo, CostTable.CountryCode, ActivityDate, (SELECT
[tbl
Vat Rates].[VATRate] FROM [tbl Vat Rates] WHERE [tbl Vat
Rates].EffectiveDate
<= CostTable.ActivityDate AND [tbl Vat Rates].[CountryCode] =
CostTable.CountryCode ORDER BY [tbl Vat Rates].EffectiveDate DESC) AS
[EffectVATRate]
FROM CostTable;

--
Build a little, test a little.


:

Hi all

I have a table holding data over the last twelve months, with a parts
and
labour total per item. The table holds data for all European countries
and
each record has a CountryCode field and ClaimNo as its key. These totals
include VAT, I need to calculate a NET total in a query for analysis.
The
VAT rates are held in a table called [tbl VAT Rates]. This has 3 fields:
CountryCode (same as totals data table), EffectiveDate and VATRate.
There
can be more than one record per country, it is keyed on Country and
EffectiveDate. I need to calculate the VAT based on a payment date in
the
totals table.

For example I might have a record for country UK paid on 31/03/10, the
VAT
table has two records for the UK - one Effective Date of 01/01/01 as 15%
and
another of 01/02/10 with 0% (ie no VAT charged). In this example it
would
need to use the 0% value to calculate the NET total of labour and parts.

Can I achieve this in a query? I have tried playing with dlookup, but
the
problem is I think it needs to return the Max of EffectiveDate, which is
less than the paymentdate - and my brain is just not working!!! Can
anyone
help please?


.
Click to expand...
Click to expand...


.
Click to expand...
 
Good to hear it's working for you. I've been thinking about this and I did
it with three queries. In the first I asked for payment dates greater than
effective dates, and added a column that calculates the difference between
the payment date and the effective date. In the second query I asked for the
claim numbers qith the payment date and minimum difference between payment
date and effective date. In the third I joined queries 1 and 2 on claim
number and difference/min_of_diff and added fields to calculate the vat.

~Rebecca

Sue said:
Thanks Karl, sorted it now using the 2 query method and if statement etc...

KARL DEWEY said:
I think you will need the two query solution and a left join with an IIF
statement to return 0 (zero) when [tbl Vat Rates].[Franchise Code] is
null.

--
Build a little, test a little.


Sue said:
I saved the query and opened it again to do more testing and this time
when
scrolling through the records a MS Access error occurs. It gives me a
msgbox
stating 'At most one record can be returned by this subquery' and all the
fields suddenly show #Name. The sql is the same, just changed field &
table
names as necessary:

SELECT [tbl Warranty Data].[Claim No], [tbl Warranty Data].[Franchise
Code],
[tbl Warranty Data].[Self Billing Date], (SELECT [tbl Vat
Rates].[VAT_Rate]
FROM [tbl Vat Rates] WHERE [tbl Vat Rates].Effective_Date
<= [tbl Warranty Data].[Self Billing Date] AND [tbl Vat
Rates].[Franchise
Code] =
[tbl Warranty Data].[Franchise Code] ORDER BY [tbl Vat
Rates].Effective_Date DESC) AS EffectVATRate
FROM [tbl Warranty Data];



"Sue" < wrote in message
Thanks Karl that's great :-). It seems to work although I haven't fully
tested it yet. Just one problem with it I have found so far - for the
dates that need to return a 0% VAT rate it displays #Error in the
EffectVatRate field?

Also, if possible I would like to display 0 by default if no vat rate
found (or I will have to enter all countries in the VAT Rate table with
0%
where they currently do not have a date record).

Thanks again... most appreciated.


Try this --
SELECT CostTable.ClaimNo, CostTable.CountryCode, ActivityDate, (SELECT
[tbl
Vat Rates].[VATRate] FROM [tbl Vat Rates] WHERE [tbl Vat
Rates].EffectiveDate
<= CostTable.ActivityDate AND [tbl Vat Rates].[CountryCode] =
CostTable.CountryCode ORDER BY [tbl Vat Rates].EffectiveDate DESC) AS
[EffectVATRate]
FROM CostTable;

--
Build a little, test a little.


:

Hi all

I have a table holding data over the last twelve months, with a parts
and
labour total per item. The table holds data for all European
countries
and
each record has a CountryCode field and ClaimNo as its key. These
totals
include VAT, I need to calculate a NET total in a query for analysis.
The
VAT rates are held in a table called [tbl VAT Rates]. This has 3
fields:
CountryCode (same as totals data table), EffectiveDate and VATRate.
There
can be more than one record per country, it is keyed on Country and
EffectiveDate. I need to calculate the VAT based on a payment date in
the
totals table.

For example I might have a record for country UK paid on 31/03/10,
the
VAT
table has two records for the UK - one Effective Date of 01/01/01 as
15%
and
another of 01/02/10 with 0% (ie no VAT charged). In this example it
would
need to use the 0% value to calculate the NET total of labour and
parts.

Can I achieve this in a query? I have tried playing with dlookup, but
the
problem is I think it needs to return the Max of EffectiveDate, which
is
less than the paymentdate - and my brain is just not working!!! Can
anyone
help please?


.





.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top