Vertical data manipulation in queries (1 Viewer)

Not open for further replies.


Local time
Today, 09:35
Jun 22, 2007
OK folks,

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:

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
1            apple
1            orange
1            grape
2            apple
2            banana
3            apple
I need a query that will give me this:
1            apple, orange, grape
2            apple banana
3            apple
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:

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)

with rs
      do until .eof
         if ![order no] = ordernum then
            mystring = mystring & !product & ", "
         end if
end with

mystring = left(mystring, (len(mystring) - 2))

concatproducts = mystring

end function
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...
Not open for further replies.

Users who are viewing this thread

Top Bottom