A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Query ??



 
 
Thread Tools Display Modes
  #1  
Old July 9th, 2008, 03:52 PM posted to microsoft.public.access.queries
dazoloko via AccessMonster.com
external usenet poster
 
Posts: 18
Default 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  
Old July 9th, 2008, 05:12 PM posted to microsoft.public.access.queries
Dale_Fye via AccessMonster.com
external usenet poster
 
Posts: 128
Default 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  
Old July 9th, 2008, 05:14 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old July 9th, 2008, 06:31 PM posted to microsoft.public.access.queries
dazoloko via AccessMonster.com
external usenet poster
 
Posts: 18
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:53 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.