Table not multiplying by queried column result

[nick1408]

I have a table called RICTweek and am trying to multiply by a value that I queried from another table. It looks like it is multiplying by the ID number rather than the returned value. How do I get it to multiply by the returned value?

A second question on the same table; how do I make the default value for Weekly Baseline the same as the month entered in week ending?

One final one; how do I make the default value for Week Ending 7 days more than the previous record (i.e. the next week available).

Hopefully some easy questions here!

 

[bob fitz]

IMHO it would be better not to use calculated fields in tables.
Read this: http://allenbrowne.com/casu-14.html

 

[nick1408]

Would it be better to set it up as a query then?

The problem I have is I need to store the data back in the table so I can reference it later. The user will be able to change a percentage and the other fields calculate. I have it working on a table already.

I see the link pretty much provides the answers. I guess I'll have to have a go at that

 

[bob fitz]

Would it be better to set it up as a query then?

IMHO: Yes, they're usually the best place for calculations.

 

[nick1408]

Cool, thanks for that. I setup the query as suggested. The problem is my data comes to me in a monthly format and I need to break it down to weekly. is there a way I can make the query list the week ending for each month that data is given? As you can see from the pictures below; my query is fairly simple at the moment. Ultimately I will be setting it up on a form so the user can adjust the %'s and using the link above to put it all on a table after the fact.

 

[nick1408]

Had a bit of a different thought on this.

Is it possible that if a user enters a date (say 29/01/17) that the return line is the one from 1/01/2017 from the picture above? This would work almost perfectly for me

 

[Cronk]

Add another hidden column to your query

format([RICT Month],"yyyymm")

and in the criteria put

format([What is the required date?],"yyyymm")

The bit in the square brackets is the prompt to the user.