Transitioning to Access for data storage- query question

[jrg24]

Currently, I am using excel to store my data, manipulate it and display the results. Due to the size of the files I am working with (several hundred thousand rows and growing), this is creating some problems with my computer resources. I want to transition to access for storage and then import queries into Excel to reduce the strain on my system and to reduce the need to work with multiple files at once and not to mention the large potential for errors. For all intents and purposes I am brand new to access besides a couple of classes I took in college over five years ago. I feel comfortable with tables and relationships, but I am a little shaky on queries.

In the link, I show a simplified breakdown of what I currently do in Excel in order to get a simple pivot table. I take Data stored in a horizontal format and put it in a vertical format so I can make a nice, neat pivot table. In Access via a query, can I get the equivalent of the excel pivot table showed in my example? As a jump off point for research, what kind of query should I be looking at?

As I said, this is just a simplified version of the source data. The real source data is 180 columns wide and hundreds of thousands of rows long. For this query I imagine I would need about thirty of these rows (24 of them relate to projected expenses, a few more relate to the location, invoice number, mileage,etc.) I will then take this data a step further and assign a cost location to each item in "Proj Exp" along with an additional type (employee, vendor, contractor, etc.). These items will be stored on a separate table.

The whole point of this exercise is to compare data from our sales system to data from our accounting system to see if the proper amount of cost is being protected on a MTD basis. Thanks for your help.

https://drive.google.com/file/d/0B52Q5LyTFEcwOTRXVXJmeGhVSTg/edit?usp=sharing

 

[alansidman]

If you have your data in a normalized table, then you can run a select query and pivot table directly in Access or you can export your query to Excel and run a pivot from there. The key will be having your data in a normalized state (as shown in your modified example.) For an explanation of normalized state, (in case you have forgotten), look at this link.

http://sbuweb.tcu.edu/bjones/20263/Access/AC101_FundamentalsDB_Design.pdf

 

[jrg24]

Thanks for the reply. So I need to normalize the data. I figured I would need to do that. My thought would be to create a table query for each "Proj Exp" where I would include "Inv #", "Proj Exp", "Proj Amt", and "Type". I would then join these tables together to create one normalized list. Doing it this way would require 8 different tables for each expense. Is this an acceptable way of achieving normalization? What would be the best way to go about this?

 

[xenou]

I don't see any need for eight tables. If the first data table is normalized then you are already in good shape to query that table for the data you need.

 

[jrg24]

I don't see any need for eight tables. If the first data table is normalized then you are already in good shape to query that table for the data you need.

After I saw your reply I gave it a shot and was able to achieve my desired result without creating the 8 tables. my query is as follows. Any feedback on the way it is written would be helpful.

