How to properly link the tables?

[Aaron]

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

 

[Pat Hartman \(MVP\)]

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]

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!

 

[Aaron]

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\)]

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%

 

[Pat Hartman \(MVP\)]

"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.