Split Records

  • Thread starter Thread starter Alec Green
  • Start date Start date
A

Alec Green

Hi

In my database, I have a linked Sales Order table from a completely
different system.

In this table I have SalesOrderNo, StockNo, Qty, UnitPrice & SalesPerson -
(these are all fields are all fixed).

My Problem is that quite often there can be 2 or even 3 Sales People per
Sales Order, So I need somehow to split the order to represent the number of
Sales people to the order.


Any Ideas?


Thanks

Alec Green
 
Dear Alec:

If you are wanting to view the order with all the lines for each SalesPerson
together, use ORDER BY SalesOrderNo, SalesPerson. If you are creating a
"totals query" (aggregate) then GROUP BY SalesOrderNo, SalesPerson. Not
unlikely you may want something like this:

SELECT SalesOrderNo, SalesPerson, SUM(UnitPrice * Qty)
FROM [Sales Order]
GROUP BY SalesOrderNo, SalesPerson
ORDER BY SalesOrderNo, SalesPerson

If not, then I just didn't get the idea of the end product you're wanting.
Please describe more thoroughly.

Tom Ellison
 
Thanks Tom for your reply, I haven't really explained myself!

In my linked SalesOrder table, it only gives the option of one SalesPerson,
where in reality it can be 2 or more Sales people.

I have tried using another table call OrderSplit containing the SalesOrderNo
& SalePerson so to give me the Sales Order that is Split and by which
SalesPerson\People, but then I get stuck if the Order is not Split and stays
with the original SalesPerson. (I really only want to enter data in
OrderSplit if it is a split order)

Hope thats a bit better!

Regards

Alec

Tom Ellison said:
Dear Alec:

If you are wanting to view the order with all the lines for each
SalesPerson together, use ORDER BY SalesOrderNo, SalesPerson. If you are
creating a "totals query" (aggregate) then GROUP BY SalesOrderNo,
SalesPerson. Not unlikely you may want something like this:

SELECT SalesOrderNo, SalesPerson, SUM(UnitPrice * Qty)
FROM [Sales Order]
GROUP BY SalesOrderNo, SalesPerson
ORDER BY SalesOrderNo, SalesPerson

If not, then I just didn't get the idea of the end product you're wanting.
Please describe more thoroughly.

Tom Ellison


Alec Green said:
Hi

In my database, I have a linked Sales Order table from a completely
different system.

In this table I have SalesOrderNo, StockNo, Qty, UnitPrice &
SalesPerson - (these are all fields are all fixed).

My Problem is that quite often there can be 2 or even 3 Sales People per
Sales Order, So I need somehow to split the order to represent the number
of Sales people to the order.


Any Ideas?


Thanks

Alec Green
Click to expand...
Click to expand...
 
Dear Alec:

The way I see this is, you have two options.

You can split the order into sub-orders, each with a salesperson.

You can record the salesperson in each line of the order.

In my opinion, the first method is more organized. Changing the salesperson
would be a small amount of additional effort for the programmer, but can be
made to look the same to the user.

I do not understand how it is a problem if the "split" is only one
salesperson. I do not recommend there be an "original salesperson" just the
table of Splits which can list one or more salespersons. You could number
them, so the lowest numbered salesperson is considered the original one, or
you could have a boolean to mark the original salesperson.

Tom Ellison


Alec Green said:
Thanks Tom for your reply, I haven't really explained myself!

In my linked SalesOrder table, it only gives the option of one
SalesPerson, where in reality it can be 2 or more Sales people.

I have tried using another table call OrderSplit containing the
SalesOrderNo & SalePerson so to give me the Sales Order that is Split and
by which SalesPerson\People, but then I get stuck if the Order is not
Split and stays with the original SalesPerson. (I really only want to
enter data in OrderSplit if it is a split order)

Hope thats a bit better!

Regards

Alec

Tom Ellison said:
Dear Alec:

If you are wanting to view the order with all the lines for each
SalesPerson together, use ORDER BY SalesOrderNo, SalesPerson. If you are
creating a "totals query" (aggregate) then GROUP BY SalesOrderNo,
SalesPerson. Not unlikely you may want something like this:

SELECT SalesOrderNo, SalesPerson, SUM(UnitPrice * Qty)
FROM [Sales Order]
GROUP BY SalesOrderNo, SalesPerson
ORDER BY SalesOrderNo, SalesPerson

If not, then I just didn't get the idea of the end product you're
wanting. Please describe more thoroughly.

Tom Ellison


Alec Green said:
Hi

In my database, I have a linked Sales Order table from a completely
different system.

In this table I have SalesOrderNo, StockNo, Qty, UnitPrice &
SalesPerson - (these are all fields are all fixed).

My Problem is that quite often there can be 2 or even 3 Sales People per
Sales Order, So I need somehow to split the order to represent the
number of Sales people to the order.


Any Ideas?


Thanks

Alec Green
Click to expand...
Click to expand...
Click to expand...
 
Back
Top