Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-10-2019, 10:45 AM   #1
m0thz
Newly Registered User
 
Join Date: Jun 2019
Posts: 11
Thanks: 3
Thanked 0 Times in 0 Posts
m0thz is on a distinguished road
Lookup in another table/Is this considered a calculation?/Should I be doing this?

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
Attached Files
File Type: zip MyFirst.zip (364.8 KB, 18 views)

m0thz is offline   Reply With Quote
Old 06-10-2019, 11:01 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,433
Thanks: 58
Thanked 1,410 Times in 1,391 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

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!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following 2 Users Say Thank You to theDBguy For This Useful Post:
m0thz (06-10-2019), Tera (06-10-2019)
Old 06-10-2019, 11:08 AM   #3
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,450
Thanks: 0
Thanked 571 Times in 567 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

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?

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 06-10-2019, 12:10 PM   #4
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 462
Thanks: 21
Thanked 92 Times in 91 Posts
mike60smart will become famous soon enough
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

Hi Jacob

Your zip file will not open.

You should close the database and then zip it
mike60smart is offline   Reply With Quote
The Following User Says Thank You to mike60smart For This Useful Post:
m0thz (06-10-2019)
Old 06-10-2019, 12:36 PM   #5
m0thz
Newly Registered User
 
Join Date: Jun 2019
Posts: 11
Thanks: 3
Thanked 0 Times in 0 Posts
m0thz is on a distinguished road
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

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.
Attached Files
File Type: zip MyFirst.zip (395.0 KB, 12 views)
m0thz is offline   Reply With Quote
Old 06-10-2019, 12:45 PM   #6
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 462
Thanks: 21
Thanked 92 Times in 91 Posts
mike60smart will become famous soon enough
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

Hi

Zip file still will not open
mike60smart is offline   Reply With Quote
Old 06-10-2019, 12:46 PM   #7
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,433
Thanks: 58
Thanked 1,410 Times in 1,391 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

Quote:
Originally Posted by m0thz View Post
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. 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!

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-10-2019, 01:02 PM   #8
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,194
Thanks: 10
Thanked 226 Times in 214 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

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...d.php?t=305419
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 06-10-2019, 01:07 PM   #9
m0thz
Newly Registered User
 
Join Date: Jun 2019
Posts: 11
Thanks: 3
Thanked 0 Times in 0 Posts
m0thz is on a distinguished road
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

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.
m0thz is offline   Reply With Quote
Old 06-10-2019, 02:28 PM   #10
m0thz
Newly Registered User
 
Join Date: Jun 2019
Posts: 11
Thanks: 3
Thanked 0 Times in 0 Posts
m0thz is on a distinguished road
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

(Micron and theDBguy, I'm not ignoring you, I'm just reading all the material that was suggested)
m0thz is offline   Reply With Quote
Old 06-10-2019, 02:34 PM   #11
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,888
Thanks: 38
Thanked 571 Times in 538 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

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.
MajP is offline   Reply With Quote
Old 06-10-2019, 02:40 PM   #12
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,194
Thanks: 10
Thanked 226 Times in 214 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

Quote:
Originally Posted by m0thz View Post
(Micron and theDBguy, I'm not ignoring you, I'm just reading all the material that was suggested)
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.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 06-10-2019, 03:10 PM   #13
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,450
Thanks: 0
Thanked 571 Times in 567 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

Did you try the query?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 06-10-2019, 04:27 PM   #14
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,676
Thanks: 93
Thanked 1,702 Times in 1,575 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

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"
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 06-12-2019, 07:49 AM   #15
m0thz
Newly Registered User
 
Join Date: Jun 2019
Posts: 11
Thanks: 3
Thanked 0 Times in 0 Posts
m0thz is on a distinguished road
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

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.

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]="ABANDON ENDANGER CHILD CRIMINAL NEGLIGENCE"
))) AND ((PraxisList.RiskLevel)=(SELECT RiskList.RiskLevel
FROM RiskList
WHERE (
[RiskList]![TotalPoints]=3
))));
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!
Attached Images
File Type: jpg Capture5.jpg (102.3 KB, 11 views)
File Type: png Capture6.PNG (4.9 KB, 9 views)
Attached Files
File Type: zip MyFirst.zip (896.4 KB, 8 views)

m0thz is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup Table or Lookup Tables? Uncle Gizmo Tables 4 10-31-2016 02:13 PM
Access form to lookup sql table and update another table jtp607 Forms 0 07-24-2014 08:34 AM
Count entriesin table based on lookup table connerc15 Queries 2 05-12-2014 08:15 AM
Tables, lookup/combo box issues... 1 value lookup autofill other table columns PsyKo Tables 5 11-12-2012 03:47 PM
Two fields in a table referring to the same field in a lookup table lmcc007 Queries 6 07-20-2009 10:02 PM




All times are GMT -8. The time now is 04:28 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World