Access Excel Query Column headers

placebo2000

New Member
Joined
May 8, 2009
Messages
29
I've been looking at the "USDA National Nutrient Database for Standard Reference, release 23"
http://www.ars.usda.gov/Services/docs.htm?docid=8964

And pulling off data into Excel.

I have this query (which can be found in the database sr23.mdb):

Code:
TRANSFORM Avg(NUT_DATA.Nutr_Val) AS AvgOfNutr_Val
SELECT NUT_DATA.NDB_No, FOOD_DES.Long_Desc
FROM FOOD_DES
INNER JOIN NUT_DATA ON FOOD_DES.NDB_No = NUT_DATA.NDB_No
GROUP BY NUT_DATA.NDB_No, FOOD_DES.Long_Desc
PIVOT NUT_DATA.Nutr_No;
This does everything I want to do, except I can't work out how to replace the column headers NUT_DATA.NDB_No with NUTR_DEF.NutrDesc. What it does is produce a list of individual food stuffs and their nutritional content, but with the nutrient code rather than description.

I'd also like to add a food group description column, FD_GROUP.FdGrp_Desc.

I can do this either by using a different query and a pivot table in excel or by using the original query and putting in another row in Excel with INDEX and MATCH, but I don't want to do it that way.

I use Excel and Access 2007.

Any help would be appreciated! Thanks.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
I worked out the solution!:biggrin:

For those that are interested, the following SQL produces the results I wanted:

Code:
TRANSFORM Avg(NUT_DATA.Nutr_Val) AS AvgOfNutr_Val
SELECT NUT_DATA.NDB_No, FOOD_DES.Long_Desc, FD_GROUP.FdGrp_Desc
FROM NUTR_DEF INNER JOIN (FD_GROUP INNER JOIN (FOOD_DES INNER JOIN NUT_DATA ON FOOD_DES.NDB_No = NUT_DATA.NDB_No) ON FD_GROUP.FdGrp_CD = FOOD_DES.FdGrp_Cd) ON NUTR_DEF.Nutr_No = NUT_DATA.Nutr_No
GROUP BY NUT_DATA.NDB_No, FOOD_DES.Long_Desc, FD_GROUP.FdGrp_Desc
PIVOT NUTR_DEF.NutrDesc;
 
Upvote 0

Forum statistics

Threads
1,222,246
Messages
6,164,807
Members
451,917
Latest member
WEB78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top