From: kbahrami@lonestar.utsa.edu (Kambiz J. Bahrami)
Newsgroups: comp.databases
Subject: Paradox...Problem and Solution
Summary: Sharing some codes with other paradox users (beginner to interm)
Date: 23 Aug 92 04:22:25 GMT
Organization: University of Texas at San Antonio

Howdy Folks,

Helping my friend, who is an inventory manager for a company, I wrote
a simple program to solve his problem in PARADOX.  Later I though, others
might have had a similar problem so I offer my solution to the problem.
( 1- It is not the most elegant solution, but it does the job.  I tried
     to use the multi table multi line solutions, but could not get them
     to work properly.  If you have the solution, let me know.
  2- I have noticed that there is not much help available to those of us
     that use paradox, the way Xbase users can count on help.
  3- Let me know if these codes are useful or not. If they are I will
     publish more codes as I write them.)


PROBLEM:
--------
Every Sunday each store sends an inventory count to the warehouse.  The
table that is sent consists of  1) Supplier             A10*
and are named  STOREx           2) Inventory number     A10*
as in STORE1, STORE2...STORE14  3) Quantity             S

Now my friend needs to create a table that lists all the inventories,
and how much each store has that specific item, and a total quantity of
that item.

(note: eventhough most items are supplied from the warehouse, each store
 has some flexibility to aquire inventory independently, that is why
 the Supplier name is needed since the inventory number is the Suppliers
 inventory number)


SOLUTION:
---------
first create a table that consists of  1) Supplier          A10*
and name it  ALLSTORE                  2) Inventory number  A10*
                                       3) Total             s
                                       4) Store 1           s
                                       5) store 2           s
                                       ...
                                       x) store n           s

create a script file (Tstore.sc)

do_it!
clearall
empty "ALLSTORE"
clearall
;************************************************************
;*  replace the X in 'Store X' & 'STOREX' with the number of the store
;*  repeate these codes for each store (no need to type in the comments)
;*  this will give you all the Suppliers and Inventory numbers in
;*    all the stores.
;************************************************************
query                                                      ;*
                                                           ;*
ALLSTORE | Supplier | Inventory number | Store X |         ;*
insert   |   _a     |   _b             |  _c     |         ;*
         |          |                  |         |         ;*
         |          |                  |         |         ;*
                                                           ;*
STOREX   | Supplier | Inventory number | Quantity |        ;*
         |  _a      |   _b             |  _c      |        ;*
         |          |                  |          |        ;*
         |          |                  |          |        ;*
                                                           ;*
endquery                                                   ;*
do_it!                                                     ;*
clearall                                                   ;*
;************************************************************

;This query finds the quantity for each item for each store
;follow the same instructions as above
;************************************************************
query

ALLSTORE | Supplier | Inventory number | Store X     |
         |    _a    |    _b            | changeto _c |
         |          |                  |             |
         |          |                  |             |

STOREX   | Supplier | Inventory number | Quantity |
         |   _a     |    _b            |   _c     |
         |          |                  |          |
         |          |                  |          |

endquery
do_it!
clearall
;************************************************************


;this query calculates the total for each item

query
ALLSTORE | Supplier | Inventory number | Total                     |
         |    _aa   |  _bb             | changeto _a+_b+_c...+_z   |
         |          |                  |                           |
         |          |                  |                           |

ALLSTORE | Store 1  | Store 2 | Store 3 | ...  | Store n |
         |   _a     |   _b    |  _c     |      |  _z     |
         |          |         |         |      |         |
         |          |         |         |      |         |

endquery
do_it!
clearall
view "ALLSTORE"

; remember to remove the   ...  and what follows since they are
; used only for showing the limits.
Newsgroups: comp.databases