asked on

Possible to assign specific query as field in table?

Is it possible to have one field in a table that allows a choice of which of several queries to use for that row?

I'm trying to sort out a problem that Shane helped with in https://www.experts-exchange.com/questions/21119225/Associate-Table-row-with-query-column.html but which turns out to be more complicated. It goes like this: several partners buy and selling merchandise and having differing shares of the costs and profits. I want to calculate what each needs to be repaid based on what they have bought and what their share of the profits is. There are basically 4 scenarios:

A partner is owed all of the cost and an equal share of the profits
A partner is owed none of the cost and an eqal share of the profits
A partner is owed none of the cost and none of the profits
A partner is owed all of the cost and all of the profits

I can easily come up with a query that calculates what a partner is owed based on fields in the Products table and show those calculations as columns in each Products row, but I can't figure out how to tie them back to the Partners table. Following is an example of the query using only two partners:

SELECT AuctionProfitQuery.EmployeeID, Sum(CCur([TotalPesoCost])) AS TotalCost, Sum(CCur([AuctionTotSale])) AS TotalSales, Sum(CCur([Profit])) AS TotalProfit, Sum(CCur(IIf([AuctionProfitQuery].[EmployeeID]=2,[TotalPesoCost],0))) AS WoodCosts, Sum(CCur(IIf([AuctionProfitQuery].[EmployeeID]=1,[TotalPesoCost],0))) AS GomezCosts, Sum(CCur(IIf([AuctionProfitQuery]![EmployeeID]=2,IIf([ShareProfit]=-1,[Profit]/2,[Profit]),0))) AS WoodProfits, Sum(CCur(IIf([AuctionProfitQuery]![EmployeeID]=1,IIf([ShareProfit]=-1,[Profit]/2,[Profit]),0))) AS GomezProfits
FROM AuctionItems INNER JOIN AuctionProfitQuery ON AuctionItems.AuctionID = AuctionProfitQuery.AuctionID
GROUP BY AuctionProfitQuery.EmployeeID;

Obviously, going beyond two gets a bit involved, so my idea is to have a table that stores queries to use and references them in a combo box. Is that doable?

Thanks
RWW

shanesuebsahakarn

Not per se, but there are some other alternatives. For example, you could store a numeric value that tells you how which calculation you need to use (say 1=all cost, share of profit, 2=no cost, share of profit etc.). You could then pass this, and the values through to a function and call that function from a query. For example:

Function CalcPayment(lngMethod As Long, curCost As Currency, curProfit As Currency, lngNoPeople As Long) As Currency
Select Case lngMethod
Case 1: CalcPayment=curCust+(curProfit/lngNoPeople)
Case 2: CalcPayment=(curProfit/lngNoPeople)
..etc..
End Select
End Function

PstWood

ASKER

Whoa. You've guessed from my other questions that I'm relatively new to Access beyond anything but the basics, so could you expand that a bit. I'm reading that I store the numeric value in one of the tables, which would have to be the Products table since each product would have a different calculation done for each partner, is that right?

Then I define the function, Method refers to which numeric is stored for the Product, but what does IngNoPeople refer to? The number of people participating in the profit I'm guessing, but where does that number come from?

Then, where does this Function get stored and how is it called by the query?

Thanks.
RWW

shanesuebsahakarn

OK, the function would be stored in a module.

I'm assuming you'd probably store the method field against each partner, rather than each product - because we'd want to conduct the calculation on a per-partner basis. Am I correct in assuming that each partner gets the same calculation for every product (i.e. if a partner gets a share of cost and profit, he gets that for all products, right?). In one of your earlier questions, you had a query that calculated the payment per partner. We just modify that query to call this function instead of performing the calculation in-query.

Are calculating each person's share of profit by calculating the total profit divided by the number of partners? We'd have to look up the number from another query or calculate it as a subquery.

PstWood

ASKER

Your assumption is wrong that each partner always gets the same calculation for all products

I have the current query figured out to be able to store the right value in a Products column with a heading for them, but that's the best I've been able to do. ie

ColumnHeadings ---> Partner1CostShare |Partner1ProfitShare | Partner2CostShare | Partner2ProfitShare
\/ \/ \/ \/
Product 1 0 $25 $100 $25
Product 2 $300 $75 0 $25

Hope that clarifies
RWW

PstWood

ASKER

Followup:

I can then sum the columns to get what is owed to each partner, but I can't then tie those figures back to a PartnerID in the Partners table, in other words so that

