Re: 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" <Aaron@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C9230DBB-B948-4393-AB1A-69ECDC8553AD@xxxxxxxxxxxxxxxx
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" <Aaron@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6A005CAF-075C-447C-9704-E0382D137A83@xxxxxxxxxxxxxxxx
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






.