ACCESS FEED: RE: [MS_AccessPros] Query to update child totals in parent record

Jumat, 24 April 2015

RE: [MS_AccessPros] Query to update child totals in parent record

Hi John,

Thanks for the response.

I know in theory you should never store calculated fields like this in a table, however the in the real world it's not always possible. J

In the Purchase Order section of this application, the user first sees a list of all the purchase orders in a continuous form, and from there they can select one to edit or delete, or add a new one. The client wanted to see the number of items on each order in this form, so I thought the easiest and fastest, from a form update perspective, way to do it would be to store the count of POLine Items in the POHeader table. Obviously, this can get out of sync, so I need a the query to run occasionally to straighten things out again.

Regards,

Andrew

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: 24 April 2015 13:19
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Query to update child totals in parent record

Andrew-

You should never store a calculated field like this in a table. I assume you're using a form to edit POHeader and a subform to edit POLines. Make the subform a Continuous form and put a text box in the form footer:

=Count(*)

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Apr 24, 2015, at 1:58 PM, andrew@adwsystems.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Hi All,
Can anyone help with this one.
I have two tables POHeader & POLines, and the POHeader table has a field that keeps track of the number of line items. I need a query that will update the NumLineItems field in the POHeader table with the correct count for the number of Line Items for that POHeader record, if you see what I mean. I can easily do it using VBA, but I'm sure it could be done using a query, but I can't quite get it to work.
Any help greatly appreciated.
Thanks,
Andrew