By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,450 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,450 IT Pros & Developers. It's quick & easy.

Valuation of database records: MoSCoW

P: n/a
Hi,

I'd like to make a valuation of database records. Suppose you have a
table:

Name Must have
Address Should have
Zip Should have
SocialSecNr Must have
City Should have
State Should have
Salary Must have
Carbrand Would have
Kids_YN Would have
Holiday_dest Would have

Now I want to give a number to every record, that gives me an idea how
far the data is supplied. The musthave-fields give me (say) 7 points,
shouldhave-fields 4 points, and wouldhave-fields 1 point.

So the following records gives me (maximum 40 points):

Name Mark Johnson --> 7 points
Address 123 Broadway --> 4 points
Zip
SocialSecNr
City NYC --> 4 points
State
Salary
Carbrand
Kids_YN YES --> 1 points
Holiday_dest Norway --> 1 points

17 points for this record.

This way, I can valuate the way our datacollection program is making
progress.

Does anyone has an idea how to implement this?

Thanks for any suggestions.

Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Create a form with a button and a textbox (TbxScore)
in the on click even of the button:

Dim Dbs as Database
Dim Rst as Recordset
Dim Qry as String

Set Dbs = "c:\whatever.mdb"
Qry = "SELECT * FROM Tbl"
Set Rst = dbs.opendatabase(Qry, dbopendynaset)
If not IsNull(Rst) then
.MoveFirst
While Not Rst.eof
If IsNull(!Name) Then
TbxScore.Value = 0
Else
TbxScore.value = 7
End If
If not IsNull(!Address) Then
TbxScore.value = TbxScore.value + 7
End if
etc.....
Do something with your calculated value and reset
tbxScore.value = 0
.MoveNext
Wend
.Close
End If

I deliberately mixed up the use of the IF statement so that you can
decide what you want to do. Also you need to do something with this
value you are calculating, otherwise it will just display in your form.

This is just a guideline and I have not checked anything.

Nov 13 '05 #2

P: n/a
Is this something that could be done in a query? Like in Oracle
select nvl(address,0,7) + nvl(name,0,7)
from mytable;
??

regards paul

Nov 13 '05 #3

P: n/a
rkc
al********@it4us.nl wrote:
Is this something that could be done in a query? Like in Oracle
select nvl(address,0,7) + nvl(name,0,7)
from mytable;
??


Lookup "Nz Function" in help while in the VBA IDE.
Nov 13 '05 #4

P: n/a
Got it. I was actually looking for

Select
IIf(IsNull([tblTest].[Address])=True,0,4) +
IIf(IsNull([tblTest].[Name])=True,0,7)
from tblTest

Now I got it working. Thanks.

Nov 13 '05 #5

P: n/a
al********@it4us.nl wrote:
Got it. I was actually looking for

Select
IIf(IsNull([tblTest].[Address])=True,0,4) +
IIf(IsNull([tblTest].[Name])=True,0,7)
from tblTest

Now I got it working. Thanks.


When using IIf, Isnull returns a boolean result so you wouldn't need the
"=True" bit in there, so:

IIf(IsNull([tblTest].[Address]),0,4)

will work and shave a few nanoseconds off each row.

--
[OO=00=OO]
Nov 13 '05 #6

P: n/a
<al********@it4us.nl> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Got it. I was actually looking for

Select
IIf(IsNull([tblTest].[Address])=True,0,4) +
IIf(IsNull([tblTest].[Name])=True,0,7)
from tblTest

Now I got it working. Thanks.


Depending on how you have your fields set up, you may have strings of
zero-length or containing only spaces, so you could have:
IIF(Len(Trim(Nz(MyField)))=0,0,4)
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.