Results 1 to 5 of 5
  1. #1
    Turncloud is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jul 2013
    Posts
    22

    Unhappy Newbie question - populating a field from a count field on a query


    I suspect this is so obvious but I need to ask as I'm going around in circles.

    I have created a query that links the 'Reference' field in Table A with the "Supplier" field in Table B and then has a Count Field to tell me how many times the match occurs. Table A only has 1 entry per manufacturer and Table B has many. The tables are in separate databases. When I run the query I get the following result.

    Click image for larger version. 

Name:	pic1.JPG 
Views:	8 
Size:	27.5 KB 
ID:	13232

    So far so good.

    On my detail form (attached to Table A) I have a field that I'd like to populate with the Count field (the 3rd column) based upon the Reference field and I just can't work out how to do it. I have tried to specify the query field as the source in the expression builder but it just gives me #NAME? in all the fields.

    Click image for larger version. 

Name:	pic2.JPG 
Views:	5 
Size:	48.7 KB 
ID:	13233

    Can someone point me in the right direction ?

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You just want to display the aggregate calc, not save to field in table? Options:

    1. form RecordSource that joins the query with other table, join type "Include all records from {primary table} and only those records from {query name} that match" then bind textbox to the count field, set properties Locked Yes and TabStop No, however, been a while since I tried this, the resulting dataset might not be editable

    2. DLookup() expression in textbox

    3. listbox or combobox with the query as RowSource and filtered to some ID on the form
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Turncloud is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jul 2013
    Posts
    22
    Quote Originally Posted by June7 View Post
    You just want to display the aggregate calc, not save to field in table? Options:

    1. form RecordSource that joins the query with other table, join type "Include all records from {primary table} and only those records from {query name} that match" then bind textbox to the count field, set properties Locked Yes and TabStop No, however, been a while since I tried this, the resulting dataset might not be editable

    2. DLookup() expression in textbox

    3. listbox or combobox with the query as RowSource and filtered to some ID on the form
    Thanks for coming back to me.

    Choosing option 2 first, if I use the following DLookup as the control source for the text box it doesn't work

    =DLookUp([Supp_main Query],[Supp_main Query]![CountOfManufact_Code],[Supp_main Query]![Reference]='[txt_reference]')

    where qry_count_ncm is the query, Countofreference is the third column in the QBE and Supp_main!Reference is the database field entry that holds the reference number I'm trying to extract out of the query (it's column 1 in the QBE)

    Using option 3 I can get a combo box with all the query results, but how to I get it to display only the row with [Supp_main]!Reference in it ?

    thanks
    Simon

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Arguments for domain aggregate functions must be enclosed in quote marks. You have field and table arguments in wrong order. Concatenate variables, reference to textbox is a variable.

    Assuming Reference is a text field.

    =DLookUp("[CountOfManufact_Code]", "[Supp_main Query]", "[Reference]='" & [txt_reference] & "'")

    RowSource of combo or list box would need to reference a query object that has reference to form textbox as filter parameter or be an SQL statement. This is known as cascading or dependent combo/list box.

    SELECT CountOfManufact_Code WHERE Reference=[txt_reference];
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Turncloud is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jul 2013
    Posts
    22
    June7, you're a star ! that Dlookup command worked.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-24-2013, 10:10 AM
  2. Replies: 5
    Last Post: 09-21-2012, 11:23 AM
  3. Replies: 3
    Last Post: 07-14-2011, 05:33 PM
  4. Query to group and count field entries
    By newtoAccess in forum Queries
    Replies: 4
    Last Post: 11-29-2010, 09:19 AM
  5. Replies: 5
    Last Post: 08-05-2009, 12:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums