Weird SQL needed

[Amy]

Hello,

In a week or so I will be given an Excel spreedsheet with a list of
names invited to a dinner, there is nothign I can do about the format of the
spread sheet, it's been like that for years, I can nto get then to change -
beleive me I've tried. Anyway, from this listing I'm going to have to
produce place names (mail merged to a work document), an alphabetical table
plan and a by table table plan.

Each line in the spread sheet is in the formate:
POSSITION LINE NAME1 NAME2 SURNAME NUMBER

With "Line" being "Mr & Mrs Joe Bloggs" - this is what will be on the table
plan lists (ordered by 'surname'), Name1 being Joe and name2 being joe's
partner X, surname Bloggs of course.

The number column lists the number of people invited in that row - i.e.
inthis example, 2

Most rows are in this formate, so fit into my database ok (I know, two
people in one record, bad but I really and truly havn't got the time or
energy to sort it out!)

Then you get the odd rows - like mine, i'm going on my own, so my line reads

POSSITION LINE NAME1 NAME2 SURNAME NUMBER
Sponser Miss Amy Kimber Amy (blank) Kimber
1

Now I can cope with most thigns, but I want a list of everyone who's going
with the name and surname on....

So, something like:
SELECT name1, Surname,
FROM tblNames
And If Number = 2 then Select name2, Surname (as well)

Is that possible at all?

Amy K

So, table looks a bit like this:

POSSITION LINE NAME1 NAME2 SURNAME
NUMBER
Sponser Miss Amy Kimber Amy (blank)
Kimber 1
Chairman Mr & Mrs Jim Kimber Jim Kate
Kimber 2

 

[GVaught]

Try this

SELECT name1, Surname FROM tblCARDS
UNION ALL SELECT name2, Surname FROM tblCARDS
WHERE name2 IS NOT NULL;

This will give you this result:
name1 Surname
Amy Kimber
Jim Kimber
Kate Kimber

 

[david epsom dot com dot au]

SELECT name1, Surname, FROM tblNames
union all
SELECT name2, Surname, FROM tblNames where number = 2;

(david)

 

[John Vinson]

Each line in the spread sheet is in the formate:
POSSITION LINE NAME1 NAME2 SURNAME NUMBER
...
Now I can cope with most thigns, but I want a list of everyone who's going
with the name and surname on....

So, something like:
SELECT name1, Surname,
FROM tblNames
And If Number = 2 then Select name2, Surname (as well)

Sure. You can use the IIF() function to do this:

SELECT Name1, Surname, IIF(Number = 2, Name2, NULL), IIF(Number = 2,
Surname, NULL) FROM yourtable;