Chart - Need to refer name to ID in horizontal axis

[wlkr.jk]

Hi,



I have a chart which is counting the number of times each specific ID (ProcessID) number occurs in a master table (ProblemTable). See attached for view of chart.

The chart is a bar chart with a simple count and the horizontal axis has each of the unique ProcessID's. However, instead of the ProcessID, I'd like the associated ProcessName which is given in ProcessTable to be on the horizontal axis.

Here is the SQL code that I used for the simple ProcessID count on the chart:

SELECT ProblemTable.[ProcessID], Count(ProblemTable.[ProcessID]) AS CountOfProcessID
FROM ProblemTable
GROUP BY ProblemTable.[ProcessID];

Here is the SQL code that I came up with to change the axis to ProcessName (but doesn't currently work):

SELECT ProblemTable.[ProcessID], ProcessTable.[ProcessName], Count(ProblemTable.[ProcessName]) AS CountOfProcessID
FROM ProblemTable INNER JOIN ProcessTable
ON ProblemTable.[ProcessID] = ProcessTable.[ProcessID]
GROUP BY ProblemTable.[ProcessName];

Thanks for your help.

Jake

[June7]

Try:

SELECT ProcessTable.[ProcessName], Count(ProblemTable.[ProcessID]) AS CountOfProcessID
FROM ProblemTable INNER JOIN ProcessTable
ON ProblemTable.[ProcessID] = ProcessTable.[ProcessID]
GROUP BY ProblemTable.[ProcessName];

[wlkr.jk]

Try:

SELECT ProcessTable.[ProcessName], Count(ProblemTable.[ProcessID]) AS CountOfProcessID
FROM ProblemTable INNER JOIN ProcessTable
ON ProblemTable.[ProcessID] = ProcessTable.[ProcessID]
GROUP BY ProblemTable.[ProcessName];

Thanks I tried that but it came up with the following error:

[June7]

Change the table prefix of the field in the GROUP BY clause. Sorry I did not notice the difference.

[wlkr.jk]

I actually changed the GROUP BY clause before since I noticed my mistake.

Chart remains unchanged. Horizontal axis values are still listed as the ProcessID's.

[June7]

That doesn't make sense to me. That SQL is the chart RowSource?

[wlkr.jk]

Yes the SQL code is in the Row Source

[June7]

Since ProcessID is not in the SELECT clause, I don't understand how the graph can reference it.

If you want to provide db for analysis, follow instructions at bottom of my post.

[wlkr.jk]

Forum DB for debug.zip

Thank you for your help.

[June7]

The bar chart RowSource is not the second query you posted nor the version I suggested.

SELECT ProcessTable.[ProcessName], Count(ProblemTable.[ProcessID]) AS CountOfProcessID
FROM ProblemTable INNER JOIN ProcessTable
ON ProblemTable.[ProcessID] = ProcessTable.[ProcessID]
GROUP BY [ProcessName];

[wlkr.jk]

That's awesome thank you for figuring that out!

I just have one more question about that code, now. I'd like to not include processes in the chart if their status is '5' (so that when their count is 0, it's not a blank part in the chart).

Here is what I figured out on my own based on what you just provided:

SELECT CASE
WHEN ProblemTable.[StatusID]<5 THEN ProcessTable.[ProcessName]
ELSE Null
END, Count(IIf(ProblemTable.[StatusID]<5,ProblemTable.[ProcessID],Null)) AS CountOfProcessID
FROM ProblemTable INNER JOIN ProcessTable
ON ProblemTable.[ProcessID] = ProcessTable.[ProcessID]
GROUP BY [ProcessName];

But I'm getting the following error:

[June7]

I've never seen that syntax in an Access query. I don't know about using CASE in query.

If you want to exclude the status 5 records, then use WHERE clause.

WHERE StatusID < 5

[hapm]

CASE is T-SQL. Use the IIf function instead.

[wlkr.jk]

Thanks everyone. So I settled on the following:

SELECT LineTable.SubLine,
Count(IIf(Now()<ProblemTable.[DatetobeClosed],IIf(ProblemTable.StatusID<5,ProblemTable.[LineID],Null),Null)) AS [On Time],
Count(IIf(Now()>=ProblemTable.[DatetobeClosed],IIf(ProblemTable.StatusID<5,ProblemTable.[LineID],Null),Null)) AS [Overdue]
FROM ProblemTable INNER JOIN LineTable ON ProblemTable.[LineID] = LineTable.[LineID]
GROUP BY LineTable.SubLine
ORDER BY Count(IIf(ProblemTable.StatusID<5,ProblemTable.[LineID],Null)) DESC;

For everyone's information, the problem table stores problems to be followed up on with a "date entered" and a "date to close the problem".

With this, the chart lists problems by line in descending order and has the name of the line on the horizontal axis as opposed to the LineID from the LineTable.