simple query to count linked records (1 Viewer)

Hi I'm a bit new to Access, although I have plenty of experience of programming SQL databases, doing everything through a Microsoft UI is a bit bewildering

I need help with two similar things which I cannot figure out

Firstly I need to query the number of linked records in a table shown in a subform, based upon the currently selected master record, and for this information to be available in a datasheet on the master form (so presumably I need to create a field based around this query in the master table). The tables and forms are working correctly but I cannot figure out how to build this query.

Secondly, in a similar scenario I need a field in the master table to hold the sum total of a given field in a list of linked records in a subform

The end result should mean that the datasheet view of the master table should be able to tell me for every record "X number of records linked in table 1" and "Y sum total of values linked in table 2"

I've done a lot of googling around this but every article I can find around building queries demonstrates how to select for a given value, eg all employees who live in "London", but never how to select only the linked records for the currently selected master record. I'm pretty sure I'm missing something really simple here but I just can't figure it out!

Sounds like you could use DCount() with a criteria referring to the main form value. More on syntax:

http://www.theaccessweb.com/general/gen0018.htm

Hi. Welcome to AWF! You should be able to add the link between the master form and subform as a criteria to narrow down the results of your query.

Hi, Welcome to AWF.

In design view, open your subform. Add a footer if it does not have one. In the footer add two controls and change their Name properties to:
txtCount
txtSum

Make the control source for the first:
=Count(*)

Make the control source for the second:
=Sum(yourfieldname)

If your subform is in single or continuous view, you will be able to see the totals fields. However, the footer won't show if the subform is in DataSheet view.

In that case, you can use two unbound controls on the main form and as their ControlSource reference the controls in the subform:

=yoursubformcontrolname.Form!txtCount
=yoursubformcontrolname.Form!txtSum

Notice that is said yoursubform control name. This is the Name property of the CONTROL which may or may not be the same as the actual object name of the subform.

Then you need two lines of code in the subform's AfterUpdate event

Me.Parent!txtCount.Recalc
Me.Parent!txtSum.Recalc