Colin Freas wrote:
>
> Our system makes use of two datasets, one for records a year old and
> older, another for newer information.
>
> I'm trying to do a query, incorporating information from both sets.
>
> In the first step, I'm writing data from the older set to a subfile,
> and in the second, I'm using, or trying to use that subfile, when I
> access our more recent data. Unfortunately, I'm having no luck in
> several different approaches, so I'm asking for any help or ideas.
>
> This program creates the subfile, and works perfectly:
>
> > SET REPORT LIMIT 1000000
> > ACCESS ARC-DETL
> > SELECT IF ARC-COMPONENT="2" AND ARC-GRADE>="E5" AND ARC-GRADE<="E8" &
> > AND ARC-COURSE[3:4]="00" AND ARC-LASTTRAN[1:1]="Z"
> > SORT ON ARC-SSN
> > SET SUBFILE NAME ARCBOARD INDEX ARC-SSN KEEP
> > REPORT SUMMARY ARC-GRADE ARC-SSN ARC-COURSE ARC-LASTTRAN
>
> This is the start of the second program, which is not working at all.
> I've tried several different permutations of the ACCESS line to no avail:
>
> 1 SET REPORT LIMIT 100
> 2 ACCESS *ARCBOARD LINK TO STUD-CRS-DETL-H AND STUD-INFO-DETL
> 3 SELECT IF COMPONENT="2" AND GRADE>="E5" AND GRADE<="E8" AND &
> 4 COURSE[3:4]="00" AND LASTTRAN[1:1]="Z"
> 5 SORT ON SSN
> 6 REPORT SSN COURSE LASTTRAN
Hi Colin!
You didn't say if key values in the ARC-DETAIL file were or were not in
the STUD-CRS-DETL-H and/or STUD-INFO-DETL files, SO, I'll assume they're
NOT, and suggest that you use ONE subfile, with the data from the most
recent data file(s) appended to the data from the older set. For
example:
(my additions in lower case)
> SET REPORT LIMIT 1000000
> ACCESS ARC-DETL
> SELECT IF ARC-COMPONENT="2" AND ARC-GRADE>="E5" AND ARC-GRADE<="E8" &
> AND ARC-COURSE[3:4]="00" AND ARC-LASTTRAN[1:1]="Z"
> define grade character * 2 = arc-grade
> define ssn character * 10 = arc-ssn
> define course character * 4 = arc-course
> define lasttran character * 2 = arc-lasttran
> SORT ON SSN
> SET SUBFILE NAME ARCBOARD INDEX SSN KEEP
> REPORT SUMMARY GRADE SSN COURSE LASTTRAN
> go
>
> ACCESS *ARCBOARD LINK TO STUD-CRS-DETL-H AND STUD-INFO-DETL
> SELECT IF COMPONENT="2" AND GRADE>="E5" AND GRADE<="E8" AND &
> COURSE[3:4]="00" AND LASTTRAN[1:1]="Z"
> SORT ON SSN
> set subfile name arcboard index ssn keep append
> REPORT summary grade SSN COURSE LASTTRAN
> go
NOTE!: I've made some assumptions about the size of the fields in the
'define' statements in the first pass - these 'defines' must be
set to the same size and type as the fields appended in the
second pass!
The above example will give you a subfile with the qualifying records
from both your archive file, and your current data files. This subfile
can then be used to create a report, or whatever.
> When I was fashioning these two sets of data together, I was trying to
> use an index or SSNs in the data, but the names for the indexes were
> different. In one, SSN in the other ARC-SSN. I couldn't find anyway
> for it to change the name of a field, or to force it to map the indices
> onto one another.
>
> If there is any way to do this, or if there's an easier way to do all
> this, I'd appreciate any advice.
>
> Any help appreciated in advance.
>
> C. Freas
In answer to your second post, there *is* a way to use keys of different
names:
> ACCESS *ARCBOARD LINK arc-ssn viaindex ssn TO STUD-CRS-DETL-H &
> AND STUD-INFO-DETL
I hope this helps, if you have any additional questions, please e-mail
again!
+------------------------------------------------------------------+
My mind is my own, as are my ideas and opinions.
My heart, body, and soul, however, all belong to others. Tim.
+------------------------------------------------------------------+
Tim Ericson tericson (503) 526-4440 (direct number)
Sr. Programmer/Analyst @denkor DenKor Dental Management Corp.
(& Systems Manager) .com (aka Willamette Dental Group)
http://www.teleport.com/~dental
+------------------------------------------------------------------+
^^
it's official! note! --> new domain name! yeah!
|
