Solved

Possible to assign specific query as field in table?

Posted on 2004-09-07
17
166 Views
Last Modified: 2008-03-06
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
Comment
Question by:PstWood
  • 8
  • 7
17 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
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

by:PstWood
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

by:shanesuebsahakarn
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

by:PstWood
ID: 11998697
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
0
 

Author Comment

by:PstWood
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

by:shanesuebsahakarn
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

by:shanesuebsahakarn
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

by:PstWood
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 41

Expert Comment

by:shanesuebsahakarn
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

by:PstWood
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

by:PstWood
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

by:shanesuebsahakarn
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
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.
0
 

Author Comment

by:PstWood
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

by:PstWood
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

by:shanesuebsahakarn
ID: 12056710
No objections.
0
 

Accepted Solution

by:
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now