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):
GW2
GW2
GW2
GW3
GW3
GW4
GW4
GW4
GW4
GW4
GW4
What I want to get is a report that looks like this(example 2):
GW2
GW2
GW2
GW3
GW3
GW4
GW4
GW4
GW4
GW4
GW4
So that ultimately I’ll get something that looks like this (example 3):
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?
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
108
70.00%
1
110
30.00%
1
113
6.00%
1
109
100.00%
1
114
2.00%
1
109
100.00%
1
111
10.00%
2
110
100.00%
3
109
100.00%
4
109
100.00%
5
109
100.00%
What I want to get is a report that looks like this(example 2):
ProjectID
Gateway
IC
IC%
Count
1
108
70.00%
1
1
110
30.00%
2
1
113
6.00%
3
1
109
100.00%
1
1
114
2.00%
2
1
109
100.00%
1
1
111
10.00%
2
2
110
100.00%
1
3
109
100.00%
1
4
109
100.00%
1
5
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?