Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 16th, 2013, 09:21 AM
Registered User
Points: 62, Level: 1
Points: 62, Level: 1 Points: 62, Level: 1 Points: 62, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2013
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default generate automatic score from existing fields access 2010

Hi,

I am having some problems choosing the best way forward with a database and was hoping for help- I am new to databases, so please bear this in mind.

Within a table (which has 100 fields or so) I have 5 fields that need to be used to calculate a score. The field names used to calculate the score are static and will not change/ be added to.

The first field has 6 possible answers (1,2,3,4,5a or 5b- do not ask why these are not simply 1-6, as this is how it has to be!) which can be selected from a combibox. The answer has to generate a score- 1=0, 2=0, 3=0, 4=0, 5a=1, and 5b=2.
For the other field the principle is similar in that each answer must generate a score:
Field 2- number field. <0.05 = 1, 0.05 or greater = 0
Field 3- combibox, yes, no, unknown. yes= 1, no=0 (the unknowns will later be assigned as a yes or no so for the moment don't need to worry about this)
Field 4- same principle as field 2 but <7.3 = 1, 7.3 or greater= 0
Field 5- same principle as field 3

These 5 fields are currently within the same table. I need the score to be automatically generated to I can see the breakdown as well as a summed score of all 5 field scores. Having looked into this, there seem to be a few ways of doing this, and I am not sure the best way and find the explanations hard to follow.

Any help is greatly appreciated (and again, I am new to this, so you may have to be specific!)

Cheers
Reply With Quote
  #2 (permalink)  
Old March 20th, 2013, 03:39 PM
Authorized User
Points: 346, Level: 6
Points: 346, Level: 6 Points: 346, Level: 6 Points: 346, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2010
Posts: 61
Thanks: 0
Thanked 13 Times in 13 Posts
Default

A table with 100 fields does not sound normalised to me, check out http://en.wikipedia.org/wiki/Database_normalization. If each field mentioned is a question entity then you should have a separate questions table and each field would become a record in the new table. The expected answers and their scores would be in another table but have a relationship to the question table via a unique id, e.g. question_id. The actual person's answers would be another table also. This reduces redundancy and would allow you to create a query that would do the automatic calculation.

I think a better understanding of exactly what you are trying to achieve may be useful.

HTH.

Malc.
Reply With Quote
  #3 (permalink)  
Old March 20th, 2013, 04:43 PM
Registered User
Points: 62, Level: 1
Points: 62, Level: 1 Points: 62, Level: 1 Points: 62, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2013
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi- thank you for your response.

I take on board your comment about the large table. Initially I had far more tables, but the data relationships were all 1 to 1 and, after a far bit of reading, found that there does not seem to be a major advantage in having many tables for what I was doing so amalgamated them.

The database is for a medical project which will use a host of indices to see if they can predict prognosis. Now whilst I am familiar with excel and SPSS I wanted to do this in access, so that I could learn how to use it and also because a small part of the data is not a 1-1 relationship. I have 3 tables- one is for the patients' identifiable information, which I have kept as a separate table for security purposes, but has a 1-1 relationship to the large table. There is one further table which has a 1- many relationship which is for patients' re-admissions into hospital.

The information in the large table is clinical information that relates to a single admission episode and the data is static.

What I need understand is how to get the data to generate a score based on certain criteria as listed at the start of post. I had come to the conclusion that running a query was probably the way forward, but I am pretty clueless when it comes to using access to do this for me.

Does this explain my problem more clearly? Hopefully I do not have a fundamentally flawed database, but if you think I have don't be afraid to say!

Thanks
Reply With Quote
  #4 (permalink)  
Old March 20th, 2013, 09:15 PM
Authorized User
Points: 346, Level: 6
Points: 346, Level: 6 Points: 346, Level: 6 Points: 346, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2010
Posts: 61
Thanks: 0
Thanked 13 Times in 13 Posts
Default

Hi,

I understand what you are trying to do now, but I still think that the patient's answers should be a record per answer rather than in fields in one table, i.e. a 1 to n relationship patient -> answer.

Since you only have 5 answers to score you can "hard code" the scoring in a query, not ideal though.

Here's some SQL that I hope may help you. We only need to check for scoring conditions otherwise the score is 0.

Code:
SELECT large_table.patient_id, IIf([field1]="5a",1,IIf([field1]="5b",2,0)) AS field1_score, IIf([field2]<0.05,1,0) AS field2_score, IIf([field3],1,0) AS field3_score, IIf([field4]<7.3,1,0) AS field4_score, IIf([field5],1,0) AS field5_score
FROM large_table;
The code above assumes the table is called large_table that has a patient_id column name. Just add the fieldx_score fields to get the total score for that patient.

HTH.

Malc.
Reply With Quote
  #5 (permalink)  
Old March 21st, 2013, 05:28 PM
Registered User
Points: 62, Level: 1
Points: 62, Level: 1 Points: 62, Level: 1 Points: 62, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2013
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Smile

Hi,

Thanks very much for the help- works perfectly.

By the way, what is the problem with "hard coding" the score as a query? I may do a similar thing for some other fields to generate different score- do you think I am going to run into trouble at a later point?

Given your comments about the structure of the database I think I need to do a bit more reading around this.

Thanks

Last edited by ceuk79; March 21st, 2013 at 05:34 PM..
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Accessing OneNote 2010 from Access 2010 alapee Access VBA 1 December 7th, 2011 07:14 PM
Using Access 2010 and Access Services with SP 2010 kmaford BOOK: Professional SharePoint 2010 Development 0 September 14th, 2010 04:25 PM
adding new data into existing fields? tangwt Access 1 March 30th, 2009 08:10 AM
How to add fields to an existing table using VBA donrafeal Access VBA 3 March 22nd, 2006 06:11 PM
How to generate automatic mails leo_vinay Classic ASP Basics 1 August 8th, 2003 10:32 AM



All times are GMT -4. The time now is 12:18 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.