How do I concatenate / refrence the column name?

[Guest]

Hi,
Im not sure if this is possible in Access, but i got a table set up like
the following:

Name Ford Chevy Dodge
----------------------------------------------
Bill x x
Jhon x
Bob x x
Tom x x

I want to make an expresion in a Query that will find the x, refrence the
column name copy that then concanate if there is more than one type (Ford ,
Chevy , Dodge)

So in the above example i would get a new column that would say for Bill :
Ford/Dodge
and for Jhon it would say:
Chevy
for Bob it would say
Chevy/Dodge

So a field that will look to see where all the x's are then go up take the
Colum name then concatenate if there is more than 1 x for a person.

Thank you for your time, and please let me know if you have a much faster
and efficient way of doing this.

 

[Guest]

Try this --
SELECT CARs.NAME, IIf([FORD]="X","FORD",Null) AS Expr1,
IIf([CHEVY]="X","CHEVY",Null) AS Expr2, IIf([FORD]="X","DODGE",Null) AS
Expr3, [NAME] & IIf([EXPR1] Is Not Null,"/" & [EXPR1],Null) & IIf([EXPR2] Is
Not Null,"/" & [EXPR2],Null) & IIf([EXPR3] Is Not Null,"/" & [EXPR3],Null) AS
Expr4
FROM CARs;

 

[John Vinson]

Hi,
Im not sure if this is possible in Access, but i got a table set up like
the following:

Name Ford Chevy Dodge
----------------------------------------------
Bill x x
Jhon x
Bob x x
Tom x x

Well... this is a badly denormalized table structure. What if you need
to add another model? Do you change your table structure, all your
queries, all your forms, all your reports? A better design would have
three tables, not one:

People
PersonID
LastName
FirstName
<etc>

Models
ModelName

Owners
PersonID
ModelName

with multiple RECORDS (not fields) in the Owners table.

I want to make an expresion in a Query that will find the x, refrence the
column name copy that then concanate if there is more than one type (Ford ,
Chevy , Dodge)

So in the above example i would get a new column that would say for Bill :
Ford/Dodge
and for Jhon it would say:
Chevy
for Bob it would say
Chevy/Dodge

You can get VBA code at http://www.mvps.org/access to do this from the
normalized structure I recommend; search for "Concatenate".

John W. Vinson[MVP]