How to query from non normalized data

[jrg24]

I previously posted asking for help and I think I may have been asking the wrong question.
http://www.mrexcel.com/forum/micros...oning-access-data-storage-query-question.html

In this link I was essentially asking how to replicate what I was doing in Excel. I realize now that this may not be the best practice. So without further Ado, here is my updated question:

I want to build a database to help with my month end procedures. I work for a domestic freight company. This database will consist of one table that contains all the information related to our moves (BusinessActivity). This table includes unique invoice numbers, customers, cost amounts, invoice amount, invoice dates, ship dates, post dates, shipper, consignee, sales person, sales location, etc. All told the table is about 180 columns long and several hundred thousand rows (but these can be broken up into date segments if need be). The table has a ton of null values. I am using a linked csv file for BusinessActivity. The remainder of the tables will be reference tables that will be static for the most part. BusinessActivity should be the only table that will have data that will be updated on a regular basis.

My first goal with this database is to identify each individual cost associated with the invoices in the file. This link provides an example of what the source data looks like. I have excluded a bunch of columns for size sake. The link below is just to give the general idea of what I am working with. The link shows the data as it is displayed on the table and it also shows what I have been doing excel for my monthly analysis.

https://docs.google.com/file/d/0B52Q...JmeGhVSTg/edit


I was able to get my data into the "manipulated data" form shown on the first link with the following code:

