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

Hi everyone!

First of all, shout out to theDBguy for being very friendly and for being extremely helpful!


My main data table is called Data Table.

I have a field called MostSeriousCharge. This is input using a form called RA Entry Form.

I need to lookup the corresponding field called OffenseCategory in a separate table called ChargeList.

I have a calculated field called RiskScore.

I need to lookup the corresponding field called RiskLevel in a separate table called RiskList.

Using OffenseCategory and RiskLevel, I need to lookup the corresponding SmartPraxisRecommendation in a separate table called PraxisList.


My question is: how can I do this? Possible follow-up questions include: can this be a field in Data Table? Should this be a query? I know the experts are against calculated fields in tables, but I'm going to be running logistic regressions on this data later, and if it weren't in the table now, I'd just have to add it in using Stata later, so I might as well do it now if possible/feasible.


I know this is Access, but here it is in Excel in case you need to know exactly what I'm looking for:

{=INDEX(PraxisList[SmartPraxisRecommendation],MATCH(1,(VLOOKUP(RiskScore,RiskList[#All],2,FALSE)=PraxisList[RiskLevel])*(VLOOKUP(MostSeriousCharge,ChargeList[#All],2,FALSE)=PraxisList[OffenseCategory]),0))}


Attached is a sample database with dummy records.



Thank you in advance for your help!

Jacob

Hi Jacob. Thanks for the shout out. I will give you my vote, which is going to be just one of many, so please take it with a grain of salt. I would say don't store the same information in multiple tables. If you can look it up or calculate it, just use a query to get the information. You should be able to export a query to Excel or perform your data analysis on the query. It doesn't have to be the table itself. The main power of Access is it's ability to connect/join multiple tables to produce a dynamic data set, based on user's needs. Again, this is just one person's opinion. Cheers!

First, advise not to use spaces nor punctuation/special characters in naming convention.

Second, there are no relationships established. You have tables in the Relationships window but no links established.

So maybe you want:

SELECT [Data Table].*, ChargeList.OffenseCategory, PraxisList.RiskLevel, RiskList.RiskScore
FROM ((ChargeList RIGHT JOIN [Data Table] ON ChargeList.MostSeriousCharge = [Data Table].MostSeriousCharge)
LEFT JOIN PraxisList ON ChargeList.OffenseCategory = PraxisList.OffenseCategory) LEFT JOIN RiskList ON PraxisList.RiskLevel = RiskList.RiskLevel;

This is basic Access functionality. Have you studied an introductory tutorial book?

Hi Jacob

Your zip file will not open.

You should close the database and then zip it

Thank you so much for letting me know about closing the db before zipping, mike60smart! I've re-uploaded the zip.

theDBguy, I appreciate your thanks, grain of salt notwithstanding

I'm not quite sure I know how I would go about putting lookup tables into one giant table. Unless you're saying that I could somehow embed the lookup tables within a query? Then I could force that query to run every time I ran a report? I don't think I'm completely understanding the underlying paradigm of Access.

Hi

Zip file still will not open

m0thz said:

Thank you so much for letting me know about closing the db before zipping, mike60smart! I've re-uploaded the zip.

theDBguy, I appreciate your thanks, grain of salt notwithstanding

I'm not quite sure I know how I would go about putting lookup tables into one giant table. Unless you're saying that I could somehow embed the lookup tables within a query? Then I could force that query to run every time I ran a report? I don't think I'm completely understanding the underlying paradigm of Access.

Click to expand...

Hi. In relational database design, there is an underlying principle we all try to follow. It's called the "Normalization Rules." One of those rules is to not store duplicate or calculated information. If you're not familiar with these rules, you might take a minute and review them by doing a search on the key term "normalization." Cheers!

I didn't look at your db but since normalization was mentioned, post 6 here might be useful to you

https://www.access-programmers.co.uk/forums/showthread.php?t=305419

mike60smart, do you have any ideas on what I can do? I downloaded the link and extracted it to my computer and it opened up fine, and theDBguy and June7 seemed to be able to access the file. I really do appreciate you taking the time to try to open the file, and I would gladly accept any help you'd be able to give, but I don't quite know how else I can compress the database since it's too big to upload on its own.

(Micron and theDBguy, I'm not ignoring you, I'm just reading all the material that was suggested)

The chargelist table needs to have a primary key first of all. I would simply add an autonumber. Call it chargeList_ID. Then in the main form you set the combobox to show the charge list description but instead you save the chargelist iD in the data table. you would need to change the field in the data table to numeric. Then by a query you will have the related offense category no need for any code or calculated field. Your form is built on a query linking the data table to chargelist by chargeListID. Same idea on risk.

Zip file opens fine for me. Win 10 64 bit Access.

m0thz said:

(Micron and theDBguy, I'm not ignoring you, I'm just reading all the material that was suggested)

Click to expand...

NP. I went over your original post and counted: you mentioned 5 fields that, in your mind, seem to be related among themselves. As a reader, I can make no such connection, which means either a) there really isn't one, which would make mining data difficult for you, or b) the naming doesn't make any relationship intuitive.

There's a lot of reading in the other thread links, so take your time and see if any of it means anything with respect to a and b above.

Did you try the query?

I'm adding in late because I've been occupied for a while.

Just as a vote, you NEVER store any calculated values in a table. (What, never? Well... hardly ever.) Computations and lookups are easily done in a query. Even lookups can be managed using JOIN queries. If you are not familiar with JOIN queries, a typical lookup will use an INNER JOIN based on a stored code in the main table joined to a table where the same codes are used as prime keys precisely to facilitate the lookups.

If you have not done so already, do some reading on:
"normalization"
"INNER JOIN"
"many-to-one relationship"

Thanks to everyone for trying to help, but no one really seems to be understanding what my problem is. Maybe it would help you if I just explained to you how pretrial works? You all seem to be law-abiding citizens, or you may live in a county that doesn't have a pretrial department, or you might not live in the US, so you probably have no idea how this works. I certainly had no clue what any of this was before I took this job.

(Btw, everything I'm about to explain to you is public record, and we're actually trying to be as transparent about this process as possible, so don't worry about reading something that's confidential or proprietary.)

Let's say you get booked on some charge. You may get booked on a bunch of charges at once, but you'll always have at least one MostSeriousCharge. Every single charge you can possibly be booked on has an OffenseCategory based on the severity of the charge. There are 5 offense categories, but there are also 5 separate categories for DWIs (we're really sensitive about those). The list of charges and their corresponding offense categories can be found in ChargeList.

Then you are given a pretrial risk assessment. This is designed to measure your probability of showing up to court and not committing a new crime. Based on the assessment, you end up with a RiskScore. That score corresponds to a RiskLevel. There are 4 risk levels. The list of risk scores and their corresponding risk levels can be found in RiskList.

Based on your risk level and the offense category, you are recommended a level of pretrial supervision. I've attached the actual matrix so you can see what I mean (Capture5). So if you commit a category 1 offense, and your risk level is 2, it's recommended that you be released under intensive supervision. If you commit a level 5 offense and your risk level is 3, it's recommended that you be released under administrative supervision (in case you're interested, the judge can go lower, higher, or disregard these recommendations completely and issue a monetary bond).

The DWI matrix doesn't depend on risk levels, just on the type of DWI charge. I assigned those categories 6-10.

I reproduced this matrix in PraxisList.

All I want is for SMART PRAXIS Supervision Recommendation in PretrialReportRisk to show the appropriate supervision level (also attached: Capture6). That's literally it. All it should say is Administrative, or Standard, or Enhanced, or Intensive, or Non-Aggravated DWIs, or 1st DWI over 0.15, or DWI Subsequent or with Child, or DWI 3rd or more.

The purpose of DataTable is to collect all of data of everyone that gets booked in jail, then wait for most of their cases to get disposed--which ends their pretrial period--then run a logistic regression to determine which of the risk items actually correlated to pretrial failure, and eliminate those that don't. We're also field testing items that aren't counted now, but we're measuring them to potentially include them in the next version of the risk assessment. We'll also be determining if there are potential racial or gender biases, etc.

We're also measuring concurrence rates of judges as well as failure rates in every single cell of the attached matrix, which is why I would love to have the Smart Praxis recommendation levels in DataTable, but you all seem to be against that, so that's ok, I can do that later in Excel.

I did create a query--called PraxisQuery--that does give me the correct recommended supervision level, so long as the charge and the risk score never ever change.


But I've hit a concrete wall trying to figure out how to point Access to where I want [ChargeList]![MostSeriousCharge] and [RiskList]![TotalPoints] to pull from. If I try to pull from the DataTable, it just prompts me to enter the parameters myself. If I try to pull from the RAEntryForm, it gives me another error. I'm at my wit's end, and I'm on the brink of just giving up and going back to Excel.

I've attached the database again, but it now includes the new PraxisQuery if you'd like to take a look.

Again, all I really want is for the correct supervision recommendation level to appear on the report. If it can only happen at the time of inputting the form, fine. If it can happen at any time on every entry, awesome. Any help would be extremely appreciated.


Thank you!

Hi

Having looked at your tables I believe your table "Data Table" needs to be normalised.

To give you one example you have a number of Yes/No Fields to record the Ethnicity of the Person.



This should be Records in a separate table ie tbluEthnicity (List of Race Values)

Then in your table "Data Table" you just need a field named EthnicityID - Number DataType.

Then on your Data Input Form you would have a Combobox to select the Ethnicity from tbluEthnicity.

Hi

Also, for your Offence Categories you need to use Cascading Combobox's.

The tables for your cascading Combobox's would be based on the the following 2 tables.



The Data Input Forms to enter the correct Categories looks like this:-

But I've hit a concrete wall trying to figure out how to point Access to where I want [ChargeList]![MostSeriousCharge] and [RiskList]![TotalPoints] to pull from.

Click to expand...

Let me take a shot at asking the questions a different way. Tell me IN ENGLISH ONLY: Where are those two fields located? Do you get the "Parameter" input box on running that query you showed us in post #15? Do you get that "Parameter" input box under other circumstances besides this query? If so, where?

The reason I ask is because when you get prompted to enter a parameter, it is because EITHER the field exists but you spelled its name or table wrong
OR it does not exist at all
OR you need to somehow map an external table or query that contains it and that hasn't been done properly yet
OR whatever you are using for a recordset source doesn't contain that item even if the actual table does. (Remember, tables and select queries are both recordset sources.)

The other possibilities for that behavior are that it actually IS a parameter - but your discussion suggests otherwise; or that Access is corrupted in some way - but I would expect other error messages in that case.

So where are those things located? Once we know that, we might be able to help you with the concrete wall.

Your current real question is "How do I find these values from where I'm asking the question?" Let's focus on that for a moment. An explanation such as you gave will often help us understand the big picture but for this specific question, we need more along the lines of a specific context.

Hi

I gave you an example of how to manage your Offence Level Categories but you also mention "but there are also 5 separate categories for DWIs"

What are the 5 DWI Categories?

mike60smart, I don't know how you did any of that, but that's a very interesting way to approach this problem. I've attached the DWI matrix; the five categories are in bold along the left-hand side.


The_Doc_Man, I love your questions, and I think they get at the heart of what I'm trying to do.

Where are those two fields located?

Click to expand...

They're in DataTable under MostSeriousCharge and RiskScore.

MostSeriousCharge is input in the form RAEntryForm. RiskScore is calculated based on various inputs from that same form.

Do you get the "Parameter" input box on running that query you showed us in post #15?

Click to expand...

No, I get a single cell result: "Intensive." This is the correct recommendation for the charge ABANDON ENDANGER CHILD CRIMINAL NEGLIGENCE and a risk level of 3. When I tried to change those from static inputs to variable inputs is when I got parameter input boxes.

The following code gave me 2 Parameter input boxes, one titled "Forms!RAEntryForm!MostSeriousCharge" and the other titled "Forms!RAEntryForm!TotalPoints":



The following gave me an empty cell:


This yielded "At most one record can be returned by this subquery."

Do you get that "Parameter" input box under other circumstances besides this query? If so, where?

Click to expand...

I do get a parameter dialogue box of "PraxisQuery" when running the report PretrialReportRisk if I bind SmartPraxis recommendation to