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
|
|||
|
|||
How to properly link the tables?
Hello everyone I'm Aaron, I've recently encountered a question in applying
the Access into my job; I've built a small database containing 4 tables,1 table is devided up by time/country/company category, another 2 are broken up into time/country, and a fourth table tracking demographic data of different countries is built up like time/country/population ages; The database is static, and sample data looks like: 1st type:1995/United States/Automobiles/var1/var2/var3 2000/Canada/Food Solutions/var1/var2/var3 2nd type:1996/United States/var4/var5/var6 2010/India/var4/var5/var6 3rd type:2000/United Kingdom/age30-45/var7/var8/var9 2007/France/age65+/var7/var8/var9 My problem is 1st. How to bind these variables to a common criteria and define their relationship? My current method is to setup a new variable binding year and country together, creating like 2010India and link these together, thus it will result 2 one-many relationships; 2nd. Based on the up mentioned method I do queries when encountered problems; Actually my boss would like me to generate a standard database table looking at time/country/selected categories' customed variables, including summing these up or looking at them differently; I think the way is to move the "company category" or the "age break" up to the table header; I've got really frustrated how to connect these variables and display them in a clean database table instead of looking at pivot tables; So I wonder if anyone can help me out either on the database linking/design or the queries; Great appreciates! 2 samples for the BOSS needs: 1st. Aggregation: 1996/United States/var1 aggregation/var2 aggregation/var4/var8 aggregation 2nd.Display company category seperately: 2000/France/Automobile var1/Food Solutions var2/var6/age25-30 var9 |
#2
|
|||
|
|||
How to properly link the tables?
I'm working on a similar problem. The var's should be rows rather than
columns. That way you will have no trouble running queries and you will have no application changes if you need to add new variables. The table should be: DataYear CountryName (ex. US, Canada, India, UK, etc.) DataCategory (ex. Auto, Food, Age 30-40) VariableName (ex. Make, Model, Rice, GradSchool, Sex, etc.) VariableValue (ex. Honda, Accord, Yes, No, Female, etc.) The first four fields would comprise the primary key. "Aaron" wrote in message ... Hello everyone I'm Aaron, I've recently encountered a question in applying the Access into my job; I've built a small database containing 4 tables,1 table is devided up by time/country/company category, another 2 are broken up into time/country, and a fourth table tracking demographic data of different countries is built up like time/country/population ages; The database is static, and sample data looks like: 1st type:1995/United States/Automobiles/var1/var2/var3 2000/Canada/Food Solutions/var1/var2/var3 2nd type:1996/United States/var4/var5/var6 2010/India/var4/var5/var6 3rd type:2000/United Kingdom/age30-45/var7/var8/var9 2007/France/age65+/var7/var8/var9 My problem is 1st. How to bind these variables to a common criteria and define their relationship? My current method is to setup a new variable binding year and country together, creating like 2010India and link these together, thus it will result 2 one-many relationships; 2nd. Based on the up mentioned method I do queries when encountered problems; Actually my boss would like me to generate a standard database table looking at time/country/selected categories' customed variables, including summing these up or looking at them differently; I think the way is to move the "company category" or the "age break" up to the table header; I've got really frustrated how to connect these variables and display them in a clean database table instead of looking at pivot tables; So I wonder if anyone can help me out either on the database linking/design or the queries; Great appreciates! 2 samples for the BOSS needs: 1st. Aggregation: 1996/United States/var1 aggregation/var2 aggregation/var4/var8 aggregation 2nd.Display company category seperately: 2000/France/Automobile var1/Food Solutions var2/var6/age25-30 var9 |
#3
|
|||
|
|||
How to properly link the tables?
Hi Pat:
So nice of your reply, but I still got a little bit confused; I think you mean I flatten every data point and incorperate them into 1 common set; Questions: 1st. Do you mean I flatten each table, which means this will be like Year Country Datacategory VariableName VariableValue 1999 China Automobile MarketShare 95% 2000 Russia Foodsolutions MarketShare 90% 2006 UAE Electronics MarketSize 900MMUS$ 2nd.If it is excutable, question is 2 tables are not devided in terms of "Datacategory", then what would I fill in into that column?See e.g How am I supposed to fill in the question marks?Or not even include this column? e.g Year Country Datacategory VariableName VariableValue 1999 China ??? GDP 900MMUS$ 2000 Russia ??? Population 100MMPersons 2006 UAE ??? Households 900MMHouseholds 3rd.How am I suppose to connect all these datasheets together? Still using the Year + Country as an index? I've no idea on this; Hope you can share your view! Thanks very much on your previous reply enabling a entire view in database building! "Pat Hartman (MVP)" wrote: I'm working on a similar problem. The var's should be rows rather than columns. That way you will have no trouble running queries and you will have no application changes if you need to add new variables. The table should be: DataYear CountryName (ex. US, Canada, India, UK, etc.) DataCategory (ex. Auto, Food, Age 30-40) VariableName (ex. Make, Model, Rice, GradSchool, Sex, etc.) VariableValue (ex. Honda, Accord, Yes, No, Female, etc.) The first four fields would comprise the primary key. "Aaron" wrote in message ... Hello everyone I'm Aaron, I've recently encountered a question in applying the Access into my job; I've built a small database containing 4 tables,1 table is devided up by time/country/company category, another 2 are broken up into time/country, and a fourth table tracking demographic data of different countries is built up like time/country/population ages; The database is static, and sample data looks like: 1st type:1995/United States/Automobiles/var1/var2/var3 2000/Canada/Food Solutions/var1/var2/var3 2nd type:1996/United States/var4/var5/var6 2010/India/var4/var5/var6 3rd type:2000/United Kingdom/age30-45/var7/var8/var9 2007/France/age65+/var7/var8/var9 My problem is 1st. How to bind these variables to a common criteria and define their relationship? My current method is to setup a new variable binding year and country together, creating like 2010India and link these together, thus it will result 2 one-many relationships; 2nd. Based on the up mentioned method I do queries when encountered problems; Actually my boss would like me to generate a standard database table looking at time/country/selected categories' customed variables, including summing these up or looking at them differently; I think the way is to move the "company category" or the "age break" up to the table header; I've got really frustrated how to connect these variables and display them in a clean database table instead of looking at pivot tables; So I wonder if anyone can help me out either on the database linking/design or the queries; Great appreciates! 2 samples for the BOSS needs: 1st. Aggregation: 1996/United States/var1 aggregation/var2 aggregation/var4/var8 aggregation 2nd.Display company category seperately: 2000/France/Automobile var1/Food Solutions var2/var6/age25-30 var9 |
#4
|
|||
|
|||
How to properly link the tables?
Hi Pat:
1st You mean flatten everydata into the form Year Country Datacategory Variablename Variablevalue 1999 China Handset MarketShare 90% 2nd Acutally 2 tables of my data are not devided by 3 dimension(Year/Country/Datacategory) how am I going to implant these into our database, say, how am I going to fill in the question marks subscript: Year Country Datacategory Variablename Variablevalue 1999 China ??? GDP 100MMUS$ 3rd Understanding there are 4 sheets all together how am I going to set the relationship between these tables or I just put them into 1 table? Thanks so much on your previous inputs, very much looking foward to your reply! "Pat Hartman (MVP)" wrote: I'm working on a similar problem. The var's should be rows rather than columns. That way you will have no trouble running queries and you will have no application changes if you need to add new variables. The table should be: DataYear CountryName (ex. US, Canada, India, UK, etc.) DataCategory (ex. Auto, Food, Age 30-40) VariableName (ex. Make, Model, Rice, GradSchool, Sex, etc.) VariableValue (ex. Honda, Accord, Yes, No, Female, etc.) The first four fields would comprise the primary key. "Aaron" wrote in message ... Hello everyone I'm Aaron, I've recently encountered a question in applying the Access into my job; I've built a small database containing 4 tables,1 table is devided up by time/country/company category, another 2 are broken up into time/country, and a fourth table tracking demographic data of different countries is built up like time/country/population ages; The database is static, and sample data looks like: 1st type:1995/United States/Automobiles/var1/var2/var3 2000/Canada/Food Solutions/var1/var2/var3 2nd type:1996/United States/var4/var5/var6 2010/India/var4/var5/var6 3rd type:2000/United Kingdom/age30-45/var7/var8/var9 2007/France/age65+/var7/var8/var9 My problem is 1st. How to bind these variables to a common criteria and define their relationship? My current method is to setup a new variable binding year and country together, creating like 2010India and link these together, thus it will result 2 one-many relationships; 2nd. Based on the up mentioned method I do queries when encountered problems; Actually my boss would like me to generate a standard database table looking at time/country/selected categories' customed variables, including summing these up or looking at them differently; I think the way is to move the "company category" or the "age break" up to the table header; I've got really frustrated how to connect these variables and display them in a clean database table instead of looking at pivot tables; So I wonder if anyone can help me out either on the database linking/design or the queries; Great appreciates! 2 samples for the BOSS needs: 1st. Aggregation: 1996/United States/var1 aggregation/var2 aggregation/var4/var8 aggregation 2nd.Display company category seperately: 2000/France/Automobile var1/Food Solutions var2/var6/age25-30 var9 |
#5
|
|||
|
|||
How to properly link the tables?
I didn't flatten your table, I normalized it. The table was flat as you
described it. I would use "None" as the data category when your data does not break down by category. The alternate solution is to create a separate table. I don't think that buys you anything and will complicate your analysis if you want to include both sets of data in a report. "3rd.How am I suppose to connect all these datasheets together? Still using the Year + Country as an index? I've no idea on this;" - I don't understand this question. You can use crosstab queries to "flatten" the data in interesting ways. for example, you can use year as the column heading to see change over time: 1999 2000 2001 China Automobile 10% 11% 15% Russia Automobile 60% 62% 70% "Aaron" wrote in message ... Hi Pat: So nice of your reply, but I still got a little bit confused; I think you mean I flatten every data point and incorperate them into 1 common set; Questions: 1st. Do you mean I flatten each table, which means this will be like Year Country Datacategory VariableName VariableValue 1999 China Automobile MarketShare 95% 2000 Russia Foodsolutions MarketShare 90% 2006 UAE Electronics MarketSize 900MMUS$ 2nd.If it is excutable, question is 2 tables are not devided in terms of "Datacategory", then what would I fill in into that column?See e.g How am I supposed to fill in the question marks?Or not even include this column? e.g Year Country Datacategory VariableName VariableValue 1999 China ??? GDP 900MMUS$ 2000 Russia ??? Population 100MMPersons 2006 UAE ??? Households 900MMHouseholds 3rd.How am I suppose to connect all these datasheets together? Still using the Year + Country as an index? I've no idea on this; Hope you can share your view! Thanks very much on your previous reply enabling a entire view in database building! "Pat Hartman (MVP)" wrote: I'm working on a similar problem. The var's should be rows rather than columns. That way you will have no trouble running queries and you will have no application changes if you need to add new variables. The table should be: DataYear CountryName (ex. US, Canada, India, UK, etc.) DataCategory (ex. Auto, Food, Age 30-40) VariableName (ex. Make, Model, Rice, GradSchool, Sex, etc.) VariableValue (ex. Honda, Accord, Yes, No, Female, etc.) The first four fields would comprise the primary key. "Aaron" wrote in message ... Hello everyone I'm Aaron, I've recently encountered a question in applying the Access into my job; I've built a small database containing 4 tables,1 table is devided up by time/country/company category, another 2 are broken up into time/country, and a fourth table tracking demographic data of different countries is built up like time/country/population ages; The database is static, and sample data looks like: 1st type:1995/United States/Automobiles/var1/var2/var3 2000/Canada/Food Solutions/var1/var2/var3 2nd type:1996/United States/var4/var5/var6 2010/India/var4/var5/var6 3rd type:2000/United Kingdom/age30-45/var7/var8/var9 2007/France/age65+/var7/var8/var9 My problem is 1st. How to bind these variables to a common criteria and define their relationship? My current method is to setup a new variable binding year and country together, creating like 2010India and link these together, thus it will result 2 one-many relationships; 2nd. Based on the up mentioned method I do queries when encountered problems; Actually my boss would like me to generate a standard database table looking at time/country/selected categories' customed variables, including summing these up or looking at them differently; I think the way is to move the "company category" or the "age break" up to the table header; I've got really frustrated how to connect these variables and display them in a clean database table instead of looking at pivot tables; So I wonder if anyone can help me out either on the database linking/design or the queries; Great appreciates! 2 samples for the BOSS needs: 1st. Aggregation: 1996/United States/var1 aggregation/var2 aggregation/var4/var8 aggregation 2nd.Display company category seperately: 2000/France/Automobile var1/Food Solutions var2/var6/age25-30 var9 |
#6
|
|||
|
|||
How to properly link the tables?
"3rd Understanding there are 4 sheets all together how am I going to set the
relationship between these tables or I just put them into 1 table?" You should use a single table to make analyzing the data easier. "Aaron" wrote in message ... Hi Pat: 1st You mean flatten everydata into the form Year Country Datacategory Variablename Variablevalue 1999 China Handset MarketShare 90% 2nd Acutally 2 tables of my data are not devided by 3 dimension(Year/Country/Datacategory) how am I going to implant these into our database, say, how am I going to fill in the question marks subscript: Year Country Datacategory Variablename Variablevalue 1999 China ??? GDP 100MMUS$ 3rd Understanding there are 4 sheets all together how am I going to set the relationship between these tables or I just put them into 1 table? Thanks so much on your previous inputs, very much looking foward to your reply! "Pat Hartman (MVP)" wrote: I'm working on a similar problem. The var's should be rows rather than columns. That way you will have no trouble running queries and you will have no application changes if you need to add new variables. The table should be: DataYear CountryName (ex. US, Canada, India, UK, etc.) DataCategory (ex. Auto, Food, Age 30-40) VariableName (ex. Make, Model, Rice, GradSchool, Sex, etc.) VariableValue (ex. Honda, Accord, Yes, No, Female, etc.) The first four fields would comprise the primary key. "Aaron" wrote in message ... Hello everyone I'm Aaron, I've recently encountered a question in applying the Access into my job; I've built a small database containing 4 tables,1 table is devided up by time/country/company category, another 2 are broken up into time/country, and a fourth table tracking demographic data of different countries is built up like time/country/population ages; The database is static, and sample data looks like: 1st type:1995/United States/Automobiles/var1/var2/var3 2000/Canada/Food Solutions/var1/var2/var3 2nd type:1996/United States/var4/var5/var6 2010/India/var4/var5/var6 3rd type:2000/United Kingdom/age30-45/var7/var8/var9 2007/France/age65+/var7/var8/var9 My problem is 1st. How to bind these variables to a common criteria and define their relationship? My current method is to setup a new variable binding year and country together, creating like 2010India and link these together, thus it will result 2 one-many relationships; 2nd. Based on the up mentioned method I do queries when encountered problems; Actually my boss would like me to generate a standard database table looking at time/country/selected categories' customed variables, including summing these up or looking at them differently; I think the way is to move the "company category" or the "age break" up to the table header; I've got really frustrated how to connect these variables and display them in a clean database table instead of looking at pivot tables; So I wonder if anyone can help me out either on the database linking/design or the queries; Great appreciates! 2 samples for the BOSS needs: 1st. Aggregation: 1996/United States/var1 aggregation/var2 aggregation/var4/var8 aggregation 2nd.Display company category seperately: 2000/France/Automobile var1/Food Solutions var2/var6/age25-30 var9 |
Thread Tools | |
Display Modes | |
|
|