If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Table Design
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? |
#2
|
|||
|
|||
Table Design
On Fri, 26 Dec 2008 11:18:00 -0800, Evan
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] |
#3
|
|||
|
|||
Table Design
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: On Fri, 26 Dec 2008 11:18:00 -0800, Evan 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] |
#4
|
|||
|
|||
Table Design
On Mon, 29 Dec 2008 16:08:04 -0800, Evan
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] |
#5
|
|||
|
|||
Table Design
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: On Mon, 29 Dec 2008 16:08:04 -0800, Evan 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] |
#6
|
|||
|
|||
Table Design
On Tue, 30 Dec 2008 09:16:05 -0800, Evan
wrote: 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] |
Thread Tools | |
Display Modes | |
|
|