update query Access 2007

[MarkGardner]

I have summarized a table(a) of 589 records into a table of 119 records(b).

I am trying to calculate a factor in (a) as a percentage of the total in (b) for each record in (a).

(a) can have multiple records for each corresponding, summarized record in (b).

I do this by dividing each of the many records in (a) by a summarized record in (b) with a common field in both.

Basically, I am dividing

Round(([(a)]![netchrg] / [(b)]![netchrg]),8)

All I get is 0.00000000

I have checked my join relationships.
I started by checking the 2nd bubble. It did not work. Eventually, I have tried selecting all three bubbles on the joins and that does not get me any different values.

I would have thought that the 2nd bubble would have given me all of (a) and match it to the the records is (b) for the calculation.

I have even tried eliminating the joins completely but I still get a 0 value.

I have verified that both tables have values in the netchrg field.

Suggestions?

Thanks.

 

[boblarson]

What's with the parentheses inside the brackets? Is your table name actualy (a) including the parenteses?

If you have two tables in the query then your calculated field would be (forgetting for now about the rounding):

MyNewField:[TableBName]![FieldName]/[TableAName]![FieldName]

If you were to rename the column in table B so it wasn't the same name as the column in Table A, it would simplify things further:

MyNewField:[FieldNameB]/[FieldNameA]

Also, remember that to get a percentage of a for values in B, you need to divide B into A, not the other way around.

 

[jzwp22]

I did a mock up database based on the info in your post and got the following query to work.


SELECT Round(([tblDetails].[netchrg]/[tblSummary].[netchrg]),8) AS Myratio, tblDetails.pkRecID
FROM tblDetails INNER JOIN tblSummary ON tblDetails.fkSummaryID = tblSummary.fkSummaryID;

The fkSummaryID relates the two tables.

 

[MarkGardner]

Thanks to both boblarson & jzwp22.

I attempted the fix as suggested by boblarson. It did not solve it, but it did lead me to the solution which I am grateful for.

I noticed by reversing the tables, as suggested, that it got me a whole number with no decimal places. (I was going for the percentage of the larger number.) That is what led me to ask myself why I had no decimal places, even though the reversal fo the tables values calculation(by calculator) should have had decimal places, carried out 8 decimal places. I then discovered in the design the feature of "scale". I am still learning here.

It is the maximum number of decimal places to the right of the decimal separator. Ta da... I figured that it was something simple. I did not know what it was though. I just needed someone to point me in the right direction. Apparently the calc was correct at the beginning. I just did not have it displayed correctly and a value less than 1 and scale set to 0, then it displays a zero.

Thanks again for the help to get me there.