Changing a table (1 Viewer)

[Peter Bellamy]

I have been working on a copy of a database to introduce some new forms and data fields. The major data change is an increase of 10 more fields to one particular table.

I now want to implement the change to this table, ie put the data from the live version of the table, which has more records in now than the snapshot I have been working on, into the new table structure.

What would be the best way to do this, bearing in mind new records have to made. The table has about 60 fields in it so using the visual tools if posible!

Cheers

 

[bparkinson]

First off, a table with 60 columns is suspicious, and likely a candidate for redesign in order to normalize it. Want to post the table design?

 

[Dairy Farmer]

If you want the keep the table as is and ignor bparkinson the is a "down and dirty" option.

Shut the database down.
Export the current table to Excel (tab1)
Export the new data to Excel (tab2)

Using vlookup, if and exact append the 10 colums to the first tab.

In Excel
To get data for first new field
=IF(EXACT(A1,(VLOOKUP(A1,Tab2,1)))=TRUE,IF((VLOOKUP(A1,Tab2,2))=0,"",(VLOOKUP(A1,Tab2,2))),"")

Note: "Tab2" in the code above is a named range
Data in Tab2 must be sorted by the first column assending
The first record in Tab2 must be <= the lowest record in Tab1

Once all 10 fields are added, replace data in the table by copy and paste.

 

[Peter Bellamy]

Silly question, how do I post the table design?

 

[vbaInet]

If you want the keep the table as is and ignor bparkinson the is a "down and dirty" option.

Why would Peter want to ignore bparkinson? What he said would have been my exact response.

 

[vbaInet]

Silly question, how do I post the table design?

Take a screenshot of the Relationships window for example.

OR

Write them all down here indicating PKs, FKs and data types

OR

Upload a db with your tables in it and some sample data.

 

[Galaxiom]

Why would Peter want to ignore bparkinson? What he said would have been my exact response.

Mine too.

Continuing on with a badly designed data structure just makes ever increasing complexity and unnecessary work.

Moreover why would anyone use a clumsy Excel method to synchronise the tables? Has Dairy Farmer never heard of an update query?

 

[Dairy Farmer]

I use the Excel method only on testing or once off cases.

I didn't mean that normilising the database should not be done. Sorry if my responce came off the wrong way.

 

[Peter Bellamy]

Thanks for your help, everyone.

The Table, one of many, is allready normalised, if avoiding duplicate data in other tables, is what you mean.
The only simplification that could be made is to split it, but one of the results of that would be 41 fields anyway so I am not sure it helps me with my updating.

Snapshot of the table design attached.
There are another 30 [andrews_component?] lines which make up the rest of the table, all with the same text structure

Cheers

 

[MSAccessRookie]

Normalization is more than just making sure that no two Tables Contain Fields for the same data. It can also refer to avoiding duplicated Fields in the same Table. For Instance, if you have a Table that has twelve sets of six Fields with Monthly related information (one for each month). While this approach might work on a spreadsheet, it should be normalized for a database to use an additional Table of Monthly related information. When you do this, Each original record will create 12 new ones, and the number of Columns will go from 72 to 7.

NOTE: In your example, you refer to fields named AndrewsComponent1 through AndrewsComponent11 (there may be more, but the picture stops at 11). These Fields are possibly subject to normalization in the way described above, especially if adding more of them is the reason that you need to modify the Table.

 

[Dairy Farmer]

Looking at the pic I would definatly split using something like the Invoice (something common but unique):

Table1
Invoice No
Date

Table2
Invoice No
Tester

Table3
Invoice No
Code for part number

And so on

 

[vbaInet]

Looking at the pic I would definatly split using something like the Invoice (something common but unique):

Table1
Invoice No
Date

Table2
Invoice No
Tester

Table3
Invoice No
Code for part number

And so on

That's not a normalized structure. You've just pretty much denormalized.

