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 19 2018, 02:34 PM
Post#1



Posts: 22
Joined: 8-January 18



Hi,

This is my first time posting on UA so hopefully I put this post in the right section..

I am working with a database that displays information on a form related to whichever order number you are viewing. On this form, I need to have a text box with an 'Add' button where you can enter other order numbers that are related to the current order you are on. Once 'Add' is clicked, the order number you entered is displayed in a listbox.

For example, If I am viewing order 1000, and relate orders 1001, 1002 and 1003, those 3 order numbers will display in the list box. If I am viewing order 2000, and relate order 1000, then orders 1001, 1002 and 1003 should also be related to order 2000 and vice versa.

In short, any order number entered should be related to the current order being viewed, any other order numbers in the listbox AND any order numbers related to those. Essentially a chain of related orders.

I hope I explained that well enough. There is probably a simple answer I am overlooking. Does anyone know of a way to do this?

Many thanks in advance
Go to the top of the page
 
theDBguy
post Feb 19 2018, 02:47 PM
Post#2


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


Hi,

Welcome to UtterAccess!
welcome2UA.gif

Do you have a separate table to store these related order numbers? If not, you may need one if you want to persist the assigned relations. Also, why a list box? Have you considered using a subform?

Just curious...
Go to the top of the page
 
GroverParkGeorge
post Feb 19 2018, 02:53 PM
Post#3


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


Further to theDBGuy's questions, you might benefit from studying more about how relational databases, like Access, work.

Go to the top of the page
 
moze
post Feb 20 2018, 09:50 AM
Post#4



Posts: 22
Joined: 8-January 18



Thanks Grover, I'll check that out!

DBguy, the list box was the just the first thing that came to mind when I started working on this but it may not be the best option. And yes, I have code behind the add button that creates a record in a separate table with the current order number and each related order number entered.
Go to the top of the page
 
projecttoday
post Feb 20 2018, 09:59 AM
Post#5


UtterAccess VIP
Posts: 10,069
Joined: 10-February 04
From: South Charleston, WV


What is your current table setup?
Go to the top of the page
 
theDBguy
post Feb 20 2018, 10:50 AM
Post#6


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


Hi moze,

Thanks for the additional information. Since you want to display the related table's data on the form anyway, you can simply add a subform to it, and you won't need to use any code at all to add data to the other table.
Go to the top of the page
 
moze
post Feb 20 2018, 12:01 PM
Post#7



Posts: 22
Joined: 8-January 18



Right now, I have a table called tblOrder which stores each order number with the respective order information. Then I have a table called tblOrder_Related which stores the order number and the related order number entered each time the add button is clicked.
Go to the top of the page
 
theDBguy
post Feb 20 2018, 12:06 PM
Post#8


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


Hi,

Understood. What I am saying is the "normal" approach to display, edit, and add records to a related table is through the use of subforms. In that way, you won't need to create any code just to add records to the related table.

Hope it makes sense...
Go to the top of the page
 
moze
post Feb 20 2018, 02:16 PM
Post#9



Posts: 22
Joined: 8-January 18



Okay, so I related orders 1001 and 1002 to order 1000 and I am able to see them appear when they are added. Since 1001 and 1002 are both related to order 1000, by default, they should both also relate to each other. This is where I am stuck. I can go to order 1001 and see order 1000 but I also want to see 1002 as related.
Go to the top of the page
 
theDBguy
post Feb 20 2018, 02:40 PM
Post#10


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


Hi,

You can certainly automate that, but there's nothing automatic in Access that will do that. For example, the main table might look like this:

OrderID
1000
1001
1002

And the related table might look like this after relating 1001 and 1002 to 1000:

ID, PriOrderID, RelatedOrderID
1, 1000, 1001
2, 1000, 1002

But as you can see, to relate 1001 to 1002 as well, an extra record needs to be added to the table, such as:

1, 1000, 1001
2, 1000, 1002
3, 1001, 1002

Imagine if there are more than two records related to one Order, you would almost have a Factorial number of records in the related table.

However, thinking more about this, it may not be necessary to add the extra records. Perhaps we can use a query to show all related records as well. I'll have to experiment with it to see if it's possible.

Just my 2 cents...
Go to the top of the page
 
moze
post Feb 20 2018, 04:00 PM
Post#11



Posts: 22
Joined: 8-January 18



