special query

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

re: special query

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
resource@pcdatasheet.com
www.pcdatasheet.com



"SusannaW" <susannaw@gmx.net> wrote in message
news:1f179e7.0405070746.14de3003@posting.google.co m...[color=blue]
> 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[/color]

re: special query

"SusannaW" <susannaw@gmx.net> wrote in message
news:1f179e7.0405070746.14de3003@posting.google.co m...[color=blue]
> 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
>[/color]

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.UniqeNumber, Items.Item1 AS ItemNo
FROM Items;

UNION SELECT Items.UniqeNumber, Items.Item2 AS ItemNo
FROM Items;

UNION SELECT Items.UniqeNumber, Items.Item3 AS ItemNo
FROM Items;

--
Steve Heath

re: special query

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

"Steve Heath" <steve.heathNOSPAM@sbcglobal.net> wrote in message news:<dePmc.15055$xu2.14605@newssvr31.news.prodigy .com>...[color=blue]
>
> 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.UniqeNumber, Items.Item1 AS ItemNo
> FROM Items;
>
> UNION SELECT Items.UniqeNumber, Items.Item2 AS ItemNo
> FROM Items;
>
> UNION SELECT Items.UniqeNumber, Items.Item3 AS ItemNo
> FROM Items;[/color]