Help in structuring my DB ?

Hi,

Here is my problem (I posted a similar problem a few days back and changed
my DB structure but I am still stuck)

Here is the structure:

1) ProductDB (ProductID, and other product infos)

2) Sales main DB (SalesID, date ot the sale, salesperson and a portal with
informations on salesDB) - salesID is unique and automatically generated
(just like a Invoice Number)

3) SalesDB (ProductID, SalesID, and other sales information) - most of these
infos are entered from the portal that is in the Sales Main DB

4) Sales people DB (salesID, salesperson) so for each salesID I have a sales
person associated

The relationships are as bellow:

ProductDB is linked to Sales DB with ProductID

Sales main DB is linked to Sales DB with SalesID

SalesDB is linked to Sales People DB with SalesID

Basicaly, on the Sales main DB, I enter all the infos about the sales, the
date, the sales person, and in the portal (to salesDB) I enter each product,
the price, the number of products etc...

what I want to do to show (somewhere - I thought the product DB is the best)
what each sales person has sold for each product. So on each product page,
there is a number of what has been sold by each sales person for that
product

Is that possible ? I am aware that my DB may not be constructed very well,
but can I still see this information ? What formula should I use ?

Thanks for any help.

Re: Help in structuring my DB ?

In article <4734b73f$0$27408$ba4acef3@news.orange.fr>,
"pj" wrote:

> Hi,
>
> Here is my problem (I posted a similar problem a few days back and changed
> my DB structure but I am still stuck)
>
> Here is the structure:
>
> 1) ProductDB (ProductID, and other product infos)
>
> 2) Sales main DB (SalesID, date ot the sale, salesperson and a portal with
> informations on salesDB) - salesID is unique and automatically generated
> (just like a Invoice Number)
>
> 3) SalesDB (ProductID, SalesID, and other sales information) - most of these
> infos are entered from the portal that is in the Sales Main DB
>
> 4) Sales people DB (salesID, salesperson) so for each salesID I have a sales
> person associated
>
> The relationships are as bellow:
>
> ProductDB is linked to Sales DB with ProductID
>
> Sales main DB is linked to Sales DB with SalesID
>
> SalesDB is linked to Sales People DB with SalesID
>
> Basicaly, on the Sales main DB, I enter all the infos about the sales, the
> date, the sales person, and in the portal (to salesDB) I enter each product,
> the price, the number of products etc...
>
> what I want to do to show (somewhere - I thought the product DB is the best)
> what each sales person has sold for each product. So on each product page,
> there is a number of what has been sold by each sales person for that
> product
>
> Is that possible ? I am aware that my DB may not be constructed very well,
> but can I still see this information ? What formula should I use ?
>
> Thanks for any help.

The sales and people should be linked by PersonID. That way each Person
has many Sales, but each Sale has only one person, and you can easily
tabulate the sales of each person.

The way you have it, each Sale can have many persons, but one person can
have only one Sale.

So Sales Main DB needs to have a field to hold PersonID. Person does not
need a field to hold Sales ID.

Re: Help in structuring my DB ?

In article
, Bill
wrote:

> In article <4734b73f$0$27408$ba4acef3@news.orange.fr>,
> "pj" wrote:
>
> > Hi,
> >
> > Here is my problem (I posted a similar problem a few days back and changed
> > my DB structure but I am still stuck)
> >
> > Here is the structure:
> >
> > 1) ProductDB (ProductID, and other product infos)
> >
> > 2) Sales main DB (SalesID, date ot the sale, salesperson and a portal with
> > informations on salesDB) - salesID is unique and automatically generated
> > (just like a Invoice Number)
> >
> > 3) SalesDB (ProductID, SalesID, and other sales information) - most of
> > these
> > infos are entered from the portal that is in the Sales Main DB
> >
> > 4) Sales people DB (salesID, salesperson) so for each salesID I have a
> > sales
> > person associated
> >
> > The relationships are as bellow:
> >
> > ProductDB is linked to Sales DB with ProductID
> >
> > Sales main DB is linked to Sales DB with SalesID
> >
> > SalesDB is linked to Sales People DB with SalesID
> >
> > Basicaly, on the Sales main DB, I enter all the infos about the sales, the
> > date, the sales person, and in the portal (to salesDB) I enter each
> > product,
> > the price, the number of products etc...
> >
> > what I want to do to show (somewhere - I thought the product DB is the
> > best)
> > what each sales person has sold for each product. So on each product page,
> > there is a number of what has been sold by each sales person for that
> > product
> >
> > Is that possible ? I am aware that my DB may not be constructed very well,
> > but can I still see this information ? What formula should I use ?
> >
> > Thanks for any help.
>
> The sales and people should be linked by PersonID. That way each Person
> has many Sales, but each Sale has only one person, and you can easily
> tabulate the sales of each person.
>
> The way you have it, each Sale can have many persons, but one person can
> have only one Sale.
>
> So Sales Main DB needs to have a field to hold PersonID. Person does not
> need a field to hold Sales ID.

In these sort of situations I think you really need to ask yourself a
question:

"Do I really need these numbers on-screen all the time
or only as an occasional report?"

By on-screen I mean always on the product's page (or salesperson's
page, etc.) continuously updating. Although this can be done, it will
cause the system to slow down as the number of sales records grows.
Plus, if you need totals of sales only between two user-defined dates
(eg. a monthly sales report), then this becomes even more complicated
as an on-screen / updating number.

Usually a better approach is to have a Summary Report which calculates
and displays the numbers only when needed (this doesn't necessarily
need to be printed). This would be done in the SalesDB where you can
sort the records by ProductID and Salesperson.

First, if it doesn't already, each SalesDB record needs to contain the
Salesperson field (name or ID) as well since that's one of the
sub-sections you're trying to split the sales. For example, SalesDB
records might be (with extra information such as dates, quantities,
etc.):

ProductID Product Name Salesperson
Record 1: P01 Apples Fred Flintstone
Record 2: P02 Bananas Barney Rubble
Record 3: P03 Onions Barney Rubble
Record 4: P03 Onions Mr Slate
Record 5: P01 Apples Mr Slate
etc.

You will also need a couple of Summary fields:

s_NumSales Summary Field Count of SalesID

s_SalesCost Summary Field Total of SalesCost

You can now create a new Layout that at ithe most beasic level is
something like:


[ProductID] [Product]

| Sub-summary by ProductID (Leading)
------------------------

[Salesperson] [s_NumSales] $[s_SalesCost]

| Sub-summary by Salesperson (Trailing)
------------------------

Total [Product] [s_NumSales] $[s_SalesCost]

| Sub-summary by ProductID (Trailing)
------------------------

Grand Total Sales $[s_SalesCost]

| Trailing Grand Summary
------------------------


where [] denotes the fields. You don't need the Body part of the Layout
unless you want all the individual record data to appear in the report.

Now to obtain the numbers you can:

- Find the records you want to report on (eg. the records for
March 2007),

- Sort them by ProductID and Salesperson, in that field order,

- Preview or print the report Layout.

You should get a report something along the lines of:

P01 Apples
Fred Flintstone 5 $15.00
Mr Slate 3 $9.00

Total Apples 8 $24.00

P02 Bananas
Barney Rubble 4 $8.40

Total Bananas 4 $8.40

P03 Onions
Barney Rubble 3 $2.25
Mr Slate 2 $1.50

Total Onions 5 $3.75

Grand Total Sales $36.15


You can create a similar Layout to create a report by Salesperson
sub-divided by Product (remembering to Sort the records correctly)
using the same Summary fields.








Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)