Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Displaying First Letter (A2K)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying First Letter (A2K)

    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.

  3. #3
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying First Letter (A2K)

    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.
    Attached Images Attached Images

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying First Letter (A2K)

    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.
    Attached Files Attached Files

  5. #5
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying First Letter (A2K)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Displaying First Letter (A2K)

    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?

  7. #7
    Star Lounger
    Join Date
    Jan 2003
    Location
    Eau Claire, Wisconsin, USA
    Posts
    68
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Displaying First Letter (A2K)

    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. <img src=/S/bullseye.gif border=0 alt=bullseye width=45 height=15>
    Again, thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •