UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query With Multiple Layers Possible?, Any Version    
 
   
j_ockmed
post Nov 7 2019, 05:20 PM
Post#1



Posts: 453
Joined: 20-August 07
From: Ohio


Question: Is it possible to query with multiple layers (parent/child associations) and get the results i'm looking for below, and if so, how can it be done?

Simplification of what i'm shooting for: Utilizing Locations & Laws as my example - Say i want to see all the city laws (unrealistic - but this is just as an example) for a particular city, and include county laws, state laws, and federal laws.

So, if i were to ask - what are the laws in Reno, NV? The query would return all the laws for Reno, The county Reno is in, the state of Nevada, and then the entire federal laws that would naturally apply to Reno. How do i do this?

I'd also want to run it and say - show me all the laws for Marathon County in Wisconsin, and continuing up the chain, show me the laws for Wisconsin, and then federal... all as one queried result

And, naturally, going up the chain, i could just want to see all of Iowa's laws... and the query result would show me Iowa... and all of Federal.

The ultimate question this query is answering (in my example to help simplify), what laws apply to... [insert location here]. Because, if you're in Reno - County/State/Federal laws will always apply (in my example). If you're in Marathon County, all State/Federal laws will apply to you... and so on.

My Thoughts: I've only gotten so far as making a single table with the following fields:
[PKID] - auto incrementing ID
[LocationNameFKID] - Foreign Key from another table containing location name values, i.e. Reno
[LocationParentFKID] - Foreign Key from another table containing location name values, i.e. Washoe (as info, the county "Reno" is in)
[LocationTypeFKID] - Foreign Key from another table containing location types, i.e. City/County/State/Federal

End result i'm looking for:
Query = Reno
Result:
Reno, all applicable laws (multiple rows containing unique laws)
Washoe, all applicable laws (multiple rows containing unique laws)
Nevada, all applicable laws (multiple rows containing unique laws)
United States, all applicable laws (multiple rows containing unique laws)

--------------------
Humor is a rubber sword - it allows you to make a point without drawing blood.
--Mary Hirsch
Go to the top of the page
 
GroverParkGeorge
post Nov 7 2019, 05:44 PM
Post#2


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


The short answer is that you can definitely get the desired results in a single query. The trick will be in defining the tables properly to model the hierarchy of government levels and in nesting the hierarchy in the query.

You will need a table of locations, with a foreign key to designate which level it is, municipal, county, state or federal jurisdiction.

You'll need a table of jurisdictions.

In the table of locations, you'll need a self-referencing key that links each location to the level above it. In other words, in the record for Marathon County, this field would hold the primary key for the state of Wisconsin. That way you can recursively query each level and get both those below and above it. This sort of recursive query, though, tends not to be flexible because it relies on a stable set of hierarchical levels. Here, I suppose, there's little chance of new, additional administrative jurisdictions being added.






--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
j_ockmed
post Nov 8 2019, 08:55 AM
Post#3



Posts: 453
Joined: 20-August 07
From: Ohio


What does the Access SQL look like for that? I'm hitting a mental wall in thinking of how to write it... how to write a query to accept one value from the end user - and populate all it's associated values and it's parents associated values (laws)

I hate to think i'm asking for someone to write it for me... so if there's a working example somewhere - i'll gladly take that

When working with junction tables outside of one level, my head starts to go a little wonky...

As for having the limitation of not being able to expand on the levels of government if needed... i don't like it either, but i'll take it though

Thank you very much for your time!

--------------------
Humor is a rubber sword - it allows you to make a point without drawing blood.
--Mary Hirsch
Go to the top of the page
 
Jeff B.
post Nov 8 2019, 09:03 AM
Post#4


UtterAccess VIP
Posts: 10,336
Joined: 30-April 10
From: Pacific NorthWet


If the "laws" aspect of your question is real, not a hypothetical, I believe there are some 'local' laws that are in direct conflict with 'federal' laws. For example, several states have decriminalized recreational marijuana, but the federal statutes still apply. Does that present an issue for your eventual analysis?

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
j_ockmed
post Nov 8 2019, 09:18 AM
Post#5



Posts: 453
Joined: 20-August 07
From: Ohio


Jeff B: nope. We're all good there... however, i do understand about the drugs and differences between state and federal as an example. Thanks for checking though!

--------------------
Humor is a rubber sword - it allows you to make a point without drawing blood.
--Mary Hirsch
Go to the top of the page
 
GroverParkGeorge
post Nov 8 2019, 09:22 AM
Post#6


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


I can mock up something later.


--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
j_ockmed
post Nov 8 2019, 09:25 AM
Post#7



Posts: 453
Joined: 20-August 07
From: Ohio


Your kindness knows no bounds - thank you!

--------------------
Humor is a rubber sword - it allows you to make a point without drawing blood.
--Mary Hirsch
Go to the top of the page
 
GroverParkGeorge
post Nov 8 2019, 10:42 AM
Post#8


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


Here's an example, VERY simple example.

Attached File  JurisdictionRelationships.png ( 74.22K )Number of downloads: 5




Attached File  recursivelawquery.zip ( 21.47K )Number of downloads: 6

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
j_ockmed
post Nov 8 2019, 12:01 PM
Post#9



Posts: 453
Joined: 20-August 07
From: Ohio


Thank you for your time George - i appreciate the hands on example!

I notice that it's only pulling laws to the municipal/city level though (perhaps because its ID is a 1?). Is there a way to write it to show all its direct parent relationship laws too? Like, show all laws for US, Oregon, Multanumah, Portland? And, if i were to have Reno, Washoe, Nevada, US in there, the query shouldn't pull any laws from Reno, Washoe, or Nevada

Your time and skill is much appreciated - thank you

--------------------
Humor is a rubber sword - it allows you to make a point without drawing blood.
--Mary Hirsch
Go to the top of the page
 
GroverParkGeorge
post Nov 8 2019, 12:05 PM
Post#10


UA Admin
Posts: 36,181
Joined: 20-June 02
From: Newcastle, WA


You should be able to use LEFT Joins instead of INNER Joins to pull ALL records from one or more levels. Give it a try with some real data and see what you can come up with.

Also, note that I only put in ONE law at each level.

You'll need to use criteria to decide which jurisdictions to include.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 12:11 AM