Arrange fields in query based on ID

L

Leo

Hi All. I had found the solution to this a long time ago, but I do not have
a sample of it saved anywhere. Basically what I have is this

ID InventoryCount
1 1000
1 2000
1 3000
2 2000
2 2500
3 4000
4 5000

and what I want is

ID InvCOunt
1 1000 2000 3000
2 2000 2500
3 4000
4 5000

Thank you very much

Leo
 
M

Michel Walsh

Hi,


A crosstab can do, but first, you need to rank your data:


SELECT a.ID, a.InventoryCount, COUNT(*) As rank
FROM myTable As a INNER JOIN myTable As b
ON a.ID=b.ID AND a.InventoryCount>= b.InventoryCount
GROUP BY a.ID, a.InventoryCount




Save that query, then, run the crosstab wizard on it: group by on ID, pivot
on Rank, aggregate with LAST on InventoryCount.


TRANSFORM LAST(InventoryCount)
SELECT id
FROM savedQuery
GROUP BY id
PIVOT rank



Hoping it may help,
Vanderghast, Access MVP
 
E

Eric Butts [MSFT]

Hi Leo,

Is the answer you are looking for is a CrossTab Query? Example:

TRANSFORM Sum(Table1.InventoryCount) AS SumOfInventoryCount
SELECT Table1.ID
FROM Table1
GROUP BY Table1.ID
PIVOT Table1.RecordID;


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
[email protected]
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights


--------------------
| From: "Leo" <[email protected]>
| Subject: Arrange fields in query based on ID
| Date: Tue, 20 Jul 2004 09:36:10 -0400
| Lines: 27
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| X-eShield-AntiVirus: Passed
| X-eShield-AntiVirus-Message: Scanned by http://www.bluecoat.com/eShield
| Message-ID: <[email protected]>
| Newsgroups: microsoft.public.access.queries
| NNTP-Posting-Host: 38.112.96.88
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.queries:207396
| X-Tomcat-NG: microsoft.public.access.queries
|
| Hi All. I had found the solution to this a long time ago, but I do not
have
| a sample of it saved anywhere. Basically what I have is this
|
| ID InventoryCount
| 1 1000
| 1 2000
| 1 3000
| 2 2000
| 2 2500
| 3 4000
| 4 5000
|
| and what I want is
|
| ID InvCOunt
| 1 1000 2000 3000
| 2 2000 2500
| 3 4000
| 4 5000
|
| Thank you very much
|
| Leo
|
|
|
|
|
 
L

Leo

Thank you. I will try the crosstab. The solution I was looking for has to do
with a Self Join query, but if this works then that's fine

Again thanks for the response

Leo
 
Top