Vertical data manipulation in queries (1 Viewer)

Status
Not open for further replies.

ajetrumpet

Banned
Local time
Today, 10:38
Joined
Jun 22, 2007
Messages
5,638
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:
Code:
order no     product
1            apple
1            orange
1            grape
2            apple
2            banana
3            apple
I need a query that will give me this:
Code:
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:
Code:
SELECT DISTINCT [order no],

concatproducts([order no]) AS [Products] FROM table
Code:
function concatproducts(ordernum as long) as string

dim mystring as string
dim rs as recordset
set rs = currentdb.openrecordset("table", dbopendynaset)

with rs
   .movefirst
      do until .eof
         if ![order no] = ordernum then
            mystring = mystring & !product & ", "
         end if
               .movenext
      loop
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...
 
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom