Lookup in another table/Is this considered a calculation?/Should I be doing this? (1 Viewer)

mike60smart

Registered User.
Local time
Today, 00:54
Joined
Aug 6, 2017
Messages
1,899
Hi

Your DWI List of DWICategoryCharges are as follows:-

DWICategory DWICategoryCharge
6 BOATING WHILE INTOXICATED
6 DRIVING WHILE INTOXICATED
6 DRIVING WHILE INTOXICATED/OPEN ALCH CONTAINER
7 DRIVING WHILE INTOXICATED BAC >= 0.15
8 BOATING INTX W/PREV INTOX MANSLAUGHTER CONV
8 DRIVING WHILE INTOXICATED 2ND
8 DWI W/PREVIOUS INTOXICATION MANSLAUGHTER CONV
8 FLYING WHILE INTOXICATED 2ND
9 BOATING WHILE INTOXICATED 3RD OR MORE
9 DRIVING WHILE INTOXICATED 3RD OR MORE
9 FLYING WHILE INTOXICATED 3RD OR MORE

This lists 4 Types ie 6 through 9

But, your guideline listing shows 5 Categories?
 

m0thz

Registered User.
Local time
Yesterday, 17:54
Joined
Jun 10, 2019
Messages
11
mike60smart, you're exactly right. I should have mentioned that there are currently no offenses in DWI Drugs. There might be at a later date, but right now, the 10th category is empty. There might be offenses that are DWIs combined with drugs, but the group that categorized everything categorized them as non-DWI for whatever reason, and that's what I have to go off of.

My apologies.
 

mike60smart

Registered User.
Local time
Today, 00:54
Joined
Aug 6, 2017
Messages
1,899
Hi

OK then your form for specifying DWI Categories would look like this:-

DWI.JPG
 

mike60smart

Registered User.
Local time
Today, 00:54
Joined
Aug 6, 2017
Messages
1,899
Hi

I have added the Risk Levels to the Offence Category Form as shown below:-

Risk.jpg

This now enables you to have what are known as Cascading Combobox's

The 1st Combobox allows you to select the Offence Category
The 2nd Combobox allows you to select from a list Only those Most Serious Charges associated with the Offence Selected
The 3rd Combobox allows you to select the Risk Level associated with the Offence Category Selected
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 19:54
Joined
Feb 28, 2001
Messages
27,001
When I tried to change those from static inputs to variable inputs is when I got parameter input boxes.

"Forms!RAEntryForm!MostSeriousCharge" and the other titled "Forms!RAEntryForm!TotalPoints"

Those two quoted items are the items not visible in the context of this query. That means that the query is unable to see "Forms!RAEntryForm" and therefore cannot see those items. Are the two named items field names from the query or control names on the form? The idea is that two different environments are involved - that of the Access GUI and that of the SQL processor (ACE, probably) and some things don't cross that barrier quite like you might have thought they would.

https://www.fmsinc.com/tpapers/primer/index.html

If those two items are actually field names from the underlying table, that isn't visible and the correct names would be that of the form's controls that hold those values.
 
Last edited:

m0thz

Registered User.
Local time
Yesterday, 17:54
Joined
Jun 10, 2019
Messages
11
Hi everyone,

Update: I figured out how to run the query how I wanted to, albeit hacked together and not very conducive to changes that may happen in 18 months. Here is the PraxisQuery that does what I want.

Code:
SELECT PraxisList.SmartPraxisRecommendation
FROM DataTable RIGHT JOIN PraxisList ON DataTable.[SMART PRAXIS Recommendation] = PraxisList.SmartPraxisRecommendation
WHERE (((PraxisList.OffenseCategory)=(SELECT ChargeList.OffenseCategory
FROM ChargeList
WHERE 
(
[ChargeList]![MostSeriousCharge]=[Forms]![RAEntryForm]![MostSeriousCharge]
)
)) AND ((PraxisList.RiskLevel)=(SELECT RiskList.RiskLevel
FROM RiskList
WHERE 
(
[RiskList]![TotalPoints]=[Forms]![RAEntryForm]![RiskScore]+1
)
)));

So that query works, which is sweet. Now the problem is forcing PraxisQuery to run when I try to print the form RAEntryForm. RAEntryForm contains the field =[PraxisQuery]![SmartPraxisRecommendation]

The print command on RAEntryForm does the following:

Code:
RunMenuCommand
Command: SaveRecord

OpenReport
Report Name: PretrialReportRisk
View: Print Preview
Filter Name:
Where Condition:
Window Mode: Normal

RunMenuCommand
Command: PrintObject

When I click on that command, the parameter value dialog box appears with "PraxisQuery"

PraxisQuery has been added to the report PretrialReportRisk. Do I have to add PraxisQuery to the form, too? If so, how do I do that?

Thanks to everyone for their patience!

(mike60smart, I promise to look more closely at what you've done as soon as I get this part sorted.)
 

Users who are viewing this thread

Top Bottom