SQL Query

Posted on 2014-08-18
Last Modified: 2014-08-22
I have two Access 2003 tables one is called Forecast and the other is called PartForecast.  One of my users updated the Forecast table when he should of been updating the PartForecast table.  Attached is the database we are using.  I need to move the data he entered from the Forecast table into the PartForecast table for Fiscal year 2015.  Can someone please assist with a SQL query that will accomplish this?  Thanks!
Question by:submarinerssbn731

    Author Comment

    The db is to large to upload.  I have created Excel sheets of the two tables in question.   Please see attached.
    LVL 30

    Expert Comment

    Create a copy of the database with just the 2 tables. Delete records leaving few records in each table (say 10 records each). Compact and repair.

    Upload the copy and show how to find out which record is updated.

    I tried to link the tables but there where too many records to handle. Also I have to make assumptions which the question did not make available.

    Author Comment

    Hello hnasr,  what are your questions and I will be happy to provide the answers.

    Author Comment

    I was able to upload the database.  Please feel free to do whatever you need to do to transfer the data from the Forecast table to the PartForecast table then send me the SQL snytax so that I can transfer the data here.  Thanks!!!
    LVL 24

    Accepted Solution

    You need to execute this query:
    INSERT INTO PartForecast ( FiscalYear, Mgr, Custcode, PSINO, AnnualVolume, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, [Dec], Obsolete, PNSI, PRICE, Updatedate )
    SELECT Forecast.FiscalYear, Forecast.Mgr, Forecast.Custcode, Forecast.PSINO, Forecast.AnnualVolume, Forecast.Jan, Forecast.Feb, Forecast.Mar, Forecast.Apr, Forecast.May, Forecast.Jun, Forecast.Jul, Forecast.Aug, Forecast.Sep, Forecast.Oct, Forecast.Nov, Forecast.Dec, Forecast.Obsolete, Forecast.PNSI, Forecast.PRICE, Forecast.Updatedate
    FROM Forecast
    WHERE (((Forecast.FiscalYear)=2015));

    Open in new window

    And then, after the recods are inserted into PartForecast, delete from Forecast:
    DELETE *
    FROM Forecast
    WHERE (((Forecast.FiscalYear)=2015));

    Open in new window

    Please make backup before executing these queries

    Author Closing Comment

    Great answer.  Idecided to delete the 2015 records out of the PartForecast table and then ran your SQL syntax and it worked great!!!  Thanks!!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now