what formula to use ?

Hi all,

I have 2 data bases that are linked

DB1 is the product data base with products A, B, C

DB2 is the sales DB where we see the products (the 2 DB are linked by
product numbers A, B, C) and I have the sales people Paul, Tom and David

any product can be sold by anyone.

On the DB1, on each product page, I want to show what each sales have sold.

I have no idea what formula to give in DB1 to show the result of what each
sales person have sold.

it is basically like showing the summary of products sold in DB2 to the
following find: Find product A and Paul

Any idea what formula to use ?

Re: what formula to use ?

"pj" wrote in message
news:4731fa0d$0$5085$ba4acef3@news.orange.fr...
> Hi all,
>
> I have 2 data bases that are linked
>
> DB1 is the product data base with products A, B, C
>
> DB2 is the sales DB where we see the products (the 2 DB are linked by
> product numbers A, B, C) and I have the sales people Paul, Tom and David
>
> any product can be sold by anyone.
>
> On the DB1, on each product page, I want to show what each sales have
> sold.
>
> I have no idea what formula to give in DB1 to show the result of what each
> sales person have sold.
>
> it is basically like showing the summary of products sold in DB2 to the
> following find: Find product A and Paul
>
> Any idea what formula to use ?
>
There're allot of ways you could do this, and I'm not sure my answered will
fit your problem because I've hade to make some assumptions about things you
haven't told us. What I'm pretty sure of is that part of your problem is how
you modeled your data. I'm gathering that sales people are a field on the
Sales table. This is giving you trouble gathering data on People because you
can't get the perspective of a single active record that represents one
person.
This is my suggestion: you need four tables: Products, People, Sales
Transactions, and Sales Metadata. All tables need a unique (non data) ID.
People are unique there is one record per person.
Products are unique.
Sales Transactions are individual sales, they are unique belong to one sales
Person, and are made up of one or many Products. So, the Sales Transaction
table has field for sales Person and is related to Person table. It's not
dependently related to the products table but should have a global
relationship relating every record to every record in Products- this is for
populating a value list.
If this is correct then the next part of the solution relies on data input
procedure. Populating the DB goes like this: People and Products obviously
go into the People and Product table. Then, when you make a new sale you
start from a layout over the sales People table. Select a Person and using a
script create a new related record in the Sales Transaction table, jump to a
layout over the Sales Transaction table and bring your new record active.
This record will have a "parent id" that is the unique id of the person
making the sale. The Sales Transaction layout will have a portal to the
Sales Metadata table that can be used to create related records. You can add
products to a Sales Transaction by populating a "product" field in the
portal row using a dropdown list that is defined by the global relationship
to the Products table. When this crates a record in the Sales Metadata
table, that record will have a "parent id" that is the "unique id" of the
Sales Transaction. It then needs an id of the person who made thee sale. You
get this, with an auto enter by lookup, from the parent Sales Transaction
record.
So.... what you have is nice clean tables of sales People, Products, and
Sales Transactions, and a meta data table with one record for every product
sold describing who sold it and what sale it was part of. You get the report
you want of this table.

Re: what formula to use ?

Hi Conrad,

thanks a lot for this information. I will re-organise my DB. It was badly
constructed. thanks again
"Conrad Lorda" wrote in message
news:Z6idnccQJ5BWq6_anZ2dnUVZ_h2pnZ2d@comcast.com...
> "pj" wrote in message
> news:4731fa0d$0$5085$ba4acef3@news.orange.fr...
>> Hi all,
>>
>> I have 2 data bases that are linked
>>
>> DB1 is the product data base with products A, B, C
>>
>> DB2 is the sales DB where we see the products (the 2 DB are linked by
>> product numbers A, B, C) and I have the sales people Paul, Tom and David
>>
>> any product can be sold by anyone.
>>
>> On the DB1, on each product page, I want to show what each sales have
>> sold.
>>
>> I have no idea what formula to give in DB1 to show the result of what
>> each sales person have sold.
>>
>> it is basically like showing the summary of products sold in DB2 to the
>> following find: Find product A and Paul
>>
>> Any idea what formula to use ?
>>
> There're allot of ways you could do this, and I'm not sure my answered
> will fit your problem because I've hade to make some assumptions about
> things you haven't told us. What I'm pretty sure of is that part of your
> problem is how you modeled your data. I'm gathering that sales people are
> a field on the Sales table. This is giving you trouble gathering data on
> People because you can't get the perspective of a single active record
> that represents one person.
> This is my suggestion: you need four tables: Products, People, Sales
> Transactions, and Sales Metadata. All tables need a unique (non data) ID.
> People are unique there is one record per person.
> Products are unique.
> Sales Transactions are individual sales, they are unique belong to one
> sales Person, and are made up of one or many Products. So, the Sales
> Transaction table has field for sales Person and is related to Person
> table. It's not dependently related to the products table but should have
> a global relationship relating every record to every record in Products-
> this is for populating a value list.
> If this is correct then the next part of the solution relies on data input
> procedure. Populating the DB goes like this: People and Products obviously
> go into the People and Product table. Then, when you make a new sale you
> start from a layout over the sales People table. Select a Person and using
> a script create a new related record in the Sales Transaction table, jump
> to a layout over the Sales Transaction table and bring your new record
> active. This record will have a "parent id" that is the unique id of the
> person making the sale. The Sales Transaction layout will have a portal to
> the Sales Metadata table that can be used to create related records. You
> can add products to a Sales Transaction by populating a "product" field in
> the portal row using a dropdown list that is defined by the global
> relationship to the Products table. When this crates a record in the Sales
> Metadata table, that record will have a "parent id" that is the "unique
> id" of the Sales Transaction. It then needs an id of the person who made
> thee sale. You get this, with an auto enter by lookup, from the parent
> Sales Transaction record.
> So.... what you have is nice clean tables of sales People, Products, and
> Sales Transactions, and a meta data table with one record for every
> product sold describing who sold it and what sale it was part of. You get
> the report you want of this table.
>
>