Query ??

[dazoloko via AccessMonster.com]

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

 

[Dale_Fye via AccessMonster.com]

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

 

[KARL DEWEY]

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.

 

[dazoloko via AccessMonster.com]

Thank you both for responding, I shall give those a whirl.

Cheers

d

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