Go Back   Access World Forums > Microsoft Access Reference > Access FAQs

Closed Thread
Thread Tools Rate Thread Display Modes
Old 03-03-2010, 04:55 PM   #1
Join Date: Jun 2007
Location: Universe - Local Group - Milky Way Galaxy - Orion Arm
Posts: 5,640
Thanks: 0
Thanked 98 Times in 45 Posts
ajetrumpet has a spectacular aura about ajetrumpet has a spectacular aura about
Send a message via MSN to ajetrumpet Send a message via Yahoo to ajetrumpet
Vertical data manipulation in queries

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...

ajetrumpet is offline  
The Following User Says Thank You to ajetrumpet For This Useful Post:
hassanogaibi (06-28-2016)
Closed Thread

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Introduction to VBA Programming Banana Access FAQs 0 10-30-2008 06:11 PM
create a progress bar in VB JohnLee Modules & VBA 3 01-08-2008 07:29 AM
How to show data on a Form from 3 queries atol Forms 9 10-31-2007 09:55 AM
data access forms and queries firestorm Queries 0 04-13-2006 06:45 AM
How to display data from 3 queries in a form? gmchale Forms 1 08-10-2005 10:34 AM

All times are GMT -8. The time now is 07:31 PM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post

Sponsored Links

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World