Query to return field with multiple values 1

[adrianvasile]

I had this problem for months now and I cannot figure it out. Basically I have a huge table that has many fields and each field has 2 - 3 values that I am interested; each account will have its account number displayed and the Salesman that will visit the account on a different day:
ACCT# SM1 SEQ1 SM2 SEQ2 SM3 SEQ3 SM4 SEQ4 SM5 SEQ4
12345 85 110 85 140

Basically this tells me that this account will be visited on day 2 (SM2) and day 4 (SM4) of the week.
I will like to be able to run a querry that will list each account every time it has a value; in this case it will look like:
12345 85 110
12345 85 140

Any ideea will be appreciated.
Thank you.

 

[adrianvasile]

What will it be the table name? My access database or my AS400 system?

 

[PHV]

Why not access directly the AS400 data ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[adrianvasile]

Sure. I guess I can try

 

[adrianvasile]

No. I can't - AS400 has the data in a raw format and that is why I needed the union query the first time ( we are loosing the toughs here ). I will need to prepare the data first using some queries (MS Access) and then use Excel

 

[PHV]

You may use an UNION query in the SQL view of MS-Query ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[adrianvasile]

OK. I think I figure it out. I have to use single NOT DOUBLE quotes. It works now except that it stops during the update process with "Code has been interrupted" error message.
There is some VBA code in the play so I'll check that out.

 

[adrianvasile]

It only does it with Office 07 .... it works in 03

 

[PHV]

Be sure to set the BackgroundQuery property of the QueryTable to False.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[adrianvasile]

i did it ; it has to do with an escape key somewhere in the code

 

[adrianvasile]

I got everything to work. Thank you all for your help.