Normalizing existing data 1

[MasterRacker]

I'm trying to fix an ad-hoc database that's completely flat. I have a single table with this column structure:
Item, Category, Supplier, 01Date, 01Supplier, 01Invoice, 01QuantityIn, 01UnitCost, 01Department, 01QuantyityOut, 02Date, 02Supplier, 02Invoice, 02QuantityIn, 02UnitCost, 02Department, 02QuantyityOut, ... on out to 48xxx <runs screaming...>

The numbered groups represent inventory transactions. I've created an actual database with tables for Products, Suppliers, Categories, Transactions, etc.

I'm having trouble wrapping my mind around how to query the existing data into the new system. I can do SELECT DISTINCT to get Products, Categories, etc. My problem is how do I flatten the transactions.

The only thing I can thing of is

INSERT INTO Dest.Transactions (Product, Supplier, Invoice, Date, Dept, UnitsRecd, UnitsDisbursed...)
VALUES
Src.Product, Src.01Supplier, Src.01Invoice, Src.01Date, Src.01Dept, Src.01QuantityIn....

I would then have to keep modifying the numbers on the fields and re-running the query. There has to be a better way.

Keep in mind I know I need to do more work to get to ID's etc. The above is just illustrative.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[lameid]

Yes you should use an insert / append query that SELECTS the values from the other table.

The bad news is you need to run 48 different queries.

The good news is you can write VBA code to increment the number portion and concatenate an SQL string together to run through a loop.

Fix up a working example SQL statement and post back if you need help with the implementation of the above.

 

[MasterRacker]

OK, here's another one. Since I have 01Department, 02Department, 03Department, ... How do I get distinct Departments? Do I have to brute force query them out into a single column in a temp table them select distinct on that?

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[RoyVidar]

If I understand you correct, you could use a union query, i e

[tt]SELECT 01Department FROM TheTable
UNION
SELECT 02Department FROM TheTable
UNION
...[/tt]

Using UNION vs UNION ALL will give you distinct occurences.

Roy-Vidar

 

[MasterRacker]

RoyVidar - that got me what I needed for Departments.

Just have to wade through the transactions now.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[MasterRacker]

I ended up writing the single transaction query then doing a lot of search/replace/cut/paste/run...

Seriously manual process but it only had to be done once, so not worth the effort of dumping it into vba.

Thanks all.

(Can hardly wait for the next little ad-hoc, non-normalized DB to come along... ;-) )

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[lameid]

To each his own... Being experienced with VBA I would find it faster. I'd say the threshhold would be around 10-15 for manual search replace vs. VBA.

Also I would use a unique index with my version and let duplicate records from appends fail.

 

[MasterRacker]

I'm sure you're faster with VBA than I am. ;-)

I dind't have any issues with duplicates.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]