1, the workday overtime
The program code:
Adodc2. The ConnectionString="Provider=Microsoft. Jet. The OLEDB. 4.0. Data Source="+ App. Path & amp; "\ attendance. MDB. Persist Security Info=False "
Adodc2. RecordSource="SELECT the serial number, name, SUM (small) overtime as workday overtime FROM attendance WHERE (right (week 1) & lt;> '6' and right (week 1) & lt;> ', ') GROUP BY serial number, the name "
Adodc2. Refresh
The Set DataGrid2. The DataSource=Adodc2
2, weekends
Adodc2. The ConnectionString="Provider=Microsoft. Jet. The OLEDB. 4.0. Data Source="+ App. Path & amp; "\ attendance. MDB. Persist Security Info=False "
Adodc2. RecordSource="SELECT the serial number, name, SUM (small) overtime work overtime as double cease FROM attendance WHERE right (week 1)='6' or right (week 1)=', 'GROUP BY serial number, the name"
Adodc2. Refresh
The Set DataGrid2. The DataSource=Adodc2
3, TAB
Adodc2. The ConnectionString="Provider=Microsoft. Jet. The OLEDB. 4.0. Data Source="+ App. Path & amp; "\ attendance. MDB. Persist Security Info=False "
Adodc2. RecordSource="SELECT the serial number, name, SUM (small) overtime work overtime as subtotal, SUM as a vacation (holiday), SUM (morning time) as the early time, the SUM (GSM) as a student of time, the SUM (night time) as the FROM night shift time attendance GROUP BY serial number, the name"
Adodc2. Refresh
The Set DataGrid2. The DataSource=Adodc2
Now, want to send this several synthesis table table, as shown in the figure below (sorry, it is drawing patchwork) :
could you please tell me, what kind of statement, how to write, to merge the above this table?
Or, how to write the above statements consolidation, generated directly above this table?
Appealed to the webmaster and passing love spare some time to help me in the busy, thank you!
CodePudding user response:
Appealed to the webmaster and passing love spare some time to help me in the busy, thank you!CodePudding user response:
Your three tables using the same data tables, and working days work overtime, weekends conditional statements have conflict, so want to use the original data table to generate a table I'm afraid I can't,Since the ability to generate working days work overtime, weekends, and statistics, will generate the three tables are merged into a data table, to solve the problem,
CodePudding user response:
Only the SQL statement, don't know conform to comply with your request, there is a question that what is the serial number you? Similar to the job number?SELECT c. serial number, c. name, total overtime, holiday together, early morning combined middle shift aggregate, working days work overtime, double cease to work overtime, night together,
The FROM (
Name, serial number, SELECT a. a. overtime together and leave together, early morning combined combined middle shift, night shift aggregate, workday overtime
The FROM (SELECT the serial number, name, SUM (small) overtime work overtime AS aggregate, the SUM (leave) AS A vacation together, the SUM (morning time) AS an early combined, the SUM (GSM) combined AS A student, the SUM (night time) AS aggregate FROM night shift attendance GROUP BY serial number, name) AS A LEFT JOIN (SELECT the serial number, name, SUM (small) overtime AS workday overtime FROM attendance WHERE (right (week 1) & lt;> '6' and right (week 1) & lt;> ', ') GROUP BY serial number, name) AS ON B (a. number=b. serial number) AND (name of a. name=B.)
) AS C
LEFT the JOIN (SELECT the serial number, name, SUM (small) overtime work overtime AS double cease FROM attendance WHERE right (week 1)='6' or right (week 1)=', 'GROUP BY serial number, name) AS D ON (C) serial number=D) serial number) AND (C) name=D) name)
CodePudding user response:
SELECT c. serial number, c. name, total overtime, holiday together, early morning combined middle shift aggregate, working days work overtime, double cease to work overtime, night together,
The FROM (
Name, serial number, SELECT a. a. overtime together and leave together, early morning combined combined middle shift, night shift aggregate, workday overtime
The FROM (SELECT the serial number, name, SUM (small) overtime work overtime AS aggregate, the SUM (leave) AS A vacation together, the SUM (morning time) AS an early combined, the SUM (GSM) combined AS A student, the SUM (night time) AS aggregate FROM night shift attendance GROUP BY serial number, name) AS A LEFT JOIN (SELECT the serial number, name, SUM (small) overtime AS workday overtime FROM attendance WHERE (right (week 1) & lt;> '6' and right (week 1) & lt;> ', ') GROUP BY serial number, name) AS ON B (a. number=b. serial number) AND (name of a. name=B.)
) AS C
LEFT the JOIN (SELECT the serial number, name, SUM (small) overtime work overtime AS double cease FROM attendance WHERE right (week 1)='6' or right (week 1)=', 'GROUP BY serial number, name) AS D ON (C) serial number=D) serial number) AND (C) name=D) name)
I have already implemented, is not an error