Thread: Extract contents of table columns

Hi Alan,

Sorry, I don't know what normalized means? I have an excel table that has months across the top as column headers, and style numbers down the left side as row headers, and then the inside is all the units we are forecasting for sales.

I need to combine multiple excel tables and do various calculations, like calculate dollar values using wholesale prices for each style.

My approach is to import that excel tables into Access where I can combine them easier. I also need to do a bunch of calculations. Like add some forecasts together,but subtract forecasts for units that are returned in each style/month. So I thought if I can get a spreadsheet that has two years of forecast months (24 columns) into a list that is very long but just has 3 columns (style, monthyear, units) it will be easier to do my calculations.

Otherwise I have to create multiple columns to calculate the dollar value of each month's units. So a column for January dollars (Jan units x price), then a column for Feb dollars (Feb units x price)....... If I have this simple list, it's just one column (monthyear units x price). Then I can pivot table to get things back with months across the top row.

Does this help you understand what I am trying to do?

It seems from my research so far it will be a complicated program to convert the tables to this 3 column format. Maybe I should just give up and import all my forecast tables into Access the way they are and figure out how to work with it there?

I'm a basic Excel user, good with pivot tables and can write very simple macros, but that's it so far.

Thanks, Amy

Hi Alan, I just found that you had already replied with a solution. I will try it and see how it works. Somehow I had read a post where you had asked me a question before I found this response with your file. Sorry, I'm very new to threads and posts and things, but kind of fun and MANY THANKS for such a prompt response! Amy

Hi Alan,

I am so excited! Thank you so much for your help. I downloaded your access file and it worked perfectly. I have never used the Excel Help Forum (or any help forum) before and can't believe I was able to find a solution to my problem between when I left work last night and this morning when I arrived. So awesome!

Thank you! Amy

Alan,

I've worked with you access solution and figured out how to add a query for another month (April). I can even make the data in the month column say "1/1/2011" instead of Jan (so I can sort better later).

What I can't figure out is how to adapt the solution to my real database which actually has field headers that are dates in stead of Jan, Feb, Mar. My field headers are 1/1/2011, 2/1/2011, 3/1/2011. I can create the individual queries, but I can't modify the UNION code.

SELECT Table1.Style, "1/1/2010" AS [Month], Table1.Jan AS Units
FROM Table1;

I'm having trouble with the last half of it - when I change Table1.Jan to Table1.1/1/2010 then I get an error message about having incorrect symbols when I try to run it.

Would you mind helping me adapt the UNION query to work with a table that has dates for field headers? I created one and tried to upload it but it said invalid file, and believe it or not, I don't know how to zip it to be smaller if that is the problem.

Anyway, this would be very helpful. I've spent about 45 minutes so far and just can't figure it out.

Thanks, Amy

Actually I think I have successfully zipped the file, where I added Table2 with my dates as field headers. I hope this makes it easier for you to respond. THANKS! Amy

Okay, thanks. What is your recommendation for the format of the field names so they can be sorted? I have two years of data, so I can't just use 1, 2,3, it needs to be 1_2010, 2_2011, etc or something. My goal is for the fields to be sortable in pivot tables so the reports come out with the proper month order.

I just discovered that my 1/1/10 and 2/1/10 field names don't sort properly for some reason, so I definitely need an alternative.

Thanks!

Hi Alan,

I finally figured out that my field headings can be text names like Jan10, Feb10 and when I do the query that combines the monthly files, I can make the data in the month column be whatever I want. When I use 01-11 and 02-11 there, the result will sort properly in a pivot table.

So I'm down to what I hope is a final question. You mentioned I should take the query that combines the months and make a table. If this query was a regular query, I know how to do it. But it seems that this query is a SQL query, and I don't know how to make it generate a table. Any clues? Thanks, Amy

Hey again. I think I'm in good shape. I just don't know how to make a table out of the results of the UNION query. I can export it to excel and then import it again, but I'm sure there is a better way.

I used the help function and it explained how to do it for a regular query, but I think this UNION query is a SQL thing, so when I open in design view I do not see an option to make table....

Thanks for all of your help. I am well on my way with my project thanks to you. This final thing would be awesome. Amy

Thanks, that worked perfectly! I'm all set. Have a great day. Amy