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.

 

[lameid]

Make a table for the layout you want.

Write 5 append queries (or for however many sets of data you have) to append to the correct layout.

Ideally the information should be in this format to start with (data normalization rules). I don't know from your post if this is something in your control or if the data comes from outside of Access.

 

[PWise]

Select ACCT# sm1,seq1,1 as dow
from tablename
union
Select ACCT# sm2,seq2,2 As dow
from tablename
union
Select ACCT# sm3,seq3,3 as dow
from tablename
union
Select ACCT# sm4,seq4,4 As dow
from tablename
union
Select ACCT# sm5,seq5,5 As dow
from tablename

 

[lameid]

Pwise's solution is the other way to go.

However you mentioned a large table... Large table means slower queries. Union queries like Pwise's solution mean slower performance as well. Slow + Slow = REALLY SLOW.

My solution will be faster if you are going to use the query for anything other than viewing / exporting (i.e. grouping in a report). It will also force you to rebuild the table if there are any changes and eat up a lot of disk space. My hunch is my solution is the right one for you but you can choose your devil

 

[adrianvasile]

The problem with this is that the data it will keep adding up. I will need information for reporting purposes only.

 

[adrianvasile]

I cannot modify the table. It is linked to our main AS400 Mainframe.
I wish I could. I only need the query for reporting purposes.

 

[lameid]

A different solution would be to have one of the programmers for the AS400 write a program to dump the table to a text file in the correct layout. Although the fastest solution is likely the queries I already metntioned.

Going out on a limb, maybe you can use the table in it's current layout to report what you want to know... I'd avoid it but it may be the lesser of the evils.

 

[PWise]

change to

Select ACCT# sm1,seq1,1 as dow
from tablename
Where sm1>0
union
Select ACCT# sm2,seq2,2 As dow
from tablename
Where sm2>0
union
Select ACCT# sm3,seq3,3 as dow
from tablename
Where sm3>0
union
Select ACCT# sm4,seq4,4 As dow
from tablename
Where sm4>0
union
Select ACCT# sm5,seq5,5 As dow
from tablename
Where sm5>0

 

[adrianvasile]

Quick question: what is "dow"?

 

[PWise]

dayofweek

 

[PWise]

in your example there now way of knowing what day of week the record refers to

12345 85 110
12345 85 140

 

[lameid]

Just in case -
dow in this context also follows AS which means it is aliasing what came before it. Aliases rename or name fields/columns for display/output.

 

[adrianvasile]

OK. Let me try it.

 

[adrianvasile]

sm field is a text. How will query against that?

 

[PWise]

should work because
"1">0 = true

 

[lameid]

The where is there to keep you from having blanks...
You might use similar criteria on SEQ1.

or...

Where sm1>0

Could also become

Where sm1 <> ""

Pwise, in a Union query I'm pretty sure he is bombing on a syntax error since there is no QBE to fix it to "0"

 

[adrianvasile]

I will try it and let you know.
thank you for your help guys

 

[PWise]

it can be fixed in sql view

 

[lameid]

should work because
"1">0 = true

I'm saying it won't autocorrect in SQL view...

If you used the QBE and made something that would yield your orignal where clause it would change it to the following...

Where sm1>"0"

Otherwise it is a syntax error when it executes. I agree in VBA the comparison would work fine.

 

[dhookom]

Actually, UNION ALL should be much faster than UNION. It is

Select [ACCT#], sm1 As SM ,seq1 As Seq,1 as dow
from tablename
Where sm1 & "" <> ""
UNION ALL
Select [ACCT#], sm2, seq2, 2
from tablename
Where sm2 & "" <> ""
UNION ALL
Select [ACCT#], sm3, seq3, 3
from tablename
Where sm3 & "" <> ""
UNION ALL
Select [ACCT#], sm4, seq4, 4
from tablename
Where sm4 & "" <> ""
UNION ALL
Select [ACCT#], sm5, seq5, 5
from tablename
Where sm5 & "" <> ""

The syntax may vary depending on the actual value in "blank" sm fields.

Duane
Hook'D on Access
MS Access MVP