Modified Trasnpose - Help

Hi,

I have data stored in Table1 as such (Field names on row 1):

Id - Type1 - Type2
123 - 0 - 50
456 - 10 - 100
789 - 50 - 0

And I would like to output it as such (Field names on row 1):

Id - Type - Amount
123 - Type1 - 50
456 - Type1 - 10
456 - Type2 - 100
789 - Type1 - 50

I will have more the 20 Types in my actual table, so I can't just make 
compounded queries.  Any help is appreciated, thanks!
0
Utf
1/19/2010 3:32:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

4 Replies
708 Views

Similar Articles

[PageSpeed] 19

SELECT Id, "Type1" As Type, Type1 As Amount
FROM MyTable
UNION
SELECT Id, "Type2", Type2
FROM MyTable
UNION
SELECT Id, "Type3", Type3
FROM MyTable
UNION
....
UNION
SELECT Id, "Type20", Type20
FROM MyTable

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"stevieb" <stevieb@discussions.microsoft.com> wrote in message 
news:E5909655-26DA-4B27-9CF4-ADD408EF83A3@microsoft.com...
> Hi,
>
> I have data stored in Table1 as such (Field names on row 1):
>
> Id - Type1 - Type2
> 123 - 0 - 50
> 456 - 10 - 100
> 789 - 50 - 0
>
> And I would like to output it as such (Field names on row 1):
>
> Id - Type - Amount
> 123 - Type1 - 50
> 456 - Type1 - 10
> 456 - Type2 - 100
> 789 - Type1 - 50
>
> I will have more the 20 Types in my actual table, so I can't just make
> compounded queries.  Any help is appreciated, thanks! 


0
Douglas
1/19/2010 3:39:22 PM
Hello,

One modification to the table below...instead of Type1, Type2, Typen, I 
really want to show it as 1,2,3..n.

For some reason when I do the compunding queries,  it does not like a number 
as a Field name.

"stevieb" wrote:

> Hi,
> 
> I have data stored in Table1 as such (Field names on row 1):
> 
> Id - Type1 - Type2
> 123 - 0 - 50
> 456 - 10 - 100
> 789 - 50 - 0
> 
> And I would like to output it as such (Field names on row 1):
> 
> Id - Type - Amount
> 123 - Type1 - 50
> 456 - Type1 - 10
> 456 - Type2 - 100
> 789 - Type1 - 50
> 
> I will have more the 20 Types in my actual table, so I can't just make 
> compounded queries.  Any help is appreciated, thanks!
0
Utf
1/19/2010 3:58:02 PM
NVM - just figured it out..I had to put brackets in my unioned queries.. ie. 
[1] as Type.

"stevieb" wrote:

> Hello,
> 
> One modification to the table below...instead of Type1, Type2, Typen, I 
> really want to show it as 1,2,3..n.
> 
> For some reason when I do the compunding queries,  it does not like a number 
> as a Field name.
> 
> "stevieb" wrote:
> 
> > Hi,
> > 
> > I have data stored in Table1 as such (Field names on row 1):
> > 
> > Id - Type1 - Type2
> > 123 - 0 - 50
> > 456 - 10 - 100
> > 789 - 50 - 0
> > 
> > And I would like to output it as such (Field names on row 1):
> > 
> > Id - Type - Amount
> > 123 - Type1 - 50
> > 456 - Type1 - 10
> > 456 - Type2 - 100
> > 789 - Type1 - 50
> > 
> > I will have more the 20 Types in my actual table, so I can't just make 
> > compounded queries.  Any help is appreciated, thanks!
0
Utf
1/19/2010 4:12:01 PM
On Tue, 19 Jan 2010 07:58:02 -0800, stevieb
<stevieb@discussions.microsoft.com> wrote:

>Hello,
>
>One modification to the table below...instead of Type1, Type2, Typen, I 
>really want to show it as 1,2,3..n.
>
>For some reason when I do the compunding queries,  it does not like a number 
>as a Field name.

Neither do I as far as that goes, and your example suggested that the results
were all going into one field! Do you mean that you had data in fields named
[1], [2] etc. to start with, or that you want multiple *fieldnames* (not field
values) in the result?
-- 

             John W. Vinson [MVP]