SELECT [Pro #] AS PRO, [Proj Exp 1] AS Carrier, [Proj Amt 1] AS Amount , [Type 1] As Type, [Ship Date],[Invoice Date],[Posted Info], "Exp 1" AS [Expense Line]  FROM 3P_Activity WHERE ([Proj Amt 1] <> NULL)
UNION ALL
SELECT [Pro #] AS PRO, [Proj Exp 2] AS Carrier, [Proj Amt 2] AS Amount, [Type 2]  As Type, [Ship Date], [Invoice Date], [Posted Info], "Exp 2" AS [Expense Line]  FROM 3P_Activity WHERE ([Proj Amt 2]<> NULL)
UNION ALL
SELECT [Pro #] AS PRO,[Proj Exp 3]AS Carrier, [Proj Amt 3] AS Amount, [Type 3]  As Type, [Ship Date], [Invoice Date], [Posted Info], "Exp 3" AS [Expense Line]  FROM 3P_Activity WHERE ([Proj Amt 3] <> NULL)
UNION ALL
SELECT [Pro #] AS PRO, [Proj Exp 4] AS Carrier, [Proj Amt 4] AS Amount, [Type 4]  As Type, [Ship Date], [Invoice Date], [Posted Info], "Exp 4" AS [Expense Line] FROM 3P_Activity WHERE ([Proj Amt 4] <> NULL)
UNION ALL
SELECT [Pro #] AS PRO, [Proj Exp 5] AS Carrier, [Proj Amt 5] AS Amount, [Type 5]  As Type, [Ship Date], [Invoice Date], [Posted Info], "Exp 5" AS [Expense Line] FROM 3P_Activity WHERE ([Proj Amt 5] <> NULL)
UNION ALL
SELECT [Pro #] AS PRO, [Proj Exp 6] AS Carrier, [Proj Amt 6] AS Amount, [Type 6]  As Type, [Ship Date], [Invoice Date], [Posted Info],"Exp 6" AS [Expense Line] FROM 3P_Activity WHERE ([Proj Amt 6] <>NULL)
UNION ALL
SELECT [Pro #] AS PRO, [Proj Exp 7] AS Carrier, [Proj Amt 7] AS Amount, [Type 7]  As Type, [Ship Date], [Invoice Date], [Posted Info], "Exp 7" AS [Expense Line] FROM 3P_Activity WHERE ([Proj Amt 7] <> NULL)
UNION ALL 
SELECT [Pro #] AS PRO, [Proj Exp 8] AS Carrier, [Proj Amt 8] AS Amount, [Type 8]  As Type, [Ship Date], [Invoice Date], [Posted Info], "Exp 8" AS [Expense Line] FROM 3P_Activity WHERE ([Proj Amt 8] <> NULL);

Now that I have what appears to be a table that contains the data as I want it, I want to to a crosstab query and check against my source data to see if my totals match up. The problem is that the "Proj Amt" field is a string, and not numeric. I have been looking for how to convert this string to a numeric format but everything I have seen does not put the code in context with a whole query. In my Query above, how and where should I convert this field to a numeric type?

 

[xenou]

You should be able to use th CCur() function. That will convert to currency data type (i.e., decimal, more or less).

SELECT [Pro #] AS PRO, [Proj Exp 1] AS Carrier, CCur([Proj Amt 1]) AS Amount , [Type 1] As Type, [Ship Date],[Invoice Date],[Posted Info], "Exp 1" AS [Expense Line]  FROM 3P_Activity WHERE ([Proj Amt 1] <> NULL)
UNION ALL
SELECT [Pro #] AS PRO, [Proj Exp 2] AS Carrier, CCur([Proj Amt 2]) AS Amount, [Type 2]  As Type, [Ship Date], [Invoice Date], [Posted Info], "Exp 2" AS [Expense Line]  FROM 3P_Activity WHERE ([Proj Amt 2]<> NULL)
UNION ALL
SELECT [Pro #] AS PRO,[Proj Exp 3]AS Carrier, CCur([Proj Amt 3]) AS Amount, [Type 3]  As Type, [Ship Date], [Invoice Date], [Posted Info], "Exp 3" AS [Expense Line]  FROM 3P_Activity WHERE ([Proj Amt 3] <> NULL)
UNION ALL
SELECT [Pro #] AS PRO, [Proj Exp 4] AS Carrier, CCur([Proj Amt 4]) AS Amount, [Type 4]  As Type, [Ship Date], [Invoice Date], [Posted Info], "Exp 4" AS [Expense Line] FROM 3P_Activity WHERE ([Proj Amt 4] <> NULL)
UNION ALL
SELECT [Pro #] AS PRO, [Proj Exp 5] AS Carrier, CCur([Proj Amt 5]) AS Amount, [Type 5]  As Type, [Ship Date], [Invoice Date], [Posted Info], "Exp 5" AS [Expense Line] FROM 3P_Activity WHERE ([Proj Amt 5] <> NULL)
UNION ALL
SELECT [Pro #] AS PRO, [Proj Exp 6] AS Carrier, CCur([Proj Amt 6]) AS Amount, [Type 6]  As Type, [Ship Date], [Invoice Date], [Posted Info],"Exp 6" AS [Expense Line] FROM 3P_Activity WHERE ([Proj Amt 6] <>NULL)
UNION ALL
SELECT [Pro #] AS PRO, [Proj Exp 7] AS Carrier, CCur([Proj Amt 7]) AS Amount, [Type 7]  As Type, [Ship Date], [Invoice Date], [Posted Info], "Exp 7" AS [Expense Line] FROM 3P_Activity WHERE ([Proj Amt 7] <> NULL)
UNION ALL 
SELECT [Pro #] AS PRO, [Proj Exp 8] AS Carrier, CCur([Proj Amt 8]) AS Amount, [Type 8]  As Type, [Ship Date], [Invoice Date], [Posted Info], "Exp 8" AS [Expense Line] FROM 3P_Activity WHERE ([Proj Amt 8] <> NULL);

Your table is not actually normalized but nice try anyway. If the table was normalized you would not need the UNION's at all.