Re: 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" <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




.

Loading