If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query ??
Dear All
I have a table with the following fields which I have uploaded from a spreadsheet ID Number Reference Type Reference There are two reference Types, NI Number and Silverlink Number, The Reference is then the number itself. If there is more than one reference type then there are two records in the table ie Id Number Reference Type Reference 11111 NI Number AA 22 33 44 X 11111 Silverlink Number 123456 22222 NI Number BB 22 33 44 X 33333 Silverlink Number 234567 44444 NI Number CC 22 33 44 X 44444 Silverlink Number 345678 There I would like a query or report in this format Id Number NI Number Silverlink Number 11111 AA 22 33 44 X 123456 22222 BB 22 33 44 X 33333 234567 44444 CC 22 33 44 X 345678 Is this possible and if so how do I do it ? Any help would be greatly appreciated. Cheers D -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Query ??
You need to create a Crosstab query.
Use the ID Number as the Row Header, the ReferenceType as the Column Header, and the Reference as the Value (since this is text, you will probably have to select the First or Last instead of Count or Sum). HTH dazoloko wrote: Dear All I have a table with the following fields which I have uploaded from a spreadsheet ID Number Reference Type Reference There are two reference Types, NI Number and Silverlink Number, The Reference is then the number itself. If there is more than one reference type then there are two records in the table ie Id Number Reference Type Reference 11111 NI Number AA 22 33 44 X 11111 Silverlink Number 123456 22222 NI Number BB 22 33 44 X 33333 Silverlink Number 234567 44444 NI Number CC 22 33 44 X 44444 Silverlink Number 345678 There I would like a query or report in this format Id Number NI Number Silverlink Number 11111 AA 22 33 44 X 123456 22222 BB 22 33 44 X 33333 234567 44444 CC 22 33 44 X 345678 Is this possible and if so how do I do it ? Any help would be greatly appreciated. Cheers D -- HTH Dale Fye Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Query ??
Try these two queries --
QRY dazoloko_1 -- SELECT dazoloko.[Id Number], IIf([Reference Type]="NI Number",[Reference],Null) AS NI_Num, IIf([Reference Type]="Silverlink Number",[Reference],Null) AS Silverlink_Num FROM dazoloko GROUP BY dazoloko.[Id Number], IIf([Reference Type]="NI Number",[Reference],Null), IIf([Reference Type]="Silverlink Number",[Reference],Null); SELECT dazoloko_1.[Id Number], Min(dazoloko_1.NI_Num) AS [N _Number], Min(dazoloko_1_1.Silverlink_Num) AS [Silverlink Number] FROM dazoloko_1 LEFT JOIN dazoloko_1 AS dazoloko_1_1 ON dazoloko_1.[Id Number] = dazoloko_1_1.[Id Number] GROUP BY dazoloko_1.[Id Number]; Account 234567 was not in data so it can not show in results. -- KARL DEWEY Build a little - Test a little "dazoloko via AccessMonster.com" wrote: Dear All I have a table with the following fields which I have uploaded from a spreadsheet ID Number Reference Type Reference There are two reference Types, NI Number and Silverlink Number, The Reference is then the number itself. If there is more than one reference type then there are two records in the table ie Id Number Reference Type Reference 11111 NI Number AA 22 33 44 X 11111 Silverlink Number 123456 22222 NI Number BB 22 33 44 X 33333 Silverlink Number 234567 44444 NI Number CC 22 33 44 X 44444 Silverlink Number 345678 There I would like a query or report in this format Id Number NI Number Silverlink Number 11111 AA 22 33 44 X 123456 22222 BB 22 33 44 X 33333 234567 44444 CC 22 33 44 X 345678 Is this possible and if so how do I do it ? Any help would be greatly appreciated. Cheers D -- Message posted via http://www.accessmonster.com |
#4
|
|||
|
|||
Query ??
Thank you both for responding, I shall give those a whirl.
Cheers d KARL DEWEY wrote: Try these two queries -- QRY dazoloko_1 -- SELECT dazoloko.[Id Number], IIf([Reference Type]="NI Number",[Reference],Null) AS NI_Num, IIf([Reference Type]="Silverlink Number",[Reference],Null) AS Silverlink_Num FROM dazoloko GROUP BY dazoloko.[Id Number], IIf([Reference Type]="NI Number",[Reference],Null), IIf([Reference Type]="Silverlink Number",[Reference],Null); SELECT dazoloko_1.[Id Number], Min(dazoloko_1.NI_Num) AS [N _Number], Min(dazoloko_1_1.Silverlink_Num) AS [Silverlink Number] FROM dazoloko_1 LEFT JOIN dazoloko_1 AS dazoloko_1_1 ON dazoloko_1.[Id Number] = dazoloko_1_1.[Id Number] GROUP BY dazoloko_1.[Id Number]; Account 234567 was not in data so it can not show in results. Dear All [quoted text clipped - 39 lines] D -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200807/1 |
Thread Tools | |
Display Modes | |
|
|