Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-12-2019, 08:18 AM   #16
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 428
Thanks: 21
Thanked 90 Times in 89 Posts
mike60smart will become famous soon enough
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

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.

Race.JPG

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.

mike60smart is offline   Reply With Quote
Old 06-12-2019, 09:09 AM   #17
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 428
Thanks: 21
Thanked 90 Times in 89 Posts
mike60smart will become famous soon enough
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

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.

RI.JPG

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

Offence Levels.jpg
mike60smart is offline   Reply With Quote
Old 06-12-2019, 10:15 AM   #18
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,341
Thanks: 87
Thanked 1,641 Times in 1,523 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?

Quote:
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.
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.

__________________
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-13-2019, 05:43 AM   #19
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 428
Thanks: 21
Thanked 90 Times in 89 Posts
mike60smart will become famous soon enough
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

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 is offline   Reply With Quote
Old 06-13-2019, 07:15 AM   #20
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, 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.

Quote:
Where are those two fields located?
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.

Quote:
Do you get the "Parameter" input box on running that query you showed us in post #15?
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":

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]![TotalPoints]
))));

The following gave me an empty cell:

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]=[DataTable]![MostSeriousCharge]
))) AND ((PraxisList.RiskLevel)=(SELECT RiskList.RiskLevel
FROM RiskList
WHERE (
[RiskList]![TotalPoints]=[DataTable]![RiskScore]
))));
This yielded "At most one record can be returned by this subquery."

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]=
(SELECT DataTable.MostSeriousCharge
FROM DataTable)
))) AND ((PraxisList.RiskLevel)=(SELECT RiskList.RiskLevel
FROM RiskList
WHERE (
[RiskList]![TotalPoints]=
(SELECT DataTable.RiskScore
FROM DataTable)
))));

Quote:
Do you get that "Parameter" input box under other circumstances besides this query? If so, where?
I do get a parameter dialogue box of "PraxisQuery" when running the report PretrialReportRisk if I bind SmartPraxis recommendation to
Code:
=[PraxisQuery]![SmartPraxisRecommendation]
Attached Images
File Type: jpg Capture7.jpg (113.7 KB, 13 views)
m0thz is offline   Reply With Quote
Old 06-13-2019, 07:25 AM   #21
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 428
Thanks: 21
Thanked 90 Times in 89 Posts
mike60smart will become famous soon enough
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

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?
mike60smart is offline   Reply With Quote
Old 06-13-2019, 07:45 AM   #22
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, 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.

m0thz is offline   Reply With Quote
Old 06-13-2019, 07:49 AM   #23
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 428
Thanks: 21
Thanked 90 Times in 89 Posts
mike60smart will become famous soon enough
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

Hi

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

DWI.JPG
mike60smart is offline   Reply With Quote
Old 06-13-2019, 08:17 AM   #24
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 428
Thanks: 21
Thanked 90 Times in 89 Posts
mike60smart will become famous soon enough
Re: Lookup in another table/Is this considered a calculation?/Should I be doing this?

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
mike60smart is offline   Reply With Quote
Old 06-13-2019, 09:24 AM   #25
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,341
Thanks: 87
Thanked 1,641 Times in 1,523 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?

Quote:
When I tried to change those from static inputs to variable inputs is when I got parameter input boxes.
Quote:
"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.
__________________
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.

Last edited by The_Doc_Man; 06-13-2019 at 09:48 AM.
The_Doc_Man is offline   Reply With Quote
Old 06-18-2019, 09:44 AM   #26
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?

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.)

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:56 AM.


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