• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Vertical data manipulation in queries (1 Viewer)

Not open for further replies.


Local time
Today, 08:08
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: 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
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