How do I combine first and last name to create a unique record?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Summing and Grouping data in a Report - 13 Jun 2006 11:30 GMT

I have created two tables, multiple queries and multiple associated reports.
When I currently run a report I have to differentiate last names (Smith1,
Smith2, etc) for my reports to collect data properly. If I could some how
combine both first and last name to create a unique identifier ( I do not
have a situation where both first and last names are the same) my reports
would print out Smith instead of Smith1.

I used the Access Wizard to reate my Tables, Queries and Reports and have an
extremely limited knowledge of SQL.

Any help would be greatly appreciated.

Reply to this Message

Tom Lake - 13 Jun 2006 11:48 GMT

>I have created two tables, multiple queries and multiple associated
>reports.
[quoted text clipped - 7 lines]
> an
> extremely limited knowledge of SQL.

In your table design, select both first and last name fields then click the
Primary Key
icon. The PK will be made up of both fields. Of course you'll have to
delete any
current PK and redefine your relationships.

Tom Lake

Reply to this Message

Douglas J Steele - 13 Jun 2006 12:20 GMT

> >I have created two tables, multiple queries and multiple associated
> >reports.
[quoted text clipped - 13 lines]
> delete any
> current PK and redefine your relationships.

Note, though, that at some point soon, you doubtlessly WILL have records
with the same first name and last name. You may want to think of something
else that you can use now, rather than waiting until then.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Reply to this Message

Ed Robichaud - 15 Jun 2006 13:31 GMT

Eventually, you will have 2 or more "Joe Smith"s, then 2 or more Joe Q
Smith"s, etc. Try adding an autonumber primary key.

>>I have created two tables, multiple queries and multiple associated
>>reports.
[quoted text clipped - 16 lines]
>
> Tom Lake

Reply to this Message

John Vinson - 13 Jun 2006 17:01 GMT

On Tue, 13 Jun 2006 03:30:01 -0700, Summing and Grouping data in a
Report <SummingandGroupingdatainaReport@discussions.microsoft.com>
wrote:

>If I could some how
>combine both first and last name to create a unique identifier ( I do not
>have a situation where both first and last names are the same)

And you can be *absolutely certain* that you never will?

Names *simply are not unique*. I know three gentlemen named Fred
Brown, and they all live in a small town.

Simply use a unique PersonID as the grouping field; then you can have
multiple Smiths, or even multiple Dave Smiths, without interfering
with your grouping.

John W. Vinson[MVP]

Reply to this Message