Pre-fill column in Table with Query outcome

[vandy05]

I have a Master Data Table and a Payments Table. I have a query that uses the relationship between the two to get the Total Payment (each specific entry in the Master Table will potentially have several payments so I made this query so we could keep track of what was paid so we can compare it to the budget). Is there a way to get the Total Payment into the Master Data table without having to run the query over and over (ie. append query)

 

[Atomic Shrimp]

You probably don't need to. A very popular and fundamental DB design mantra is 'never store what you can calculate or look up'

Just create the query that shows you the data you want (maybe by joining the master and payment tables), then use that query as if it were a table - in your report or form or whatever - and it will be automatically requeried on demand every time.

 

[vandy05]

I understand I don't need to but is there a way to do it?

 

[Atomic Shrimp]

No, there is not. Not without running an update query each time you want it updated.

That update query could be automated to run when a form opens, or something, but that approach adds no benefits over just using a query, but multiplies risks and potential points of failure.

I don't understand why you would want to do it that way, when it can be done more easily and reliably with a query.