Using Append Query

[NateL]

Can I use the append query to update a table by replacing all existing information with updated information while at the same time adding all data that isn't currently in the table?



Right now every time I run the Append Query it just adds the same information as a new row instead of replacing the existing row with the updated information.

[ItsMe]

Append and Update are two different types of Action queries. It sounds as though you need an Update query to edit information. As you discovered, an Append query will only append rows to a table.

[NateL]

Is it really as simple as just changing the query from "Append" to "Update?

My issue is I am combining more than 10 reports with 100 columns that are all the same fields. Append Query allows me to add them all to one table. Update query doesn't let me choose a table to update like the Append Query does.

[ItsMe]

I would suggest creating a Select query using the query builder. Once you have the fields that interest you selected, change the query to an Update query. If you are still having trouble, post the SQL of the query you created along with a brief explanation of what, how, and where you want to run the Update. For instance, in a form I want the user to click a button and run the update query based on these fields/values in the form's current record.

[NateL]

I have pasted the current SQL in italics below. As you can see I have many fields! I am really raw when it comes to Access.

Right now I have I have 20 linked Excel tables in my database. So far I created a delete query to clear the "MasterSKUTable" before I insert them all back into the table. I created an append query for every one of these tables to update the "*MasterSKUTable". There has to be a better way to do this. I would much rather just be able to update the "MasterSKUTable" without having to delete it and then run all of the append queries again.

Let me know if this doesn't make sense. I am still so new to this and I don't necessary understand all the terminology. However, my end goal to this process is to combine all of our programs monthly usage reports into one master report with all of the information in it. The individual usage reports are currently being run by 5-10 people on a bi-monthly basis which is why we don't just have one report for all of this. Also, considering these are all program specific and the person managing that account will know all the information necessary to manage each specific account. Thanks in advance!

