Add a column to the table with a query (1 Viewer)

[dz2k7]

Hi there,

I have a big table bringing Access up to almost 2G limit.
I need to add a column to that table using a query.
I can’t make another table because of the volume.

I have 3 columns like

Company Division Branch
W X P

I need to add the column having

Company Division Branch BranchCode
W X P WXP

Thank you.

 

[Guus2005]

Why not use a query all together i.s.o. an extra column?

HTH

 

[Bilbo_Baggins_Esq]

Firstly, unless this is an older version of Access, I don't think your limit of 20M is accurate.
If I remember correctly, even Access 97 hada a limit of 100MB, but my memory may be flawed on the specifics, but I am reasonably sure it was higher than 20MB.

Regardless of that, you do not add columns to a table with a query.
You can populate EXISTING columns in a table with an UPDATE query, but you physically add the columns to the table by opening the table in design view.

Once you have the columns added to the table, you can then populate the new columns by building appropriate UPDATE queries.
NOTE: You must use UPDATEABLE QUERIES to populate these columns. In other words, the queries you use can not be, or sources from CROSSTAB, GROUP BY, or include other summary calculations.

All this being said, if in fact you're bumping up against a size ceiling, you're risking corrupting the entire table if you try to trick the database into adding data via adding columns instead of a seperate table.

I would seriously consider breaking table up based on some criteria relevant to your data.
You can easily store parts of your data in seperate databases, and then LINK to the tables from another database.
For example, break your table up based on yearly or quarterly data.
Name these housing databases something like 2007_Quarter_1, 2007_Quarter_2, etc.
Then, in a totally seperate database, link to the tables in the other databases and work with the data from there.

Alternately, since that can be somewhat of a burden (although managable), I do know that Access 2003 has a 2GB ceiling. That might allow you room to grow for quite some time before you start reaching the ceiling.

 

[dz2k7]

I ment 2 GB of course
The thing is that that table is been downloaded from AS400 every night.
The developed table is gonna be destroyed.
So is it possible to add a blank column with a macro or something?

 

[Bilbo_Baggins_Esq]

You probably can using VBA, but I doubt it with MACROS.

Is the intent to have the AS400 populate the new column (if added) or will it need to be populated AFTER the AS400 downloads?

Regardless, your database is only going to keep getting larger and larger regardless of the question of adding a column or not.

It would seem to me that your business may have outgrown Access and should consider SQL Server or some larger capacity equilivant.

For now, to add the column, and continue to allow the AS400 to work with the existing database, here is what I would look at.

Create a new database

In the new database, create a new table that has all the existing columns, PLUS the new column. (there are numerous easy methods of doing this).
Probably the easiest would be to create a copy of the original, IN THE ORIGINAL database, but copy the STRUCTURE ONLY. Then drag the copy of the table from the original db to the new db, then open it in design view and add the new column.

Add a TABLE LINK to the new database to the table in the original database.
Create a query that ascertains the MAX_ID contained in the new table of the NEW DATABASE.
Then, write an append query that appends from the existing database, AND populates the added column all at once. Add the query that ascertains the MAX ID value, and set the criteria in the ID column to be greater than the MAX ID in that query.
Once the new table in the new database is populated I would verify it's integrity, and then I would go an clean out the table in the original database.

Still though, this is only a band aid.
This may buy you some time, but the new database is also going to to be bouncing off the 2GB ceiling.
Unless your company looks at higher capacity databases, you'll have to repeat this process every time the main table of the original database starts to approach the 2GB ceiling.
Depending on how rapidly that is, you could end up with numerous storage databases whereas if using Microsoft SQL server (et al), the capacity will not be an issue.

 

[Guus2005]

Do you really have millions of records?
Compact your database.

Yes, you can use a query to add a column. Using a make table query.
However, i would use VBA to create the query and execute is. I never use macro's.

 

[neileg]

Your original posting hints that the branch code is a compound of Company, Division & Branch. If this is the case, why do you want to store this anyway?

 

[dz2k7]

Yep I've got about 2.5 mil of records, 60 columns each.
I manage inventory of international structure
The only thing I wanted is to create a 3 digit Branch code automatically.

Thank you all for your answers.
That was helpful
I added a column and got it done.

 

[The_Doc_Man]

I think you have a serious problem that you are flat ignoring. The access size limit is going to bite your butt soon. Plan NOW to cover your butt so you avoid unsightly tooth marks.

Strategy one: Move up to SQL server. Holds more records.

Strategy two: Identify something that you can split out from this DB to make another DB file, then link to TWO MDB files. That makes your limit 4 Gb (2 each).

Strategy three: Since you didn't show us your actual schema, I can only guess whether this is even possible - or whether it is already done. I would look at your company, division, branch stuff to see if you could SEPARATELY encode those guys and link back to tables that correspond to each. I.e. instead of storing "Ontario" branch and "Wyoming" branch and "Kuala Lumpur" branch, store codes to lookup tables. Over a span of 2 million records, this might save you 20 - 50 Mb depending on the sizes of the fields. But applying this principle elsewhere might save a lot more than that. On the other hand, from your description it is not clear whether you had already done this.

 

[dz2k7]

Well I have SQL 2005 installed on my PC
I told to my IT that I'm a gooroo in it.
The problem is that I don't know a thing about it.
I can't even link to existing table.
Or to import it into SQL Server.
Everything is so unclear there.
I tried some books.
There is lots of stuff, but I can't find what I need.
May be I started from the wrong side of the problem.
Sorry for complaining.

 

[neileg]

May be I started from the wrong side of the problem.

That's what I was thinking!

We can help, but you have to help us to help you.

 

[dz2k7]

How I can help you?

 

[Bilbo_Baggins_Esq]

I told to my IT that I'm a gooroo in it.
The problem is that I don't know a thing about it.

sigh...


The old database analysis addage "garbage in = garbage out"

I think what neileg is saying that in order to be of the most assistance to you, you need to share with us what your real objectives are.
We spent time, and you spent time trying to work with a database that is really not intended to do what you clearly need, and we suggested that you use SQL server, and it turns out that you have SQL server, but don't know how to use it for this task.

I am sure there are those here who can help with SQL Server, but you need to let us know that is what you need...

Hence:
"you have to help us to help you..."