Microsoft Access - Reducing 3 columns to 1 with indexing

Asked By Jason on 29-Sep-11 04:53 PM
This might be an easy question but I've tried researching the answer with no success. What I am trying to do is take 3 columns and turn them into 1 by creating a second table.  For example, lets say there are 4 unique values in each of the 3 columns in the first table.  I want to create a second table that includes all of the combinations of these values (4x4x4=64 rows) and assign a primary key to this table.  Then reinsert this primary key into the original table to replace the existing 3 columns and thus reduce them to 1.  I will be adding data constantly that will contain the original 3 columns but I don't want this data imported or at least stored anywhere in the table.  The whole reason I'm doing this is that I'm trying to reduce the space of my database. I'll have millions of rows and the database does not hold as much of a time frame as I would like it. Reducing the number of columns would greatly reduce my database size... especially these text columns.  Your help is greatly appreciated as always.

Thanks,
Jason
pete rainbow replied to Jason on 29-Sep-11 09:05 PM
create lookup table with your 3 columns and pri key col XXXcol as an identity column

then insert you unique data from the main table

SELECT distinct col1, col2 col3
INTO LookupTable
from FirstTable

then you'll have your lookup, then just a case of replacing your 3 columns in the orig table

add a new foreign key lookup column in FirstTable XXXcol

update FirstTable ft set ft.XXXcol = lt.XXXcol
join LookupTable lt on ft.col1 = lt.col1 and ft.col2 = ft.col2 and lt.col3 = ft.col3

then i guess drop the 3 columns after checking all ok and maybe drop nullable etc?
Jason replied to pete rainbow on 30-Sep-11 11:44 AM
I am struggling with the lookup part.  I've tried using the lookup column wizard but it only inserts a column with a combo box where you select the values. 

"add a new foreign key lookup column in FirstTable XXXcol"


Also I'm not sure what to do with this?  Is this an update query?

"update FirstTable ft set ft.XXXcol = lt.XXXcol
join LookupTable lt on ft.col1 = lt.col1 and ft.col2 = ft.col2 and lt.col3 = ft.col3"

I tried entering that in the rowsource of the lookup and it gives me a syntax error and highlights the word join.
pete rainbow replied to Jason on 30-Sep-11 03:53 PM
sorry posted sql syntax

think access will be something like

update FirstTable ft
inner join LookupTable lt on ft.col1 = lt.col1 and ft.col2 = ft.col2 and lt.col3 = ft.col3"

set ft.XXXcol = lt.XXXcol
Jason replied to pete rainbow on 03-Oct-11 01:16 PM
Ok so far I am able to use that update query to insert the values I want.  However I will be adding data to this table everyday.  Is there anyway to avoid running this update query everyday?  I have a lot of rows so this will take some time if I need to run an update query all the time.
pete rainbow replied to Jason on 03-Oct-11 04:15 PM
you should insert at the same time then? which would be a slight mod of the sql?
Jason replied to pete rainbow on 03-Oct-11 10:44 PM
Yes adding the values at the same time would be ideal.  I'm not sure how to modify the sql code to do that though.
pete rainbow replied to Jason on 04-Oct-11 06:04 AM
post up what you are currently doing in the insert and also the other update

and i'll try and give a hand
Jason replied to pete rainbow on 04-Oct-11 03:48 PM
So right now i have a lookup column in my "first table" and i'm using the following update query to insert values into that lookup column.  If I'm being to vague let me know.

UPDATE [IBS Actuals] AS ia INNER JOIN DeliveryBrandAccount AS bd ON (ia.Account = bd.Field3) AND (ia.Brand_Generic = bd.Field2) AND (ia.[Delivery System] = bd.Mail) SET ia.BrandID = bd.Brandcode;
pete rainbow replied to Jason on 04-Oct-11 06:09 PM
INSERT INTO  [IBS Actuals] ( col1, col1 , ...)
SELECT l.col1 , other static data cols? FROM
    lookuptable l
INNER JOIN blah blah

is the kind of thing, but not sure how you are inserting, ie whre the data is coming from


Jason replied to pete rainbow on 06-Oct-11 09:54 AM
O I apologize, I wasn't sure what you meant. I'm importing text files through a macro.  The text files are just simply stored on a network drive and I use a macro and preset importation settings to bring in the text files.  Specifically this is the command I use: DoCmd.TransferText acImportFixed,

So I'm guessing I would need a macro to run the current code i have in place that imports the data and then also run a sql code to insert the values I need?  What I'm confused about is how the program will know what values to insert if I don't import the three columns (which is the ultimate goal)
Jason replied to pete rainbow on 20-Oct-11 11:14 AM
Any Ideas?