Row Count in Query

Pezzini

Registered User.
Local time
Today, 07:18
Joined
Dec 9, 2015
Messages
20
Hi

Having a problem with Access 2013, I believe I could have done this with pivot tables before, but now they’ve been removed as an option, I’m more sure how to do what I need to do, so any advice would be welcome.

The situation is that I have a number of tables which hold sub data for a project, the user selects a code and gives it a percentage, they can use as many codes as they want as long as the sum is 1. The table looks like this:

Tbl-InvCase:
[Tbl-InvCase].ProjectID,
[Tbl-InvCase].Gateway,
[Tbl-InvCase].IC, (I’ve removed descriptions below, but this is a text field not a number)
[Tbl-InvCase].[ICpc] (pc standing for percentage here)

There are a number of different tables for different analysis but they are all in this sort of format. What I have been asked to do is to export all project data in a single line so I’d get something that looks like this:
ProjectID, project details, InvCasen, ICpcn, InvCasen+1, ICpcn+1, InvCasen+2, ICpcn+2, etc

What I thought I’d do was put a rank on each line, then I could use that to spread the data across the row. I’ve been on the internet looking for ways to do this as Dcount but I seem to be missing something (a brain probably it is blue Monday after all), and I can’t get Dcount, count or any other format to work.

Here’s the query without the count column:
SELECT [Tbl-InvCase].ProjectID, [Tbl-InvCase].Gateway, [Tbl-InvCase].IC, [Tbl-InvCase].[IC%]
FROM [Tbl-InvCase]
ORDER BY [Tbl-InvCase].ProjectID, [Tbl-InvCase].Gateway, [Tbl-InvCase].IC;

This gives results that look like this (example 1):
ProjectID
Gateway
IC
IC%
1
GW2
108
70.00%
1
GW2
110
30.00%
1
GW2
113
6.00%
1
GW3
109
100.00%
1
GW3
114
2.00%
1
GW4
109
100.00%
1
GW4
111
10.00%
2
GW4
110
100.00%
3
GW4
109
100.00%
4
GW4
109
100.00%
5
GW4
109
100.00%



What I want to get is a report that looks like this(example 2):
ProjectID
Gateway
IC
IC%
Count
1
GW2
108
70.00%
1
1
GW2
110
30.00%
2
1
GW2
113
6.00%
3
1
GW3
109
100.00%
1
1
GW3
114
2.00%
2
1
GW4
109
100.00%
1
1
GW4
111
10.00%
2
2
GW4
110
100.00%
1
3
GW4
109
100.00%
1
4
GW4
109
100.00%
1
5
GW4
109
100.00%
1


So that ultimately I’ll get something that looks like this (example 3):
ProjectID
GW2IC1
GW2ICpc1
GW2IC2
GW2ICpc2
GW2IC3
GW2ICpc3
GW3IC1
GW3ICpc1
GW3IC2
GW3ICpc2
GW3IC3
GW3ICpc3
GW4IC1
GW4ICpc1
GW4IC2
GW4ICpc2
GW4IC3
GW4ICpc3
1
108
70.00%
110
24.00%
113
6.00%
109
80.00%
114
2.00%


109
90.00%
111
10.00%


2
110
100.00%
















3
109
100.00%
















4
109
100.00%
















5
109
100.00%


















I think I know how to get to example 3, but example 2 that is proving the problem. I tried this:
SELECT [Tbl-InvCase].ProjectID, [Tbl-InvCase].Gateway, [Tbl-InvCase].IC, [Tbl-InvCase].[IC%],DCount("[IC]","[Tbl-InvCase]","[IC]<=" & [IC]) AS row_id
FROM [Tbl-InvCase]
ORDER BY [Tbl-InvCase].ProjectID, [Tbl-InvCase].Gateway, [Tbl-InvCase].IC,
GROUP BY [Tbl-InvCase].ProjectID, [Tbl-InvCase].Gateway, [Tbl-InvCase].IC;

But that just tells me there are errors in the query.

Then I tried:
SELECT [Tbl-InvCase].ProjectID, [Tbl-InvCase].Gateway, [Tbl-InvCase].IC, [Tbl-InvCase].[IC%], Count(*)+1 AS rank
FROM [Tbl-InvCase], [Tbl-InvCase] AS t2
GROUP BY [Tbl-InvCase].ProjectID, [Tbl-InvCase].Gateway, [Tbl-InvCase].IC, [Tbl-InvCase].[IC%];

But this just ranks everything with the same number, so I’ve no idea what else to do.

Can anyone advise?
 
You need to attend to the formatting of your post first, it is unreadable in its current format. Take some screenshots and post images instead of trying to format your data in the thread.
 
Your formatting has been "Webbed" which means it now makes not much sense. However it sounds like maybe a crosstab query might get you what you need.

If not post up a picture of the desired output in excel or similar and some trial data with an view of how it relates?
 
I tried posting pictures, but it didn't work. I'll try crosstab queries. If they fail, I'll give up and tell the boss to go back to Excel.
 
I myself use a 'report' table. I sort the rankings, then append to the report table.
Then using a vb, scans the table 'ranking' the records.
Then I append the next batch of ranks.

Code:
  'append ranking data to a 'report' table, then use a sorted query 'qsRankSort' to rank the records
Public Sub Ranks()
Dim rst
Dim r As Long
 Set rst = currentdb.openrecordset("qsRankSort")
With rst
  While Not .EOF
     If IsNull(.Fields("[Rank]").Value) Then
         r = r + 1
        .Fields("[Rank]").Value = r
     End If
     
     .movenext
  Wend
End With
End Sub
 
Last edited:
Wow, Ranman256, I'm both impressed and horrified. I'm impressed you have an answer and horrified that I look at this code and understand nothing. It's not just that my vba is rusty, I'm not sure that I would ever have understood that. I'll give it a go though.

While on here should say that the Crosstab query seems to give me a result that while not what I was looking for, it's one I can use. Thanks Minty for that.
 
Well, been playing with this for a while, and the crosstabs worked for some of the answers, the problem was that for others there are so many options when I put them all together, there access tells me I've selected too many columns and can't run the query. But I can see how Crosstab queries will make other reports in this database much more efficient - again, thanks Minty.

To get back to the original question, I have found a way to get the ranking that I wanted and it's this:

SELECT *,
(select count(*)
from [Tbl-InvCase] as tbl2
where [Tbl-InvCase].IC > tbl2.IC
and [Tbl-InvCase].ProjectID = tbl2.ProjectID
and [Tbl-InvCase].Gateway = tbl2.Gateway) + 1 as rank
FROM [Tbl-InvCase]
ORDER BY [Tbl-InvCase].ProjectID, [Tbl-InvCase].Gateway, [Tbl-InvCase].IC;


Hope that helps someone else in the future.
 

Users who are viewing this thread

Back
Top Bottom