I've seen too many of these threads, so let me try a short explanation of why Access is not built to manipulate data vertically, from record to record...
A database is a 3 dimensional creature, and thus the need for manipulating and analyzing data vertically within one datasheet should be eliminated by the use of multiple datasheets and relationships between them. The most common question I think that comes up is how to create a cumulative running total in a query. If it is set up correctly, Microsoft has a solution article for this: http://support.microsoft.com/kb/290136
There really should never be another reason to need vertical analysis like this, but if there is, I would suggest using Excel as your program. As a flat file program, you can analyze in all directions.
If you absolutely have to analyze vertically in an access query, you will more than likely need a function call to do it. This can fairly easy if you are querying a table, but if you are trying to query another query, it gets more complicated and sometimes cannot even be done. If you need to refer to "previous" rows or "future" rows in the same query for analysis, this is virtually impossible unless you utilize one of the techniques outlined in the above article.
Here's an example of a question someone posted here recently:
I have the following table:
order no product
I need a query that will give me this:
1 apple, orange, grape
2 apple banana
First of all, let me say that I don't see any reason why there is a need for this, but in reality there was. And the data obviously has to be manipulated in a vertical manner here. This is solution I gave the person:
SELECT DISTINCT [order no],
concatproducts([order no]) AS [Products] FROM table
function concatproducts(ordernum as long) as string
dim mystring as string
dim rs as recordset
set rs = currentdb.openrecordset("table", dbopendynaset)
do until .eof
if ![order no] = ordernum then
mystring = mystring & !product & ", "
mystring = left(mystring, (len(mystring) - 2))
concatproducts = mystring
This is generally how you will have to manipulate your data vertically if you intend on doing it with an Access query. There aren't many more options available...