MySQL-Syntax with 'if' in Access-SQL not permitted?!

  • Thread starter Thread starter Bernd Muent
  • Start date Start date
B

Bernd Muent

Hi togehter,
I have a data table like this:

ID LANG TERM
12345 DEU Haus
12345 ENG House
12345 FRA Maison

12346 ENG Sun

12347 DEU Mensch
12347 FRA Homme
12347 ESP Hombre

So, there are groups of corresponding translations in various languages.

I would like to create a new table like this:

ID DEU ENG ESP FRA
12345 Haus House Maison
12346 Sun
12347 Mensch Hombre Homme

Somebody gave me the hint for MySQL:
select ID, max(if(LANG='DEU',TERM,'')) as DEU, max(if
(LANG='ENG',TERM,'')) as ENG, max(if (LANG='ESP',TERM,'')) as ESP,max(if
(LANG='FRA',TERM,'')) as FRA from TERMS group by ID

And it is exactly doing what I like.
But when I try to execute this SQL statement in Access, there is the
error message: "Undefined function 'if' in SQL statement".

I have got no idea how to change this SQL so that it is working in Access.

Thank you for tips, Bernd
 
Hi Bernd,

"if" used in an Access query is referred
to as the "immediate if"..
so use "IIF" instead of "if"

Actually, I might use a crosstab query
in Access to do what you want.

Start a simple select query bringing
all of your fields down into the grid.

Then, under "Query" in the top menu,
click on "Crosstab"

The grid will change to something like
(if your table name were "tblTranslation")

--(ID Column)

Field: ID
Table: tblTranslation
Total: Group By
Crosstab:

change to:

Field: ID
Table: tblTranslation
Total: Group By
Crosstab: Row Heading

--(TERM Column)

Field: TERM
Table: tblTranslation
Total: Group By
Crosstab:

change to:

Field: TERM
Table: tblTranslation
Total: Max
Crosstab: Value

------------
Your SQL would look like:

TRANSFORM Max(tblTranslation.TERM) AS MaxOfTERM
SELECT tblTranslation.ID
FROM tblTranslation
GROUP BY tblTranslation.ID
PIVOT tblTranslation.LANG;

Please respond back if I have misunderstood.

Good luck,

Gary Walter
 
Somewhere in cut-and-paste, left out

--(LANG Column)

Field: LANG
Table: tblTranslation
Total: Group By
Crosstab:

change to:

Field: LANG
Table: tblTranslation
Total: Group By
Crosstab: Column Heading

If you select this column,
right-mouse click on it and
choose "Properties"

there is a property row called
"Column Headings" where you
can "hard code" all the column
headings you want (or leave out
what you do not want), and in the
order you want them to appear.

"FRA","DEU","ENG","ESP"

TRANSFORM Max(tblTranslation.TERM) AS MaxOfTERM
SELECT tblTranslation.ID
FROM tblTranslation
GROUP BY tblTranslation.ID
PIVOT tblTranslation.LANG In ("FRA","DEU","ENG","ESP");
 
Gary said:
Your SQL would look like:

TRANSFORM Max(tblTranslation.TERM) AS MaxOfTERM
SELECT tblTranslation.ID
FROM tblTranslation
GROUP BY tblTranslation.ID
PIVOT tblTranslation.LANG;

Please respond back if I have misunderstood.
Click to expand...

Thx a lot. This was exacty what I was looking for. B.
 
Back
Top