Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Oct 2015
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts

    MS Access 2013: Compare two tables and append when fields equal and Month and Year not equal

    I have an MS Access db with multiple tables. One table contains job codes (tblJobCode) and another contains Monthly Job Code Contractor Cost information (tblContractorCost).

    In tblJobCode I have 1 of each Job code and a short description of the job.

    In tblContractorCost I list each job code(JobCode), month(Period), year(Yr) and Cost(Cost) for that month. Each JobCode can exist multiple times though only once for any Month/Year combination. A primary key field (ContractorCostID) exists.

    What I need to do is compare the JobCode from tblJobCode to tblContractorCost field JobCode and if they match then compare the current month and Year to the fields Period (month) and Yr(Year). If the jobcode doesn't already exist for the current month/year then I wish to append a new record in tblContractorCost to include a new ContractorCostID, the JobCode, the current month, current Year, and a cost field with a default value of 0 (zero).

    I have been able to compare the appropriate fields in a query but it creates a new record for each existing record instead of one job code.

    I was thinking of using either a combination of a query and VB or just VB but am kind of new at this and am unsure of how to use VB to compare the fields and create a new record only if the combination of the 3 comparison fields (JobCode, month, Year) don't match anywhere in the tblContractorCost table but continues with the search with the next JobCode if there is a comparison match.


    ie. I check JobCode AA1000 and there is found a match with the JobCode for October 2015 so the next JobCode, AB1000 finds no match so it creates a new record with the 5 fields filled.

    I hope this explains it okay and you can help me figure this out.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    It would be helpful to post the SQL string for they query that you tried. In theory it should be possible to do what you intend, but the devil is likely in the details. You can do that by simply switching the query from design view to SQL view, then copy the text string and paste it into a new reply to this thread.
    Wendell

  3. The Following User Says Thank You to WendellB For This Useful Post:

    PapsTransmissions (2015-10-06)

  4. #3
    New Lounger
    Join Date
    Oct 2015
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts
    INSERT INTO tblContractorCost ( ContractorCostID, JobCode, Contractor, Cost, Period, Yr )
    SELECT tblContractorCost.ContractorCostID, tblJobCode.JobCode, tblContractorCost.Contractor, MonthName(DatePart('m',Now())) AS Period, DatePart('yyyy',Now()) AS Yr, 0 AS Cost
    FROM tblJobCode INNER JOIN tblContractorCost ON tblJobCode.JobCode = tblContractorCost.JobCode
    WHERE (((tblJobCode.JobCode)=[tblContractorCost].[JobCode]) AND ((tblContractorCost.Yr)=DatePart('yyyy',Now())) AND ((tblContractorCost.Period)<>(MonthName(DatePart(' m',Now())))));


    I forgot that I need to include the Contractor field (Contractor) which can be left blank. Hope this is what you needed.

    When I ran that query it created a record for each record already there instead of just 1 new one for each JobCode.
    Last edited by PapsTransmissions; 2015-10-06 at 13:46.

  5. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Sorry, I've been tied up with other things the last couple of days. It would also help to have a description of the tables involved for problems like this, but I think your issue is that you really need to identify the records where you don't have an existing record, and your current query doesn't do that. There are two ways to do that. One involves creating a subquery, but that's more complicated to debug, and my preference is to create a query which shows you what records in tblContractorCost already have Job codes for the current year. The SQL for that would look like:
    Code:
    SELECT tblJobCode.JobCode, tblContractorCost.Period, tblContractorCost.Yr
    FROM tblJobCode INNER JOIN tblContractorCost ON tblJobCode.[JobCode] = tblContractorCost.[JobCode]
    WHERE (((tblContractorCost.Period)=Month(Date())) AND ((tblContractorCost.Yr)=Year(Date())));
    You then save the query with an appropriate name - I chose qryExistingJobCodes

    Then you create a query that shows you the cases where you don't have a record for the existing job codes for the current month and year using what is referred to as an outer Join - there is a wizard in Access that you can use a few times to understand the principles involved - it is Find Unmatched Records. The SQL I created to do that looks like:
    Code:
    INSERT INTO tblContractorCost ( JobCode, Period, Yr )
    SELECT tblJobCode.JobCode, Month(Date()) AS Period, Year(Date()) AS Yr
    FROM tblJobCode LEFT JOIN qryExistingJobCodes ON tblJobCode.[JobCode] = qryExistingJobCodes.[JobCode]
    WHERE (((qryExistingJobCodes.JobCode) Is Null));
    Note that I first created this as a SELECT query, and then changed it to an INSERT query - Access calls them Append queries. Also note that you have the Month() and Year() date functions in Access so you don't need the more complex expressions you were using.
    Wendell

  6. #5
    New Lounger
    Join Date
    Oct 2015
    Posts
    3
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Thumbs up

    Quote Originally Posted by WendellB View Post
    Sorry, I've been tied up with other things the last couple of days. It would also help to have a description of the tables involved for problems like this, but I think your issue is that you really need to identify the records where you don't have an existing record, and your current query doesn't do that. There are two ways to do that. One involves creating a subquery, but that's more complicated to debug, and my preference is to create a query which shows you what records in tblContractorCost already have Job codes for the current year. The SQL for that would look like:
    Code:
    SELECT tblJobCode.JobCode, tblContractorCost.Period, tblContractorCost.Yr
    FROM tblJobCode INNER JOIN tblContractorCost ON tblJobCode.[JobCode] = tblContractorCost.[JobCode]
    WHERE (((tblContractorCost.Period)=Month(Date())) AND ((tblContractorCost.Yr)=Year(Date())));
    You then save the query with an appropriate name - I chose qryExistingJobCodes

    Then you create a query that shows you the cases where you don't have a record for the existing job codes for the current month and year using what is referred to as an outer Join - there is a wizard in Access that you can use a few times to understand the principles involved - it is Find Unmatched Records. The SQL I created to do that looks like:
    Code:
    INSERT INTO tblContractorCost ( JobCode, Period, Yr )
    SELECT tblJobCode.JobCode, Month(Date()) AS Period, Year(Date()) AS Yr
    FROM tblJobCode LEFT JOIN qryExistingJobCodes ON tblJobCode.[JobCode] = qryExistingJobCodes.[JobCode]
    WHERE (((qryExistingJobCodes.JobCode) Is Null));
    Note that I first created this as a SELECT query, and then changed it to an INSERT query - Access calls them Append queries. Also note that you have the Month() and Year() date functions in Access so you don't need the more complex expressions you were using.
    Thanks, That gives me an option I hadn't thought of when I first posted it. I did end up finally figuring it out. I created a query to check all JobCodes with current year and month that didn't exist and then when they didn't, I inserted them. The query looks like this:

    INSERT INTO tblContractorCost ( JobCode, Contractor, Period, Yr, Cost )
    SELECT tblJobCode.JobCode, Null AS Contractor, MonthName(DatePart('m',Now())) AS Period, DatePart('yyyy',Now()) AS Yr, 0 AS Cost
    FROM tblJobCode
    WHERE (((Exists (SELECT *
    FROM tblContractorCost
    WHERE tblContractorCost.JobCode =
    tblJobCode.JobCode
    AND Yr = DatePart('yyyy',Now())
    AND Period = MonthName(DatePart('m',Now()))))=False));


    I appreciate the feedback and you definitely provided the help I needed. And believe me, I know about getting distracted by other issues.

    Again, Thanks for the help and the insight!

  7. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Glad you got it solved - your approach uses the subquery which is sometimes more efficient, but I find it more abstract to understand and debug.
    Wendell

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
  •