Results 1 to 3 of 3
  1. #1
    Join Date
    May 2013

    Unanswered: Matching Year and Month from Dates

    I need help in matching records. I have a table with planned costs by date and another table with the actual costs by date. I would like to total each by year and month and then match them and save into a new table.

    I am having trouble because Access seems to be trying to match on the entire date. Can someone give me some guidance in this?


  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    its all down to the where clause

    WHERE YEAR(plannedcost.datecolumna) = year(actualcost.datecolumnb)
     AND MONTH(plannedcost.datecolumna) = MONTH(actualcost.datecolumnb)
    you will need to replace
    plannedcost with the name of the table containing the plan costs
    datecolumna with the name of the column containing the date in the plan costs table
    actualcost with the name of the table containing the actual costs
    datecolumnb with the name of the column containing the date in the actual costs table
    if you used a space in the table or column name enclose them in square brackets eg;
    WHERE YEAR([planned cost].[date column a])...
    if you are only used to using the query designer:-
    then pull up the query in the query designer,
    switch to SQL view (left most button under file),
    then cut and paste the code above, paste it as the last line (if you already have a where clause add the code without the where word (you shoudl only have 1 where in a sql statement
    if you have problems please cut and paste the whole query back here

    then change the table and column names as required

    don't save the data into another table, instead extract it from both tables as required
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2013

    Cool Dates

    Put it this way think of it as an alternative path to get certain data.

    Class A, B and C

    A is not linked to C but requires the information it holds. B and C are linked, so you go through class B to get to C using appropriate links.

    to find date

    WHERE (nameofdatefield) = e.g 2012

    You should remember that the dates are (mostly) set in US not UK

    Good Luck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts