Separating data & Sorting: is it possible?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, I Have created a basic database consisting of three fields: Name of
business, Representative and Attending, in which I have run various queries,
report etc.

In the Representative field has the first name and last name of a person. Is
it possible to run a report where I can sort A to Z on the last name in the
Rep field?

Hence the data is entered i.e. John Smith. How can I sort the field by only
Last name?
 
If your "Representative" field holds a "full name", your data needs a bit
more normalization. As you have already found, it is harder to separate
multiple facts (firstname, middlename, lastname) than it would be to use one
field for one fact.

If you are ABSOLUTELY certain that your Representative field will ALWAYS
hold "firstname-space-lastname", you can use the Instr() and Mid() functions
to derive the "lastname" in a query.

If there is ANY chance someone has entered "lastname-comma-space-firstname",
you'll have to take a different approach, adding in the IIF() function to
test for the comma.

And if the field holds other variations (or other data, as in a "contact's
phone number"), you'll need to rely totally on USB (using someone's brain).

I recommend you take the time now to create new fields to hold the
individual data elements, rather than having to continually work around the
less-than-optimal design.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Hi, I Have created a basic database consisting of three fields: Name of
business, Representative and Attending, in which I have run various queries,
report etc.

In the Representative field has the first name and last name of a person. Is
it possible to run a report where I can sort A to Z on the last name in the
Rep field?

Hence the data is entered i.e. John Smith. How can I sort the field by only
Last name?
Click to expand...

If all of the names are exactly FirstName LastName, then you can use,
in a query....
LastName:Mid([FullName],Instr([FullName]," ")+1)
If you are viewing the query, sort on this column.
Use the query as record source for your report.

In your report, you would use the report's Sorting and Grouping dialog
to sort on this new field.

However, what is the last name of someone named "Mary Lou van der
Meer"? How would Access know?

I would strongly suggest that you properly normalize your database,
and have separate fields for FirstName and LastName. It's simple to
combine them, much more complicated to separate them.
 
Not to mention problems with names like
Mary Anne Smith.

Mary Anne is the first name and it does have space in it - and don't ever
call her Mary.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top