Displaying First Letter (A2K)

Topic

New Reply

tblRecruit has four fields: FName, LName, MiddleInitial, RecruitSSN. All four fields are text. MiddleInitial is size 1, Recruit SSN is size 9.

A report shows LName, FName. Now the user wants to complicate matters by eliminating the first name on this report. He only wants the last name displayed – unless two people have the same last name. Then he wants to display last name, first initial. middle initial. for just the people with the same last names.

for example:

Baker
Cooper, I. M.
Cooper, B. A.
Davis

To make matters worse, he wants to display the last four digits of the RecruitSSN for those people who happen to have the same last name and the same first two initials.

for example

Baker
Cooper, I. M.
Cooper, B. A., 1324
Cooper, B. A., 2345
Davis

There are other reports that use the entire first name and SSN. So I do not want to remove the data, just what is displayed. Any and all assistance dealing with this problem will be greatly appreciated.

Reply | Quote

Replies

This is going to take a series of queries:

1. A query based on tblRecruit that adds the initials as a calculated field. The SQL for this query is

SELECT *, Left([FName],1) & “. ” & [MiddleInitial] & “.” As Initials FROM tblRecruit

Save this query as qryRecruits.

2. A query based on qryRecruits that returns all recruits that match on last name AND on initials. This query can’t be displayed in design view, only in SQL view and in datasheet view. The SQL is:

SELECT qryRecruits.*, [qryRecruits].[LName] & “, ” & [qryRecruits].[Initials] & “, ” & Right([qryRecruits].[RecruitSSN],4) AS DisplayName
FROM qryRecruits INNER JOIN qryRecruits AS qryRecruits_1 ON (qryRecruits.LName = qryRecruits_1.LName) AND (qryRecruits.RecruitSSN qryRecruits_1.RecruitSSN) AND (qryRecruits.Initials = qryRecruits_1.Initials)

Save this query as qryMatchBoth.

3. A query based on qryRecruits and qryMatchBoth that returns all recruits that match on last name only. This query can’t be displayed in design view, only in SQL view and in datasheet view. The SQL is:

SELECT DISTINCT qryRecruits.*, [qryRecruits].[LName] & “, ” & [qryRecruits].[Initials] AS DisplayName
FROM (qryRecruits INNER JOIN qryRecruits AS qryRecruits_1 ON (qryRecruits.Initials qryRecruits_1.Initials) AND (qryRecruits.RecruitSSN qryRecruits_1.RecruitSSN) AND (qryRecruits.LName = qryRecruits_1.LName)) LEFT JOIN qryMatchBoth ON qryRecruits.RecruitSSN = qryMatchBoth.RecruitSSN
WHERE qryMatchBoth.RecruitSSN Is Null

Save this query as qryMatchLast.

4. A query based on qryRecruits that returns all recruits with unique last names. The SQL for this query is

SELECT *, LName AS DisplayName
FROM qryRecruits
WHERE LName In (SELECT [LName] FROM [qryRecruits] As Tmp GROUP BY [LName] HAVING Count(*)=1)

Save this query as qryUnique.

5. A union query that combines the results of the previous queries. This query can’t be displayed in design view, only in SQL view and in datasheet view. The SQL is:

SELECT * FROM qryUnique
UNION
SELECT * FROM qryMatchLast
UNION
SELECT * FROM qryMatchBoth

Save this query and use it as record source for the report.

Reply | Quote

Hello, Hans.
Thanks for replying.

I created the queries as you said by copying your sql statements and pasting them into new queries. (You didn’t tell me what to name the union query, so I named it qryUniqueNamesForReport.) When I tried to open the union query, I got the following error message: “The number of columns in the two selected tables or queries of a union query do not match.”

Since all the fields in tblRecruit are not required, I created another query selecting only the fields needed (FName, LName, MiddleInitial, RecruitSSN, and ASMOOut (this is a yes/no field which is irrelevant right now – it will come into play later)) and then based qryRecruits on that query (qryRecruitNamesForWQSB.) I removed the * and placed only the required fields. I still got the same error message. Looking at the various queries, I noticed that qryMatchBoth and qryMatchLast had six fields (the extra one called DisplayName) whereas qryRecruits only has five (ASMOOut is not displayed – Initials is the fifth field.) I figured Initials was not need in qryMatchBoth and qryMatchLast since the new field was DisplayName so I removed the * and replaced it with the other four fields. BTW, all the queries (except the union query) work great – they display perfectly. (I am wondering about qryUnique, though. It shows Initials whereas qryMatchLast and qryMatchBoth show DisplayName. Do the column names have to match in all three queries for the union query to work?)

Now I’m in trouble: I no longer get the previous error message when I try to open the union query. It APPEARS that nothing happens at all – until I go to close a form and then I get the attached error. Clicking “Yes” has no effect. I have to open task manager and shut down Access to recover.

Obviously, I don’t know what I’m doing so now I am once again asking for your assistance.
Thanks.

Reply | Quote

1. You must have made a mistake in one of the queries. I actually created a table tblRecruits and the queries when trying out things for my previous reply, and it works correctly. qryRecruits should display all fields from tblRecruits, plus the new field Initials. All the other queries should display all fields from tblRecruits, plus Initials and DisplayName.

2. The error message you get now probably has a different cause, since the queries don’t involve VBA code at all.

I have attached a zipped version of my test database.

Reply | Quote

Well, I’ll be a monkey’s uncle! You’re right – I must have made a mistake somewhere but I can’t find it. Your attachment works like a charm, though. I used another bit of code I learned here in the Lounge in order to eliminate the extra period for those people who do not have a middle initial: ([MiddleInitial]+”.”)

Again, I appreciate the time you spend helping me.
Thanks.

Reply | Quote

What happens if you
– Make a copy of your own database.
– Delete the queries you created yourself based on this thread.
– Import the queries from the database I attached; you can rename the Union query to the name you used.
Does it work OK then?

Reply | Quote

Yep, it sure does. I had already done that before I replied back to your post with the attachment. The only thing I can think of is I must have inserted extra spaces or something somewhere. But all that really matters is that you got me back on the straight and narrow.
Again, thanks.

Reply | Quote

Yep, it sure does. I had already done that before I replied back to your post with the attachment. The only thing I can think of is I must have inserted extra spaces or something somewhere. But all that really matters is that you got me back on the straight and narrow.
Again, thanks.

Reply | Quote

What happens if you
– Make a copy of your own database.
– Delete the queries you created yourself based on this thread.
– Import the queries from the database I attached; you can rename the Union query to the name you used.
Does it work OK then?

Reply | Quote

Well, I’ll be a monkey’s uncle! You’re right – I must have made a mistake somewhere but I can’t find it. Your attachment works like a charm, though. I used another bit of code I learned here in the Lounge in order to eliminate the extra period for those people who do not have a middle initial: ([MiddleInitial]+”.”)

Again, I appreciate the time you spend helping me.
Thanks.

Reply | Quote

1. You must have made a mistake in one of the queries. I actually created a table tblRecruits and the queries when trying out things for my previous reply, and it works correctly. qryRecruits should display all fields from tblRecruits, plus the new field Initials. All the other queries should display all fields from tblRecruits, plus Initials and DisplayName.

2. The error message you get now probably has a different cause, since the queries don’t involve VBA code at all.

I have attached a zipped version of my test database.

Reply | Quote

Hello, Hans.
Thanks for replying.

I created the queries as you said by copying your sql statements and pasting them into new queries. (You didn’t tell me what to name the union query, so I named it qryUniqueNamesForReport.) When I tried to open the union query, I got the following error message: “The number of columns in the two selected tables or queries of a union query do not match.”

Since all the fields in tblRecruit are not required, I created another query selecting only the fields needed (FName, LName, MiddleInitial, RecruitSSN, and ASMOOut (this is a yes/no field which is irrelevant right now – it will come into play later)) and then based qryRecruits on that query (qryRecruitNamesForWQSB.) I removed the * and placed only the required fields. I still got the same error message. Looking at the various queries, I noticed that qryMatchBoth and qryMatchLast had six fields (the extra one called DisplayName) whereas qryRecruits only has five (ASMOOut is not displayed – Initials is the fifth field.) I figured Initials was not need in qryMatchBoth and qryMatchLast since the new field was DisplayName so I removed the * and replaced it with the other four fields. BTW, all the queries (except the union query) work great – they display perfectly. (I am wondering about qryUnique, though. It shows Initials whereas qryMatchLast and qryMatchBoth show DisplayName. Do the column names have to match in all three queries for the union query to work?)

Now I’m in trouble: I no longer get the previous error message when I try to open the union query. It APPEARS that nothing happens at all – until I go to close a form and then I get the attached error. Clicking “Yes” has no effect. I have to open task manager and shut down Access to recover.

Obviously, I don’t know what I’m doing so now I am once again asking for your assistance.
Thanks.

Reply | Quote

This is going to take a series of queries:

1. A query based on tblRecruit that adds the initials as a calculated field. The SQL for this query is

SELECT *, Left([FName],1) & “. ” & [MiddleInitial] & “.” As Initials FROM tblRecruit

Save this query as qryRecruits.

2. A query based on qryRecruits that returns all recruits that match on last name AND on initials. This query can’t be displayed in design view, only in SQL view and in datasheet view. The SQL is:

SELECT qryRecruits.*, [qryRecruits].[LName] & “, ” & [qryRecruits].[Initials] & “, ” & Right([qryRecruits].[RecruitSSN],4) AS DisplayName
FROM qryRecruits INNER JOIN qryRecruits AS qryRecruits_1 ON (qryRecruits.LName = qryRecruits_1.LName) AND (qryRecruits.RecruitSSN qryRecruits_1.RecruitSSN) AND (qryRecruits.Initials = qryRecruits_1.Initials)

Save this query as qryMatchBoth.

3. A query based on qryRecruits and qryMatchBoth that returns all recruits that match on last name only. This query can’t be displayed in design view, only in SQL view and in datasheet view. The SQL is:

SELECT DISTINCT qryRecruits.*, [qryRecruits].[LName] & “, ” & [qryRecruits].[Initials] AS DisplayName
FROM (qryRecruits INNER JOIN qryRecruits AS qryRecruits_1 ON (qryRecruits.Initials qryRecruits_1.Initials) AND (qryRecruits.RecruitSSN qryRecruits_1.RecruitSSN) AND (qryRecruits.LName = qryRecruits_1.LName)) LEFT JOIN qryMatchBoth ON qryRecruits.RecruitSSN = qryMatchBoth.RecruitSSN
WHERE qryMatchBoth.RecruitSSN Is Null

Save this query as qryMatchLast.

4. A query based on qryRecruits that returns all recruits with unique last names. The SQL for this query is

SELECT *, LName AS DisplayName
FROM qryRecruits
WHERE LName In (SELECT [LName] FROM [qryRecruits] As Tmp GROUP BY [LName] HAVING Count(*)=1)

Save this query as qryUnique.

5. A union query that combines the results of the previous queries. This query can’t be displayed in design view, only in SQL view and in datasheet view. The SQL is:

SELECT * FROM qryUnique
UNION
SELECT * FROM qryMatchLast
UNION
SELECT * FROM qryMatchBoth

Save this query and use it as record source for the report.

Reply | Quote