asked on

Associate Table row with query column

I have a query that is calculating several numbers based on other tables or queries which I would like to associate with one of several rows in a table that has no connection with the calculating query. For example, several partners in a business who have no direct connection to either sales or expenditures of the business, but who participate in unequal degrees in the profits of the company.

I want a query that will keep track of what they are owed from the profits (which I can calculate, but not with any tie to to table that keeps track of the partners names and ID's) and I can keep track of the cash paid to each of the partners (which I *can* tie to each partner), but how do I then query to see how much of each one's piece of the pie is owed to them?

Thanks.
RWW

will_scarlet7

Hi RWW,
Can you post a sample of the current query and the partners table as well as your desired result?

PstWood

ASKER

Query:
SELECT ItemCosts.AuctionID, ItemCosts.TotalPesoCost, ItemSales.AuctionTotSale, (Nz([AuctionTotSale],0))-[TotalPesoCost] AS Profit, IIf([Profit]>0,[Profit]/2+[TotalPesoCost],Nz([AuctionTotSale]/2)) AS OwedRWW, IIf([Profit]>0,[Profit]/2,Nz([AuctionTotSale]/2)) AS OwedElclon
FROM ItemCosts LEFT JOIN ItemSales ON ItemCosts.AuctionID = ItemSales.AuctionID;

Partner table has only PartnerID and PartnerName for fields

PartnerID 1---->OwedRWW
PartnerID 2---->OwedElclon

HTH
RWW

will_scarlet7

Can you build a table to calculate what percent each partner gets from each category? If so it should be fairly easy to create a query that uses a cartesian join to calculate the amount based on the percentage due from each category.

PstWood

ASKER

Yes and no. How's that for non-commital?

One of the partners is putting up the $$ for initial purchase of stock, and so needs to be paid back that plus a percent of the profits. The other partners are doing the leg work, and get only a percent of the profits. Consequently, straight percentage of profit for each partner is doable, but since each product purchase varies in price, one of the partners gets a percentage plus cost of purchase, so I'm not sure how you would set up a table for that scenario.

I guess I need to add more points to this question. I thought it would be easier than this.

Thanks.
RWW

ASKER CERTIFIED SOLUTION

shanesuebsahakarn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PstWood

ASKER

Two questions:

Are you assuming that qryProfit is the one row query refered to in your opening paragraph?

Shouldn't it be :

SELECT *, (SELECT Sum([Profit]) FROM qryProfit)*[[Partners].PercentageProfit]/100+IIf([Partners].[ReceivesCost],(SELECT Sum([TotalPesoCost]) FROM qryProfit),0) FROM tblPartners;

Thanks.
RWW

shanesuebsahakarn

No, in this case I'm assuming that qryProfit is actually the query that you posted up at the top. The embedded SELECTs turn that query (effectively) into one row, returning a total value from the appropriate columns. I've just used an embedded query instead of a saved one-row query - it's mainly a matter of preference. Using a saved query does have advantages - it makes the final query easier to read, for one thing!

Breaking down the payment part, this part:
(SELECT Sum([Profit]) FROM qryProfit) - get the total profit from qryProfit

IIf([ReceivesCost],(SELECT Sum([TotalPesoCost]) FROM qryProfit),0) - If the [ReceivesCost] field in tblPartners is True get the total peso cost from qryProfit, otherwise return 0.

PstWood

ASKER

Yeah, that works. I wasn't seeing what you were doing. Unfortunately, it doesn't return the right numbers, but it's my fault, not yours. I'm showing profit in the qryProfit as a negative number if sales haven't reached the break even point, so I have to revise that, and then it should work fine and allow me to calculate the other figures necessary.

There's a lot to learn but each question teaches me a little more. How long have you been doing this stuff to be able to see how to write these queries? I have trouble with the simple ones getting them bracketed right. I make a mean chocolate cheesecake, though.

Thanks again.
RWW

shanesuebsahakarn

> How long have you been doing this stuff to be able to see how to write these queries

Waaaaayyyy too long :-)

Glad I could help!