SQL code: modifying a query to have multiple column headings

[charlescdean]

Hello,

I am new to Microsoft Access 2010 and have recently learnt how to create queries, crosstab queries and basic SQL coding.

However I am trying to convert the data from this format:

Country_name	Major_product	Main_product	Species_group	Species	Sub_product	Value ($)
Latvia	Wood	Logs	Softwood	Other	Sawlogs	0
Honduras	Wood	Logs	Softwood	Other	Sawlogs	0
France	Wood	Logs	Softwood	Other	Sawlogs	0
Belgium	Wood	Logs	Softwood	Other	Sawlogs	0
Germany	Wood	Logs	Softwood	Other	Sawlogs	0
Finland	Wood	Logs	Softwood	Pine	O/T sawlogs	7700
Sweden	Wood	Logs	Softwood	Pine	O/T sawlogs	6611
Ireland	Wood	Logs	Softwood	Pine	O/T sawlogs	1972
Latvia	Wood	Logs	Softwood	Pine	O/T sawlogs	848
France	Wood	Logs	Softwood	Pine	O/T sawlogs	49

Into the following format:

Major_product	Wood
Main_product	Logs
Species_group	Softwood
Species	Other	Pine
Sub_product	Sawlogs	O/T sawlogs
Country_name
Latvia	0	848
France	0	49
Honduras	0
Belgium	0
Germany	0
Finland		7700
Sweden		6611
Ireland		1972

Is this possible? I have ~ 4000 entries so it would take too long to organise this manually. I have quite a lot of programming experience, but am relatively new to Access / SQL. I imagine this wouldn't be possible using the simple query wizard. I expect it will take some SQL code - I would be very grateful if anyone might know how to perform this operation and are willing to share this information.

[ranman256]

Just build a few 'make table' queries.
That 1st one would be
select DISTINCT Major_product,main, spec_grp, species, subprod INTO tCata from table

key the fields needed.
then do the same for other tables to be made.

[charlescdean]

Thankyou for the reply - I've figured out how to build 'make table' queries.

but I'm going to need you to help me with the coding if that's okay.

What is tCata?

Can you provide more details on the exact syntax, as it will take me forever to figure this out on my own (if ever!)

Thanks,

[charlescdean]

I tried what you suggested but got the

"Query input must contain at least one table or query"

error message.

Tried different code but can't get past this message.

[ranman256]

you didnt supply table names, so I made 'em up.

qry error: put a table in the query above the grid.

[charlescdean]

above which grid?

when i put in the code you suggest with the table name (table name is 'multiple_headings') I get:

"Syntax error in FROM clause."

Any ideas what I'm doing wrong?

[ranman256]

The query grid. Send a picture of your query design in the query grid.

[charlescdean]

Here is the screenshot:


I'm not a dilbert in real life - just never used access before and trying to meet a deadline! Your help will be much appreciated.

[ranman256]

It may not like your naming....use letters not characters >,<!@#$%

[June7]

Good point ranman, should avoid spaces and special characters/punctuation (underscore is only exception) in naming convention, even though Access will accept them they can cause frustration. If they are used, make sure to enclose names in [] in queries and code. Access will try but sometimes will be confused by special characters and fail to add in the [].

Consider instead of making tables, building a report using Grouping and Sorting features with aggregate calcs in footer section.