473,505 Members | 14,950 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to Reverse a Crosstab-Query

58 New Member
Hi all!
I have a table in this format:
Expand|Select|Wrap|Line Numbers
  1. FirstName  LastName  Field1 Field2  Field3
  2. JJ         Ku          3      2       1
  3. Lee        Moah        2      5       3
  4. Mr         John        7      2       7
  5.  
I want this table to be like this
Expand|Select|Wrap|Line Numbers
  1. FirstName  LastName  FieldNames   Values
  2. JJ         Ku        Field1          3
  3. JJ         Ku        Field2          2
  4. JJ         Ku        Field3          1
  5. Lee        Moah      Field1          2 
  6. Lee        Moah      Field2          5
  7. Lee        Moah      Field3          3                   
  8. Mr         John      Field1          7 
  9. Mr         John      Field2          2
  10. Mr         John      Field3          7
  11.  
Please help!!
Mar 19 '11 #1
4 7749
Rabbit
12,516 Recognized Expert Moderator MVP
Access doesn't have an unpivot but you could use a union query.
Mar 20 '11 #2
NeoPa
32,557 Recognized Expert Moderator MVP
You probably want to use a UNION connector to join the same table to itself a number of times :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [FirstName]
  2.        , [LastName]
  3.        , 'Field1' AS [FieldNames]
  4.        , [Field1] AS [Values]
  5. FROM     [YourTable]
  6. UNION ALL
  7. SELECT   [FirstName]
  8.        , [LastName]
  9.        , 'Field2' AS [FieldNames]
  10.        , [Field2] AS [Values]
  11. FROM     [YourTable]
  12. UNION ALL
  13. SELECT   [FirstName]
  14.        , [LastName]
  15.        , 'Field3' AS [FieldNames]
  16.        , [Field3] AS [Values]
  17. FROM     [YourTable]
  18. ORDER BY [FirstName]
  19.        , [LastName]
  20.        , [FieldNames]
Mar 20 '11 #3
Moah Scout
58 New Member
wAOOOOO!!!
I couldnt imagine if this one were possible.
God has created you for us!
Finally I could solve my problem thanks again.
Thanks alot Neopa.
Mar 20 '11 #4
NeoPa
32,557 Recognized Expert Moderator MVP
No problem Moah :-)
Mar 20 '11 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

35
3701
by: Raymond Hettinger | last post by:
Here is a discussion draft of a potential PEP. The ideas grew out of the discussion on pep-284. Comments are invited. Dart throwing is optional. Raymond Hettinger ...
59
4242
by: Raymond Hettinger | last post by:
Please comment on the new PEP for reverse iteration methods. Basically, the idea looks like this: for i in xrange(10).iter_backwards(): # 9,8,7,6,5,4,3,2,1,0 <do something with i> The...
3
2048
by: engsolnom | last post by:
I need to use the built-in method 'reverse', but notice strange behavior. Foe example: print my_list.reverse() doesn't work. new_list = my_list.reverse() doesn't work. my_list.reverse()...
8
3623
by: Jim Langston | last post by:
I have a class I designed that stores text chat in a std::vector<sd::string>. This class has a few methods to retrieve these strings to be displayed on the screen. void ResetRead( bool Reverse,...
14
14715
by: ford_desperado | last post by:
Why isn't ALLOW REVERSE SCANS the default? Why do we have to - drop PK - create an index - recreate PK What are the advantages of indexes that do not allow reverse scans?
15
5041
by: Fady Anwar | last post by:
Hi while browsing the net i noticed that there is sites publishing some software that claim that it can decompile .net applications i didn't bleave it in fact but after trying it i was surprised...
20
19749
by: mike7411 | last post by:
Is there any easy way to reverse the order of the bits in a byte in C++? (i.e. 00000001 becomes 10000000)
41
3336
by: rick | last post by:
Why can't Python have a reverse() function/method like Ruby? Python: x = 'a_string' # Reverse the string print x Ruby: x = 'a_string' # Reverse the string
15
6243
by: rajash | last post by:
Thanks for the additional comments. Here is a solution to an exercise I had problems with. I still don't think it's really what's wanted as it uses a "state variable" n - but I can't see how to...
1
10743
by: sunnyluthra1 | last post by:
Hi, I was creating an Application in MS Access for Geocoding a particular Address from Google to get the Lat & Long. I successfully able to did that. Here is the code:...
0
7098
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
7367
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
7471
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
1
5028
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...
0
4699
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
0
3187
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...
0
3176
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
754
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
407
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.