?
Solved

SQL Query

Posted on 2014-08-18
7
Medium Priority
?
269 Views
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!
0
Comment
Question by:submarinerssbn731
  • 4
6 Comments
 

Author Comment

by:submarinerssbn731
ID: 40267579
The db is to large to upload.  I have created Excel sheets of the two tables in question.   Please see attached.
Forecast2015.xls
PartForecast2015.xls
0
 
LVL 31

Expert Comment

by:hnasr
ID: 40270176
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.
0
 

Author Comment

by:submarinerssbn731
ID: 40272305
Hello hnasr,  what are your questions and I will be happy to provide the answers.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:submarinerssbn731
ID: 40272327
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!!!
PSI-Budget-2015TEST.mdb
0
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 40278063
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
0
 

Author Closing Comment

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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
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…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

862 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