ACCESS FEED: RE: [MS_AccessPros] Query to put multiple records into one record

Senin, 05 Maret 2012

RE: [MS_AccessPros] Query to put multiple records into one record

Troy-

This will get you close:

TRANSFORM Sum([Process HRS]) As SumOfHours
SELECT [MyTable].[Job#]
FROM MyTable
GROUP BY [MyTable]. [Job#]
PIVOT [MyTable].[Process];

The result should be:

Job# Process1 Process2 Process3
1234 7 2 1
4567 3 5 2

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Nashua, NH)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Troy Sherven
Sent: Monday, March 05, 2012 10:50 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Query to put multiple records into one record

I'm not sure if this is possible, but I thought I would give it a shot.

I have a table that stores process data for a job. There is a single
record for each process within the job. Each job may have 1 process or
it may have 10 processes. I'd like to do this through a SQL statement.

Current table:

Job# Process Process HRS

1234 Process1 7

1234 Process2 2

1234 Process3 1

Here's what I want my query to return:

Job# Process1 Process1_HRS Process2
Process2_HRS Process3 Process3_HRS

1234 Process1 7
Process2 2
Process3 1

Is this possible?

Thanks,

Troy Sherven

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links