The database I am looking to implement this into has over 10,000 records so creating a new record for each related order related to another related order(if that makes sense) may take up a bit of space after a period of time and slow down the process I'm assuming. So a query to somehow relate them may be the better option. Due to my experience(or lack there of), I have not been able to find anything. The example table structures from your last post are how my tables are setup.
Go to the top of the page
 
theDBguy
post Feb 20 2018, 04:06 PM
Post#12


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


Hi,

Either way we go with this moving forward, I still don't see any reason to use code. You should try using a subform to assign the related records. We'll try to come up with a query to "show" the secondary or implied relationships between related records.

To help us move along, you might consider posting a sample copy of your db with some test data to play with.

Cheers!
Go to the top of the page
 
GroverParkGeorge
post Feb 20 2018, 04:56 PM
Post#13


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


PMFJI:

Sometimes, when one starts out with a design that seems like it "ought to work", it's hard to pull back and accept guidance that suggests maybe there is a better way. Abandoning that early work is hard.

However, I think theDBguy has been giving you solid advice and that you should take it rather than pursuing what appears to him--and to me--to be a dead-end, just because that's how it started.

Please don't take offense to hearing that there most likely is a better way. It's just that we've seen many, many, many databases over the years and we know what works well, and what doesn't.
This post has been edited by GroverParkGeorge: Feb 20 2018, 05:21 PM
Go to the top of the page
 
moze
post Feb 21 2018, 02:11 PM
Post#14



Posts: 22
Joined: 8-January 18



No offense taken! The way I was describing was just the original way that was suggested to approach this but if there is a better way, I am more than open to taking that approach instead. I've attached a sample db with some test data if you would like to take a look.
Attached File(s)
Attached File  RelatedOrders.zip ( 29.23K )Number of downloads: 12
 
Go to the top of the page
 
mike60smart
post Feb 22 2018, 03:40 PM
Post#15


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


Hi moze

I would suggest that you need a Main Form based on Orders and a Subform based on Related.

See the attached example

Attached File  RelatedOrders.zip ( 27.03K )Number of downloads: 14
Go to the top of the page
 
moze
post Feb 26 2018, 11:23 AM
Post#16



Posts: 22
Joined: 8-January 18



Okay, so is there a way to show related orders of the other related orders?

For example, order 1000(parent) has orders 1001, 1002, and 1003(children) related to it so they should all be related as well. So if I view order 1001, I would like to see orders 1000, 1002, and 1003. Or if I view order 1002, I would like to see orders 1000, 1001, and 1003. Essentially relating all of the related orders together and not just to the order number they were added to. Is there a query that would do this?
Go to the top of the page
 
theDBguy
post Feb 26 2018, 12:39 PM
Post#17


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


Hi,

A query similar to the following should show all "related" records:

CODE
SELECT tblOrder_Related.order_id, tblOrder_Related.related_id
FROM tblOrder_Related
UNION
SELECT tblOrder_Related.related_id AS parent, tblOrder_Related_1.related_id AS child
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))

Hope it helps...
Go to the top of the page
 
mike60smart
post Feb 26 2018, 12:40 PM
Post#18


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


Hi

I would ask WHY you need to see this??

Essentially an Order Nr has 3 Order items 1001 / 1002 & 1003

Why would need to say Order Item 1002 is related to Order 1001 & 1003

This to me is making it more complicated that it actually is



Go to the top of the page
 
zaxbat
post Feb 26 2018, 12:55 PM
Post#19



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


My main question would be.... are these orders all peers? are they all at the same level? OR, is there a hierarchy? If they are peers and need a circular reference.....wow.....sounds problematic.
Go to the top of the page
 
tina t
post Feb 26 2018, 01:14 PM
Post#20



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


QUOTE
For example, If I am viewing order 1000, and relate orders 1001, 1002 and 1003, those 3 order numbers will display in the list box. If I am viewing order 2000, and relate order 1000, then orders 1001, 1002 and 1003 should also be related to order 2000 and vice versa

my question is: what do orders 1000 - 1003 have in common that makes you want to "relate" them? and what commonality do orders 2000 and 1000 share? and is it the same thing that 1000 - 1003 share?

what i'm wondering is if we're missing a core relational key that will allow these orders to be grouped appropriately.

hth
tina
Go to the top of the page
 
3 Pages V  1 2 3 >


Custom Search
RSSSearch   Top   Lo-Fi    25th September 2018 - 04:23 PM