0
John
1/19/2010 11:34:05 PM
Reply:

Similar Artilces:

Newbi needs a very small help, thank you very much.
Newbi needs a very small help, thank you very much. Hello everyone and thank you very much for your time. I Have a small db for invoicing and on my form (with a subform) there is a CANCEL button which will just cancel what had been selected or entered in the form ... here is the code behind the cancel button... Private Sub Cancel_Click() Me![BillsFormSub].Form.Refresh DoCmd.SetWarnings False DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70 DoCmd.SetWarnings True Exit_Cancel_Click: Exit Sub End Sub This was working ...

Help With Powershell Script.
Hi Folks, Still a novice here so bear with me if this was discussed before. I wrote a small script to kill and restart a custom service that runs on our servers in our network, only to discover, that some of the servers have a 64bit version of the service hat also has a different name. I would like to run one script, detects what service is running on those servers and kills is using pskill from sysinterals (now owned by Microsoft) and then start it. The service name is for now, myservice and the 64bit version name is myservice64 I originally had the script as follows. ...

need help on how to add comment to xml schema with C#
how do you add a comment line/section to an xml schema xsd file? like this <!-- =============================================================== --> <!-- =================== My comment line ======================== --> <!-- =============================================================== --> I like to add a section before each element. Currently, I am creating a large xsd file with many elements. Thanks comic_rage@yahoo.com wrote: > how do you add a comment line/section to an xml schema xsd file? > > like this > > <!-- ==========================...

Help, Pls
C Drive crashed, installed O2002; have a months old .wab address backup, but O2002 won't import or allow additional address book. Is there any way to recover the addys in the backup file into O2002? TIA - wlc ...

Microsoft visualc ++ 5.0
It is stopping all development. I know I can upgrade to version 6 but version 5.0 is fine for me. It really is not good enough for Microsoft to say they are no longer supporting 5.0 and then to kill it off with xp sp2. I am going to have to uninstall sp2 to carry on. Anybody know of a fix for this ? Regards "Terry" <terry@tbean.freeserve.co.uk> wrote in message news:d3dorb$q18$1@news8.svr.pol.co.uk... > It is stopping all development. > > I know I can upgrade to version 6 but version 5.0 is fine for me. > > It really is not good enough for Microsoft ...

Russ Valentine MVP: Can you help with my Microsoft Outlook Contacts folder problem?
Hi Russ, In searching for some help with my problem, I noticed you seemed to be the resident expert on solving problems involving Microsoft Outlook. I am running Windows XP SP2 and using Outlook 2002 V10 Build 4219. I deleted my primary contacts folder (see below for the longer story).* I then generated a second contacts folder and named it "Friends". When I go to the Contacts folder (under my personal folder list) I have series of blank gray bars and the title is "Contacts (blank)-(blank)". If I click on any of these blank gray bars I open up the window showing the spe...

Documenting Changes to Modified Forms
I have inherited some Modified Forms (Forms with VBA are not a problem). I would like to know what changes were made to the Modified Forms from the original Forms. Any suggestions? Is there anything like "Print Definition" in Report Writer that would give me something tangible to compare? With VBA code I can decipher what the code is doing. With Modified Forms, there are so many properties to compare, I am afraid I might miss one. Thanks you for any help you can give Don You could export the form as a package from Customisation Maintenance. Then rename the forms.dic and ...

Can the Business Portal Timesheet Print be modified?
Hi, We need to know if the BP timesheet that is printed from BP can be modified. Any help is welcome. Thank you ...

Help, importing most current data from web query
I have the following code but how do i edit this macro that will allo me to do a web query on the most recent data. For example, I just nee the high, low, close of stock X as of Feb 27th. So on the web query, only need the following data in my sheet: Date Open High LowClose Volume Adj. Close* Feb-26-04 49.7 50.8249.2950.5 3,342,100 50.5 Thanks in advance and any help is appreciated. Sub Stock1() Dim CoSym As String CoSym = Worksheets("Main").Range("C3").Value With Sheets("Stock 1").QueryTables(1) .Connection = _ "URL;http://table.finance.yahoo.com/k?s=...

