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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V < 1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> Relate Order Numbers On A Form And Display In A Listbox, Access 2016    
 
   
moze
post Feb 26 2018, 01:36 PM
Post#21



Posts: 22
Joined: 8-January 18



mike60smart,

I need to see this because that is a requirement of the project. If 2 is related to 1, and 3 is related to 1, then 2 and 3 should also be related. If I am related to my father, and my sister is related to my father, then I should be related to my sister.

zaxbat,

Essentially, they are all peers. All of the order numbers exist in 1 table as equals. However, if I am viewing order 1 for client A, and order 2 and 3 are also for client A, I would relate 2 and 3 to order 1. If I view order 2, I will see 1 and 3 as related. If I view order 3, I will see 1 and 2 related.
Go to the top of the page
 
moze
post Feb 26 2018, 01:51 PM
Post#22



Posts: 22
Joined: 8-January 18



tina t,

The commonality is at the discretion of the user. It can range anywhere from orders being for the same client or simply because the user just felt like relating them.

There isn't actually a relational key which is why I think this is difficult to do.
Go to the top of the page
 
theDBguy
post Feb 26 2018, 01:58 PM
Post#23


Access Wiki and Forums Moderator
Posts: 73,269
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Re: "...which is why I think this is difficult to do."

Did you happen to see my previous post? Here are some screenshots from my sample data:

Attached File  table.png ( 11.51K )Number of downloads: 0

Here's the table showing the assigned related records.

Attached File  query.png ( 10.08K )Number of downloads: 0

Here's the query showing all assigned and implied relationships.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Feb 26 2018, 02:04 PM
Post#24


UA Admin
Posts: 33,514
Joined: 20-June 02
From: Newcastle, WA


hm.

I'm going to suggest that, if you haven't already let us see the actual tables, this would be a good time to do that.

It sounds like you're going to have to create a form where you can see each order and a multi-select listbox where potentially related orders are ALL listed. Then you can select one or more of them to associate with the "master" order.

