Results 1 to 7 of 7
  1. #1
    michaelwh1 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    9

    Correlate dates between two tables.

    I have 2 tables. One has a single column that contains week starting dates for 18 months. The other table has a customer, start date, end date and hours worked fields. I want to create a table that shows by week starting in table 1 the customer and number of hours worked each week divided by the number of weeks specified by the date range in table 2. The dates used in table two match those in table 1.



    Table 1

    Week Starting
    4/7/14
    4/14/14
    4/21/14
    4/28/14
    5/5/14
    5/12/14

    Table 2

    Customer Start End Hours Worked

    Acme 4/14/14 4/21/14 100
    Supply 4/21/14 5/5/14 150


    New Table or Query Result

    Week Hours Worked
    4/7/14
    4/14/14 50
    4/21/14 100
    4/28/14 50
    5/5/14 50
    5/12/14

    Not sure how to achieve this. Thanks in advance.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    How is the data getting into your database in this format? In other words is there a specific reason you're not using a normalized structure where you'd have a total hours per week per customer instead of a range of weeks? I can't really think of a way to do this without using VBA and a temporary table because of the poor structure.

  3. #3
    michaelwh1 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    9
    I agree, however, the two tables are independant data sources that are brought into Access and I am trying to correlate.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    The only way I can think to do this is using VBA and a temp table, if you're open to that we can proceed, if you're trying to do it with queries I don't know if I can help.

  5. #5
    michaelwh1 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    9
    I am open to using VBA. Thanks

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Simple example, just run the module and it should give you the results you indicated.

    michaelwh1.zip

  7. #7
    michaelwh1 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2013
    Posts
    9
    You have my sincere thanks. This will work perfectly.

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

Similar Threads

  1. Match Dates from two tables
    By GabyArco in forum Queries
    Replies: 2
    Last Post: 06-02-2013, 06:03 PM
  2. Join Tables with differing Dates
    By sberti in forum Queries
    Replies: 2
    Last Post: 11-29-2012, 10:22 PM
  3. Combing two tables with missing dates
    By Jerseynjphillypa in forum Queries
    Replies: 1
    Last Post: 07-19-2012, 10:51 AM
  4. tables and dates
    By bigmac in forum Access
    Replies: 7
    Last Post: 02-24-2012, 06:39 AM
  5. Query two tables for specified dates
    By funkygoorilla in forum Queries
    Replies: 2
    Last Post: 12-12-2011, 11:12 AM

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