Arrange fields in query based on ID

  • Thread starter Thread starter Leo
  • Start date Start date
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
 
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
 
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
(e-mail address removed)
"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
|
|
|
|
|
 
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
 
Back
Top