Query Help!

A

alex

Hello,

Using Access ’03…

I have a query that results in the following:

E.g.,

When UserID Field1 Field2 Field3 Field4 Field5
Feb 09 rsmith E
Feb 09 rsmith A B C D

It’s a union query, so I think I have to run another query; which is
fine. How can I construct another query that will produce:

When UserID Field1 Field2 Field3 Field4 Field5
Feb 09 rsmith A B C D E

I’ve tried a simple group by; did not work.
I’ve tried outer and self joins; did not work.
This seems like it would be simple…!

Thanks,
alex
 
D

Danny J. Lesandrini

It's not completely clear what might end up being the case in all circumstances,
but if one assumes that each Field (2 through 5) will appear only in one row,
a Group By query will work, this way.

Assume your Union query is named qryMyUnionQuery

SELECT [When], [UserID],
Max(Nz([Field2],"")) AS F2,
Max(Nz([Field3],"")) AS F3,
Max(Nz([Field4],"")) AS F4,
Max(Nz([Field5],"")) AS F5
FROM qryMyUnionQuery
GROUP BY [When], [UserID]
ORDER BY [When], [UserID]

This will give you squirrely results if data appears in both rows for a given column,
but it's a start.
--
Danny J. Lesandrini
[email protected]
www.amazecreations.com


Hello,

Using Access ’03…

I have a query that results in the following:

E.g.,

When UserID Field1 Field2 Field3 Field4 Field5
Feb 09 rsmith E
Feb 09 rsmith A B C D

It’s a union query, so I think I have to run another query; which is
fine. How can I construct another query that will produce:

When UserID Field1 Field2 Field3 Field4 Field5
Feb 09 rsmith A B C D E

I’ve tried a simple group by; did not work.
I’ve tried outer and self joins; did not work.
This seems like it would be simple…!

Thanks,
alex
 
A

alex

It's not completely clear what might end up being the case in all circumstances,
but if one assumes that each Field (2 through 5)  will appear only in one row,
a Group By query will work, this way.

Assume your Union query is named qryMyUnionQuery

SELECT [When], [UserID],
             Max(Nz([Field2],"")) AS F2,
             Max(Nz([Field3],"")) AS F3,
             Max(Nz([Field4],"")) AS F4,
             Max(Nz([Field5],"")) AS F5
FROM qryMyUnionQuery
GROUP BY [When], [UserID]
ORDER BY [When], [UserID]

This will give you squirrely results if data appears in both rows for a given column,
but it's a start.
--
Danny J. Lesandrini
[email protected]

Hello,

Using Access ’03…

I have a query that results in the following:

E.g.,

When UserID Field1 Field2 Field3 Field4 Field5
Feb 09 rsmith E
Feb 09 rsmith A B C D

It’s a union query, so I think I have to run another query; which is
fine.  How can I construct another query that will produce:

When UserID Field1 Field2 Field3 Field4 Field5
Feb 09 rsmith A B C D E

I’ve tried a simple group by; did not work.
I’ve tried outer and self joins; did not work.
This seems like it would be simple…!

Thanks,
alex
Click to expand...

That worked Danny! I didn't think about tricking it like
that...thanks again.
alex
 
Top