Table design

[formcreator]

Hello,
I have a question on Table design. I need to create a table which has
the following fields. Can this be done.

BP_ID BP_Name BPRank BP_Weight BP_INdex
1 A 1 3 (NO. of
records -BPRank)*BP_Weight


Can someone guide me on how to create this table.

In excel it is possible to create a formula for this but can we do it in
Access.


Thanks in Advance
Shri

 

[Duane Hookom]

You can create your calculation in a query or report or form. The value
should not normally be stored in any table.

Create your query might look something like:
SELECT BP_ID, BP_Name, BPRank, BP_Weight, ((SELECT Count(*) FROM
tblYourTable) - BPRank) * BP_Weight as BP_Index
FROM tblYourTable;

 

[tedmi]

Rule 1: Access is NOT Excel
Rule 2: Tables are NOT spreadsheets
Rule 3: Do NOT store calcuted data in tables

Your table, let's call it tblBP, should have fields BP_ID, BP_Name, BPRank
and BP_Weight
The last column belongs in a query, not a table, like this:
SELECT BP_ID, BP_name, BP_Rank, BP_Weight,
(DCount("tblBP")-BP_Rank)*BP_Weight AS BP_Index
FROM tblBP

 

[formcreator]

Thanks Duane, that works but if I use this formual and I have 2 records with
the following info

BP_Rank BP_Weight BP_Index
1 3 3
2 2 0

this is because count(*) = BP_Rank for the last record. How do I resolve
this issue.


Thanks
Shri

 

[Duane Hookom]

"How do I resolve this issue" what issue? What is there to resolve? What
would you expect to be calculated? What is the calculation you need/want?

 

[formcreator]

Duane,
I am sorry I didn't explain it right, the query for BP_Index

((SELECT Count(*) FROM tblYourTable) - BPRank) * BP_Weight as BP_Index
returns a 0 for the last record so the BP_Index for the last record is 0.
this is because BPrank is the record number and count gives the total number
of records.

My question is how do I find if it is the last record in the table and
assign 1 as the value for the ((SELECT Count(*) FROM tblYourTable) expression.

Thanks

 

[Duane Hookom]

Does this work for you?
((SELECT Count(*)+1 FROM tblYourTable) - BPRank) * BP_Weight as BP_Index
Or
((SELECT Count(*) FROM tblYourTable) - IIf(BPRank=1,0,BPRank)) * BP_Weight
as BP_Index

 

[formcreator]

yes that helps.

now can I do bp_Rank/sum(bp_rank) in another field called BP_total

Thanks

 

[Duane Hookom]

Yes. Did you change the spelling of the field name? The Sum() can be returned
by a subquery like previous calculations.

 

[formcreator]

Duane,
I tried to use the subquery, but I am getting a circular reference
error. Here is the formula

SELECT Business_Process.BProcess_ID, Business_Process.BProcess_Name,
Business_Process.BProcess_Rank, Business_Process.BProcess_Weight, ((SELECT
Count(*) + 1 FROM
Business_Process)-BProcess_Rank)*BProcess_Weight AS BP_Index,
bp_Index/(Select Sum(BP_INdex) from newquery)
FROM Business_Process;

newquery is the name of the query. Do I have to create another query for this.

Thanks
Shri

 

[Duane Hookom]

Creating a new query might be the easiest. It could probably all be done in a
single query but I'm not smart enough to provide a quick answer.

 

[formcreator]

Thanks
That's what I have done for now. May be I'll find a better way later.


Thanks for all your help
Shri