Troy Can you help me again Please 
Author Message
 Troy Can you help me again Please

Hi

I tried using some of the results that worked in one database to work in
this one and am not getting the right results, Hopefully one of you here
might be able to help me out.

I have two tables one "Anodes", one called "Cathodes"
I run a querry where AnodeWeight/CathodeWeight must be BETWEEN 2.86 AND
2.96.  Each anode and cathode is linked through Electrode and Batch Number.
This works, but I get too many results showing.

Both tables have approx 50 records in each, So needless to say I can have a
querry that returns thousands of things. Because each Anode Electrode could
possibly match with each Cathode Electrode which would return a querry of
2500 plus.  I tried with smaller number of records and have lots of returns.

How can I get

ie.

Anode Electrode Number  x  to go through all CathodeElectrodes, do the
calculation to get the ratio, and upon the first one that falls into the
category, goto the next Anode Electrode Number do the calculation and again
if it falls in the ratio go to the next, and if not go to the next Cathode
Electrode do the calculation and so on.

BUT HERE IS THE CATCH  :)
Once one anode number is used or once one cathode number is used, it can not
be used again.......But moved into a table so it is always on record,.
 THis part I can do I think, do an append querry once a match is found.))

Thanks again if anyone can help.

Mike Biemans

CNA



Sun, 26 Aug 2001 03:00:00 GMT  
 Troy Can you help me again Please
I don't have a clear picture of what you are trying, but..

My gut reaction is that you should use 2 recordsets in code and "walk"
through the data just like in the old dBase days.
Get the first record form table1 and use it as you go through all the
records in table2. Then loop back to table1, get the next value and do it
again.  Whenever, you get an "interesting" value store it in a temp table
and keep going.
When done, the temp table should be what you are looking for.

This type of looping usually can be accomplished with some fancy SQL which
is over my head. (Multiple nested subqueries).  If you want the name of a
SQL guru I can give you one.

Quote:

>Hi

>I tried using some of the results that worked in one database to work in
>this one and am not getting the right results, Hopefully one of you here
>might be able to help me out.

>I have two tables one "Anodes", one called "Cathodes"
>I run a querry where AnodeWeight/CathodeWeight must be BETWEEN 2.86 AND
>2.96.  Each anode and cathode is linked through Electrode and Batch Number.
>This works, but I get too many results showing.

>Both tables have approx 50 records in each, So needless to say I can have a
>querry that returns thousands of things. Because each Anode Electrode could
>possibly match with each Cathode Electrode which would return a querry of
>2500 plus.  I tried with smaller number of records and have lots of
returns.

>How can I get

>ie.

>Anode Electrode Number  x  to go through all CathodeElectrodes, do the
>calculation to get the ratio, and upon the first one that falls into the
>category, goto the next Anode Electrode Number do the calculation and again
>if it falls in the ratio go to the next, and if not go to the next Cathode
>Electrode do the calculation and so on.

>BUT HERE IS THE CATCH  :)
>Once one anode number is used or once one cathode number is used, it can
not
>be used again.......But moved into a table so it is always on record,.
> THis part I can do I think, do an append querry once a match is found.))

>Thanks again if anyone can help.

>Mike Biemans

>CNA



Sun, 26 Aug 2001 03:00:00 GMT  
 Troy Can you help me again Please
You don't actually need a select query. The matching cathodes /anodes can be
appended directly into the new CathodeAnode table if you make sure that the
cathodeID and anodeID fields in the CathodeAnode table are marked as indexed(no
duplicates).
When you append the records Access will prevent each cathode and anode from
being entered more than once.

Function insertMatchingAnodesAndCathodes()
Dim db As Database
Dim strSql As String

strSql = "INSERT INTO AnodeCathode ( AnodeID, CathodeID ) "
strSql = strSql & "SELECT Anodes.AnodeID, Cathodes.CathodeID "
strSql = strSql & "FROM Anodes, Cathodes "
strSql = strSql & "WHERE ((([anodesize]/[cathodesize]) "
strSql = strSql & "Between 2.86 And 2.96));"

Set db = CurrentDb
db.Execute strSql
MsgBox "Number of records inserted is " & db.RecordsAffected
Set db = Nothing
End Function

Seeya
Chris Bergmans



Quote:
>I don't have a clear picture of what you are trying, but..

>My gut reaction is that you should use 2 recordsets in code and "walk"
>through the data just like in the old dBase days.
>Get the first record form table1 and use it as you go through all the
>records in table2. Then loop back to table1, get the next value and do it
>again.  Whenever, you get an "interesting" value store it in a temp table
>and keep going.
>When done, the temp table should be what you are looking for.

>This type of looping usually can be accomplished with some fancy SQL which
>is over my head. (Multiple nested subqueries).  If you want the name of a
>SQL guru I can give you one.


>>Hi

>>I tried using some of the results that worked in one database to work in
>>this one and am not getting the right results, Hopefully one of you here
>>might be able to help me out.

>>I have two tables one "Anodes", one called "Cathodes"
>>I run a querry where AnodeWeight/CathodeWeight must be BETWEEN 2.86 AND
>>2.96.  Each anode and cathode is linked through Electrode and Batch Number.
>>This works, but I get too many results showing.

>>Both tables have approx 50 records in each, So needless to say I can have a
>>querry that returns thousands of things. Because each Anode Electrode could
>>possibly match with each Cathode Electrode which would return a querry of
>>2500 plus.  I tried with smaller number of records and have lots of
>returns.

>>How can I get

>>ie.

>>Anode Electrode Number  x  to go through all CathodeElectrodes, do the
>>calculation to get the ratio, and upon the first one that falls into the
>>category, goto the next Anode Electrode Number do the calculation and again
>>if it falls in the ratio go to the next, and if not go to the next Cathode
>>Electrode do the calculation and so on.

>>BUT HERE IS THE CATCH  :)
>>Once one anode number is used or once one cathode number is used, it can
>not
>>be used again.......But moved into a table so it is always on record,.
>> THis part I can do I think, do an append querry once a match is found.))

>>Thanks again if anyone can help.

>>Mike Biemans

>>CNA



Tue, 28 Aug 2001 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP, PLEASE HELP,

2. Can anyone HELP me PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE PLEASE

3. UDP again - please help ms!

4. Please HELP Again!

5. Help with Passwords = again please

6. VB 3 Memory Problems again, PLEASE HELP!!!

7. help please again?????????

8. Please help again: VB3 and Memory Limitations

9. Please help me again

10. Posting Again Please Help With Status Bar

11. Please Help Again!

12. Please Help Again!

 

 
Powered by phpBB® Forum Software