special query

[SusannaW]

Hi NG

I need to create a query wich shows me several fields of one record in
several records - in access97.

Well, here's an example:

ok, that's my table:

UniqeNumber Item1 Item2 Item3
123456789 ABC AFF GRR
987654321 EFF ABC ELB
And my query should look like this:
UniqeNumber Item
123456789 ABC
123456789 AFF
123456789 GRR
987654321 EFF
987654321 ABC
987654321 ELB
I tried different ways with creating an own field and entered in the
expression: "item: [item1] or [item2] or [item3]" --> This only shows
me that there is a value in one of those fields. The same with "and",
here it shows me that there is a value in all fields. I even trief to
put the records in several tables and put them together - but that
don't work too.

Do you have any Ideas how to solve this? I can't change the table
definition, because it's an already full working software.

Thanks a lot for your help

susanna

[PC Datasheet]

Susanna,

You need to change your table to this:

TblItems
ItemID
ItemDesc
ItemNum

"Item1", "Item2" and :Item3" go in ItemDesc and UniqueNumber goes in ItemNum.

Your problem then goes away and creting the query you want is a piece of cake!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.com
www.pcdatasheet.com

"SusannaW" <su******@gmx.n et> wrote in message
news:1f******** *************** **@posting.goog le.com...

Hi NG

I need to create a query wich shows me several fields of one record in
several records - in access97.

Well, here's an example:

ok, that's my table:

UniqeNumber Item1 Item2 Item3
123456789 ABC AFF GRR
987654321 EFF ABC ELB
And my query should look like this:
UniqeNumber Item
123456789 ABC
123456789 AFF
123456789 GRR
987654321 EFF
987654321 ABC
987654321 ELB
I tried different ways with creating an own field and entered in the
expression: "item: [item1] or [item2] or [item3]" --> This only shows
me that there is a value in one of those fields. The same with "and",
here it shows me that there is a value in all fields. I even trief to
put the records in several tables and put them together - but that
don't work too.

Do you have any Ideas how to solve this? I can't change the table
definition, because it's an already full working software.

Thanks a lot for your help

susanna

[Steve Heath]

"SusannaW" <su******@gmx.n et> wrote in message
news:1f******** *************** **@posting.goog le.com...

Hi NG

I need to create a query wich shows me several fields of one record in
several records - in access97.

Well, here's an example:

ok, that's my table:

UniqeNumber Item1 Item2 Item3
123456789 ABC AFF GRR
987654321 EFF ABC ELB
And my query should look like this:
UniqeNumber Item
123456789 ABC
123456789 AFF
123456789 GRR
987654321 EFF
987654321 ABC
987654321 ELB
I tried different ways with creating an own field and entered in the
expression: "item: [item1] or [item2] or [item3]" --> This only shows
me that there is a value in one of those fields. The same with "and",
here it shows me that there is a value in all fields. I even trief to
put the records in several tables and put them together - but that
don't work too.

Do you have any Ideas how to solve this? I can't change the table
definition, because it's an already full working software.

Thanks a lot for your help

You should really change the table, but if you can't you can use a union
query. A union query can't be built in design view... you must use SQL
view.

Where the table name is "Items" the union query would look like:

SELECT Items.UniqeNumb er, Items.Item1 AS ItemNo
FROM Items;

UNION SELECT Items.UniqeNumb er, Items.Item2 AS ItemNo
FROM Items;

UNION SELECT Items.UniqeNumb er, Items.Item3 AS ItemNo
FROM Items;

--
Steve Heath

[SusannaW]

Hi Steve
Thanks a lot, UNION SELECT worked very well.... :o) Excactly as I needed!
Greets
Susanna

"Steve Heath" <st************ ***@sbcglobal.n et> wrote in message news:<de******* ************@ne wssvr31.news.pr odigy.com>...

You should really change the table, but if you can't you can use a union
query. A union query can't be built in design view... you must use SQL
view.

Where the table name is "Items" the union query would look like:

SELECT Items.UniqeNumb er, Items.Item1 AS ItemNo
FROM Items;

UNION SELECT Items.UniqeNumb er, Items.Item2 AS ItemNo
FROM Items;

UNION SELECT Items.UniqeNumb er, Items.Item3 AS ItemNo
FROM Items;