Show companies first, second, third ... N engagements (1 Viewer)

[Sketchin]

Hello,

I have a dataset that includes Company Name, Date and Program Type. I need a query that will list distinct company names in rows with 1st, 2nd, 3rd ..etc engagements as column headers and the program type as the data.

Please see the attached image for a clear picture of what I need to do.

I feel like this shouldn't be impossible to solve, but can't even think of how to google it!

Would this be easier to do in Excel?

Thanks!

 

[plog]

That can be done with a cross-tab query:https://support.office.com/en-us/ar...cc8-ba60-2cd8484667e8?ui=en-US&rs=en-US&ad=US

Itself will be built of a running total query (https://support.microsoft.com/en-us...te-a-running-totals-query-in-microsoft-access). That query will allow you to order your engagements. Which leads to an important question--how are you ordering your engagements? By the Date field?

 

[Sketchin]

Thanks for the reply. Yes, I am ordering engagements by the date field.

I believe that in order to do this in a crosstab, I would first need to create a query that has a calculated field that somehow determines the number of engagement (1st, 2nd...etc.) From there, I could use that calculated field as the column heading and the company name as the row heading with the program type as the value field.

Problem is, I have no idea how to determine the engagement number in an Access query!

 

[plog]

The simplest way is to use a DCount in a query to count the number of records that come before the current one. Can you post some sample data from your table? Include table and field names.

 

[Sketchin]

Plog, here is a sample of the RAW data:

Company Name Engagement Date Program
Company A 5/9/2015 IND
Company A 5/9/2016 GEO
Company A 5/9/2017 TAP
Company B 5/9/2015 GEO
Company B 5/9/2016 IND
Company C 5/9/2014 GEO
Company C 5/9/2015 TAP
Company C 5/9/2016 IND
Company C 5/9/2017 TAP

Here is how I would like it to look:

Company Name 1st 2nd 3rd 4th
Company A IND GEO TAP
Company B GEO IND
Company C GEO TAP IND TAP

 

[plog]

Name and Date are bad field names because they are reserved words (https://support.microsoft.com/en-us...n-access-2002-and-in-later-versions-of-access) which make coding and querying a little more difficult. I suggest you rename them by prefixing them with what the are the name and date of (EngagementName, EngagementDate, etc.).

For your subquery, to assign order on your records you would create a query on your table, bring all of the fields into the query and then an additional one using this code:

EngagementOrder: DCount("[Company], "YourTableNameHere", "[Company]="' & [Company] & "' AND [Name]='" & [Name] & "' AND [Date]<=#" & [Date] & "#")

Then, you use that query as the basis for your crosstab query.

 

[Sketchin]

Hi plog,

Thanks for the reply. I implemented this and it didn't quite work. Here is a sample of the data output that should give you an idea of what went wrong.

CompanyName	Engagement Date	ProgramType	EngagementOrder
4iiii Innovations Inc.	1/22/2013	              TAP	                 1
4iiii Innovations Inc.	7/15/2013	              GEO	                 1
4iiii Innovations Inc.	6/26/2014	              GEO	                 2
4iiii Innovations Inc.	8/6/2015	              TAP	                 2
4iiii Innovations Inc.	10/13/2015          	TAP	                 3

I had to change the code a bit just to fit my data names, maybe I screwed something up?

EngagementOrder: DCount("[CompanyName]","UNIQRYCompanyFirstLabEngagementAndProjectStartDates","[CompanyName]='" & [CompanyName] & "' AND [ProgramType]='" & [ProgramType] & "' AND [Engagement Date]<=#" & [Engagement Date] & "#")

 

[plog]

Remove the ProgramType data from the criteria of your DCount.

 

[Sketchin]

I made an adjustment tothe calculation because the program type doesn't really matter, its just the Company Name and Engagement Date that are important.

I am closer to a solution, but now am missing data!

CompanyName	    Engagement Date	ProgramType	EngagementOrder
4iiii Innovations Inc.	1/22/2013	            TAP	                 1
4iiii Innovations Inc.	7/15/2013	            GEO	                 2
4iiii Innovations Inc.	6/26/2014	            GEO	                 3
4iiii Innovations Inc.	8/6/2015	            TAP	                 6
4iiii Innovations Inc.	10/13/2015         	TAP	                 7

I have no idea why it is skipping #4 and 5??

 

[Sketchin]

Alright, I think I am good for now. For some reason, sorting isnt workign in Access, but I can do it in my linked Excel Sheet.

Thanks for the help!