I am printing a Crystal Report from VB off an ADO recordset. The DB is SQL Server 6.5. This report requires information from many different tables. One of the peices of information i need to get is the camp run grade for each species. The camp run grade is defined as the lowest ranked(biggest number in rank column) Merch grade(gradetyperec column. Merch = 1). In the table below the record with rec = 18 is the camp run grade for species 1 and the graderec column for that record is what i'm interested in. Getting this peice of information requires a 2 table join. Here is the querey i used.
select spgr.*, G.gradetyperec
from spgr
JOIN grade as G ON (spgr.graderec = G.rec)
where spgr.speciesrec = 1
order by spgr.rec
rec speciesrec graderec rank gradetyperec
----------- ----------- ----------- ----------- ------------
1 1 1 1 1
2 1 2 2 1
3 1 3 3 1
4 1 16 4 1
5 1 4 5 1
6 1 18 6 1
7 1 5 7 1
8 1 6 8 1
9 1 19 9 1
10 1 7 10 1
11 1 34 11 1
12 1 8 12 1
13 1 29 13 1
14 1 21 14 1
15 1 22 15 1
16 1 23 16 1
17 1 24 17 1
18 1 12 18 1
19 1 28 19 2
20 1 31 20 2
21 1 35 21 2
22 1 9 22 2
23 1 30 23 2
24 1 13 24 3
What i would like to do is either be able to incorporate this info into my report JOIN statement (join to a table with a speciesrec but no graderec so the resulting column would be the graderec from the camp run grade for that species). Can anyone help me with this? As another option i could build the table in advance by selecting all camp run records from the spgr table and putting them into a temp table i can join to by species rec. Something like this:
rec speciesrec camprungraderec
--- ---------- ---------------
1 1 12
This would be a small table (35 - 50 rows) and joining to it shouldn't incur as much overhead as the convoluted JOIN i would need to get it done the other way. I guess all i really need help with is how i would select that rec = 18 record from this table given only the speciesrec = 1 and the rules about the camp run grade. I should be able to take it from there. TIA Ruairi
Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.
select spgr.*, G.gradetyperec
from spgr
JOIN grade as G ON (spgr.graderec = G.rec)
where spgr.speciesrec = 1
order by spgr.rec
rec speciesrec graderec rank gradetyperec
----------- ----------- ----------- ----------- ------------
1 1 1 1 1
2 1 2 2 1
3 1 3 3 1
4 1 16 4 1
5 1 4 5 1
6 1 18 6 1
7 1 5 7 1
8 1 6 8 1
9 1 19 9 1
10 1 7 10 1
11 1 34 11 1
12 1 8 12 1
13 1 29 13 1
14 1 21 14 1
15 1 22 15 1
16 1 23 16 1
17 1 24 17 1
18 1 12 18 1
19 1 28 19 2
20 1 31 20 2
21 1 35 21 2
22 1 9 22 2
23 1 30 23 2
24 1 13 24 3
What i would like to do is either be able to incorporate this info into my report JOIN statement (join to a table with a speciesrec but no graderec so the resulting column would be the graderec from the camp run grade for that species). Can anyone help me with this? As another option i could build the table in advance by selecting all camp run records from the spgr table and putting them into a temp table i can join to by species rec. Something like this:
rec speciesrec camprungraderec
--- ---------- ---------------
1 1 12
This would be a small table (35 - 50 rows) and joining to it shouldn't incur as much overhead as the convoluted JOIN i would need to get it done the other way. I guess all i really need help with is how i would select that rec = 18 record from this table given only the speciesrec = 1 and the rules about the camp run grade. I should be able to take it from there. TIA Ruairi
Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.