The table needs a complete overhaul and the business logic needs to be understood. It requires a lot of time investment (which I don't have now) so maybe bparkinson might have the time to go through the normalization steps with the OP.

 

[Dairy Farmer]

I thought that Normalization meant dividing large tables into smaller tables and defining a relationship between them.

Well if you take this example:
Everything related to the invoice is in one table (InvNo, Date, Salesman...)
Everything related to testing is in another table (InvNo, Tester, Date Tested,..)
Parts required for the invoice are in a third table (InvNo, product, parts 1 - xx)

Each table have data specific to a group. All three tables are linked via the invoice number. Something common, but "unique". Commom because groups of data can be related to it. "Unique" because there is only one invoice number.

 

[vbaInet]

It's hard to infer from the screenshot provided. It doesn't give much to go on.

@Peter Bellamy: It looks like the below is the better option:

Upload a db with your tables in it and some sample data.

You also need to explain what the table is for.

 

[Peter Bellamy]

The db is for a manufacturer and records all the salient details of a product and its major components. This gives the Company traceability of who, what and when.
The tables are in a Back End db with no forms, queries reports etc.
I have zipped the whole db as is, easier and quicker for me than shrinking.
Please treat as confidential and destroy when this thread closes.
It contains some copy tables that are there for safety, they are not used by the Front End and can be ignored.

Allthough I am willing to 'tidy up' this db using the good advice you all give, I am doing this for the Company as a favour and unpaid, so if it will mean days of work then it is a non-starter!

Thanks

 

[vbaInet]

Yep, the entire db will need to be re-written, i.e.

* drawing out and mapping your tables and their relationships
* transforming the data into the new structure
* importing them whilst still maintaining PKs
* changing all the forms and reports to fit this new structure

Not a few day's job.

 

[bparkinson]

I looked at the Andrews CWH table, and this is pretty easy to sort out. The business object looks like what I've seen when a product is built from components, and called an assembly. The CWH table has poor logical and physical design, and it will take more than a couple of days to refactor it.

Bunches of data need to be moved to their own tables. New tables include: Customer, ProductType, Person (for Tester), Part, PartLink, and Return.

That said, I think these people need to hire a professional developer. This is an amount of work that I would not do for free for a commercial enterprise. Frankly, the design of this database makes me wonder what other data horrors exist at this company. That this design made it into production means they don't have anyone with DBA skills in their IT department, at least not logical and physical DB design skills.

 

[spikepl]

#17 A tough judgement, hehe. Another possible explanation (out of many) is that they DO have an IT-department with all kinds of skills, especially the skill of saying "We are busy"

So someone obviously got fed up with Excel, plugged the lot into Access as-is, the application became business-critical, the IT then said "we'll have nothing to do with this" ... and here we are

Since the company survived using unnormalized data structure, then they'll probably also survive the addition of 10 more fields to the table. And since the content of Andrews CWH does not seem to link to anything else, then that should be a straightforward operation - unless some other tables have records linking to Andrews CWH ( I have not looked inside the DB)

So to answer the question posed in #1:

1. Backup you production backend.
2. From development backend, copy table Andrews CWH (just structure) into the production backend, call it AndrewsNew
3. Open AndrewsNew in the production backend in design view, select all the new field definitions, CTRL C , close that table
4. Open the Andrews CWH in the production backend in design view, paste the field definitions at the end.
5. You are done, as far as the production backend is concerned.

 

[Peter Bellamy]

Thanks one and all, especially spikepl.
The Company is a small family run manufacturer with no IT department and no money to spend on external IT specialists (they tell me).
The db was written by a previous product development manager who could see a need and knew a bit of Access and put it together.
Fortunately the db is small and any of its shortcomings have not effected it working or its success in providing what they wanted.

I will try the cut and paste suggestion and once it is working quietly walk away

 

[Galaxiom]

I thought that Normalization meant dividing large tables into smaller tables and defining a relationship between them.

Well if you take this example:
Everything related to the invoice is in one table (InvNo, Date, Salesman...)
Everything related to testing is in another table (InvNo, Tester, Date Tested,..)
Parts required for the invoice are in a third table (InvNo, product, parts 1 - xx)

No. You need to go right back to the beginning on normalization as you are completely on the wrong track. Using multiple fields such as "parts 1 - xx" is a classic symptom of wrong structure.

In this example [Parts] should be a separate table with records for 1 to xx.

Testing would only be another table if there were multiple tests applied to an invoice. It there is a one to one relationship between records in tables there is no point separating them into different tables.