473,320 Members | 2,073 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

tracing relationships

I am trying to solve a connectivity problem. A simplification:

tblTemp

Section Connected_Section id
A B 1
B C 2
C D 3
D E 4
G H 5
H I 6

What I want to do is set the id so that it groups the above into
similarly labelled blocks, as below:

Section Connected_Section id
A B 1
B C 1
C D 1
D E 1
G H 5
H I 5
I have used the following

UPDATE (tblTemp AS a INNER JOIN tblTemp AS b ON
b.section=a.connected_section) SET b.id=a.id

This works recursively upto a point and then fails to change some
records. Leaving the only option of rerunning the above query over and
over.

similarly I have also tried

UPDATE tblTemp AS a, tblTemp AS b SET b.id=a.id WHERE
b.section=a.connected_section

Is there anyway that I can make this follow through the whole table.
Some groups have about 120 connections so I don't want to have to loop
the query 120 times. Also the records are not in any order and sorting
will not put them into a correct order.

Please help

Si

Nov 13 '05 #1
2 1035
> I am trying to solve a connectivity problem. A simplification:

Glad to see this, I've been fighting a similar problem.
I have used the following

UPDATE (tblTemp AS a INNER JOIN tblTemp AS b ON
b.section=a.connected_section) SET b.id=a.id

This works recursively upto a point and then fails to change some
records. Leaving the only option of rerunning the above query over and
over.
Which is what I ended up doing. You can examine the RecordsAffected
property of the querydef (if you put it in a query) or the database (if
you .Execute it) to see if you still have work left to do.
Is there anyway that I can make this follow through the whole table.
Some groups have about 120 connections so I don't want to have to loop
the query 120 times. Also the records are not in any order and sorting
will not put them into a correct order.


You don't want to... There is another approach. Use an intermediate
table Progress(section, id)

For every record
is Section in Progress? ("left side")
yes-> change tblTemp.id into Progress.id
is there more than one matching record, with different ids, in
Progress? ("merger")
yes-> update tblTemp changing highest id into lowest
change Progress changing highest id into lowest
no -> is Connected_Section in Progress? ("right side")
yes-> change tblTemp.id into Progress.id
do same merger check as above
no -> add Section and Connected_Section to Progress

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
For human replies, replace the queue with a tea

Nov 13 '05 #2
It works!

I wasn't sure about speed but it is really fast too.

Many thanks

Si

Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Trace User | last post by:
Hello, I have a design question regarding Tracing and Trace Switches. I understand that Trace Switches can be configured through an application's .config file. When a switch is instantiated,...
0
by: Paul Ibison | last post by:
HI when I have a page which calls a component I want to do the following - tracing set to false on the pag tracing set to true in the componen tracing set to false in the page after the call to...
5
by: Dabbler | last post by:
When I first start up an ASP.NET application with tracing enabled in web.config the first few pages show trace at bottom of the page but then at some point the pages return to normal with no trace...
6
by: serge calderara | last post by:
Dear all, I have an applicatin that generate a querry to an SQL server, then display results on a second webform. I try to see how tracing works, then I have notice that as soon as I...
2
by: deepukutty | last post by:
Hi all, I know tht we can do tracing in two ways.one in application level and the other is at Page level. I am able to see the details of trace either on the page itself or .../trace.axd page....
45
by: salad | last post by:
I'm curious about your opinion on setting relationships. When I designed my first app in Access I'd go to Tools/Relationships and set the relationships. Over time I'd go into the window and see...
0
by: cnys | last post by:
We have an ASP.NET 2.0 (C#) app and we're trying to add tracing into it. The tracing functionality within .NET is great, but when we output this to a file, it's kind of sparse. So, we're looking...
0
by: GB | last post by:
All, There's a few messages around about the Environment.GetResourceString causing a "Resource lookup failed - infinite recursion detected." error but nothing detailed. I have a problem...
0
by: rehto | last post by:
We have an ASP.NET 2.0 (C#) app and we want to enable tracing (see the code snippets below). The first time a user navigates to the app., the tracing works fine (the ASP.NET tracing appears on...
1
by: RedLars | last post by:
Hi Does the class System.Diagnostics.Trace use a singelton ? I'm able to do this; System.Diagnostics.Trace.WriteLine("test"); However, these give compiler errors; System.Diagnostics.Trace...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.