INSERT INTO [*MasterSKUTable] ( SKU, [Import Sku/Matrix], Name, Status, Category, [Sub Category], Sizing, [SKU Prefix], [Reference Sku], Gender, Color, [Domestic/Overseas], [Months In Program], [2009 Jan ], [2009 Feb ], [2009 Mar], [2009 April ], [2009 May], [2009 June], [2009 July], [2009 Aug], [2009 Sept], [2009 Oct], [2009 Nov], [2009 Dec], [2010 Jan ], [2010 Feb ], [2010 Mar], [2010 April], [2010 May], [2010 June], [2010 July], [2010 Aug], [2010 Sept], [2010 Oct], [2010 Nov], [2010 Dec], [2011 Jan], [2011 Feb], [2011 Mar], [2011 Apr], [2011 May], [2011 Jun], [2011 July], [2011 Aug], [2011 Sep], [2011 Oct], [2011 Nov], [2011 Dec], [2012 Jan], [2012 Feb], [2012 Mar], [2012 Apr], [2012 May], [2012 Jun], [2012 Jul], [2012 Aug], [2012 Sep], [2012 Oct], [2012 Nov], [2012 Dec], [2013 Jan], [2013 Feb], [2013 Mar], [2013 Apr], [2013 May], [2013 Jun], [2013 July], [2013 Aug], [2013 Oct], [2013 Nov], [Total Shipped], [Last Year Shipped], [Last 6 Mo# Shipped], [Last 3 Mo# Shipped], Available, Outputs, [Total Stock], [Mon# Usage Last 12 Months], [Mon# Usage Last 6 Months], [MIS (Last 12 Months)], [MIS After Restock (Last 12 Months)], [MIS (Last 6 Months)], [MIS After Restock (Last 6 Months)], [Stock Needed], [To order], [Case Quantities], [Import Stock Available], [Import Stock Outputs], [Total Import Stock] )

SELECT [ACME#SKUS].SKU, [ACME#SKUS].[Import Sku/Matrix], [ACME#SKUS].Name, [ACME#SKUS].Status, [ACME#SKUS].Category, [ACME#SKUS].[Sub Category], [ACME#SKUS].Sizing, [ACME#SKUS].[SKU Prefix], [ACME#SKUS].[Reference Sku], [ACME#SKUS].Gender, [ACME#SKUS].Color, [ACME#SKUS].[Domestic/Overseas], [ACME#SKUS].[Months In Program], [ACME#SKUS].[2009 Jan ], [ACME#SKUS].[2009 Feb ], [ACME#SKUS].[2009 Mar], [ACME#SKUS].[2009 April ], [ACME#SKUS].[2009 May], [ACME#SKUS].[2009 June], [ACME#SKUS].[2009 July], [ACME#SKUS].[2009 Aug], [ACME#SKUS].[2009 Sept], [ACME#SKUS].[2009 Oct], [ACME#SKUS].[2009 Nov], [ACME#SKUS].[2009 Dec], [ACME#SKUS].[2010 Jan ], [ACME#SKUS].[2010 Feb ], [ACME#SKUS].[2010 Mar], [ACME#SKUS].[2010 April], [ACME#SKUS].[2010 May], [ACME#SKUS].[2010 June], [ACME#SKUS].[2010 July], [ACME#SKUS].[2010 Aug], [ACME#SKUS].[2010 Sept], [ACME#SKUS].[2010 Oct], [ACME#SKUS].[2010 Nov], [ACME#SKUS].[2010 Dec], [ACME#SKUS].[2011 Jan], [ACME#SKUS].[2011 Feb], [ACME#SKUS].[2011 Mar], [ACME#SKUS].[2011 Apr], [ACME#SKUS].[2011 May], [ACME#SKUS].[2011 Jun], [ACME#SKUS].[2011 July], [ACME#SKUS].[2011 Aug], [ACME#SKUS].[2011 Sep], [ACME#SKUS].[2011 Oct], [ACME#SKUS].[2011 Nov], [ACME#SKUS].[2011 Dec], [ACME#SKUS].[2012 Jan], [ACME#SKUS].[2012 Feb], [ACME#SKUS].[2012 Mar], [ACME#SKUS].[2012 Apr], [ACME#SKUS].[2012 May], [ACME#SKUS].[2012 Jun], [ACME#SKUS].[2012 Jul], [ACME#SKUS].[2012 Aug], [ACME#SKUS].[2012 Sep], [ACME#SKUS].[2012 Oct], [ACME#SKUS].[2012 Nov], [ACME#SKUS].[2012 Dec], [ACME#SKUS].[2013 Jan], [ACME#SKUS].[2013 Feb], [ACME#SKUS].[2013 Mar], [ACME#SKUS].[2013 Apr], [ACME#SKUS].[2013 May], [ACME#SKUS].[2013 Jun], [ACME#SKUS].[2013 July], [ACME#SKUS].[2013 Aug], [ACME#SKUS].[2013 Oct], [ACME#SKUS].[2013 Nov], [ACME#SKUS].[Total Shipped], [ACME#SKUS].[Last Year Shipped], [ACME#SKUS].[Last 6 Mo# Shipped], [ACME#SKUS].[Last 3 Mo# Shipped], [ACME#SKUS].Available, [ACME#SKUS].Outputs, [ACME#SKUS].[Total Stock], [ACME#SKUS].[Mon# Usage Last 12 Months], [ACME#SKUS].[Mon# Usage Last 6 Months], [ACME#SKUS].[MIS (Last 12 Months)], [ACME#SKUS].[MIS After Restock (Last 12 Months)], [ACME#SKUS].[MIS (Last 6 Months)], [ACME#SKUS].[MIS After Restock (Last 6 Months)], [ACME#SKUS].[Stock Needed], [ACME#SKUS].[To order], [ACME#SKUS].[Case Quantities], [ACME#SKUS].[Import Stock Available], [ACME#SKUS].[Import Stock Outputs], [ACME#SKUS].[Total Import Stock]
FROM [ACME#SKUS];

[ItsMe]

Is anybody working out of your Access database yet? What function does it have today? When I look at the SQL it leads me to believe that operations relies on the spreadsheets to manage their daily functions. The names of the fields/cells indicate there is some data in the spreadsheets that does not belong in a Relational Database Management System (RDBMS).

[NateL]

Nobody is working out of the db yet. I just started creating this yesterday. All of our data is coming from a separate system and we have created formulas to be able to manage inventory on a per program basis. We import updated shipped quantities, available quantity and replenishment quantities bi-monthly.

You are correct in this statement, "When I look at the SQL it leads me to believe that operations relies on the spreadsheets to manage their daily functions.".

I am trying to find a way to combine all of these individual reports into one master report which has all of the information. I have attached an example of what one of these excel spreadsheets look like.

I really appreciate your assistance and patience.

[June7]

If link to spreadsheet is possible and you don't intend to do data entry/edit in Access, maybe no need to do INSERT and UPDATE sql actions.

Linked spreadsheets can be worked with just like tables (except for editing). They can be used in queries and as the basis for reports.

A UNION query could possibly generate the 'master' SKU dataset.

However, if the goal is total conversion to Access and elimination of Excel, need to get data structure correct first.

[ItsMe]

I would start by creating some subfolders in a local C drive. I would have folders for TestDB, PracticeDB, Garage, Archive. I would use these folders for developing the DB. I would have other folders for the spreadsheets. Maybe, ImportSpread, HistoricalSpread, Archive.

You need to figure out a way to import the data into the DB. You need to start by developing tables. Your relations will determine what data gets imported and what data does not. You need to normalize your data structure and not import spreadsheets. You need to import data. Nice, clean, pure, data. RDBMS are not spreadsheets. They manage data. When you look at a spreadsheet, it is intuitive. When you look at a single table in a normalized database, it is not intuitive.

Look at your spreadsheets and determine what data is changing often and what data is not changing as often. The data that is constantly changing will most likely be calculated data or cells with formulas. This data you do not want to import. The data that may change not as often as the formulated cells will likely go into a table for events or activities. An order gets placed, a sale happens, etc. This data will go into a table of its own. The remaining cells with data will also need to go into tables. Most likely you will need several tables for this remaining data that does not change often. One example of such data may be the names of the tabs on the worksheets. The worksheet names may belong in one or more tables, or even need to be in a "Junction Table".

That is why all of the folders. You will want a place to store a copy of the spreadsheets and DB file you will practice with. After you make some steps forward, you will want to begin again with something familiar, ie the original files. After you break some files and goof stuff up, you can put them in the archive folders and begin the process again. You will also want to save your good work in a backup folder.

You have your work cut out for you. Even after you get it figured out and the DB works well, you will need to bring the data into the DB and get operations transferred over to the new system. Plan your phases and don't put the horse before the cart.