PartnerID PartnerOwed
\/ \/
1 Partner1ProfitShare + Partner1CostShare
2 Partner2ProfitShare + Partner2CostShare

shanesuebsahakarn

OK, in that case we'd store the pricing method against the product. Your query would end up looking something like this:

Product 1 Partner 1 $300
Product 1 Partner 2 $300
Product 2 Partner 1 $0

You could then write a query to total and display these as needed. Is the profit divided equally between all partners who are set to receive a share? Do you have a query to retrieve the total profit and cost per product?

shanesuebsahakarn

Can you post the SQL of the query that gives you the values in different columns (not the sum query)?

PstWood

ASKER

When profit is shared, it is shared evenly, however there are scenarios where it all goes to one individual. Cost is always paid completely by one or the other individual, but who pays doesn't determine whether or not the profit is shared. I know it's a screwy set up, but for now, it's what we have to work with. Here's the query for determining costs and profits, etc. with the partner doing the buying being an EmployeeID and ShareProfit being a yes or no value.

SELECT ItemCosts.AuctionID, ItemCosts.TotalPesoCost, TotaItemlSales.AuctionTotSale, IIf((Nz([AuctionTotSale],0))-[TotalPesoCost]>0,(Nz([AuctionTotSale],0))-[TotalPesoCost],0) AS Profit, ItemCosts.EmployeeID, ItemCosts.ShareProfit
FROM ItemCosts INNER JOIN TotaItemlSales ON ItemCosts.AuctionID = TotaItemlSales.AuctionID;

Thanks again.
RWW

shanesuebsahakarn

Right - but how do you know which partner pays the cost on a particular item? When you say "pays", does that mean they don't get any part of the cost back? I think I can see how this can be done although I need to go away for a few hours.

PstWood

ASKER

The buyer is the one listed in the ItemCosts.EmployeeID field and yes, he is reimbursed that amount + his share of the profit, which is determined by the ItemCosts.ShareProfit field. If ItemCosts.ShareProfit is yes, he gets an equal share of the profit with the others. Should I think about breaking those columns out into their own tables?

Thanks.
RWW

PstWood

ASKER

I've made some changes that hopefully will make this easier to resolve. First, I made two new tables called ItemBuyers and ProfitShare, each of which has only two columns, one being the ProductID, and the other being either the PartnerID or a yes/no field.

Then I added to the ProductProfitQry two columns, one that determines the Profit for the person buying the product based on whether or not the profit is shared (he gets 100% if it is not and an equal share if it is). The second column determines the profit for any of the partners who are non-buyers (it is an equal share if the profit is shared and 0 if is not) Both of these use a one column query that counts the number of PartnerIDs in the Partners table to calculate the equal share.

A crosstab query of the ProductProfitQry produces a column for each of the Partners that lists their profit if they were a buyer for the ProductID in the row header.

The one thing I can't figure out is how to get the non-buyer profit into a query only for those partners who were not the buyer for a specific Product.

Ideas? Was this a good way to proceed, or am I running around in circles?

Thanks.
RW Wood

shanesuebsahakarn

OK, the table is a good idea. I think you could probably combine them into one table though - just have a check box to indicate whether the person is an item buyer and whether they get a profit share for that particular product. I think it would look something like this:

ID (primary key)
PartnerID
ProductID
IsBuyer (yes/no)
GetsProfitShare (yes/no)

To get the non-buyer profit, I think you would use an IIf statement - check to see if the partner ID is null in the itembuyers table (assuming that it's joined using a LEFT rather than an INNER join) and then perform your calculation in that way.

PstWood

ASKER

How do I get the yes no value for the IsBuyer field? As it stands, I have a field on the AddInventory form that is a combo box with a list of the partners names from which the user selects to add the buyer to the inventory table.

Thanks.
RWW

PstWood

ASKER

The more I think about it, the more I'm not sure how this would work. Since there are more than one partner whose IsBuyer and GetsProfitShare columns need an entry, there would be one record for each partner for each ProductID. 4 partners = 4 rows for each ProductID (Originally I thought to have the ProductID as the primary key, but I see now that wouldn't work since there would be more than one entry for each product)

I can see that this would be ideal for arriving at the final figures, but I'm not sure how you go from "This partner is the buyer and has this % of the profit" to "All the other partners get this percent of the profit"

Thanks.
RWW

shanesuebsahakarn

No objections.

ASKER CERTIFIED SOLUTION

modulo

membership

This solution is only available to members.

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

Start Free Trial