Help!!! Autofill rows
I have a report that gets updated monthly. Some info comes in the report, the rest I have to put in. I take my original report and copy it to two worksheets within the workbook (Original, Changes, Inventory). On one sheet (Inventory), I can vlookup information from the previous month's report, and it fills it in. Vlookup doesn't work on the other sheet (Changes), even though there are no formatting differences, using same report from previous month. Col a = company number col's b-h need to be filled in identically to all other months based on the company number (column ...

leaderboard style chart??? help!
This is probably an easy question to most. I am hosting a tournament an need to be able to enter all scores and then have it give me 1st, 2n 3rd etc. Can anyone help? I have fill out all the names, can get th sum function to give total but not in order of name and score. Thank -- evanmc ----------------------------------------------------------------------- evanmc7's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2740 View this thread: http://www.excelforum.com/showthread.php?threadid=46919 Can you sort your data by the score column? evanmc7 wrote: > > ...

I'm beginner in powershell.....Neeed your help.
Hello, I wrote my first script in powershell. This script lists all users in a group on a server. It seems easy for the specialist but I think my code is not optimized. Could you help me to improve my code and I need your advice to avoid the gaps. Thanks in adanace for your help. Commande line PS > EnumerateGroupAndUsersV2.ps1 desktop15931 106013 Script fileName =3D EnumerateGroupAndUsersV2.ps1 function get-groupmember([string]$group, [string]$server=3D".", [string] $user ){ if (! $group) { throw "Vous devez pr=E9ciser le nom du groupe =E0 par...

Access Page Help!!!
Hi, I found a few messages about DAP's in this group, but none with info that will help me. I've spent a considerable amount of time searching, but can't seem to find answer and I'm hoping someone here can help. I have a form in a simple database that holds a literature collection with links to pdf files on server. Some users do not have database and I'm trying to set up the data access page for them to use. There will be no user entry, just searches and linking to pdf files. I have a form that I set up with the following code for a command button for each record...

Publisher 2003 now mirrors all documents on the printer. help!
Publisher 2003 has been working properly for several months and now eveything I print is mirrored. All other programs print correctly. Compaq SR1923WM, Windows XP SP2, HP Officejet Pro L7500 series Printer on USB02. Yes, Mirroring is set to Off in Properties. Printing a document at home is normal, so it's not the document and I can set mirroring on & print mirrored if desired. No, I have not reloaded Publisher. Oh by the way, that computer is not Internet connected. There is no Phone line in the 100+ year Old Museum Bldg and no funds for a wireless connection. I would greatly...

help #4
please help me get my money back just download the brochure http://www.ArmchairTycoon.com/bobcam/business dont have to read it, just delete it thanks ...

Add ,Modify data in a flat file
i would like to know how to add delete modify a falt file in VC++(MFC) "CodeMan" <anonymous@discussions.microsoft.com> skrev i en meddelelse news:C246ED01-DD08-4755-965D-922BC6441069@microsoft.com... > i would like to know how to add delete modify a falt file in VC++(MFC) Depending on what you want to do, but the CFile class comes to mind. With that you can create, append to and delete files. -- Bjarne Nielsen No not CFile,im talkin of data inside a file , i can read the file but i need to add ,delete and modify data such as Bill gates , 022, Microsoft , ,5Million...

Need help with QFX import
The only format that my 401k provider offers for statement download is QFX (that is Q as in quicken, not O as in Oscar). Is there anyway to import this format into Microsoft Money 2004 Premium? The 'convert quicken file' and 'import' menu options do not seem to work. The process of manually entering transaction takes forever. Can anyone help me? Thanks.. Dwayne In microsoft.public.money, John Smith wrote: >The only format that my 401k provider offers for statement download is QFX >(that is Q as in quicken, not O as in Oscar). Is there anyway to import &g...

Index Match Help
Data is dumped into Sheet1 and on Sheet2 I need a formula to pull out certain pieces of information. Here is what the data looks like: D.......P..R S 10 B R 3 10 B F 5 10 L R 10 10 L D 9 11 B R 20 11 B F 6 11 S R 20 I want to extract numbers from column S based on certain values in columns D, P and R. So, in order to give me the result of 5 from column S, here is what I'm using: =INDEX(S2:S41,MATCH(1,(D2:D41="10")*(R2:R41="F")*(P2:P41="B"),0)) I do Ctrl-Shift-Enter...

Query on a text file..help needed
I am trying to query a large text file and I'm beginning to wonder if it is just impossible to do. I used the excel query wizard but because I am working with a fixed length file I am not coming up with the columns I need. The wizard picks up each row as a solid block of information. Is there a way to pick out certain information from the text file into the query? so if my text line reads: 0110172006johndoe234street excel could pick out 01 10/17/2006 John Doe 234 street I know someone ran a query on my file and put it into the most beautiful excel spread sheet but it just isn't ...

Need help : Open a csv file with semicolon separation
Hello everybody, I need to open a csv file with semicolon separation but the separation used is always the comma. I tried this differents methods : - Workbooks.OpenText Filename:="D:\Excel\data\file.csv", Semicolon:=True, Comma:=False - Workbooks.open Filename:="D:\Excel\data\file.csv", Format:=4 - Workbooks.open Filename:="D:\Excel\data\file.csv", Delimiter:=";" - Workbooks.open Filename:="D:\Excel\data\file.csv", Delimiter:=";" , Format:=4 If I rename the file as a text file, it works !!! If anyone has an idea, i want to...

NEED HELP:Soap error: operation requested in the Soap message with soapAction isn't defined in the WSDL file.
Hi all, I need some help. We have an application which is using SOAP to relay messages between two applications. We are having a problem, which started to only occur intermittently, but now happens all of the time. When communicating between the apps, we receive this message... Soap error: The operation requested in the Soap message with soapAction <http://tempuri.org/FormAPI/action/COutlookForm.isCateringAvailable> isn't defined in the WSDL file. This may be because it is in the wrong namespace or has incorrect case. The SOAP sender application is a VB6 COM dll hosted in a...

help with formula #25
I was going to have form submitted to me from a site. The data comes in the form of headers representing fields and the answers as number. So if it was a yes or no answer, the Yes is represented by a 1 and the no is a 2. Is there a formula that would change the number to a word. Thank you in advance. Hi It depends whether you want to replace your Yes and No with a number, or have a separate value column. For the first, you could use Find/Replace. For the second, in blank column alongside the data you could use something like this: =IF(A1="Yes",1,2) Hope this helps. Andy. &q...

excel formula help #5
hi all, i have two questions (1) ineed help on a formula to count names in a filtered list. (2) is it there a way to use ron's easyfilter when the page is protected. any help will be appreciated. thx wynb -- wynb 1) =SUBTOTAL(3,B2:B20) gives the number of items in a filtered list. 2) I will leave that to Ron. -- HTH RP (remove nothere from the email address if mailing direct) "WYN" <WYN@discussions.microsoft.com> wrote in message news:3AC44CE9-547F-4584-9116-4A3E2D0F18CC@microsoft.com... > hi all, i have two questions (1) ineed help on a formula to count n...

Stuck
Hello, I'm trying to write in a formula whereby i'm referencing 2 cells. A1: Containing a pre-entered number and A2 containing a Y or N. If A2 = Y I need the formula to be A1/60 THEN the number displayed to be the answer to that formula. If A2 =N then I need the cell to remain blank. Am messing it up as the best I can get is the cell to display the text A1/60 rather than the answer to the formula. Help appreciated! Amy One way: =IF(A2="Y",A1/60,"") In article <C1DE2559-6542-4BE4-BE12-4A44792248EA@microsoft.com>, Amy <Amy@discussions.microsof...

VBA Excel Help
I am trying to do the following: I am using excel and I have a checkbox and once that box is checked I want it to see if it has been saved if not then i want the "Save as" method to show but if the file has already been saved then I want it to just the save the changes and not show the "Save As" dialog every time the check box is checked The following is the code i am working: any ideas on what i may be doing worng??? Sub CheckBox1_Click() Dim amessage As Outlook.MailItem Dim test As String Dim filename As String If Not ActiveWorkbook.Saved Then filesavename =3D E...