First - Last

[Guest]

My query is pulling 2 fields, Item and code. For each item I can have
multiple codes, i.e. C1, C2, C3 and so on. When running a query for Item A I
get back 4 lines becuase I have 4 different codes (C1, C2, C3, & C4). If I
write my query using first and last I can return 1 line with Item, C1, and
C4. Is there a way I can return all codes associated with the item in 1 line
instead of 4? Like is there a second, and third function like the first and
last? This way my result would be:

ItemA C1 C2 C3 C4 instead of

ItemA C1
ItemA C2
ItemA C3
ItemA C4

Thanks,

 

[Guest]

Hi,

First thing is watch out for First and Last. They are about worthless unless
you sort your records by something like a date and time field. Min and Max
are often better choices.

To get the output required, you need a crosstab query. First create a query
to get just the fields and records that you want. Next go to Query, New and
build a crosstab based on the other query that you just built. Don't be
surprized if it takes a few tries to get what you want. Crosstab's are a
little complicated.

 

[John Nurick]

Hi Sean,

If a crosstab query doesn't do what you want, go to
http://www.mvps.org/access and search for "concatenate".

 

[Michel Walsh]

Hi,


Sorting could eventually occur AFTER the aggregation, so technically, First
and Last are not returning necessary what can be thought, EVEN if there is
an ORDER BY clause. First and Fast return the values from the first/last
record seen in the GROUP (after the join and the where clause has been
applied). They are quite useful if you wish to pump data from one record or
the group. By comparison,

SELECT f1, MAX(f2), MAX(f3) FROM mytable GROUP BY f1


may be that MAX(f2) is from a different record than the one from MAX(f3) is
taken.


SELECT f1, LAST(f2), LAST(f3) FROM mytable GROUP BY f1



would give you values LAST(f2) and LAST(f3) from the same record.



Hoping it may help
Vanderghast, Access MVP