469,898 Members | 1,503 Online

# calculations from data tables

Hi,

I had sent this earlier, and would appreciate any suggestions on this.

I need to make calculations for unemployment rate for three different
data sources (A,B,C) for many countries and age groupings. The
calculation is:

unemployment rate = number of unemployed/number of labour force

These are the data tables I have in Access:

Table 1 (data on number of unemployed as reported by source A)
Table 2 (data on number of people in the labour force as reported by
source A)
Table 3 (data on number of unemployed as reported by source B)
Table 4 (data on number of people in the labour force as reported by
source B)
Table 5 (data on unemployment rate as reported by source C)

Each table is basically the following format:

country year agegroup indicator measurement value

Here the field "indicator" can either be unemployment or labour force
depending on the table.
The field "measurement" can be number or rate depending on the table.
Value is the actual absolute number if measurement is number or a
percentage if measurement is rate.

The labour force data and the unemployment data have corresponding age
groups for different countries and years. Each data source (A and B)
have their own values for the same age groups for different countries
and years. So I'd like to compare the difference in calculated
unemployment rates between data source A and B.

I hope this hasn't been too complicated. Could you clarify the best way
to create the query in order to generate
a new table in which the unemployment rate has been calculated for each
data source for corresponding country, year and agegroups.

Thanks, and looking forward to hearing from you.

Brian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #1
3 2364
brian kaufmann wrote:
Hi,

I had sent this earlier, and would appreciate any suggestions on this.

I need to make calculations for unemployment rate for three different
data sources (A,B,C) for many countries and age groupings. The
calculation is:

unemployment rate = number of unemployed/number of labour force

These are the data tables I have in Access:

Table 1 (data on number of unemployed as reported by source A)
Table 2 (data on number of people in the labour force as reported by
source A)
Table 3 (data on number of unemployed as reported by source B)
Table 4 (data on number of people in the labour force as reported by
source B)
Table 5 (data on unemployment rate as reported by source C)

Each table is basically the following format:

country year agegroup indicator measurement value

Here the field "indicator" can either be unemployment or labour force
depending on the table.
The field "measurement" can be number or rate depending on the table.
Value is the actual absolute number if measurement is number or a
percentage if measurement is rate.

The labour force data and the unemployment data have corresponding age
groups for different countries and years. Each data source (A and B)
have their own values for the same age groups for different countries
and years. So I'd like to compare the difference in calculated
unemployment rates between data source A and B.

I hope this hasn't been too complicated. Could you clarify the best way
to create the query in order to generate
a new table in which the unemployment rate has been calculated for each
data source for corresponding country, year and agegroups.

Thanks, and looking forward to hearing from you.

Brian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

This seems to be an easy problem to solve. So I know I don't fully
understand it.

First, I might create a union query that selects all countries, year,
and age groups from all sources. That way you have all records counted.

That would be the main "table" in the next query. Create a new query
and select the above group query. Add Tables 1-5 (if all of them are

Draw lines from the Country, year, and age group fields in the main
table to all of the other tables. Now click ALL the link lines and
change it from where only records that match are selected to All records
in the main query and any matching records in that table.

Since there may be records that are missing (ex: You have Country USA,
Year 2004, agegroup 18to24 in Table1 but there is no corresponding match
in table2. The value you extract from Table2 will be Null. So you want
to create you columns with numbers by using the NZ() function. Ex:
NumUnemployed : NZ(Table2.NumberField,0)

Now drag all the fields from the respective tables that will be used in

Now you can use this query in a form to present the date by making the
fields on the form calculations or you can create another query from the
second query defined above and do the calculations from there.
Nov 13 '05 #2

Thanks for the suggestions and description. I will try it out.

Brian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #3

This was from a while back, you had made some suggestions on how to go
about doing calculations from data tables.

I must admit I'm very new to Access. Just clarifying, first step you
said was to make a union query. I know this is basic, but how would you
go about making this union query - through the design way or is there an
sql code or another way?

Thanks.

Brian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #4

### This discussion thread is closed

Replies have been disabled for this discussion.