Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Possible to assign specific query as field in table?

Posted on 2004-09-07
Medium Priority
177 Views
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 http://www.experts-exchange.com/Databases/MS_Access/Q_21119225.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
0
Question by:PstWood
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 8
• 7

LVL 41

Expert Comment

ID: 11997652
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
0

Author Comment

ID: 11998161
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
0

LVL 41

Expert Comment

ID: 11998244
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.
0

Author Comment

ID: 11998697

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
0

Author Comment

ID: 11998786
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
0

LVL 41

Expert Comment

ID: 11998788
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?
0

LVL 41

Expert Comment

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

Author Comment

ID: 11999135
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
0

LVL 41

Expert Comment

ID: 11999183
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.
0

Author Comment

ID: 11999306
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
0

Author Comment

ID: 12006524
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
0

LVL 41

Expert Comment

ID: 12006605
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
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.
0

Author Comment

ID: 12008012
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
0

Author Comment

ID: 12008678
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
0

LVL 41

Expert Comment

ID: 12056710
No objections.
0

Accepted Solution

modulo earned 0 total points
ID: 12057428
PAQ-ing the question and refunding 500  points

Thanks shanesuebsahakarn !

modulo

Community Support Moderator
Experts Exchange
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Itâ€™s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrickâ€™s Day holiday can be felt throughout the world.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Whatâ€™s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macroâ€™s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increasedâ€¦
###### Suggested Courses
Course of the Month8 days, 10 hours left to enroll