|
|
I've created a table called country with fields: CountryID (PK), CountryName.
For each Country I've created a separate schedule table with fields:
CountryID (FK), Low, High, Base, TaxRate. There are btw 4 to 5 rows of data
for each country's shedule table. In relationship mgr, I've linked each
country's schedule table to the Country table via the CountryID field. My
problem: The Country table subdata sheet only displays the first country's
schedule table I select in this case Canada. When I click on the plus sign
in front of every other country in the Country table, its corresponding table
pops up but without the date just 0's in one row of data. What's wrong. Is
there a better way to construct my tables?
|
|
On Fri, 26 Dec 2008 11:18:00 -0800, Evan <Evan[ at ]discussions.microsoft.com>
wrote:
[Quoted Text] >I've created a table called country with fields: CountryID (PK), CountryName.
> For each Country I've created a separate schedule table with fields:
>CountryID (FK), Low, High, Base, TaxRate. There are btw 4 to 5 rows of data
>for each country's shedule table. In relationship mgr, I've linked each
>country's schedule table to the Country table via the CountryID field. My
>problem: The Country table subdata sheet only displays the first country's
>schedule table I select in this case Canada. When I click on the plus sign
>in front of every other country in the Country table, its corresponding table
>pops up but without the date just 0's in one row of data. What's wrong. Is
>there a better way to construct my tables?
Your tables look OK but you'll probably need to tweak your Query. I would NOT
recommend trying to use subdatasheets for this purpose! They're quite limited.
If you have a separate schedule (shedule??) table for each country, you're on
the wrong track entirely. What are the other tables here???
--
John W. Vinson [MVP]
|
|
The separate schedules = tax schedules for each country such as here. So,
Fields Low & High represent an income range. Not sure how to do it other
than this. But, is there a better way to do this?
CountryID Low High Base TaxRate
17 0 8025 0 0.1
17 8025 32550 802.5 0.15
17 32550 78850 4481.25 0.25
17 78850 164550 16056.25 0.28
17 164550 357700 40052.25 0.33
17 357700 1000000000 103792.75 0.35
"John W. Vinson" wrote:
[Quoted Text] > On Fri, 26 Dec 2008 11:18:00 -0800, Evan <Evan[ at ]discussions.microsoft.com>
> wrote:
>
> >I've created a table called country with fields: CountryID (PK), CountryName.
> > For each Country I've created a separate schedule table with fields:
> >CountryID (FK), Low, High, Base, TaxRate. There are btw 4 to 5 rows of data
> >for each country's shedule table. In relationship mgr, I've linked each
> >country's schedule table to the Country table via the CountryID field. My
> >problem: The Country table subdata sheet only displays the first country's
> >schedule table I select in this case Canada. When I click on the plus sign
> >in front of every other country in the Country table, its corresponding table
> >pops up but without the date just 0's in one row of data. What's wrong. Is
> >there a better way to construct my tables?
>
> Your tables look OK but you'll probably need to tweak your Query. I would NOT
> recommend trying to use subdatasheets for this purpose! They're quite limited.
>
> If you have a separate schedule (shedule??) table for each country, you're on
> the wrong track entirely. What are the other tables here???
> --
>
> John W. Vinson [MVP]
>
|
|
On Mon, 29 Dec 2008 16:08:04 -0800, Evan <Evan[ at ]discussions.microsoft.com>
wrote:
[Quoted Text] >The separate schedules = tax schedules for each country such as here. So,
>Fields Low & High represent an income range. Not sure how to do it other
>than this. But, is there a better way to do this?
>
>CountryID Low High Base TaxRate
>17 0 8025 0 0.1
>17 8025 32550 802.5 0.15
>17 32550 78850 4481.25 0.25
>17 78850 164550 16056.25 0.28
>17 164550 357700 40052.25 0.33
>17 357700 1000000000 103792.75 0.35
It depends on what "this" is. What's the table that you're linking to? How are
you linking?
You will NOT be able to do this with a table datasheet or subdatasheet; these
tools are very limited. You'll need - at least - a "non equi join" query
joining by CountryID and by a term like
Income >= [Low] AND Income < [High]
but without knowing more about the structure of your tables I can't be
specific.
--
John W. Vinson [MVP]
|
|
All the CountryTaxSchedule tables are joined using "union all" in this query:
SELECT CountryName
FROM Country
UNION ALL
SELECT CountryID, Low, High, Base, TaxRate
FROM [BRFdTxSch]
ORDER BY CountryID, Low
SELECT CountryID, Low, High, Base, TaxRate
FROM .......
I get an error message by adding the fldCountryName from tblCountry: The
number of columns in the two selected tables or queries of a union query do
not match.
My goal is to take some Income level as a criteria in the query that will
show each country's tax rate and tax amount on that income level.
"John W. Vinson" wrote:
[Quoted Text] > On Mon, 29 Dec 2008 16:08:04 -0800, Evan <Evan[ at ]discussions.microsoft.com>
> wrote:
>
> >The separate schedules = tax schedules for each country such as here. So,
> >Fields Low & High represent an income range. Not sure how to do it other
> >than this. But, is there a better way to do this?
> >
> >CountryID Low High Base TaxRate
> >17 0 8025 0 0.1
> >17 8025 32550 802.5 0.15
> >17 32550 78850 4481.25 0.25
> >17 78850 164550 16056.25 0.28
> >17 164550 357700 40052.25 0.33
> >17 357700 1000000000 103792.75 0.35
>
> It depends on what "this" is. What's the table that you're linking to? How are
> you linking?
>
> You will NOT be able to do this with a table datasheet or subdatasheet; these
> tools are very limited. You'll need - at least - a "non equi join" query
> joining by CountryID and by a term like
>
> Income >= [Low] AND Income < [High]
>
> but without knowing more about the structure of your tables I can't be
> specific.
> --
>
> John W. Vinson [MVP]
>
|
|
On Tue, 30 Dec 2008 09:16:05 -0800, Evan <Evan[ at ]discussions.microsoft.com>
wrote:
[Quoted Text] > All the CountryTaxSchedule tables are joined using "union all" in this query:
>
>SELECT CountryName
>FROM Country
>UNION ALL
>SELECT CountryID, Low, High, Base, TaxRate
>FROM [BRFdTxSch]
>ORDER BY CountryID, Low
>SELECT CountryID, Low, High, Base, TaxRate
>FROM .......
>
>I get an error message by adding the fldCountryName from tblCountry: The
>number of columns in the two selected tables or queries of a union query do
>not match.
>
>My goal is to take some Income level as a criteria in the query that will
>show each country's tax rate and tax amount on that income level.
A UNION query "stacks" to sets of data, top to bottom. It sounds like you want
to join the two side by side. Assuming that there is a CountryID primary key
in the Country table, try
SELECT Country.CountryName, [BRFdTxSch].Low, [BRFdTxSch].High,
[BRFdTxSch].Base, [BRFdTxSch].TaxRate
FROM [BRFdTxSch] INNER JOIN Country
ON [BRFdTxSch].CountryID = Country.CountryID
WHERE [Low] <= [Enter income:] AND High > [Enter income:];
--
John W. Vinson [MVP]
|
|
|