Table Design

[Evan]

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?

 

[John W. Vinson]

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???

 

[Evan]

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]

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.

 

[Evan]

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.

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]

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:];