Allocation Query

[Nicole]

I am new to Access and and am hoping someone can help. I have a table that
consists of 4 columns: LA Code (which is text), LA Revenue (number), LP Code
(text) and LP Revenue number. One LA Code may have a relationship with
several LP Codes. I am trying to allocate the LA Revenue to the associated
LP Codes based on the % of LP Code revenue to Total LP Code Revenue for the
particular LA Code. Here's an example.

LA Code LA Revenue LP Code LP Revenue
LA1A7 $2,046 LP1MG $13,218
LP1MH $297,460

In other words, I want to allocate $2,046 to LP1MG based on $13,218/$310,678
and to LP1MH based on $297,460/$310,678.

 

[vanderghast]

SELECT LAcode,
LPrevenue/(SELECT SUM(b.LPrevenue)
FROM tableNameHere AS b
WHERE b.LPcode=a.LAcode)
FROM tableNameHere AS a


should do. The sub query sum the LP_Revenue values for all records where
LP_code = LA_code, the LA_code value being supplied by the actual record
having our attention.

It may be faster to proceed with two queries.

Q1:
SELECT LPcode, SUM(LPrevenue) AS lpr
FROM tableNameHere
GROUP BY LPcode


Q2:
SELECT LAcode, LPrevenue/Q1.lpr
FROM tableNameHere INNER JOIN Q1
ON tableNameHere.LAcode = Q1.LPcode


and use Q2.


Vandegrhast, Access MVP

 

[Nicole]

Thank you so much for your help. I have another question. I am in the
Query Design View and am trying to create Query1 that you show below and need
some guidance due to my lack of Access experience.

In the first column of Design View, I entered the following:
Field: LP Code
Table: My table name
Total: Group by

In the next column, I entered:
Field: LP Revenue
Table: My table name
Total: Sum

Is this correct? Where do I enter "AS lpr" that you mentioned in Q1?

 

[vanderghast]

You give an alias to the SUM, lpr as example, by entering, in the first line

lpr: LP Revenue

rather than just the field name, and, if you switch in SQL view, you should
be able to spot:

... SUM([LP Revenue]) AS lpr

Alternatively, you can add the AS lpr in SQL view, at the
right place, and switch back in design view.

Note that you can use another name than lpr, as long as it is not a reserved
word, neither an existing field name. LPSumOfRevenue can be a nice one too,
as example (even if it is a little bit too long for my liking, I don't have
to 'like' it in the first place, isn't it )



Vanderghast, Access MVP