Results 1 to 14 of 14

Control Source from another table

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

    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 is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,012
    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.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  3. #3
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    42
    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 offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    105
    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
    42
    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 is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,012
    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.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



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

  8. #8
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,012
    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.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  9. #9
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    42
    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
    42
    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
    42
    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 is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,012
    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.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  13. #13
    bishmedia is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    42
    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 is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,012
    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.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



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, 06:05 PM
  2. Replies: 7
    Last Post: 08-13-2013, 02:54 PM
  3. Replies: 1
    Last Post: 04-23-2013, 02:35 PM
  4. Control Source on Form Reading Value in Table
    By hammer187 in forum Forms
    Replies: 1
    Last Post: 10-23-2012, 01: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, 03: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
  •  
Tech Forums: Microsoft Office Forums