Macro function in Paradox 9 ?

[pieternl]

Good Day All,

I wonder if there is a macro function in Paradox 9 that makes it possible to run a query, and subsequently close the window with the answer, then run a second query, and subsequently close the window with the answer, and so on.

Thanks in advance.

Kind regards,
peter

 

[Dean4144]

Could you ask your question a little more clearly? For instance;

are you running the second query using the information generated by the first query?

what is the end use of the information you generate from the two queries?

 

[pieternl]

Dean4144 (TechnicalUser) 5 Nov 10 23:30
wrote:

Could you ask your question a little more clearly? For instance;
are you running the second query using the information generated by the first query?
>>
>>
Yes, actually it is a combination of 4 queries of which each uses the information generated by the former query, and this cycle of 4 queries has to be repeated up to 40 times depending on the target data.
**

what is the end use of the information you generate from the two queries?
>>
>>
The queries calculate the percentage of impact (aka contribution of blood) that an ancestor has on its descendants in a pedigree database. I use two tables that both contain an identical copy of the pedigree collection.
The relevant fields contain: name / sire / dam plus three numerical fields for name value / sire value / dam value.
The target ancestor gets the value 100 filled in one table.
The queries subsequently take care that the kids of that ancestor get value 100/2=50, and then the cycle is repeated for the kids of those kids etc.

There is most likely a more clever way to do this, but I don't have that sort of skill.

Thank you for listening and for your help.

Kind regards,
Peter van Arkel

 

[Dean4144]

Peter, could you give me a clue about how you do this. Is your method coded or do you have separate queries that you run?

I ask this question, because usually, I would expect to be sitting looking at a form of some sort when the basic question gets asked. Regardless of the number of tables your code might visit, you should eventually get to the point where you can say "it is done!" All this from a single click on a button and perhaps the setting of some parameters as the code sets off.

Ross.

 

[pieternl]

Hi Ross,


Dean4144 (TechnicalUser)
You wrote:
Peter, could you give me a clue about how you do this. Is your method coded or do you have separate queries that you run?
>>
>>
No, it is plain and simple mouse work in query windows...I guess you could call it a quick and dirty method .
I have two tables:
--------------------------
MASTER-table (with 4 relevant fields)
NAME // SIRE // DAM // ImpactValue
(at the start -100- is entered in the target ImpactValue field)
--------------------------
SUBFILE-table (with 6 relevant fields)
NAME // SIRE // DAM // NameValue // SireValue // DamValue
--------------------------

and 4 QUERIES:
--------------------------
the first query links the two tables via the NAME & SIRE fields
and enters the content of ImpactValue in the (empty) SireValue fields
--------------------------
the second query links the two tables via the NAME & DAM fields
and enters the content of ImpactValue in the (empty) DamValue fields
--------------------------
the third query addresses the SUBFILE-table and adds up the values
in the SireValue & DamValue fields, divides the outcome by factor 2,
and enters that result in the (empty) NameValue fields
--------------------------
the fourth query links the two tables via the NAME & NAME fields
and enters the content of the NameValue fields in the (empty)
ImpactValue fields
--------------------------

And then the cycle is repeated.
**

You wrote:
I ask this question, because usually, I would expect to be sitting looking at a form of some sort when the basic question gets asked. Regardless of the number of tables your code might visit, you should eventually get to the point where you can say "it is done!" All this from a single click on a button and perhaps the setting of some parameters as the code sets off.
>>
>>
Because I do this manually I see that the job is done when the 'answer' windows don't show changes anymore. I assume that if it is possible to automate the process, that it is necessary to limit the number of cycles in order to avoid a loop situation. The data (dog pedigrees) I work with are quite detailed so I can see how many generations away a dog is from the present, the maximum number of generations at present is 40.

Kind regards,
Peter van Arkel

 

[Dean4144]

Okay, I think I can see your thought process.

Two thoughts:

One, I would be tempted to set up just the one table and use calculated fields.

The table would them contain the fields; NAME // SIRE // DAM // ImpactValue //NameValue // SireValue // DamValue.

At data entry when you pressed enter after filling the ImpactValue field, the remaining fields would be calculated and filled.


Second, rather using queries, I would probably be looking at using a Tcursor and computing all the values on one pass.
(Scan perhaps)(While loop)

What I don't yet grasp, is why you have to repeat this process over and over. (did you say 40 times?)

 

[pieternl]

Hi Ross,


Dean4144 (TechnicalUser) 6 Nov 10 15:26
You wrote:
Okay, I think I can see your thought process.
Two thoughts:
One, I would be tempted to set up just the one table and use calculated fields.
The table would them contain the fields; NAME // SIRE // DAM // ImpactValue //NameValue // SireValue // DamValue.
At data entry when you pressed enter after filling the ImpactValue field, the remaining fields would be calculated and filled.
>>
>>
As said, there are probably better ways to do it than the method I use. I am dependant on, and limited to, what I can achieve with basic operators in queries.
But I also do not 'see' how it can be done within a single table.
**

Second, rather using queries, I would probably be looking at using a Tcursor and computing all the values on one pass.
(Scan perhaps)(While loop)
>>
>>
I would not know how to use/implement those functions.
**

What I don't yet grasp, is why you have to repeat this process over and over. (did you say 40 times?)
>>
>>
That depends on how many generations there are between the target ancestor and its current descendants.
The table I am working with at the moment contains some 11.000 dogs born between 1998 and 2008 plus
some 12.000 ancestors born between 1915 and 1997.
There are 40 generations in between the most ancient founder and the most recent descendant.
When I select for example a certain ancestor born in 1945 and in the 13th generation I probably have to repeat the process 27 times in order to produce values for all its descendants. When the target ancestor is born in say 1980, it will only take something like 10 times.
Maybe I misunderstand your reservation, but the average dog pedigree is not much like a human pedigree in terms of inbreeding. Certain popular studs from the past produced dozens of kids and sometimes a hundred or more grandkids.
At some point (often already in the third or fourth generation) the descandents of an ancestor are crossed. Certain popular ancestors from the past occur hundreds, even thousands, of times in a current pedigree.
You can also deduct the extent to which this happened from the fact that in the pedigrees of 11.000 current dogs the combined ancestry only adds up to 12.000 different unique individuals while the number of 'possible' ancestors in a 40 generation deep pedigree is astronomical.
But maybe I also did not make clear enough what I am doing.
Besides that the target ancestor gets value 100 at the start, some 30 founder dogs (with unknown parentage) get value zero, and all the dogs born at an earlier date than the target ancestor get value zero too.
During the first cycle the entire first generation (whose value fields are still blank) out of the parents with a value entered gets its own value, thus either 50 or zero.
During the next cycle the entire second generation (whose value fields are still blank) gets its own value based on the values of the preceding generation, thus either 25 or zero.....or 50 in cases where a brother and sister were mated.
Etc.
Because I don't see how this can be done in one go, or in a single table, I ended up with the method as described. I have no doubt that a programmer would come up with a much better method.
For the time being I would be very happy with a macro-like solution that runs the queries as a series and 'skips' the opening of answer/changed windows.
Alas, I cannot export the queries as SQL to another well known database program that has a standard macro function, probably because my queries rely on the 'changeto' operator.

Kind regards,
Peter van Arkel

 

[Dean4144]

Okay Peter,

I think I am getting closer to your issue. Can I check:

Do you call the queries from a form, or picking up your description of a mouse, do you simply double click on the queries in a file list and run them?

The coding required to achieve the task you seem to be describing is not complex and I would be happy to offer some lines but it needs to happen from a form.

Ross

 

[pieternl]

Thank you.