Such as the employee's attendance data:
Search those employees have late this month:
Select * from the user where the status='late'

Search has this month taken how to write?
I use the: select * from the user where status<> 'late' also displays has been late,

CodePudding user response:

Select * from [user] where status<> 'late'
I here the user may be a keyword, need to add brackets.
Perform didn't found the overdue will be shown.
I wrote a VBS script, you can look at:
Link: https://pan.baidu.com/s/1OXiZJXLW_JH9Kh3R98WHmQ
The extracted code: lhag

CodePudding user response:

Script code is as follows:

 Set conn=createobject (" adodb. Connection ") 
Conn. Open the provider="Microsoft. Jet. The OLEDB. 4.0; Data Source=d: \ \ my documents \ document database1. MDB 
"Set the rs=createobject (" adodb. You ") 
Rs. Open the "select * from [user] where status<> 'late' ", conn, 1, 3 
Rs. Movefirst 
While Not rs. Eof 
Msgbox rs (" status ") 
Rs. Movenext 
Wend 

Database:

Results:

CodePudding user response:

Id is different because of you, I said the same employees have 30 a month record, unless the 30 pen are not late, late for any
When the search for & lt;> 'late' the employee will not display

CodePudding user response:

reference QQ342151559 reply: 3/f

id is different because of you, I said the same employees have 30 a month record, unless the 30 pen are not late, late for any
When the search for & lt;> The employees' late 'don't show

This should be very simple, because there are 30 records, so you must have two tables, one is employee information table, one is the staff attendance sheet, employee name from the employee information table stored in an array, and then loop check each employee to have late, leave early, leave, absenteeism, and finally to display results is ok,

CodePudding user response:

reference 4 floor ZHRXJR response:

Quote: reference QQ342151559 reply: 3/f

Id is different because of you, I said the same employees have 30 a month record, unless the 30 pen are not late, late for any
When the search for & lt;> The employees' late 'don't show

This should be very simple, because there are 30 records, so you must have two tables, one is employee information table, one is the staff attendance sheet, employee name from the employee information table stored in an array, and then loop check each employee to have late, leave early, leave, absenteeism, and finally to display results,

If a large amount of data, the influence is very big, SQL statements can deal with this?

CodePudding user response:

Your test data is not a month? Added in the attendance sheet date field, according to the problem of daytime there is no special data, which is to query a years of data, each employee, which is less than 400 records, can't even use 500 milliseconds,

CodePudding user response:

Reply QQ342151559:
The design of the key problems is the data table, data table design is reasonable, the interface design is reasonable, the code is no problem, the program runs is very fast,

CodePudding user response:

Have to search for the 'late' data sets, and then the search is not in the data in a data set, the idea should be able to take, but how to write the

CodePudding user response:

Select the id from the user where id not in (select id from the user where the status='late')

CodePudding user response:

Search id is never late for the id of the data set, but has been in a circle, not as a result, the statement have what problem?

CodePudding user response:

According to your requirements and prompted to change the:
Code:

 Set conn=createobject (" adodb. Connection ") 
Conn. Open the provider="Microsoft. Jet. The OLEDB. 4.0; The Data Source=D: \ BaiduNetdiskDownload \ testsql \ database1 MDB 
"Set the rs=createobject (" adodb. You ") 
Rs. Open the "select distinct name from t1 where [user] (select count (name) from [user] t2 where t1. The name=t2. The name and status='late')=0", conn, 1, 3 
Rs. Movefirst 
While Not rs. Eof 
Msgbox rs (" name ") 
Rs. Movenext 
Wend 

Database:

Results:

Download address:
Link: https://pan.baidu.com/s/1OXiZJXLW_JH9Kh3R98WHmQ
The extracted code: lhag

CodePudding user response:

If is essentially a can try this:

 select distinct name from t1 where [user] not the contains (name, 'late'); 

CodePudding user response:

The

refer to 12 floor milaoshu1020 reply:

if is essentially a can try this:

 select distinct name from t1 where [user] not the contains (name, 'late'); 

Looks like I understand the meaning of the contains wrong, the contains of meaning in a field of a data full-text search, rather than in the whole data of a field of search keywords and returns a result.
So it can't use the contains, should use the above methods, I also is a subquery.

CodePudding user response:

If the table structure according to the pattern design, you should have a staff list, then you should call this form attendance list, and attendance records should have dates of attendance,
Determine the attendance, attendance days is equal to a certain time period should be normal, normal working day

 
Select the name, count (1) 
From the user 
Where attendance dates between (start date and end date) 
Group by name 
Having a count (1)=attendance dates within the scope of the working days