Re: Qryout file problem

John Taylor <John.Taylor@britax-pmg.com> wrote:

>I need to generate a file containing part numbers and locations
>with one record per part number with multiple locations, from a
>file containing multiple records per part number.

John,

Say your original file is called PARTFILE. The first step is to run a query
to produce a file with a record for each part at its lowest location. File
selection will be PARTFILE. Sort by part number. Report summary functions -
select minimum on location. Report break will be on part number. Output
type and form will be database file, summary only. Let's call the file
PARTLOC1.

Now we can produce a file with a record for each part at its next lowest
location. File selection will be PARTFILE and PARTLOC1, matched records,
part number = part number, PARTFILE location > PARTLOC1 location. Sort by
part number.  Report summary functions - again select minimum on location.
Report break will be on part number. Output type and form will be database
file, summary only. Let's call this file PARTLOC2.

The third query will similarly produce a file with a record for each part
at its third lowest location. File selection will be PARTFILE, PARTLOC1 and
PARTLOC2, matched records,  PARTFILE part number = PARTLOC1 part number,
PARTFILE location > PARTLOC1 location,  PARTFILE part number = PARTLOC2
part number, PARTFILE location > PARTLOC2 location. Report summary
functions, report break, output type and form all as before. We'll call
this file PARTLOC3.

We bascially keep going in this way until we hit a query that produces no
more records. The last query will match PARTLOC1, PARTLOC2, PARTLOC3...
PARTLOCn. Select matched records with primary file, matching the part
number in PARTLOC1 with the part number in each of the other files. Select
and sequence fields - select PARTLOC1 part number, and location from each
file. Select records - break level = '1' to avoid picking up the grand
total record. Output type and form is database file, detail. Call it what
you like - it's the file you wanted.

Dave Kahn, ABB Steward Ltd.


+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---