SELECT [BusinessActivity].[Pro #] [COLOR=blue]As[/COLOR] PRO, [BusinessActivity].[Proj Exp 1] [COLOR=blue]As[/COLOR] Carrier,[COLOR=blue]CCur[/COLOR]([BusinessActivity].[Proj Amt 1]) [COLOR=blue]As[/COLOR] Amount, [BusinessActivity].[Type 1] [COLOR=blue]As[/COLOR] Type, [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Ship Date]) [COLOR=blue]As[/COLOR] [Ship Date], [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Invoice Date]) [COLOR=blue]As[/COLOR] [Invoice Date], [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Posted Info]) [COLOR=blue]As[/COLOR] [Post Date], "Exp1" FROM [BusinessActivity] WHERE ([Proj amt 1] <> Null) 

UNION ALL 

SELECT [BusinessActivity].[Pro #] [COLOR=blue]As[/COLOR] PRO, [BusinessActivity].[Proj Exp 2] [COLOR=blue]As[/COLOR] Carrier,[COLOR=blue]CCur[/COLOR]([BusinessActivity].[Proj Amt 2]) [COLOR=blue]As[/COLOR] Amount, [BusinessActivity].[Type 2] [COLOR=blue]As[/COLOR] Type, [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Ship Date]) [COLOR=blue]As[/COLOR] [Ship Date], [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Invoice Date]) [COLOR=blue]As[/COLOR] [Invoice Date], [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Posted Info]) [COLOR=blue]As[/COLOR] [Post Date], "Exp2" FROM [BusinessActivity] WHERE ([Proj amt 2] <> Null) 

UNION ALL 

SELECT [BusinessActivity].[Pro #] [COLOR=blue]As[/COLOR] PRO, [BusinessActivity].[Proj Exp 3] [COLOR=blue]As[/COLOR] Carrier, [COLOR=blue]CCur[/COLOR]([BusinessActivity].[Proj Amt 3]) [COLOR=blue]As[/COLOR] Amount, [BusinessActivity].[Type 3] [COLOR=blue]As[/COLOR] Type, [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Ship Date]) [COLOR=blue]As[/COLOR] [Ship Date], [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Invoice Date]) [COLOR=blue]As[/COLOR] [Invoice Date], [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Posted Info]) [COLOR=blue]As[/COLOR] [Post Date], "Exp3" FROM [BusinessActivity] WHERE ([Proj amt 3] <> Null) 

UNION ALL 

SELECT [BusinessActivity].[Pro #] [COLOR=blue]As[/COLOR] PRO, [BusinessActivity].[Proj Exp 4] [COLOR=blue]As[/COLOR] Carrier, [COLOR=blue]CCur[/COLOR]([BusinessActivity].[Proj Amt 4]) [COLOR=blue]As[/COLOR] Amount, [BusinessActivity].[Type 4] [COLOR=blue]As[/COLOR] Type, [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Ship Date]) [COLOR=blue]As[/COLOR] [Ship Date], [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Invoice Date]) [COLOR=blue]As[/COLOR] [Invoice Date], [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Posted Info]) [COLOR=blue]As[/COLOR] [Post Date], "Exp4" FROM [BusinessActivity] WHERE ([Proj amt 4] <> Null) 

UNION ALL 

SELECT [BusinessActivity].[Pro #] [COLOR=blue]As[/COLOR] PRO, [BusinessActivity].[Proj Exp 5] [COLOR=blue]As[/COLOR] Carrier, [COLOR=blue]CCur[/COLOR]([BusinessActivity].[Proj Amt 5]) [COLOR=blue]As[/COLOR] Amount, [BusinessActivity].[Type 5] [COLOR=blue]As[/COLOR] Type, [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Ship Date]) [COLOR=blue]As[/COLOR] [Ship Date], [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Invoice Date]) [COLOR=blue]As[/COLOR] [Invoice Date], [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Posted Info]) [COLOR=blue]As[/COLOR] [Post Date], "Exp5" FROM [BusinessActivity] WHERE ([Proj amt 5] <> Null) 

UNION ALL 

SELECT [BusinessActivity].[Pro #] [COLOR=blue]As[/COLOR] PRO, [BusinessActivity].[Proj Exp 6] [COLOR=blue]As[/COLOR] Carrier, [COLOR=blue]CCur[/COLOR]([BusinessActivity].[Proj Amt 6]) [COLOR=blue]As[/COLOR] Amount, [BusinessActivity].[Type 6] [COLOR=blue]As[/COLOR] Type, [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Ship Date]) [COLOR=blue]As[/COLOR] [Ship Date], [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Invoice Date]) [COLOR=blue]As[/COLOR] [Invoice Date], [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Posted Info]) [COLOR=blue]As[/COLOR] [Post Date], "Exp6" FROM [BusinessActivity] WHERE ([Proj amt 6] <> Null) 

UNION ALL 

SELECT [BusinessActivity].[Pro #] [COLOR=blue]As[/COLOR] PRO, [BusinessActivity].[Proj Exp 7] [COLOR=blue]As[/COLOR] Carrier, [COLOR=blue]CCur[/COLOR]([BusinessActivity].[Proj Amt 7]) [COLOR=blue]As[/COLOR] Amount, [BusinessActivity].[Type 7] [COLOR=blue]As[/COLOR] Type, [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Ship Date]) [COLOR=blue]As[/COLOR] [Ship Date], [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Invoice Date]) [COLOR=blue]As[/COLOR] [Invoice Date], [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Posted Info]) [COLOR=blue]As[/COLOR] [Post Date], "Exp7" FROM [BusinessActivity] WHERE ([Proj amt 7] <> Null) 

UNION ALL 

SELECT [BusinessActivity].[Pro #] [COLOR=blue]As[/COLOR] PRO, [BusinessActivity].[Proj Exp 8] [COLOR=blue]As[/COLOR] Carrier, [COLOR=blue]CCur[/COLOR]([BusinessActivity].[Proj Amt 8]) [COLOR=blue]As[/COLOR] Amount, [BusinessActivity].[Type 8] [COLOR=blue]As[/COLOR] Type, [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Ship Date]) [COLOR=blue]As[/COLOR] [Ship Date], [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Invoice Date]) [COLOR=blue]As[/COLOR] [Invoice Date], [COLOR=blue]CDate[/COLOR]([BusinessActivity].[Posted Info]) [COLOR=blue]As[/COLOR] [Post Date], "Exp8" FROM [BusinessActivity] WHERE ([Proj amt 8] <> Null);

I am not sure if this code is good practice or not. The sample data I am using only contains 19,000 original records. Doing this union creates just over 32k records. I feel like the query is slow and Access stutters for a bit and shows it is not responding before catching up. This seems wrong for such a relatively small data set. Should I really be using UNION ALL to combine data from the same table? Also, my first attempt at this I created eight separate tables (one for each Proj Exp column). This seemed to run faster than using the code above without any of the stuttering issues.

I want to compare each Proj Exp to another table (Carrier List) that contains all possible Proj Exp and the Cost Location of that Proj Exp. At the end of the day I want to be able to see total Amount of Proj Exp for each Cost Location. What would be the best way of going about this? Also, If anyone can recommend any good books on the subject I would be open to doing some further research.

I am sure this is confusing to read so I apologize in advance. Thanks for your help.

 

[xenou]

For better performance one trick is to append your data to a temp table. First create a table (the temp table) that has the right fields that match to the queries above. Then append the records from each of your queries above into it. Next time, you just need to clear this table and then you can use it again the same way. Or possibly you can maintain it as a "data warehouse" and each week or month append new records to keep it up to date.

Also possible is to normalize your table so you don't have to write queries for non-normalized data (let's, say, if you really want to get crazy here).

Also, make sure your tables are indexed appropriately on foreign keys and any fields used in JOIN clauses or WHERE clauses. That can be a big help in some cases.