Not Showng All Surnames

[Sharon1717]

I have a report which displays Surname, first name etc etc, when I run the report it is not showing surnames which are duplicate, for example Carol Smith, Vince Smith, it only shows one Smith surname for some reason........and not the rest.....WHY?

Also the report shows employees "Trained In" what I want to be able to do is show all employees whether they have been trained in something or not. What am I doing wrong?

I have 2 tables set up one employees and one trained in

Thanks

 

[raskew]

Hi -

Please post your underlying query SQL(s).

Bob

 

[Sharon1717]

SQL Bits

This is the SQL bit of the query I am using:

SELECT [Table Employee Listing MASTER].Surname, [Table Employee Listing MASTER].[First Name], [Table Employee Listing MASTER].[Clock No], [Table Employee Listing MASTER].Dept, [Table Employee Listing MASTER].Manager, [Table Employee Listing MASTER].[Left Company], [Table Trained In1 MASTER].[Trained In], [Table Trained In1 MASTER].[Date Start Trained], [Table Trained In1 MASTER].[Date Comp Trained], [Table Trained In1 MASTER].[Next Due Date], [Table Trained In1 MASTER].[Trained By]
FROM [Table Employee Listing MASTER] INNER JOIN [Table Trained In1 MASTER] ON [Table Employee Listing MASTER].[Clock No]=[Table Trained In1 MASTER].[Clock Number];

 

[raskew]

Hi -

Do you know the underlying source of your report?

If you're unsure, open the report in design view. Right-click at the very top of the report and select Properties, then Data. It will either list an SQL statement, e.g., SELECT ...., or the name of a query.

If it's a SELECT statement, right-click on it then select ZOOM. That will highlight the statement. Select Ctrl-C to copy it, then Ctrl-V to paste it into a response to this thread.

If it's the name of the query, open the query in design view, then select SQL view. Highlight the entire body of the query, then do the Ctrl-C/Ctrl-V business described above.

Bob

 

[Sharon1717]

REPORT INFO
SELECT [Table Employee Listing MASTER].ID, [Table Employee Listing MASTER].[First Name], [Table Employee Listing MASTER].Surname, [Table Employee Listing MASTER].[Clock No], [Table Employee Listing MASTER].Dept, [Table Employee Listing MASTER].Manager, [Table Trained In1 MASTER].[Trained In], [Table Trained In1 MASTER].[Date Start Trained], [Table Trained In1 MASTER].[Date Comp Trained], [Table Trained In1 MASTER].[Next Due Date], [Table Trained In1 MASTER].[Trained By] FROM [Table Employee Listing MASTER] INNER JOIN [Table Trained In1 MASTER] ON [Table Employee Listing MASTER].[Clock No]=[Table Trained In1 MASTER].[Clock Number];

QUERY INFO
SELECT [Table Employee Listing MASTER].Surname, [Table Employee Listing MASTER].[First Name], [Table Employee Listing MASTER].[Clock No], [Table Employee Listing MASTER].Dept, [Table Employee Listing MASTER].Manager, [Table Employee Listing MASTER].[Left Company], [Table Trained In1 MASTER].[Trained In], [Table Trained In1 MASTER].[Date Start Trained], [Table Trained In1 MASTER].[Date Comp Trained], [Table Trained In1 MASTER].[Next Due Date], [Table Trained In1 MASTER].[Trained By]
FROM [Table Employee Listing MASTER] INNER JOIN [Table Trained In1 MASTER] ON [Table Employee Listing MASTER].[Clock No] = [Table Trained In1 MASTER].[Clock Number];

 

[raskew]

Hi -
You are employing Inner Joins, which returns information only if there is matching data in the training table.

Inner join: Only include rows where the joined fields from both tables are equal.

Change: INNER JOIN to LEFT JOIN.

See if that corrects the problem.

Bob

 

[Sharon1717]

Hi Bob,

thanks for that I have managed to get the report to show all employees whether or not thay have training. I still have a problem with the duplicate surname for some reason. There should be six individual smiths and the report is only showing one Smith for some reason and pulled in the other Smiths "Trained In" bits.

I have attached the report.

Thanks for your help too it is much appreciated!

 

[raskew]

Hi -

Does the revised query (vs. the report) return the correct info?

Bob

 

[Sharon1717]

Yes the query and the report return the correct information but the report is not showing all Smiths for some reason, only showing one Smith but adding the other Smiths training info under the one Smith.

 

[maxmangion]

Hi,

by any chance did you make any grouping in your report to group by Surname as that might be the cause?

 

[Sharon1717]

Bob i have attached the query.

 

[Sharon1717]

by any chance did you make any grouping in your report to group by Surname as that might be the cause?

Yes i have grouped by Surname but if I remove this then the report does not show correctly the info I need which is by Surname.

 

[maxmangion]

Hi,

Try grouping by the ID field instead.

 

[Sharon1717]

That does work but on alltering fields its not showing the persons training details directly under the surname/name in a list, if you know what i mean....

I would like the report to look like the attached: EMPLOYEE FULL LISTING TEST.pdf (85.6 KB, 2 views)

 

[maxmangion]

Hi,

Sorry i didn't understand exactly what you mean, maybe if you attach a screenshot that may help. As regards your problem when group by surname you are grouping all people which have the same surname ... in fact in your scenario it happened only with Smith because it is the only surname that you have more than one person. In fact if you had to add another person with a surname which you already have, it will happen the same.

 

[Sharon1717]

Hi,

I have managed to group by Surname and First Name this seems to have solved the problem.

Is there anything that I could do to have "Surnames" to show "all duplicates" whould this then show duplicated surnames with different first names? which is what I want.....

 

[maxmangion]

I have managed to group by Surname and First Name this seems to have solved the problem.

Unfortunately that solved your problem temporarily ... as soon as you have a new person with the same name and surname as one which you already have, the problem will return.

From the excel sheet which you attached, it seems that the ID or clock no fields are the ideal candidates for the grouping. However, i still didn't understand the problem you're getting when you group by this field(s).

 

[Sharon1717]

I have attached the Grouped By ID report, as you can see its duplicating the Surname, First Name, Departments etc, but it has picked up all the Smiths so this report will pick up duplicate Surnames with different first names.

Is it possible to have the report display only one line of the duplicates?

 

[Sharon1717]

I have fixed it I think, you can hide duplicates in the Properties Section of the Text Box, its starting to look ok now.....

 

[maxmangion]

Hi,

Go to the design view of your report, then go the sorting/grouping and set to yes the "group header", then place your surname and name in the group header section of your report.