Query Help!

[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

 

[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
(e-mail address removed)
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

 

[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
(e-mail address removed)

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

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