Confused data in report...

[palesa]

Can anyone help, please?

I have a report that is used as an invoice and it works fine until I ask it to give me the results of a group of people and there are two or more of them with the same name. When this happens, only one person with that name comes up and the data is incorrect - it will get an address from one of the other people with the same name. I had three Janes - only one came up with someone else's address - the other two Janes didn't even show. I also had two Smiths - one Jane and one Janet - and they also became confused. I have fields 'Title', 'Name' and 'Surname' in the database and query. On the report I have =[Title]&" "&[Name]&" "&[Surname] , which works on all other entries.

The query that the report is based on shows the correct information. It's only when I open the report that it becomes confused.

How can I get it to give me the correct information when this happens? Does anyone have any advice, please? I have tried for hours to sort this out but am getting nowhere fast! Suggestions will be greatly appreciated.

 

[ScottGem]

This indicates you have your database contructed incorrectly. Each record should have a primary key (a unique value that identifies the record). For Access its recommend that you use an Autonumber field to create the primary key. You then do your grouping on the Primary key, not a field like name that can have duplicates.

 

[palesa]

Thanks for responding. I have used a Primary Key - my CID (Customer ID) field is the primary key. I forgot to mention that I have this field in my query too. The CID is correct in all cases, except for when I have duplicate names.

 

[ScottGem]

Ok, then you group on your CID, not on names. You can add the name fields into your group header, but the grouping should be on CID.

 

[palesa]

Thank you, Scott. I was concentrating on the field instead of the header. It is sorted now! Much appreciated.

 

[ScottGem]

glad to assist