Query-Join help needed (1 Viewer)

[MiniD011]

Good morning,

I have a query I plan to base a report on, and am having trouble with something which seems as though it should be quite basic, but as I'm new to this I am struggling.

Essentially I want to produce a query which looks at a table and says how many records came from each department. I have two tables, one where the data is recorded (tblAdjustments) and another, centrally linked, which gives the department name and ID (Department).

In my current query I have all of the relevant information, however can't show the Department Name, I just keep getting the Department ID. Below is my code:

SELECT Count(tblAdjustments.ID) AS CountOfID, tblAdjustments.DeptName
FROM Department INNER JOIN tblAdjustments ON Department.Dept_Id = tblAdjustments.DeptName
GROUP BY tblAdjustments.DeptName;

I am new to creating queries so I am hoping it is something fairly simple, and hopefully can take any help from this to other work of a similar nature.

Thanks in advance for any help!

Dan

 

[JHB]

Do they not have to be both an ID?

Department.Dept_Id = tblAdjustments.DeptName

Should the department name not come from the Department table?

SELECT .... , tblAdjustments.DeptName ...

I think the correct query should look something like below:

SELECT Count(tblAdjustments.ID) AS CountOfID, Department.DeptName
FROM Department INNER JOIN tblAdjustments ON Department.Dept_Id = tblAdjustments.ID GROUP BY Department.DeptName;

 

[MiniD011]

Hi again,

Thanks for the reply, I have tried to implement this but instead of producing a table with the number of adjustments per department, the above code just produced a list of all of the departments but didn't consider the number of records from tblAdjustments at all.

My original query produces the table with all of the correct data, but simply has numbers in place of department names, which I am trying to reverse.

If any more information is needed please let me know, I am sorry if I am not presenting this well!

Thanks again for any help.

Dan

 

[JHB]

If any more information is needed please let me know, I am sorry if I am not presenting this well!

Ok - then show some sample data from both tables together with thier columns name.

 

[MiniD011]

Here is the initial query and count of records, along with the table of departments. The DeptName in the query is giving the ID from the Department table, and I am hoping to join it using SQL to show the department names for the purposes of reporting. Thanks again for the help, as always I can provide more info if necessary. I copied them into an Excel file to present it a little better as I couldn't paste a table directly into here.

 

[MiniD011]

Hi JHB,

I have just realised I've been a bit of an idiot and there was nothing wrong with my query or code at all, it was just to do with a lookup in the table going to the wrong column. Sorry for the confusion, but thanks for all of your time and effort, it is much appreciated!

Dan

 

[JHB]

Good you got it.