Should be simple

[kevmc]

I am running a Query from a Table which contains info about employees. I want to create mailing labels, but I get multiple records containing employee names ie a lot of Smiths, and I only want A.Smith. So I tried using a Not operator in the ID field. But used it with the Or operator eg Not"1"Or"2"Or"3" etc. When I run the Query it only omits the first number.

I know it's probably something really simple, any ideas.

Thanks

kevmc

 

[dcx693]

If you only want the first instance of a last name with it's associated first initial, use a totals query and specify "First" as the total on the last name field.

 

[kevmc]

It's not necessarilly the first instance of a surname, it may be the middle one of three for example, I just need to extract the surname with the corresponding first name. I can't put a first name in the first name column as this would omit other records. The ID field is the key field, that's why I chose to use that field to filter the query.

Thanks

kevmc

 

[Rich]

Why not use a multiselect list box to select the actual employees you wish to mail? You'll then have the correct ID

 

[kevmc]

All I wanted to do was run a Query from the Employees table, then create mailing labels. If you put Smith in as a name to be returned, it returns multiple Smiths. I want to extract the correct Smith. I don't really know how the multiselect list box would work, apart from it would have to be in a Form. The Query seems the best option, I just need to know how to extract the data required.

Thanks

kevmc

 

[Knowbody]

I'm still a little new at this myself but I wanted to toss this idea out to see if it might work for you.

I would assume you have a form of some sort to see/enter the employee information easily. Why not design the query to be run on the primary key of the specific record? Each record should have some unique identifier. If you are looking at the form with that record up, then you should be able to print the mailing label that way. I think I did one of those a few years back with success. I'll have to check my old PC. It would be the same as printing an invoice so you may find something useful in the Northwind sample database or even one of the other Access templates. The code could then be modified to print your mailing label instead of their invoice.

Does that make sense to anyone else, or do I seem way off?

Erich

 

[dcx693]

I tried this out on a data table with some sample data. I created a totals query where I used Group By on the Last name field and Min on the First name to get the first alphabetical instance of the first name. No sorting necessary.

Using "First" for the Total: line gets the first instance of the first name, which as you pointed out, is not necessarily the first name that occurs earliest in the alphabet.

 

[kevmc]

First of all I would like to thank you all for your help. I may have confused the issue a little, I will try to clarify.

I am entering more than one name in the Last name column of the Query eg: "Robinson" Or "Brown" Or "Smith". So I am getting a table with eg 3xRobinson, 2xBrown and 4x Smith.
What I need to do is extract the correct Robinson, Brown and Smith from table I have created with the Query, to then create mailing labels. I have tried doing this by entering, Not "ID" Or "ID" Or "ID", ID being the ID number for each employee, in the ID coumn of the Query. This will return a table with the first ID omitted but not the rest.
I hope this explains things a little, and apologise for any misunderstanding.

Thanks again to you all.

kevmc

 

[dcx693]

What I need to do is extract the correct Robinson, Brown and Smith from table I have created with the Query, to then create mailing labels.

OK, but how do you indentify the correct one? You need to enter their ID number?

Well, if that's all, then you need a basic normal select query. Under the Lastname field, enter In("Robinson","Smith","Brown") in the criteria line to get all employees with those last names. To omit certain members of that, under the ID field enter Not In(xx,xxx,xxxx) to omit certain known ID numbers. Don't enclose those xx numbers in quotes if they are stored in the database as numbers.

 

[Pat Hartman]

kevmc, your logic escapes me. Why would you enter a series of last names and then enter a series of Not ID's? It would be far simpler to use a form with a combo so you can select specific ID's.

 

[Rich]

Or even a multiselect list box.

 

[kevmc]

dcx693, thanks for that it worked a treat. Sorry for being so stupid.

Pat & Rich, I have also tried your ways but, creating the combo and list boxes is ok, I just don't know what to do from there to create mailing labels. I would be interested to know.

Thanks to you all.

kevmc

 

[Pat Hartman]

Here's a function that I use to build the criteria string. You'll need to concatinate the output of the function into the SQL string. In my case, I needed to limit the number of selections because the SQL was going to build a crosstab to feed a report and only 8 columns would fit across the report line:

Public Function fMultiSelect(ctlRef As ListBox) As Variant
    Dim Criteria As String
    Dim i As Variant

   ' Build criteria string from selected items in list box.
    Criteria = ""
    For Each i In ctlRef.ItemsSelected
        If i > 8 Then
            MsgBox "Only first 9 selections were accepted.  Excess discarded.", vbOKOnly
            fMultiSelect = Criteria
            Exit Function
        End If
        If Criteria <> "" Then
            Criteria = Criteria & ","
        End If
        Criteria = Criteria & Format(ctlRef.ItemData(i), "0000000")
    Next i

fMultiSelect = Criteria
End Function