Help please with query

I have what seems to be a strange situation. I have a table where
Sales are recorded. Most of the time there is only a primary salesman
involved (Salesman1). When there is a secondary salesman, the
reporting for that sale (i.e. estimated Gross, Units ect) are split in
half. The problem arises when I have to give the other salesman the
other half. For reporting purposes, sales are totaled and grouped by
salesman on a monthly basis. It seems that another record needs to be
created that is a duplicate of the first with Salesman 2 in the same
field where Salesman1 is in the original record. I've tried writting
the Salesman2 data to a seperate table, but don't know how to stack
those records onto the original table.

I'm confused and need help!

Any suggestions would be appreciated.

Thanks,

Bob

Sounds like you don't have a standard Salesman--(1,M)---Sale
relationship, but a many-to-many. IOW, two salesmen can share the
sale. So you do something like this:

Salesman---(1,M)---SalesmanSaleDetails---(M,1)----Sale

Then you can split the commission. You could do something like a
DCount of related SalesmanSaleDetails on the Sale and split DCount
ways.

You could have another table called "Commissions" or "SaleMakers" or
something, and make a one (sale) to many (Salemakers) relationship between
the two. Thus for each sale, you would have a list of salemakers involved,
that could be displayed in a subform. The list would most often just be one
person, but could be 2 (or 3 or more). Then to decide how to divy up the
commissions, you could divide by the count of salemaker records for a given
sale.
hope this helps
-John

<technobob@bellsouth.netwrote in message
news:1157430193.671192.22050@h48g2000cwc.googlegro ups.com...

Quote:

>I have what seems to be a strange situation. I have a table where
Sales are recorded. Most of the time there is only a primary salesman
involved (Salesman1). When there is a secondary salesman, the
reporting for that sale (i.e. estimated Gross, Units ect) are split in
half. The problem arises when I have to give the other salesman the
other half. For reporting purposes, sales are totaled and grouped by
salesman on a monthly basis. It seems that another record needs to be
created that is a duplicate of the first with Salesman 2 in the same
field where Salesman1 is in the original record. I've tried writting
the Salesman2 data to a seperate table, but don't know how to stack
those records onto the original table.
>
I'm confused and need help!
>
Any suggestions would be appreciated.
>
Thanks,
>
Bob
>

John,

Thjanks for your reply!

It helps some, but my real problem is showing both Salesman 1 and
Salesman2 in the same column when grouped by month and salespersons.
That query is already pretty complicated with 4 different parameters to
drill down. One of those parameters is the salesman. I need to be
able to show Salesman1 and Salesman2 in the same column and grouping
with their individual sales calculated.

I've been thinking of using an append query, but will also need to be
able to trap edits and use a delete query, than an append query. I was
hoping for something less complicated.

IBob

John Welch remove remove wrote:

Quote:

You could have another table called "Commissions" or "SaleMakers" or
something, and make a one (sale) to many (Salemakers) relationship between
the two. Thus for each sale, you would have a list of salemakers involved,
that could be displayed in a subform. The list would most often just be one
person, but could be 2 (or 3 or more). Then to decide how to divy up the
commissions, you could divide by the count of salemaker records for a given
sale.
hope this helps
-John
>
<technobob@bellsouth.netwrote in message
news:1157430193.671192.22050@h48g2000cwc.googlegro ups.com...

Quote:

I have what seems to be a strange situation. I have a table where
Sales are recorded. Most of the time there is only a primary salesman
involved (Salesman1). When there is a secondary salesman, the
reporting for that sale (i.e. estimated Gross, Units ect) are split in
half. The problem arises when I have to give the other salesman the
other half. For reporting purposes, sales are totaled and grouped by
salesman on a monthly basis. It seems that another record needs to be
created that is a duplicate of the first with Salesman 2 in the same
field where Salesman1 is in the original record. I've tried writting
the Salesman2 data to a seperate table, but don't know how to stack
those records onto the original table.

I'm confused and need help!

Any suggestions would be appreciated.

Thanks,

Bob

Thanks for your reply.

I think what you're suggesting will get thew summary data, but I also
need to show the details in the same sorting and grouping.

Maybe an append query, but than I have to be able to deal with edits.

Bob
pietlinden@hotmail.com wrote:

Quote:

Sounds like you don't have a standard Salesman--(1,M)---Sale
relationship, but a many-to-many. IOW, two salesmen can share the
sale. So you do something like this:
>
Salesman---(1,M)---SalesmanSaleDetails---(M,1)----Sale
>
Then you can split the commission. You could do something like a
DCount of related SalesmanSaleDetails on the Sale and split DCount
ways.

Help please with query

re: Help please with query

re: Help please with query

re: Help please with query

re: Help please with query

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about