Could create a new recordset based an exist recordset via SQL directly

  • Thread starter Thread starter Jason Jiang
  • Start date Start date
J

Jason Jiang

Could we create a new recordset based an exist recordset via SQL directly,
not from temparory table?

Thank you very much for your help
 
Show us an example of what you want to do. A recordset object has the
ability to be filtered and produce a new recordset from that, and there are
other options as well, but we'll need more details about what you want to
do.
 
Dear Ken
Thank you for your kind help.

My application is like that:

I need to analyze a product specification organized as xml format. The nodes
deep is dynamic.
My current solution is
First,
I read all the nodes recurstive to a table A as
Part Item Value
A 1 5.3
A 2 6.7
B 1 7.7
B 3 8.9

Second:
I use cross query to transform the table A to query B
PartName 1 2 3
A 5.3 6.7 -
B 7.7 - 8.9

Third:
I use another query C to get my answer based query B
PartName Answer:(function(1,2,3))
A 12
B 16.6

It's ok if I only ananylize one product once a time. But if I want to create
a class module to deal with this specification in case I need to compare two
or more products once a time, it will be wrong to use same temperory table.

Would you mind let me know how to deal with this directly without use
temperory table and euqery.

If you don't get this clearly, please let me know.

Thank you very much.

with best regards

Jason
 
I do not understand what the full question is, I'm sorry. When you say you
want to compare more than one item at a time, I don't know exactly what that
would mean based on the example data that you've presented. Can you post a
more complete example of what you would like to accomplish?
 
Dear Ken

Thank you for your mail.

The complete sample is too long.

Could you let me know if it's possible that I do these three steps directly
in recordsets without temporary table and query?

With best regards

Jason
 
Dear Ken

Thank you for your mail.

The complete sample is too long.

Could you let me know if it's possible that I do these three steps directly
in recordsets without temporary table and query?

With best regards

Jason
 
Without a better understanding of what you want to do exactly, I cannot say
with certainty whether it's possible nor how you'd do it. As I noted before,
it's possible to make new recordsets from an existing one, but I need a
better understanding of what you wish to accomplish before I can suggest an
approach -- I'm sorry. Maybe someone who is reading this thread has a better
idea of what you're asking?
 
Dear Ken

Could you give me your email? I would like to send you detail.

Thank you very much

Jason
 
I prefer to continue our communications here in the hewsgroup. Is the detail
proprietary such that you cannot post information here?
 
It's ok. I am really sorry I am not good at English. I am trying to make my
question clearly and avoid to get you involved in the cable fields issue.

All my cable specification is organized as xml files. like:

UL1015-18.xml
<UNIT ID="INSULATION" UNITNUM="1" UNITRELATE="NONE">
<SUBSTANCE>
<MATERIAL>
<NAME>PVC</NAME>
<MODEL>Lead Free PVC</MODEL>
</MATERIAL>
<SHAPE>Hollow</SHAPE>
<DIMENSION>
<DIAMETER>2.8</DIAMETER>
<THICKNESS>0.82</THICKNESS>
</DIMENSION>
</SUBSTANCE>
<UNIT ID="CONDUCTOR" UNITNUM="1" UNITRELATE="NONE">
<SUBSTANCE>
<MATERIAL>
<NAME>COPPER</NAME>
<MODEL>STC</MODEL>
<SPEC>16*0.254</SPEC>
</MATERIAL>
<SHAPE>Solid</SHAPE>
<DIMENSION>
<DIAMETER>1.16</DIAMETER>
<AWM>18</AWM>
</DIMENSION>
</SUBSTANCE>
</UNIT>
</UNIT>


I need to analyze this specification and automatically calculate the weight
of material.

My current solution is
First step,
I read all the nodes recurstive to a table A as

Table A (temporary) Part Item Value
INSULATION Multi 1
INSULATION SubNode PVC Lead Free PVC Hollow 2.8 0.82
INSULATION NAME PVC
INSULATION MODEL Lead Free PVC
INSULATION SHAPE Hollow
INSULATION DIAMETER 2.8
INSULATION THICKNESS 0.82
CONDUCTOR Multi 1
CONDUCTOR SubNode COPPER STC 16*0.254 Solid 1.16 18
CONDUCTOR NAME COPPER
CONDUCTOR MODEL STC
CONDUCTOR SPEC 16*0.254
CONDUCTOR SHAPE Solid
CONDUCTOR DIAMETER 1.16
CONDUCTOR AWM 18


Second step:
I use cross query to transform the table A to query B
Material List Query Part NAME MODEL SPEC SHAPE AWM DIAMETER Multi SubNode
THICKNESS
CONDUCTOR COPPER STC 16*0.254 Solid 18 1.16 1 COPPER STC 16*0.254
Solid 1.16 18

INSULATION PVC Lead Free PVC
Hollow
2.8 1 PVC Lead Free PVC Hollow 2.8 0.82 0.82




Third step:
I use another query C to get my answer based query B

Weight List Query Part NAME MODEL SPEC Density Weight
CONDUCTOR COPPER STC 16*0.254 8.9 9.407
INSULATION PVC Lead Free PVC
1 5.101


It's seems ok if I only ananylize one product a time.

And now, I thought it would be a better way to pack this function to a class
module.
Here is my problem, if I want to deal with this in this way, when I create
two instances of products in same time , I thought the temporary table A
would be wrong.

I need to find out the way to do this without temporary tables and queries.
Such like
a. I create a recordset directly instead of using table A.(I already know
how to do)
b. When the first step finished, I need to carry on the second step
directly from the recordset without Table A and query B.
C. The same to the third step,

I thought I need a way to realize this:
Old way: Recordset A --> Table A ----- SQL String ----> Recordset
B
Target way: Recordset A ----------------- SQL String ----> Recordset
B


I don't know if I make this clear, Please help me. I am a sales man and not
a professional programmer. But I love programming, I always want my program
perfect. This has tortured me for couple weeks.

Thank you very much sincerely

Jason
 
I did not get an opportunity to review your post last night. I will post a
reply later today.
 
Work is keeping me busy tonite... I will reply, but it will be tomorrow at
earliest ... sorry for additional delay....
 
OK - I have a better understanding of what you are wanting to do.

Is your concern about using the temporary table because the data from one
product will still be in the table when you want to analyze the next
product? It appears to me that you are analyzing one product at a time in
this setup? Let me know if I am understanding this concern correctly.

If your concern is what I've asked, you can "empty" the table after the
first analysis (using a delete query) before you start another analysis. A
class module could do this as part of its initialization code steps.

My experience has been that using a table with indices will let a query run
much faster than trying to manipulate data in queries, which have no
indices. And making one query from another query at a minimum will involve
temporarily creating a query in the database (kind of like a temporary
table) so that the next recordset can see it and use its data. So I don't
think this will be better than using a temporary table, in my opinion.

ADO recordsets can be disconnected (unlike DAO recordsets), but I don't
think they would offer any advantage to you in this case.
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Recordset too large? 10
A theoretical question: how best to manage perpetual recordset needs? 25
Make table from recordset 2
Disconnected recordset with filter and sort appliedshows only 100 records when bound to an access co 0
email several reports using Do Loop and Recordset 7
Recordset changes underlying table automatically 5
Problem filtering records with many to many relationships 4
Creating a parameterised editable query 7

Back
Top