Results 1 to 14 of 14
  1. #1
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44

    Control Source from another table

    This is probably a simple question but one that is alluding me, i have a report but i want to add some totals from another table.

    The report already has simple control source items like =Count(IIf([LocationShort]="A101",0)) but i want to count other items in a different table
    using the same method????


    Click image for larger version. 

Name:	report.PNG 
Views:	22 
Size:	5.3 KB 
ID:	34668

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Why not use DCount() and/or DSum() functions as the control source of a text box on your report
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44
    One step closer, so if i wanted to count the number of times say 'Wrigleys' appeared as the customer name, expression builder is giving me the following but it throws an error?

    =DCount( [qryTableMerge]![Customer] = "Wrigley")

  4. #4
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    check out the DCount syntax here...https://www.techonthenet.com/access/...ain/dcount.php

  5. #5
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44
    Thats a numeric value in the example, trying different combinations :-(

    =DCount("qryTableMerge","Customer","Customer=WRIGL EY")
    =DCount("qryTableMerge","Customer=WRIGLEY")
    =DCount("qryTableMerge","Customer","Customer="WRIG LEY"")
    =DCount("qryTableMerge","Customer="WRIGLEY")

  6. #6
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps:
    =DCount("qryTableMerge","Customer='WRIGLEY'")
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  7. #7
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44
    Yes I tried this as well but i get #Error :-(

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Should be:
    =DCount("Customer","qryTableMerge","Customer='WRIGLEY'")
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  9. #9
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44
    Super stuff, thankyou Bob that worked a treat, Didn't quite find anything on Google that fitted the bill :-)

  10. #10
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44
    Bob

    =DCount("Customer","qryTableMerge","Customer='WRIGLEY'")

    If i wanted to add a 2nd parameter where I want to count 'Customer' = Wrigley where 'Aisle' = 'A101'

    How would I do this??

  11. #11
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44
    It's odd but after a bit of googling, the following works........

    =DCount("Customer","qryTableMerge","Customer='WRIG LEY' AND Aisle='A101'")

  12. #12
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Try:
    Code:
    =DCount("Customer","qryTableMerge","Customer='WRIGLEY' AND Aisle = 'A101'")
    missed post #10 while testing code
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  13. #13
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    44
    Thats what Ive got in the end too.

    Some of my aisles have 2 or 4 characters so I need to use a left function for AISLE or a wildcard perhaps
    Ive tried eg AISLE = 'JH*' but I get zero how would i add a wildcard or perhaps a left function of 2 for the aAISLE????
    =DCount("Customer","qryTableMerge","Customer='WRIGLEY' AND Aisle = 'A101'")

  14. #14
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I don't understand the problem.

    It doesn't matter how many characters are held in "Aisle"
    The expression that we've agreed works returns a count of records for WRIGLEY where Aisle = A101

    If you want a count for WRIGLEY where Aisle = JH then you would use:
    =DCount("Customer","qryTableMerge","Customer='WRIGLEY' AND Aisle = 'JH'")
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

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

Similar Threads

  1. Different table control source
    By NJK in forum Forms
    Replies: 3
    Last Post: 07-16-2015, 07:05 PM
  2. Replies: 7
    Last Post: 08-13-2013, 03:54 PM
  3. Replies: 1
    Last Post: 04-23-2013, 03:35 PM
  4. Control Source on Form Reading Value in Table
    By hammer187 in forum Forms
    Replies: 1
    Last Post: 10-23-2012, 02:43 PM
  5. Should forms use a table or query as their control source?
    By cheyanne in forum Database Design
    Replies: 2
    Last Post: 05-30-2012, 04:00 AM

Tags for this Thread

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