add a field in a query for ALL observations, where only the value of the field is available for only

[zia]

Hi
I have two tables. Table 1 has say HHID 1 to 1000 (1000 rows) and
different fields. Table 2 has only intermittent HHIDs say 456, 12,
23..and 100 such HHID (so it has 100 rows) and a field, say field1. I
want to merge table 2 to table 1 with HHID's being the common field.
However, a query results in only those HHIDs that are common to both.
But I would want all 1000 HHIDs (with its fields) to be present and
field1 to be added to table 1(linked via HHIDs), if possible with 0
values for the households that are not common to both tables.
Thank you in advance.
Merry Christmas,
Zia

 

['69 Camaro]

Hi, Zia.

I would want all 1000 HHIDs (with its fields) to be present and
field1 to be added to table 1(linked via HHIDs), if possible with 0
values for the households that are not common to both tables.

If I understand you correctly, you want to physically merge the data so that all
the data in Table 2 is also in Table 1. If you only want to create a query that
shows the data in both tables, please let me know, but I'll assume you want
merged data in this example.

First, make a backup of Table 1 just in case something goes wrong. In this
example, I'm going to use tblHHID_Base as the name for Table 1 and tblHHID_New
as Table 2.

Add a column to the first table manually or do it with a DDL query, such as the
following:

ALTER TABLE tblHHID_Base
ADD COLUMN Field1 Long;

Next, create a new query and run it:

UPDATE tblHHID_Base AS BL LEFT JOIN
tblHHID_New AS N ON BL.HHID = N.HHID
SET BL.Field1 = IIF(ISNULL(N.Field1), 0, N.Field1);

This will update all the records in tblHHID_Base and put the matching value from
tblHHID_New (the second table) in Field1, and put a zero if there's no matching
record in tblHHID_New. However, if tblHHID_New has any records with values for
HHID that is outside of the range in tblHHID_Base (the first table), say 1001,
then this query won't add the new records. Do you have any such records? If
so, I'll show you how to create a query for those, too.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[zia]

Hi Gunny
Many Thanks for the quick reply, yes I was trying to merge the two
sets.
Another qustion, when I am running a query by GROUP BY, i want harmonic
mean, instead of regular average of those variables. Is it doable in
access?
Cheers,
Zia

 

['69 Camaro]

Hi, Zia.

You're welcome.

Another qustion, when I am running a query by GROUP BY, i want harmonic
mean, instead of regular average of those variables. Is it doable in
access?

Not with the built-in aggregate function AVG( ). When using GROUP BY, the
column with the AVG( ) aggregate function will give the arithmetic mean of that
group for that column. For example, if you have two rows for the same group
(State = 'Texas'), where one value is 2 in Col1 and the other row is 4 in Col1,
then the AVG(Col1) will result in 3, the arithmetic mean of all rows in the
group 'Texas.'

If you want to use a different formula for the mean value of a group, you must
write your own.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[zia]

Hey Gunny
Thanks again. However, I am afraid you'll have to tell me how to write
my own function/formula for harmonic mean! I have started using access
only 3 days. Please bear in mind that the group by may have between 1
to 10 observations in each group (not a constant number).
Cheers,
Zia

 

[Smartin]

Hey Gunny
Thanks again. However, I am afraid you'll have to tell me how to write
my own function/formula for harmonic mean! I have started using access
only 3 days. Please bear in mind that the group by may have between 1
to 10 observations in each group (not a constant number).
Cheers,
Zia

This seems like a kludge to me but it works (^:

First, insert the following code into a standard module:

' --------- code begin
Public Function HMean(InputSelect As String) As Double
' Computes the harmonic mean of a set of numbers
' Parameter InputSelect is the SQL SELECT statement that
' returns the values to be evaluated
Dim A As Double ' accumulator
Dim N As Long ' counter
Dim Irs As DAO.Recordset

Set Irs = DBEngine(0)(0).OpenRecordset(InputSelect)
Irs.MoveFirst
Do While Not Irs.EOF
If Not IsNull(Irs.Fields(0).Value) Then
A = A + 1 / Irs.Fields(0).Value
N = N + 1
End If
Irs.MoveNext
Loop
HMean = N / A
Irs.Close
Set Irs = Nothing
End Function
' --------- code end

Next fashion a query that selects the values over which you wish to
calculate the Harmonic Mean. Make sure the values are in the first column.

SELECT MyNumbers FROM MyTable;

Next fashion a query that includes the above query as the parameter to
the HMean function:

SELECT HMean('SELECT MyNumbers FROM MyTable;');

Et viola, the trick she is done.

I didn't build any error checking into the function, but at least it
will skip null fields.

HTH

 

['69 Camaro]

Hi, Zia.

you'll have to tell me how to write
my own function/formula for harmonic mean!

Really? I have to? Where's that rule written? I don't see it listed in the
Ten Commandments, the Bill Of Rights, or even on my engine light checklist in my
car's owner's manual. Did I not get the memo?

I have started using access
only 3 days.

Then you shouldn't be jumping into tasks at the expert level this early in your
career. You should get some training in Access so that you can design and
develop your Access database application correctly from the start. Please see
the following Web page for an excellent list of resources available:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

['69 Camaro]

Hi, Smartin.

I don't mean to be nitpicky, but the following was the requirement for the
harmonic mean calculation function:

And most of the harmonic mean formulas I've seen had some additional
restrictions as well.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Gary Walter]

Many Thanks for the quick reply, yes I was trying to merge the two
sets.
Another qustion, when I am running a query by GROUP BY, i want harmonic
mean, instead of regular average of those variables. Is it doable in
access?

PMFBI

here be what I think I know....

harmonic mean is useful as an avg of RATES

it is the reciprocal of the arithmetic mean
of the reciprocals

1.0/(mean(1.0/[numfield]))

or, in SQL language

COUNT([numfield])/SUM(1.0/[numfield])

by ""putting" the field in the denominator,
large "extremes" tend to have little effect,

but, if [numfield]=0, division-by-zero error!!

so if you were thinking of finding the harmonic
mean of the field where you just made a bunch
of them 0, think again why you want this harmonic
mean.....

because you are just going to have to eliminate
them in the calculation (untested)

SELECT
somefield,
COUNT([numfield])/SUM(1.0/[numfield]) AS HM
WHERE
[numfield]<>0
GROUP BY
somefield;

BTW, above WHERE clause will ignore any
[numfield] that is 0 or NULL, ie.,

NULL<>0 will "evaluate" to FALSE as far as
what records are returned in each group...

 

[Gary Walter]

{sorry, forgot FROM clause...}

SELECT
somefield,
COUNT([numfield])/SUM(1.0/[numfield]) AS HM
FROM sometable
WHERE
[numfield]<>0
GROUP BY
somefield;

Many Thanks for the quick reply, yes I was trying to merge the two
sets.
Another qustion, when I am running a query by GROUP BY, i want harmonic
mean, instead of regular average of those variables. Is it doable in
access?

PMFBI

here be what I think I know....

harmonic mean is useful as an avg of RATES

it is the reciprocal of the arithmetic mean
of the reciprocals

1.0/(mean(1.0/[numfield]))

or, in SQL language

COUNT([numfield])/SUM(1.0/[numfield])

by ""putting" the field in the denominator,
large "extremes" tend to have little effect,

but, if [numfield]=0, division-by-zero error!!

so if you were thinking of finding the harmonic
mean of the field where you just made a bunch
of them 0, think again why you want this harmonic
mean.....

because you are just going to have to eliminate
them in the calculation (untested)

SELECT
somefield,
COUNT([numfield])/SUM(1.0/[numfield]) AS HM
WHERE
[numfield]<>0
GROUP BY
somefield;

BTW, above WHERE clause will ignore any
[numfield] that is 0 or NULL, ie.,

NULL<>0 will "evaluate" to FALSE as far as
what records are returned in each group...

 

[zia]

Hi all
Thank you so much for your suggestions. Do not have any zero values, so
it's fine!
Gunny, apologies if my previous email sounded rude. English is not my
first language, and sometimes my emails do not sound as I intended them
to.
Cheers all
Zia

 

[Smartin]

Hi, Smartin.

I don't mean to be nitpicky, but the following was the requirement for the
harmonic mean calculation function:

Duly noted. 'Twas an exercise; no guarantee of fitness for any
particular purpose is made. Hence the closing "HTH". I have never tried
to code an aggregate function before. Perhaps someone will find it a
useful starting point.

And most of the harmonic mean formulas I've seen had some additional
restrictions as well.

The definition of "harmonic mean" on which I based my code on may be
found at http://mathworld.wolfram.com/HarmonicMean.html . The only
glaring omission I can see is I have not accounted for the possibility
of a zero in the set. The code would be pretty easily adjusted for this
though.

HTH.
Gunny

Regards,

 

['69 Camaro]

Hi, Smartin.

'Twas an exercise; no guarantee of fitness for any particular purpose is made.

Agreed. Unfortunately, many folks use the newsgroups for copy/pasting code, but
if there is any error in the code they have no way of knowing unless they have
the math and programming skills themselves. It's a catch-22, since if they had
the skills, they wouldn't have copy/pasted someone else's code.

Perhaps someone will find it a useful starting point.

It's a good starting point, but folks may not realize it's only the starting
point, and use it as is. That's why I wanted you to look at your code with a
more critical eye, which you did.

The only glaring omission I can see is I have not accounted for the
possibility of a zero in the set.

There's more than one formula for harmonic mean. None of the values can be
zero, because the values are used in the denominator of the equation. However,
most common uses of harmonic mean require only positive real numbers in the data
set. Consider what the result would be if there were any negative real numbers
in the data set.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Smartin]

Hi, Smartin.


Agreed. Unfortunately, many folks use the newsgroups for copy/pasting code, but
if there is any error in the code they have no way of knowing unless they have
the math and programming skills themselves. It's a catch-22, since if they had
the skills, they wouldn't have copy/pasted someone else's code.


It's a good starting point, but folks may not realize it's only the starting
point, and use it as is. That's why I wanted you to look at your code with a
more critical eye, which you did.


There's more than one formula for harmonic mean. None of the values can be
zero, because the values are used in the denominator of the equation. However,
most common uses of harmonic mean require only positive real numbers in the data
set. Consider what the result would be if there were any negative real numbers
in the data set.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

Your points are well taken, Gunny. Presumably, the OP has a good grasp
of what She or He needs in terms of the data, but is on the steeper part
of the learning curve of coding knowledge. I did include a couple
disclaimers to discourage untested use (i.e., "kludge", "[no] error
checking") but perhaps I am being too technical.

I reckon I tend to operate on the premise that most readers are like
myself: inquisitive, a bit adventuresome, and willing to try something
even if it looks like gibberish in the hopes that it magically works.
When things work out we can study the example to figure out the /how/
and improve our understanding. I learn well through examples such as
this, and hope in turn my examples can serve others in the same capacity.

I certainly don't claim to be a pro though! I don't want to offer any
bad or misleading information, so I do appreciate constructive
criticism, including yours.

Now about this:

Consider what the result would be if there were
any negative real numbers in the data set.

Interesting... I regard the site I was referring to as an excellent
source of mathematical information yet it mentions nothing about the
domain of the members of the set. An innocent omission perhaps? Clearly
zero is invalid since we are adding reciprocals, but if we have, say, R
and -R as our set, then we will get:

1/H = 1/2 * (1/R + 1/-R)
1/H = 1/2 * 0
1/H = 0
uh oh...

I am hoping the OP knows of these pitfalls!

Thanks again,

 

['69 Camaro]

Hi, Zia.

Gunny, apologies if my previous email sounded rude.

Don't worry. I was laughing when I read it, so my response was tongue-in-cheek.
I could see you were already being helped, so I didn't interfere. Good luck on
working with and learning Access.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

['69 Camaro]

Hi, Smartin.

Presumably, the OP has a good grasp of what She or He needs in terms of the
data

Anyone I know who knows what the harmonic mean is also knows the formula, so I
wasn't worried about Zia. It's the others who "discover" this thread later and
copy/paste without the proper background knowledge of which data sets it's
appropriate for, or not appropriate for.

I did include a couple disclaimers to discourage untested use (i.e., "kludge",
"[no] error checking") but perhaps I am being too technical.

That's all one can do. Don't think you're being "too technical." This is a
technical newsgroup with certain expectations of the audience it attracts.

I reckon I tend to operate on the premise that most readers are like myself:
inquisitive, a bit adventuresome, and willing to try something even if it
looks like gibberish in the hopes that it magically works.

Many are, but the most common "browsers" in the newsgroups are folks looking for
simple shortcuts: ready-made code, like instant soup or frozen dinners. One
can only hope that they take a good look at it before they eat it.

I certainly don't claim to be a pro though! I don't want to offer any bad or
misleading information, so I do appreciate constructive criticism, including
yours.

Don't get me wrong. Your help is much appreciated. You had the right idea.
You just needed to take it to the next step.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

Hi, Smartin.


Agreed. Unfortunately, many folks use the newsgroups for copy/pasting code,
but if there is any error in the code they have no way of knowing unless they
have the math and programming skills themselves. It's a catch-22, since if
they had the skills, they wouldn't have copy/pasted someone else's code.


It's a good starting point, but folks may not realize it's only the starting
point, and use it as is. That's why I wanted you to look at your code with a
more critical eye, which you did.


There's more than one formula for harmonic mean. None of the values can be
zero, because the values are used in the denominator of the equation.
However, most common uses of harmonic mean require only positive real numbers
in the data set. Consider what the result would be if there were any
negative real numbers in the data set.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

Your points are well taken, Gunny. Presumably, the OP has a good grasp of what
She or He needs in terms of the data, but is on the steeper part of the
learning curve of coding knowledge. I did include a couple disclaimers to
discourage untested use (i.e., "kludge", "[no] error checking") but perhaps I
am being too technical.

I reckon I tend to operate on the premise that most readers are like myself:
inquisitive, a bit adventuresome, and willing to try something even if it
looks like gibberish in the hopes that it magically works. When things work
out we can study the example to figure out the /how/ and improve our
understanding. I learn well through examples such as this, and hope in turn my
examples can serve others in the same capacity.

I certainly don't claim to be a pro though! I don't want to offer any bad or
misleading information, so I do appreciate constructive criticism, including
yours.

Now about this:

Consider what the result would be if there were
any negative real numbers in the data set.

Interesting... I regard the site I was referring to as an excellent source of
mathematical information yet it mentions nothing about the domain of the
members of the set. An innocent omission perhaps? Clearly zero is invalid
since we are adding reciprocals, but if we have, say, R and -R as our set,
then we will get:

1/H = 1/2 * (1/R + 1/-R)
1/H = 1/2 * 0
1/H = 0
uh oh...

I am hoping the OP knows of these pitfalls!

Thanks again,

 

['69 Camaro]

Hi, Smartin.

I regard the site I was referring to as an excellent source of mathematical
information yet it mentions nothing about the domain of the members of the
set. An innocent omission perhaps?

I've only ever used harmonic mean where the domain requirement was positive real
numbers. I don't see this domain restriction listed in some of the definitions
of the formula, so theoretically someone uses it without this restriction. I
used it for mass (grams) and rates of speed, so only positive real numbers could
be used. Zeros and negative numbers would have indicated bad data.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

Hi, Smartin.


Agreed. Unfortunately, many folks use the newsgroups for copy/pasting code,
but if there is any error in the code they have no way of knowing unless they
have the math and programming skills themselves. It's a catch-22, since if
they had the skills, they wouldn't have copy/pasted someone else's code.


It's a good starting point, but folks may not realize it's only the starting
point, and use it as is. That's why I wanted you to look at your code with a
more critical eye, which you did.


There's more than one formula for harmonic mean. None of the values can be
zero, because the values are used in the denominator of the equation.
However, most common uses of harmonic mean require only positive real numbers
in the data set. Consider what the result would be if there were any
negative real numbers in the data set.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

Your points are well taken, Gunny. Presumably, the OP has a good grasp of what
She or He needs in terms of the data, but is on the steeper part of the
learning curve of coding knowledge. I did include a couple disclaimers to
discourage untested use (i.e., "kludge", "[no] error checking") but perhaps I
am being too technical.

I reckon I tend to operate on the premise that most readers are like myself:
inquisitive, a bit adventuresome, and willing to try something even if it
looks like gibberish in the hopes that it magically works. When things work
out we can study the example to figure out the /how/ and improve our
understanding. I learn well through examples such as this, and hope in turn my
examples can serve others in the same capacity.

I certainly don't claim to be a pro though! I don't want to offer any bad or
misleading information, so I do appreciate constructive criticism, including
yours.

Now about this:

Consider what the result would be if there were
any negative real numbers in the data set.

Interesting... I regard the site I was referring to as an excellent source of
mathematical information yet it mentions nothing about the domain of the
members of the set. An innocent omission perhaps? Clearly zero is invalid
since we are adding reciprocals, but if we have, say, R and -R as our set,
then we will get:

1/H = 1/2 * (1/R + 1/-R)
1/H = 1/2 * 0
1/H = 0
uh oh...

I am hoping the OP knows of these pitfalls!

Thanks again,