Display value based on selection from another combo box

[Mattcow1]

Hi, I'm working on a database that has been around for many years, so it's a bit convoluted. I'm not great at Access, so I hope my explanation makes sense.



In my form, I have six combo box drop-downs. The text options in each combo box are pulled from one column of a table. Each combo box has its own individual table it refers to.
The tables have other columns, including "Value" (primary key) "Display" (-1 or 0 [ y/n, essentially]) "DisplayOrder" and "Score." The six tables have the same headings, just different info in each.
Once you choose an option, it outputs the "Value" associated with your choice to a master table. It doesn't store the associated text or Score in that master table. We have some queries that will output that info when run, however.

What I want to do is add a box on this form that will display the Scores.
For example, there is a combo box named "Hysterectomy" (this is a surgical database) - the options are Yes or No. The score associated with Yes is 1, No is 0.
When I choose Yes from the drop-down, I would like a little box next to it to display 1. If I change it to No, I'd like that to display 0.

Then I'd like a box to sum all of the scores ; actually, if there was a way to ONLY have the sum, that would be better.

One of our queries has the scores and sum in it, and I've used DLookUp to get those scores to display, but we have to re-run the query every time we want to update the values, and it's a huge database, so it takes a while and isn't very elegant.

Any alternatives?
Thanks for the input

[June7]

Why bother with displaying 1/0?

If the combobox is bound to a yes/no field then the actual value saved is -1/0, Yes and No are just display formatting.

If the Score is just the absolute value of -1/0 then try: Sum(Abs([Display]))

Don't even need Score field.

[Mattcow1]

It's not really a yes/no field, it's bound to the table where yes and no are the option. Hysterectomy is the simple one, the other boxes have up to ten options each.

[Mattcow1]

The score is the important data point I'm trying to add here. For Hysterectomy, there are only two options, but for "Bowel Resection" there are ten options and the scores associated with each option varies.

[June7]

You said Display is -1 or 0 which implies a Yes/No field. So suggestion works only for Hysterectomy table?

Otherwise, options are:

1. include the lookup table in the form's RecordSource so the Score field will be available for Sum()

2. Score is value of combobox and Score is saved instead of Value (BTW, value is a reserved word, should not use reserved words as names for anything)

3. DSum() not DLookup()

[Mattcow1]

I appreciate your suggestions, including the lookup table in the record source worked!
Sort of.
There is already one table joined (unrelated to these six I'm asking about). If I join one of the new six, it also works. I can then make a text box, choose "Score" and it will show the score for Hysterectomy and update as I change options.
If I try to add and join another table, I get an error (JOIN expression not supported).
I have a very superficial knowledge of Access and no knowledge of SQL beyond Googling... I'm just trying to poke my way through this, so I greatly appreciate your help.

Original:
SELECT mstrSurgPath.xxx (many)
FROM mstrSurgPath
LEFT JOIN mstrSurgPathAttachments ON (mstrSurgPath.StudyID = mstrSurgPathAttachments.StudyID) AND (mstrSurgPath.SurgicalPathNum = mstrSurgPathAttachments.SurgicalPathNum)
ORDER BY mstrSurgPath.SurgeryDate;

Working:
SELECT mstrSurgPath.xxx (many), lkpHysterectomy.Score
FROM (mstrSurgPath
LEFT JOIN mstrSurgPathAttachments ON (mstrSurgPath.StudyID = mstrSurgPathAttachments.StudyID) AND (mstrSurgPath.SurgicalPathNum = mstrSurgPathAttachments.SurgicalPathNum))
RIGHT JOIN lkpHysterectomy ON mstrSurgPath.Hysterectomy = lkpHysterectomy.HysterectomyID
ORDER BY mstrSurgPath.SurgeryDate;

--> JOIN Expression not supported
SELECT mstrSurgPath.xxx (many), lkpHysterectomy.Score, lkpOmentectomy.Score
FROM ((mstrSurgPath
LEFT JOIN mstrSurgPathAttachments ON (mstrSurgPath.StudyID = mstrSurgPathAttachments.StudyID) AND (mstrSurgPath.SurgicalPathNum = mstrSurgPathAttachments.SurgicalPathNum))
RIGHT JOIN lkpHysterectomy ON mstrSurgPath.Hysterectomy = lkpHysterectomy.HysterectomyID)
RIGHT JOIN lkpOmentectomy ON mstrSurgPath.Omentectomy = lkpOmentectomy.OmentectomyID
ORDER BY mstrSurgPath.SurgeryDate;

[Mattcow1]

I should mention that I tried to do this in the Query Builder first and got the same error message after adding more than one table.

[June7]

The usual guideline is 1 form to 1 table for data entry/edit. Lookup table(s) can be included just to display related info but cannot edit the lookup table.

Why is mstrSurgPathAttachments included in the query?

[Mattcow1]

Ended up finding a different way to do this.
Added lkpHysterectomy.Score to the SELECT section of the drop-down combo box's Row Source code (and similar for the other five drop-downs), then in the same form I made my text box and set its Control Source to:
=CInt(nz(([Hysterectomy].[column](2)),0))+CInt(nz(([Omentectomy].[column](2)),0))+....
(where Hysterectomy and Omentectomy are the names of the drop-down combo boxes, and __.Score is the third item in the SELECT sections)

[June7]

Yes, I did consider suggesting that approach to bring Score to the form but I thought you needed to sum Score. I still wonder why mstrSurgPathAttachments is included in the query.