Create two (or three) records from one

[Bill Lentz]

I have a database with a linked table that comes from a dbase IV file.
Each record has a field (SALESPER) that can have from 1 to 4 sets of
initials seprated by commas. For example:

SAS,
SAS,
SAS,HWL,
HWL,
HWL,SMG,VLG

Each record also has a field with a dollar amount in it. I'm trying
to use this file to generate a commision report where if there is one
set of initials (tje vast majority of cases), that person gets all of
the amount in the dollar amount field, but if there are two or more
sets of initials, the dollar amount field is split equally among the
individuals. Ideally, the report would include a line on each
person's commission report that shows the sale and their percent of
the dollar amount.

Is it possible to create a query that would generate two, three or
four records from one record?

Thanks
Bill

 

[James r]

My idea might not be proper DB design, but I would suggest
adding a yes/no field for each salesperson. Your query
then could sum all the yes/no fields to derive the total
salespersons involved in each transaction (yes=-1, no=0).
Divide -1 by the total, and you have your percentage for
splitting commissions.

You could derive a report for each salesperson listing all
transactions for that salesperson and the sale amount, %
split, and net commission.

Hope that was helpful. Good luck

James r