In my family database, I assume each person will have two parents (biologically that is the only thing that makes sense, although that's obviously changing as we speak). I assume that each person will have one Current Spouse, but may have multiple former spouses. To do that, I have a subtable in which persons are related to other persons as spouses, with only one being active at any given time.

In your case, you need only the one "Parent" or "Master" order field in which to record the master order for one or more related orders. The "Master" order, could be a related order for another order, making this essentially a hierarchical relationship.

So, that's how i'd go, but I'm assuming, for the time being, you actually have tables set up that way. You previously uploaded sample data, but I'm not sure it actually represents the tables in your real database.

But, FWIW, here's what I would do, using what is sometimes referred to as a Self-Join.

tblOrders
==============
OrderID PK
ParentOrderID FK

Other fields you need for the order table.

With that in place, create a relationship, one-to-many, from OrderID to ParentOrderID in the same table.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
zaxbat
post Feb 26 2018, 02:23 PM
Post#25



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


If the end user must manually create the association between the entities.....then it is not so hard. But if the system must do it automatically, it could be difficult to test its functionality. Additionally it may connect so many things to so many other things that FB (you know...sounds like spacebrook) people would get jealous.

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
theDBguy
post Feb 26 2018, 02:31 PM
Post#26


Access Wiki and Forums Moderator
Posts: 73,269
Joined: 19-June 07
From: SunnySandyEggo


To show which records were assigned or not, I modified the query to use the following SQL statement:

CODE
SELECT tblOrder_Related.order_id, tblOrder_Related.related_id, "Assigned" As Relationship
FROM tblOrder_Related
UNION SELECT tblOrder_Related.related_id, tblOrder_Related_1.related_id, "Implied"
FROM tblOrder_Related INNER JOIN tblOrder_Related AS tblOrder_Related_1 ON tblOrder_Related.order_id = tblOrder_Related_1.order_id
WHERE ((([tblOrder_Related].[related_id]=[tblOrder_Related_1].[related_id])=0));


Here's the result:
Attached File  query.png ( 12.91K )Number of downloads: 0


Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
zaxbat
post Feb 26 2018, 02:37 PM
Post#27



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


If you have a query already creating that table then.... notworthy.gif

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
moze
post Feb 26 2018, 04:04 PM
Post#28



Posts: 22
Joined: 8-January 18



Okay, so the query almost gave me the results I was looking for with the given data.

Although, order 1 is not showing as related to anything when you view different orders. And upon relating order 5 to order 2, it does not relate 5 to any other orders or relate anything to 5.

Here is the result set:
Attached File  queryresults.png ( 6.1K )Number of downloads: 0


Go to the top of the page
 
theDBguy
post Feb 26 2018, 04:46 PM
Post#29


Access Wiki and Forums Moderator
Posts: 73,269
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Not sure what you did. Can you post the SQL statement you're actually using. Here's what I did:

I added a relationship between 2 and 5 in my table.
Attached File  table.png ( 12.09K )Number of downloads: 0


And when I opened the query, I see the implied relationship between 5 and 3.
Attached File  query.png ( 17.08K )Number of downloads: 0


Are you saying you want to see a relationship between 5 and 1 and then to 4 as well?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Feb 26 2018, 05:41 PM
Post#30



Posts: 5,518
Joined: 11-November 10
From: SoCal, USA


QUOTE
The commonality is at the discretion of the user. It can range anywhere from orders being for the same client or simply because the user just felt like relating them.

fair enough, though it's hard to imagine a real-world business model where this would be necessary. usually, you can get what you want by following the indirect relationships between entities. for instance, you can look at all the orders by a specific salesperson, and then segue to all the orders of customers who have an order by that salesperson. or all the salespeople who sold a specific item, and then get all the items (or item in a particular category) sold by those salespeople. etc, etc.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
mike60smart
post Feb 27 2018, 05:04 AM
Post#31


UtterAccess VIP
Posts: 12,739
Joined: 6-June 05
From: Dunbar,Scotland


Hi

I agree with Tina

I also would like to see the table relationships


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
moze
post Feb 27 2018, 11:44 AM
Post#32



Posts: 22
Joined: 8-January 18



theDBguy,

My apologies, I was able to recreate the recordset from your previous post.

To answer your question, yes, there should be a relationship to 1 for each and 4 should show 1, 2, 3 & 5
Go to the top of the page
 
moze
post Feb 27 2018, 12:03 PM
Post#33



Posts: 22
Joined: 8-January 18



Essentially, for this sample db with orders 1, 2, 3, 4 & 5, each order number should show the other 4 orders as related.
Go to the top of the page
 
theDBguy
post Feb 27 2018, 12:24 PM
Post#34


Access Wiki and Forums Moderator
Posts: 73,269
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I added a minor tweak to the query and got the following result:

Attached File  query.png ( 18.03K )Number of downloads: 0


Are you saying the query should also show the following?

1 - 5
4 - 5
5 - 1
5 - 4

Correct?

PS. Don't worry about the duplicate 2 - 3 showing in the result for now.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
moze
post Feb 27 2018, 01:19 PM
Post#35



Posts: 22
Joined: 8-January 18



Yes, Correct.
Go to the top of the page
 
theDBguy
post Feb 27 2018, 01:30 PM
Post#36


Access Wiki and Forums Moderator
Posts: 73,269
Joined: 19-June 07
From: SunnySandyEggo


Okay. Based on the current set of records, I was able to get the following result.

Attached File  query.png ( 10.54K )Number of downloads: 0


Let me do some more testing to make sure it is correct.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
moze
post Mar 1 2018, 10:17 AM
Post#37



Posts: 22
Joined: 8-January 18



What was the query you ran to get that result set? I can test on my end as well
Go to the top of the page
 
theDBguy
post Mar 1 2018, 02:31 PM
Post#38


Access Wiki and Forums Moderator
Posts: 73,269
Joined: 19-June 07
From: SunnySandyEggo


Hi,

I have been trying a couple different approaches but none of them is perfect yet. Here's the result from my latest experiment.

Table:
Attached File  table.png ( 11.62K )Number of downloads: 0


Query:
Attached File  query.png ( 15.03K )Number of downloads: 0


Can you tell me what's missing and what shouldn't be showing? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
zaxbat
post Mar 2 2018, 07:03 AM
Post#39



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


In my approach to this I am using a junction table to log all of the connections. It works fine. Question though.....easy enough to link in all of the implied connections when you connect one that brings 5 others with it.....

BUT, when you disconnect that one......does your app need to know to disconnect the other 5 that came with it. In my scenario, I do not keep track of (i want to call them) child connections and think that would be really complicated. So when I break a relationship...i only break one and do not break others.

Is that right, or does it matter?
This post has been edited by zaxbat: Mar 2 2018, 07:27 AM
Attached File(s)
Attached File  relationships.jpg ( 118.25K )Number of downloads: 4
 

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
zaxbat
post Mar 2 2018, 10:38 AM
Post#40



Posts: 1,184
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


At last check, Access did not support recursive SQL..... thinking this could be a lot easier if it did....

--------------------
Kindest regards, and Cheers!
ZAX

A picture is worth a thousand words and a zipped DB is worth a thousand pictures.
Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
3 Pages V < 1 2 3 >


Custom Search
RSSSearch   Top   Lo-Fi    25th September 2018 - 02:06 AM