Results 1 to 5 of 5

VBA For Comparing Data From Multiple Tables

  1. #1
    Botfly is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    4

    Question VBA For Comparing Data From Multiple Tables

    Hello fellow programmers, I am having issues with working out some VBA logic. I am working on a large project and I have come to a road block. I will describe 2 tables and 4 fields I am concerned with.


    The first table is called "JobsAndQuotes". 2 fields from this table I am concerned of are called "JobNumber"(Short Text) and "Open Job"(Yes/No).


    A second table is called "Job Sheet Hours Worked". 2 fields from this table I am concerned of are called "Job Number"(Short Text) and "Hours Worked"(Number).


    Here's the part I am focused on:
    I need to add up every number in the "Job Sheet Hours Worked" table under the "Hours Worked" field in which that object's "Job Number" is the same as the "JobNumber" in the table "JobsAndQuotes" which also has 'Yes/True' for its "Open Job" field.


    All the code I have tried does not work. It adds it up to 0. And every time I tried using arrays, the RecordSets were always equal to "nothing".

    --------------------------


    Dim dbs As DAO.Database, rst As DAO.Recordset
    Set rst = dbs.OpenRecordset("SELECT JobsAndQuotes.JobNumber FROM JobsAndQuotes WHERE (((JobsAndQuotes.[Open Job])=True))")


    In this code, "rst" always equals "nothing". I have spent 20 hours now over 4 days trying to get "rst" to equal literally anything by using all kind of test criteria. I have given up on this part as well.


    ---------------------------




    Dim test1 As Double
    test1 = DSum("[Hours Worked]", "[Job Sheet Hours Worked]", "([Job Sheet Hours Worked]![Job Number] = [JobsAndQuotes]![JobNumber]")


    In this code, I am not even attempting yet to check if the "JobNumber" object also has "true" for its "Open Job" field. Yet, test1 equals 0 even though there is data in the tables which should add up to a number above 0.


    Any thoughts would be appreciated. If there is any information I left out, please let me know and I will clarify.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    46,082
    You declare a dbs variable but don't set it.

    Post sample data. If you want to provide db, follow instructions at bottom of my post.

    I suspect you really just need to do aggregate query.

    Build a query that joins the two tables with filter criteria under OpenJobs of True.

    Then click Totals button on ribbon to make aggregate design available.

    Or build a report based on the SELECT query and use report Sorting & Grouping features with aggregate calcs in textboxes.

    The domain aggregate must reference some parameter input for filter criteria, which in this case would be a control on form. Assuming [Job Number] is a number type field:

    test1 = DSum("[Hours Worked]", "[Job Sheet Hours Worked]", "[Job Number] = " & Me.tbxJobNumber)

    A domain aggregate expression can be in textbox ControlSource.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    Botfly is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    4
    Quote Originally Posted by June7 View Post
    You declare a dbs variable but don't set it.

    Post sample data. If you want to provide db, follow instructions at bottom of my post.

    I suspect you really just need to do aggregate query.

    Build a query that joins the two tables with filter criteria under OpenJobs of True.

    Then click Totals button on ribbon to make aggregate design available.

    Or build a report based on the SELECT query and use report Sorting & Grouping features with aggregate calcs in textboxes.

    The domain aggregate must reference some parameter input for filter criteria, which in this case would be a control on form. Assuming [Job Number] is a number type field:

    test1 = DSum("[Hours Worked]", "[Job Sheet Hours Worked]", "[Job Number] = " & Me.tbxJobNumber)

    A domain aggregate expression can be in textbox ControlSource.
    Thanks! You've made really great suggestions here! I have been figuring out how to manipulate aggregate queries and I have gotten 1/9th of it to work now. I never knew you could use queries to make a temporary table combining results from querying 2 other tables. This is very useful! I believe I will be able to continue getting the rest to work as I play with it some more. If I make 9 separate queries I think I can get this to work. However I am going to try to use 3 queries once I figure out how to get the syntax to work better in the VBA portion. If I run into more specific problems with this, I will report back here.

    Edit: And thanks for the fast reply! I really appreciate your help!

  4. #4
    Botfly is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    4
    I have an aggregate query which generates a temporary table with some values including the "Hours Worked" and "Machine Or Employee Hours" fields.

    So, I used this line of code to add up the total in the aggregate query in respect to the objects that are "Employee Only":
    test1 = DSum("[Hours Worked]", "[Aggregate Query for WIP All Open Jobs Date Range]", "[Machine Or Employee Hours]= " & Me.EmployeeOnly)

    However, it adds it up to 0 when I include the criteria portion of the DSum method. When I just use this code it works:
    test1 = DSum("[Hours Worked]", "[Aggregate Query for WIP All Open Jobs Date Range]")

    I want to just have the one query and be able to get totals for employee only hours and machine only hours. Otherwise I would need to make 2 queries. I tried all different types of syntax and it always adds to 0 if I include any criteria in the DSum. And by the way, the Me.EmployeeOnly is a text field that I am just using to test this which I manually typed in "Employee Only".

    Any thoughts?

  5. #5
    Botfly is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2019
    Posts
    4
    I have solved my own issue. The code that works is this:
    test1 = DSum("[Hours Worked]", "[Aggregate Query for WIP All Open Jobs Date Range]", "[Machine Or Employee Hours]= '" & "Employee Only" & "'")

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

Similar Threads

  1. Replies: 5
    Last Post: 01-04-2019, 08:25 AM
  2. Comparing Data From Two Tables
    By dcoley in forum Access
    Replies: 12
    Last Post: 05-01-2014, 11:09 AM
  3. Replies: 5
    Last Post: 12-09-2012, 01:29 PM
  4. Query comparing data in two tables
    By KatyOftedahl in forum Queries
    Replies: 1
    Last Post: 07-07-2011, 03:48 PM
  5. Replies: 2
    Last Post: 02-21-2011, 12:31 PM

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