Append Criteria

[CORINEREYES]

Hi All,



Good day to everyone.

I hope you can help me with my issues with append criteria.

Okay, i have this database that contains employee details like employee number, name, position department and so on. I use Append Query to select some records and create a table for it. How do i select multiple employee numbers? What i have previously is selecting 1 record only, putting the [Enter Employee Number] in the query criteria, now i need to select n numbers of employee numbers to put in a table. Is there's any syntax to write or any method to do it?

Thanks in advance.

Corine

[Minty]

If the purpose is to create a list of EmpID's for use somewhere else - training courses etc. then you would normally use something like a multi select list box to pick the employees you need, and add the selected EmpID's to the training table.
Is this the sort of thing you are looking for?

[ArviLaanemets]

Insert Into YourTargetTable (TargetField1, TargetField2, ...)
SELECT SourceFiled1, SourceField2, ... FROM YourSourceTable
WHERE ConditionField IN (ConditionValue1, ConditionValue2, ConditionValue3, ...)

[CORINEREYES]

If the purpose is to create a list of EmpID's for use somewhere else - training courses etc. then you would normally use something like a multi select list box to pick the employees you need, and add the selected EmpID's to the training table.
Is this the sort of thing you are looking for?

Hi Minty,

Thank you for your quick response. Is there any way to select multiple employee numbers from a table using the query criteria as shown in my screenshot ? Is there a syntax to do it? or is it even possible?

Corine

[orange]

Corine,

Can you tell us in a few words the business process that you are trying to support with this set up? We needmore context, and there may be options.

[mike60smart]

HI
As Minty as suggested the best method is to use a Multi Select List.

[CORINEREYES]

Corine,

Can you tell us in a few words the business process that you are trying to support with this set up? We needmore context, and there may be options.

Hi orange,

Thank you for your reply. Ok we have around 5000++ employees, i use access to print most of the forms required to renew their contracts. These forms are sent to their departments for the employee to fill up. Each form for an employee requires a transmittal report (like attached) . How do i select n records from my the table 'Employee Details' using the append query criteria of 'STAFF ID' and display all the selected employees one transmittal report. Is that even possible in append query? Btw, data type for STAFF ID is numbers.

[Welshgasman]

I would use the IN() function.

[mike60smart]

Hi Corine

I would still go along with Minty's suggestion of a Multi Value List Box your selection of Staff Members will vary.

[Micron]

To be able to use IN in a query like that you'd need a parameter prompt where you'd have to enter 123, 558, 114, etc. I was going to say miss one comma and you're toast and Heaven forbid the empl numbers are text. However, I can get IN(4210,1236) to work but not if I type them into a parameter prompt.
Go with the form and listbox and don't allow users to dicker with queries and tables as a general rule.

[Minty]

A multi select list box will work but will be cumbersome with 5000 + records.

In these circumstances I would make a list box with a filter to get you to a more restricted list (Type in "Smith" and it would list all the Smith's for example).
I would have a second list that you would add the selected records to you can add and filter to your hearts content.
Once complete use that second list to drive the query.

I would probably store the selected items in a table called tb_ReportEmpIDs - simply join the report Empid to it, you can use it anywhere you need then.

You could go a stage further and store named lists and then edit them if they get used on a regular basis.
The same list box form would allow you to edit the existing lists.

There is a simple version of this type of thing here:
http://www.baldyweb.com/multiselect.htm

PBaldy's site.

[Micron]

Well, whaddayaknow? There is a way to use IN clause

WHERE ((InStr([enter nums],[myTable].[myField])>0));

as long as the id's are numbers separated by commas, the likes of 4210,1236 does work. That could be used in any fashion; e.g. parameter prompt from a query based form or a list of numbers in a form textbox (handy if you know the numbers) or whatever.

[xps35]

Ok we have around 5000++ employees, i use access to print most of the forms required to renew their contracts. These forms are sent to their departments for the employee to fill up.

I add two comments here.
First, the question why you should save the data again for this. Apparently all the data is already there.
In addition, you should ask yourself why you choose certain employees. Instead of a series of numbers, you may also be able to come up with criteria that determine whether an employee qualifies.

[ArviLaanemets]

With 5000+ employees you may try something like this:
Create a table e.g. tblEmployeeSelection with fields for STAFF_ID, and for as much of other employee info fields as you think you need, plus a field Selected (boolean, or smallint, or whatever). In case you have your database split to front- and backend, and there is a possibility for several users printing or otherwise processing data, then you must have this table in front-end of every user, otherwise you can have it in backend.

Whenever any user opens the database, all data from this table must be cleared, and then filled from employee details table - with Selected field set indicating that all records are not selected (FALSE, or 0, or whatever).

In front-end, you create a continuous form based on this table, where user(s) can mark employees as Selected (TRUE, 1, or whatever). It would be a good idea to have a button on form, which clears all selections when user clicks on it. And another button to refresh listed employees (and resetting field Selected too). You can also in header form have combo(s), where you can select filter condition(s) (e.g. department) for this form - setting the condition sets filter on the form (only employees matching the condition are displayed), and clears all selections from rows not displayed currently.

You create a report, with source like

In case you haven't all needed fields for report in tblEmployeeSelection:
SELECT ed.* FROM EmployeeDetails ed
WHERE ed.STAFF_ID IN (SELECT STAFF_ID FROM tblEmployeeSelection WHERE Selected = 0)

In case you have all needed fields for report in tblEmployeeSelection:
SELECT * FROM tblEmployeeSelection
WHERE  Selected = 0

The final step is to create a button on form which opens the report.

[orange]

Are all 5000+ employees' contracts renewed at the same time?
Do you have some sort of master list that identifies whose Contract needs to be renewed and when?

You have been given some ideas for identifying Employees requiring new Contract. Let us know what your solution is. It may be helpful to others.