Cross Tab Line Numbers

I have two fields, Emp_ID and Proj.  Each employee could have as many as 10 
projects at any given time.  What I want to do is build a CrossTab Query that 
has the emp_id as column headers and under each name the list of projects 
assigned to them.  So what I was trying to do is to create a linenumber of 
some sort between 1 and 10 to go with each record so that when I pull the 
Query into the Cross tab query I could use the number as row headers.  Pleae 
help!

Example of what I want limit to 5 to save space (not 10):

no.  Emp1  Emp2  Emp3 .....
1     proj1   proj2  proj8
2     proj3   proj5  proj6
3               proj4   proj9
4               proj7
5

0
Utf
11/20/2007 8:26:01 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
1350 Views

Similar Articles

[PageSpeed] 20

As far as the column headings go, you can use a Select query grouping by 
Emp_ID and counting Projects.  Use the results of this query as your column 
headings ex. "Proj" & [CountofProj].

I'm still trying to figure out the rest of it though (similar problem here). 
 I can only make it give me the same number across all the columns (whether 
that's a sum, count, first, etc).  If you know how to do the rest, let me 
know.

"DDBeards" wrote:

> I have two fields, Emp_ID and Proj.  Each employee could have as many as 10 
> projects at any given time.  What I want to do is build a CrossTab Query that 
> has the emp_id as column headers and under each name the list of projects 
> assigned to them.  So what I was trying to do is to create a linenumber of 
> some sort between 1 and 10 to go with each record so that when I pull the 
> Query into the Cross tab query I could use the number as row headers.  Pleae 
> help!
> 
> Example of what I want limit to 5 to save space (not 10):
> 
> no.  Emp1  Emp2  Emp3 .....
> 1     proj1   proj2  proj8
> 2     proj3   proj5  proj6
> 3               proj4   proj9
> 4               proj7
> 5
> 
0
Utf
12/19/2007 12:30:01 AM
Assumptions:
 -- There are cases where there are two records with the same EmpId and 
ProjId
-- There are less than 250 employees

Base Query: qRankedProjects
SELECT EmpID, Proj
, (SELECT Count(Proj)
   FROM SomeTable as S2
   WHERE S2.EmpId = S.EmpID
   AND S2.Proj < S.Proj) +1 as Rank
FROM SomeTable as S

Crosstab query: Use above query as the source of the crosstab
TRANSFORM First(Proj) as TheProj
SELECT Rank
FROM qRankedProjects
GROUP BY Rank
PIVOT EmpID

Post back if you cannot do this with the SQL view of the query and need help 
to use the Design view.
-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"DDBeards" <DDBeards@discussions.microsoft.com> wrote in message 
news:264B9875-BC33-4298-95C4-A421D23C49A4@microsoft.com...
>I have two fields, Emp_ID and Proj.  Each employee could have as many as 10
> projects at any given time.  What I want to do is build a CrossTab Query 
> that
> has the emp_id as column headers and under each name the list of projects
> assigned to them.  So what I was trying to do is to create a linenumber of
> some sort between 1 and 10 to go with each record so that when I pull the
> Query into the Cross tab query I could use the number as row headers. 
> Pleae
> help!
>
> Example of what I want limit to 5 to save space (not 10):
>
> no.  Emp1  Emp2  Emp3 .....
> 1     proj1   proj2  proj8
> 2     proj3   proj5  proj6
> 3               proj4   proj9
> 4               proj7
> 5
> 


0
John
12/19/2007 12:37:02 PM
FORGOT to limit the records returned to 5

Crosstab query: Use above query as the source of the crosstab
TRANSFORM First(Proj) as TheProj
SELECT Rank
FROM qRankedProjects
WHERE Rank <= 5
GROUP BY Rank
PIVOT EmpID


-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"John Spencer" <spencer@chpdm.edu> wrote in message 
news:eXcHIvjQIHA.5524@TK2MSFTNGP05.phx.gbl...
> Assumptions:
> -- There are cases where there are two records with the same EmpId and 
> ProjId
> -- There are less than 250 employees
>
> Base Query: qRankedProjects
> SELECT EmpID, Proj
> , (SELECT Count(Proj)
>   FROM SomeTable as S2
>   WHERE S2.EmpId = S.EmpID
>   AND S2.Proj < S.Proj) +1 as Rank
> FROM SomeTable as S
>
> Crosstab query: Use above query as the source of the crosstab
> TRANSFORM First(Proj) as TheProj
> SELECT Rank
> FROM qRankedProjects
> GROUP BY Rank
> PIVOT EmpID
>
> Post back if you cannot do this with the SQL view of the query and need 
> help to use the Design view.
> -- 
> John Spencer
> Access MVP 2002-2005, 2007
> Center for Health Program Development and Management
> University of Maryland Baltimore County
> .
>
> "DDBeards" <DDBeards@discussions.microsoft.com> wrote in message 
> news:264B9875-BC33-4298-95C4-A421D23C49A4@microsoft.com...
>>I have two fields, Emp_ID and Proj.  Each employee could have as many as 
>>10
>> projects at any given time.  What I want to do is build a CrossTab Query 
>> that
>> has the emp_id as column headers and under each name the list of projects
>> assigned to them.  So what I was trying to do is to create a linenumber 
>> of
>> some sort between 1 and 10 to go with each record so that when I pull the
>> Query into the Cross tab query I could use the number as row headers. 
>> Pleae
>> help!
>>
>> Example of what I want limit to 5 to save space (not 10):
>>
>> no.  Emp1  Emp2  Emp3 .....
>> 1     proj1   proj2  proj8
>> 2     proj3   proj5  proj6
>> 3               proj4   proj9
>> 4               proj7
>> 5
>>
>
> 


0
John
12/19/2007 2:06:03 PM
Reply:

Similar Artilces:

Creating Cross-Project Links
Hi 1). What is the usage of Creating Cross-Project Links ? As I understand, "Cross-project links are useful when two or more projects have only a few tasks that affect one another. For example, a milestone within a development project might trigger a task in an advertising campaign project." 2). How it is implemented in Project 2007 ? When I copied the task from the source project into my project ( target project ) using "Paste Special", the new task I created become another task in my target project. My observation is both task ( ie. task in the source...

Is there a way to have two values (percent and number) in a label.
I am trying to format a cell to give the percent value as well as the numeric value from the chart. I tried to format using the "Special " format but it is not giving the correct information. I am using Excel 2003 What kind of label? Data Label, Axis label? Could you use a formula like this? =Format(<numeric value>,<numeric format>)&" "&Format(<percent>,<percent format>) The <numeric value> and <percent> are links to cells, while the two <formats> are number formats such as you'd assign when formatting a cell. -...

Removing every other line
Hello All I need a script to remove every other line in a excel document, starting at say line 10 and delete line 11,13,15 and so on is this something that can be done. Thanks for any help Scott Try this: Just one rule: Cells A9 through A11 must contain any kind of value A1: MyCriteria A2: =ISEVEN(ROW(A10)) Data|Filter|Advanced Filter List Range: (select from A9 down as far as you need) Criteria Range: (Select A1:A2) Click the [OK] button The filter will only display even numbered rows under A9 Select the filtered rows under A9 Edit|Delete (Excel will only allow you to delete entire...

Customer Cross Reference
2nd Request - Posted a few Months Ago, No Reply --------------------------------------------------------------------------- Has anyone out there used the Customer Part Number Cross Ref v8.0? I loaded a customer list the other day. When I entered the Customer Number on the Sales Order, it was instantly converted over to our number. Worked Great... I'm wondering if there is a way to automatically reference the original Customer Number on the Packing List and/Or Invoice or is that something that needs to be manually typed in. I tried adding notes, but I don't know if they carry down...

column-line charts
I understand the default for col-line charts is col on the primary y axis, line on the secondary axis. I am trying to chart 4 series (columns) onto the primary and 1 series (line) onto the secondary axis. The chart wizard always starts with placing 3 series onto col / 2 series onto lines. I can change one of the line series to the primary axis but it still does not format the data into a "column" but remains as a "line". any suggestions. Thanks. Bag the built-in custom chart types. Make a column chart with all of your data. Select the series you want to change. G...

No "Move Mailbox" cross site migration
Hi everybody: We have a forest with subdomains, the main domain is AD 2000 and has 1 Cluster Exchange 2003 SP1, 1 Server Exchange 2003 SP1 with ADC SP1 and SRS, and 3 Exchange 5.5 in the same Exchange 5.5 site. We have 8 subdomains in AD2000 with Exchange 5.5 SP4 server with hotfix applied for cross-site in each subdomain, and they had an own site in Exchange 5.5. We are trying to consolidate and move all mailbox and public folders to the Exchange 2003 cluster, we did the domainprep in each subdomain and create a RUS in the Exchange 2003 cluster to each subdomain. When we tried to move cros...

Tab names to a list?
Is there a way to get a printout of just the names on the tabs of each worksheet in a workbook? I have one that has a ton of them and it would be just so much easier to get a printout than try to copy each name manually. Is there any way? Thanks. :oD This sub will make a list in col F Sub listshtnames() For i = 1 To Sheets.Count Cells(i, "f") = Sheets(i).Name Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "StargateFanNotAtHome" <IDon'tAcceptSpam@NoJunkMail.com> wrote in message news:hh95d49s4c8egbeuanjul52c...

keep tab order after protection
I used the Insert, Name, Define, OK function in EXCEL to create a tab order, which works great. However, after protecting the sheet, the tab order doesn't work. Is there anway to maintain this order and protect the worksheet. Any help would be appreciated! Thanks! IF I understand you, the cells desired must be unlocked. -- Don Guillett SalesAid Software donaldb@281.com "Rawley" <Rawley@discussions.microsoft.com> wrote in message news:BB0D352F-031F-41F2-9E08-1B023791CEC6@microsoft.com... > I used the Insert, Name, Define, OK function in EXCEL to create a tab order,...

Cross table
Hi everybody !! i come again here with a problem i can seem to solve :s i hope someone will be able to give me a hand on this... here it is : i have a sheet with 2 rows: location and activity. several activities can be found in one location, even several of the same activities. schematic exemple location activity 1 A 1 B 2 A 3 A 3 B 3 C 4 B 4 C I would like to create something that give me as a result a sheet where we can see the spatial relations between the activities. so i would have all...

modify macro to include more tabs
Hi, The macro below allows users to group/ungroup rows in a protected sheet. How do I modify this macro to either incorporate more worksheets or apply it to the entire workbook? Thanks, Charlie Sub workbook_open() With Worksheets("Sheet1") .Protect Password:="password", userinterfaceonly:=True .EnableOutlining = True End With End Sub Try something like this, not tested Sub workbook_open() Dim wks as worksheet For each wks in Activeworkbook With wks) .Protect Password:="password", userinterfaceo...

block by subject line
Is there a way to block emails by subject line from the Exchange 2003 server without add-ons? Regards, Mark On Sat, 15 Oct 2005 08:25:46 -0400, "Mark" <canadaone@rogers.com> wrote: >Is there a way to block emails by subject line from the Exchange 2003 server >without add-ons? > >Regards, > >Mark > No. Exchange has some connection/recipient filtering capability however. http://support.microsoft.com/default.aspx?scid=kb;en-us;823866 "How to configure connection filtering to use Realtime Block Lists (RBLs) and how to configure recipient filter...

Other symbols for delimiter other than TAB
Dear all, Hope you can assist me on this matter: How do I program the Excel so that if I save it as a text file the ! symbol (or any other symbol) is the delimiter instead of the TAB? Currently what I do is: 1. Copy all the cells and an additional blank cell after the last. 2. Paste into Wordpad. 3. Copy the TAB; run the Replace All command and replace the TAB with the symbol of my choice. Is there a way to automate so that when I save the spreadsheet into text format, it will automatically use my symbol instead of the TAB? If there is a way, can you show me how to program it? Samp...

IE8 dead tabs
Windows 7, 32 bit. All required updates applied. IE 8.0.7600.16385 If I right click on a link and select "open in new tab", a new tab opens but it doesn't open linked web page, "connecting" message appears only. These actions don't fix this problem : -Removed antivirus software. -Tried at safe mode with networking -Cleared Internet Options>Connections>Lan settings>Automatically detect settings -Cleared Internet Options>Advanced>Enable 3rd party browser extensions -Tried with IE8 with "no add-on" Thank you. Hi, Try the r...

Taking numbers off a website and importing them into Excel? Possible?
I have no idea if this is possible, but please help if you have a idea. I need to get some data off of a website, particularly a certain set o numbers from this website, and enter them into data in Excel. Here is an example of the website I need: http://www.covers.com/data/ncf/matchups/g8_main_1.aspx There is a table near the top of the website which lists the tw teams.... I need some way to automatically go to this website, find Syracuse, an then cross reference that with two sets of numbers, AF and AA, whic works out to be 20.0 and 25.7 I then need these numbers to be transported int...

insert serial numbers on the fly in GP 9
How can I insert serial numbers on the fly in GP 9. I don't think you can do this. The individual item has to be set up to track by serial number. If it is, then it is possible to enter a serial on a sales order on the fly - but this will mess up your inventory so it's not advisable. Really need to do an inventory adjustment to bring the appropriate quantities with the respective serial numbers into inventory. "Jack Tundra" wrote: > How can I insert serial numbers on the fly in GP 9. > > > Jack- You can do this--I wrote a small app for one of my ...

How to populate filled lines from several sheets to one sheet?
I have a file with many sheets. On the last sheet, I want to populate the filled lines from the sheets. The order must be per date that is filled in one of the lines. How to perform? During the time, more lines will be filled and the last sheet will be more populated. Bart ...

cross-reference
I created a cross reference in word to a section of my document that has a heading of 'Heading 3' I used Heading for the reference type and for Insert reference to I used Heading Number (Full context). I was expecting to get something like this 4.8.2 but instead I just got 0. is there some setting somewhere that needs to be tweaked? Make sure that numbering is correctly applied to your headings. If you are using Word 2007, you can choose the "1 Heading 1 1.1 Heading 2 1.1.1 Heading 3" format at Home tab | Multilevel List. If you are using Word 2003, see http://www...

count number of dates in range
In column A, various contractor names are listed. In column B, completion dates have been entered for when their projects were completed thoughout the year. I would like to have the next spreadsheet tab summarize the data so it lists the number of times between 2/1/20010 and 2/28/2010 a project was completed for "Fred." Can you tell me what formula I should enter? Thanks Try one of these... Use cells to hold the criteria. D2 = Fred E2 = lower date boundary = 2/1/2010 F2 = upper date boundary = 2/28/2010 In Excel 2007: =COUNTIFS(A2:A20,D2,B2:B20,"&...

blank subject lines
Outlook 2007. Where do I set the option to warn me about sending messages with blank subject lines? here is one way http://www.dotnetfunda.com/articles/article590-validate-blank-subject-warning-for-outlook-2007.aspx badgolferman wrote: > Outlook 2007. > > Where do I set the option to warn me about sending messages with blank > subject lines? > ...

Conditional formatting by number of characters in cell
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi, <br><br>Struggling to work out how to do this and would value help. <br><br>I'm entering text into a cell and I simply want the colour of that text to change to red when I exceed 255 characters. <br><br>How do I do this? Using &quot;Cell Value Is&quot;, or &quot;Formula Is&quot;? <br><br>Thanks <br><br>Giles > <br> > I'm entering text into a cell and I simply want the colour of that text to change to red when ...

Tab controls/ Hiding Tabs
I am looking for some coding assistance. I have a tab control called "Master Tab" with 3 tabs on it called "Type" "CIP" and "Vacuum". When I load the tab control form I want "CIP" and "Vacuum" to be hidden - only "Type" should be showing. Then when I want to go to the "CIP" tab, I would like to create an event that hides the "Type" form and makes the "CIP" form visible. Any ideas? Thanks, Zag -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms...

cross reference
Greeting all, Hope you experts can help me with following issue. I have 2 order information spreadsheets, the first one has a column which is our product codes, the second one is a reference which has 2 columns map our product codes to customers product codes. Is there any easy way in Excel which can replace our product codes in first excel file with our customer product codes in second excel file? I know I can use Access to do that, just want find out a simple way in Excel. Do I need to write any VB ? Thank you very much in advance!! ------------------------------------------------ ~~...

Auto Fill workshseet tabs?
I have a workbook with a page for each day of the month, named for the day and date. I need to copy it for each month. Can I auto fill the tabs on the worksheets like I would a column? Maybe a little macro??? Option Explicit Sub testme() Dim iCtr As Long Dim myDate As Date myDate = DateSerial(2005, 8, 1) For iCtr = DateSerial(Year(myDate), Month(myDate), 1) _ To DateSerial(Year(myDate), Month(myDate) + 1, 0) Worksheets.Add after:=Worksheets(Worksheets.Count) On Error Resume Next ActiveSheet.Name = Format(iCtr, "ddd...

How do I set up page numbering to count each spread as one page, .
I am trying to count each spread as one page, and only show page numbers on the bottom right of the right hand page. I dont see many detailed options for this. Is there a way to write my own script to determine the page number, such as Miscrosoft has written the # script which displays the page number. Joe <Joe@discussions.microsoft.com> was very recently heard to utter: > I am trying to count each spread as one page, and only show page > numbers on the bottom right of the right hand page. I dont see many > detailed options for this. Is there a way to write my own scrip...

Disable Mail Format tab
We are running Outlook 2000 and I'm trying to disable the Mail Format tab under options. Any ideas? I can gray out the Options tab by disabling the Object ID using Outlook Administrative Template but can't find the Object ID for the Mail Format tab. Thank You, Jeff Fisher You can't disable an Options page, but you can disable most if not all the options on that page by setting their values with group policy objects. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlo...