MS AccessMulti Column Add using ALTER TABLE errors out if Column already exists. Not SQL experienced

[vicneedshelp]

I apologize if this has already been answered, but I’ve tried several suggestions within the website, but I’m not sure if I’m following advice correctly based on my limited SQL experience .



I have a demand schedule based on weekly buckets that changes weekly and if there is no demand in a particular week the week will not appear. I need to add missing weeks to the dataset based on the weekly run to satisfy a full year’s worth of weeks (W 04/2014 through W 03/2015) So far with help from several forums, I pieced together this simple SQL statement which works fine if the column doesn’t already exist. If it already exists, it errors out and aborts the query. I have no idea what I’m doing. Can anyone help?

Database name is MasterSchedule.accdb
Table requiring additional columns = [Master Schedule]

Weeks are exported as [W 03/2015] format. (It’s exported out of SAP this way and prefer not to manipulate unless I have to)

SQL query working except for if column exists.

================================================== =========
[ALTER TABLE [Master Schedule]

ADD [W 01/2015] SINGLE,
[W 02/2015] SINGLE,
[W 04/2015] SINGLE,
[W 05/2015] SINGLE,
[W 06/2015] SINGLE,
[W 07/2015] SINGLE,
[W 08/2015] SINGLE,
[W 09/2015] SINGLE,
[W 10/2015] SINGLE,
[W 11/2015] SINGLE,
[W 12/2015] SINGLE,
[W 13/2015] SINGLE;]

[JoeM]

This is not good database design (as matter as fact, it violates the rules of database normalization).
You do NOT want to add a new field your each week. Your data table structure should rarely change.
Working with data that is not normalized will cause you many headaches!

Rather, you should have a field for week number.
So, for each ID, instead of having one record with 52 fields (one for each week), you would have 52 records for each ID.
So each record would have ID, Week Number, and Value for Week.

[vicneedshelp]

Thanks for the comment! I won’t argue that it’s a terrible design… I’m certainly open to suggestions, except I’m not grasping your advice fully.. looking for more clarity.
More background:

I run a data dump that contains 1000 part numbers (no duplicates) that contains a value over a 52 week span (for now).

When running the same file extract weekly, parts may drop off or new parts maybe added and weeks for these parts may disappear or new weeks may be added.

I was trying to keep my data table uniform by using this auto add column query on the original data dump so that I may append the data to a base query that will remain unchanged structure wise.

There are many uses for this data, but the ultimate goal is to track part changes at the weekly level every week, quarter, and year… this is basically going to evolve into a historical trending database.

[JoeM]

Here is an example of the issues that design may cause for you.

Let's say that you have many full weeks of data out there. Now, let's say that you want to write a formula that would return how many weeks each part number appears. Can you do that? With the current format, it is quite long and cumbersome. If you normalize your database, it is quick and easy. De-normalized data makes tasks that should be simple quite difficult.

If you are not quite grasping what the data design looks like, post your first few records under your current table structure, and I will show you how it should be structured.

[vicneedshelp]

I don’t have a table structure yet. That what I was trying to design on paper first. Ultimately, it’s supposed to mirror the existing SAP data dump (excel) with attribute fields added including cost plus calculated fields. Ultimately stacking the weekly data sets on top of each other and distinguishing each data set by a weekly snapshot was the plan.

Screenshot below (snapshot dates 7/14, 7/21, 7/28) shows partial output of the excel file. I removed all the additional weeks, but the original data dump currently shows weeks through 3/2015 but weeks 1/2015 and 2/2015 are missing (no data in those weeks for 1000 parts).

This output is supposed to show the difference between running the same report each week.

The quantities may change by week, the week may disappear if there is not data (7/21), or a part may be removed or added (7/28)

Does this make any sense? I’m not sure how I would “normalize” this if all this data is coming from 2 data sources (Excel output and Cost table).

I appreciate the help, but I may be a lost cause. We’ve done things the wrong way for so long that I’m not sure I can go back.

[June7]

I am confused. I don't see 3/2015 in the image. What are 14_2014, 15_2014, 16_2014? Missing 15_2014 for 7/21/2014 snapshot causes misalignment of 16_2014.

This output could probably be generated in Access from normalized data with a CROSSTAB query.

If this is what the SAP data dump looks like, then importing to Access normalized structure will require significant code effort.

[JoeM]

The thing is, which you can easily SUM up across columns (fields) in Excel, you cannot in Access. You have to add each one up individually. So your sum would look something like:
[W 14_2014] + [W 15_2015] + [W 16_2014] + ...
So you would have 52 fields in your equation if you want to write a formula to sum all 52 weeks!

Based on your structure above, you would probably need at least two tables. One that has your individual Part details like this:

PART	LOCATION	Reqmt type	Version	Active	Cost	Unit
001	US	           ABC	             00	Y	$1.00	EA
002	US	           DEF	             00	Y	$2.00	EA

And one for your weekly data, like this:

PART	Week_Num	Weekly_Value
001	W 14_2014	200.000
001	W 15_2014	96.000
001	W 16_2014	53.000
002	W 14_2014	20.000
002	W 15_2014	20.000
002	W 16_2014	40.000

You would then join these values in a query, and if you wanted the structure you have displayed, you would probably need to use a Crosstab Query or Pivot Table (using Excel).

Regardless of what you choose, you probably have a bit of work ahead of you. But good development will save you loads of trouble down the road. If you are an inexperienced Access user, be careful about trying to create an Access database from scratch. If you do not have a good grasp of the concepts of Relational Databases, especially the Rules of Normalization, it will be a struggle. It is not an intuitive or quick concept to pick up.

There is lots of information on it out on the net. Here is one such link to get you started: http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

[vicneedshelp]

I cropped the image because the original data dump is 48 columns wide....Was just trying to show the format. 14_2014, 15_2014 etc represents SAP week within the year. I cut 17/2014 through 3/2015 out of picture. I agree on the misalignment. That's my problem. For the same data set the weeks may appear and disappear depending on the demand needs. The structure isn't consistent. I was trying to take the original excel data dump, import into access, and add missing weeks using the ALTER TABLE SQL query... and it worked as long as the week doesn't already exist. That's were I stopped in my tracks. While I know I'm in trouble in terms of creating a normalized structure... I still think a short term fix would be to get the ALTER TABLE (ADD) column to work, if I can just figure out how to bypass/ignore situations where the date column already exists.

[June7]

I think would have to use TableDefs to check if the field already exists. http://forums.devarticles.com/micros...vba-58559.html

Can also create field: http://office.microsoft.com/en-us/ac...080204714.aspx