从其他表中使用SQL在Access中获取范围值并在VB.NET中将其显示在DataGridView中。

huangapple
go评论68阅读模式
英文:

How to get range values ​from other tables in access with Sql to datagridview in vb.net

问题

如何使用SQL从其他表中获取Access中的范围值，并在VB.Net中将其显示在DataGridView中？

是否可以从工资决策表中获取一定范围的值，或者我必须更改工资决策表中的字段，请指导我。

是否有其他解决方案？

以下是您提供的代码部分，已经过翻译：

Public Sub jokenselect(ByVal sql As String)
    Try
        con.Open()
        With cmd
            .Connection = con
            .CommandText = sql
        End With

    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
    End Try
    con.Close()
    da.Dispose()
End Sub

Private Sub RetrieveData()
    jokenselect("
SELECT 
EC.EMPID
, E.NAME
, format(EC.SKILL/(select MIN(MINSKILL.SKILL) from tblEmployeeCriteria MINSKILL)*100*(select WCSKILL/100 from tblWeightingCriteria),'#,##0.00') as SKILL
, format(EC.EXPERIENCE/(select MIN(MINSKILL.EXPERIENCE) from tblEmployeeCriteria MINSKILL)*100*(select WCEXPERIENCE/100 from tblWeightingCriteria),'#,##0.00') as EXPERIENCE
, format(EC.APPEARANCE/(select MIN(MINSKILL.APPEARANCE) from tblEmployeeCriteria MINSKILL)*100*(select WCAPPEARANCE/100 from tblWeightingCriteria),'#,##0.00') as APPEARANCE
, format(EC.EDUCATION/(select MIN(MINSKILL.EDUCATION) from tblEmployeeCriteria MINSKILL)*100*(select WCEDUCATION/100 from tblWeightingCriteria),'#,##0.00') as EDUCATION
, format(EC.SKILL/(select MIN(MINSKILL.SKILL) from tblEmployeeCriteria MINSKILL)*100*(select WCSKILL/100 from tblWeightingCriteria)+EC.EXPERIENCE/(select MIN(MINSKILL.EXPERIENCE) from tblEmployeeCriteria MINSKILL)*100*(select WCEXPERIENCE/100 from tblWeightingCriteria)+EC.APPEARANCE/(select MIN(MINSKILL.APPEARANCE) from tblEmployeeCriteria MINSKILL)*100*(select WCAPPEARANCE/100 from tblWeightingCriteria)+EC.EDUCATION/(select MIN(MINSKILL.EDUCATION) from tblEmployeeCriteria MINSKILL)*100*(select WCEDUCATION/100 from tblWeightingCriteria),'#,##0.00') as TOTAL CPI 
FROM tblEmployeeCriteria EC 
INNER JOIN tblemployee E ON EC.EMPID = E.EMPID 
ORDER BY 7 DESC")
    filltable(DataGridView2, "EmpPic")
    lbltotalemployee.Text = DataGridView1.RowCount
End Sub

您提供的SQL代码的结果如下：

名称 技能 经验 外貌 教育 总CPI
测试1 145.80 83.91 109.80 129.60 469.11
测试2 121.50 109.73 85.40 113.40 430.03

工资决策表（tblPayrollDecision）如下：

CPI范围 工资范围
451-470 11,000,000 - 12,000,000
431-450 10,000,000 - 11,000,000
411-430 8,000,000 - 10,000,000

以下是所需的SQL代码结果，但未更新为来自tblPayrollDecision的"RangeSalary"：

 jokenselect("
SELECT
EC.EMPID
,E.[NAME]
,format(EC.SKILL/(select MIN(MINSKILL.SKILL) from tblEmployeeCriteria MINSKILL)*100*(select WCSKILL/100 from tblWeightingCriteria)+EC.EXPERIENCE/(select MIN(MINSKILL.EXPERIENCE) from tblEmployeeCriteria MINSKILL)*100*(select WCEXPERIENCE/100 from tblWeightingCriteria)+EC.APPEARANCE/(select MIN(MINSKILL.APPEARANCE) from tblEmployeeCriteria MINSKILL)*100*(select WCAPPEARANCE/100 from tblWeightingCriteria)+EC.EDUCATION/(select MIN(MINSKILL.EDUCATION) from tblEmployeeCriteria MINSKILL)*100*(select WCEDUCATION/100 from tblWeightingCriteria),'#,##0.00') as TOTAL
FROM tblEmployeeCriteria EC 
INNER JOIN tblemployee E ON EC.EMPID = E.EMPID
ORDER BY 3 DESC")

所需结果如下：

名称 总CPI 工资范围
测试1 469.11 11,000,000 - 12,000,000
测试2 430.03 10,000,000 - 11,000,000

根据 @june17 的要求，我提供了原始数据。

tblemployee 表如下：

EMPID 名称
10037 Tamendran
10046 Ponisan
10049 Sudio

tblEmployeeCriteria 表如下：

EMPID 技能 经验 外貌 教育
10037 90 65 90 80
10046 85 75 70 85
10049 75 85 70 70
10085 50 55 50 50

tblWeightingCriteria 表如下：

WCSKILL WCEXPERIENCE WCAPPEARANCE WCEDUCATION
81 71 61 81 
jokenselect("
SELECT
EC.EMPID
,E.[NAME]
,format(EC.SKILL/(select MIN(MINSKILL.SKILL) from tblEmployeeCriteria MINSKILL)*100*(select WCSKILL/100 from tblWeightingCriteria)+EC.EXPERIENCE/(select MIN(MINSKILL.EXPERIENCE) from tblEmployeeCriteria MINSKILL)*100*(select WCEXPERIENCE/100 from tblWeightingCriteria)+EC.APPEARANCE

<details>
<summary>英文:</summary>

How to get range values ​​from other tables in access with SQL to DataGridView in VB.Net?

Is it possible to get a range of values ​​from the payroll decision table or I have to change the fields from the payroll decision table, please guide me.

Is there any other solution?

```vb.net
Public Sub jokenselect(ByVal sql As String)
    Try
        con.Open()
        With cmd
            .Connection = con
            .CommandText = sql
        End With

    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Exclamation)
    End Try
    con.Close()
    da.Dispose()
End Sub

Private Sub RetrieveData()
    jokenselect(&quot;
SELECT 
EC.EMPID
, E.NAME
, format(EC.SKILL/(select MIN(MINSKILL.SKILL) from tblEmployeeCriteria MINSKILL)*100*(select WCSKILL/100 from tblWeightingCriteria),&#39;#,##0.00&#39;) as SKILL
, format(EC.EXPERIENCE/(select MIN(MINSKILL.EXPERIENCE) from tblEmployeeCriteria MINSKILL)*100*(select WCEXPERIENCE/100 from tblWeightingCriteria),&#39;#,##0.00&#39;) as EXPERIENCE
, format(EC.APPEARANCE/(select MIN(MINSKILL.APPEARANCE) from tblEmployeeCriteria MINSKILL)*100*(select WCAPPEARANCE/100 from tblWeightingCriteria),&#39;#,##0.00&#39;) as APPEARANCE
, format(EC.EDUCATION/(select MIN(MINSKILL.EDUCATION) from tblEmployeeCriteria MINSKILL)*100*(select WCEDUCATION/100 from tblWeightingCriteria),&#39;#,##0.00&#39;) as EDUCATION
, format(EC.SKILL/(select MIN(MINSKILL.SKILL) from tblEmployeeCriteria MINSKILL)*100*(select WCSKILL/100 from tblWeightingCriteria)+EC.EXPERIENCE/(select MIN(MINSKILL.EXPERIENCE) from tblEmployeeCriteria MINSKILL)*100*(select WCEXPERIENCE/100 from tblWeightingCriteria)+EC.APPEARANCE/(select MIN(MINSKILL.APPEARANCE) from tblEmployeeCriteria MINSKILL)*100*(select WCAPPEARANCE/100 from tblWeightingCriteria)+EC.EDUCATION/(select MIN(MINSKILL.EDUCATION) from tblEmployeeCriteria MINSKILL)*100*(select WCEDUCATION/100 from tblWeightingCriteria),&#39;#,##0.00&#39;) as TOTAL CPI 
FROM tblEmployeeCriteria EC 
INNER JOIN tblemployee E ON EC.EMPID = E.EMPID 
ORDER BY 7 DESC&quot;)
    filltable(DataGridView2, &quot;EmpPic&quot;)
    lbltotalemployee.Text = DataGridView1.RowCount
End Sub

The result of the sql code that I posted

NAME SKILL EXPERIENCE APPEARANCE EDUCATION TOTAL CPI
TEST1 145.80 83.91 109.80 129.60 469.11
TEST2 121.50 109.73 85.40 113.40 430.03

Payroll decision table (tblPayrollDecision)

RANGECPI RANGESALARY
451-470 11000000 - 12000000
431-450 10000000 - 11000000
411-430 8000000 - 10000000

the desired sql code results but not updated with "RangeSalary" from tblPayrollDecision

 jokenselect(&quot;
SELECT
EC.EMPID
,E.[NAME]
,format(EC.SKILL/(select MIN(MINSKILL.SKILL) from tblEmployeeCriteria MINSKILL)*100*(select WCSKILL/100 from tblWeightingCriteria)+EC.EXPERIENCE/(select MIN(MINSKILL.EXPERIENCE) from tblEmployeeCriteria MINSKILL)*100*(select WCEXPERIENCE/100 from tblWeightingCriteria)+EC.APPEARANCE/(select MIN(MINSKILL.APPEARANCE) from tblEmployeeCriteria MINSKILL)*100*(select WCAPPEARANCE/100 from tblWeightingCriteria)+EC.EDUCATION/(select MIN(MINSKILL.EDUCATION) from tblEmployeeCriteria MINSKILL)*100*(select WCEDUCATION/100 from tblWeightingCriteria),&#39;#,##0.00&#39;) as TOTAL
FROM tblEmployeeCriteria EC 
INNER JOIN tblemployee E ON EC.EMPID = E.EMPID
ORDER BY 3 DESC&quot;)

Desired Result

NAME TOTAL CPI RANGESALARY
TEST1 469.11 11000000 - 12000000
TEST2 430.03 10000000 - 11000000

According to the request from @june17, I provide raw data

tblemployee

EMPID NAME
10037 Tamendran
10046 Ponisan
10049 Sudio

tblEmployeeCriteria

EMPID SKILL EXPERIENCE APPEARANCE EDUCATION
10037 90 65 90 80
10046 85 75 70 85
10049 75 85 70 70
10085 50 55 50 50

tblWeightingCriteria

WCSKILL WCEXPERIENCE WCAPPEARANCE WCEDUCATION
81 71 61 81 
        jokenselect(&quot;
SELECT
EC.EMPID
,E.[NAME]
,format(EC.SKILL/(select MIN(MINSKILL.SKILL) from tblEmployeeCriteria MINSKILL)*100*(select WCSKILL/100 from tblWeightingCriteria)+EC.EXPERIENCE/(select MIN(MINSKILL.EXPERIENCE) from tblEmployeeCriteria MINSKILL)*100*(select WCEXPERIENCE/100 from tblWeightingCriteria)+EC.APPEARANCE/(select MIN(MINSKILL.APPEARANCE) from tblEmployeeCriteria MINSKILL)*100*(select WCAPPEARANCE/100 from tblWeightingCriteria)+EC.EDUCATION/(select MIN(MINSKILL.EDUCATION) from tblEmployeeCriteria MINSKILL)*100*(select WCEDUCATION/100 from tblWeightingCriteria),&#39;#,##0.00&#39;) as [TOTAL CPI]
FROM tblEmployeeCriteria EC 
INNER JOIN tblemployee E ON EC.EMPID = E.EMPID
ORDER BY 3 DESC&quot;)

Results from the above query

EMPID NAME TOTAL CPI
10037 Tamendran 469.11
10046 Ponisan 457.62
10049 Sudio 430.03

tblPayrollDecision

RangeCPI RangeSalary
451-470 11.000.000 - 12.000.000
431-450 10.000.000 - 11.000.000
411-430 8.000..000 - 10.000.000

Desired result final

EMPID NAME TOTAL CPI RangeSalary
10037 Tamendran 469.11 11.000.000 - 12.000.000
10046 Ponisan 457.62 11.000.000 - 12.000.000
10049 Sudio 430.03 10.000.000 - 11.000.000

答案1

得分: 1

对象名称中带有空格必须用括号（[ ]）括起来。因此，您的原始查询应该不起作用，因为别名字段名TOTAL CPI没有使用括号。建议不要在命名约定中使用空格。

由于RANGECPI的值是文本字符串而不是数字，所以会出现复杂性。如果范围限制在单独的字段中，SQL可以使用https://stackoverflow.com/questions/12604146/sql-join-on-table-a-value-within-table-b-range，因此您需要将RANGECPI字符串解析为数字值。另外，总CPI的值为430.03将不会与任何范围匹配，因为它位于范围的开始和结束之间。在查询中解决这个问题是另一个复杂性。

将RangeSalary的值引入您的原始查询将需要在JOIN或WHERE子句中重复整个TotalCPI计算。在另一个查询中使用您的查询：

SELECT EmpID, [Name], TotalCPI, RangeSalary FROM (your original query here) AS E 
INNER JOIN
    (SELECT Left(RangeCPI,3) AS Start, Mid(RangeCPI,5,3) + 1 AS End, RangeSalary 
     FROM tblPayrollDecision) AS PD
ON E.TotalCPI >= PD.Start AND E.TotalCPI < PD.End;

请注意，不能在设计视图中构建/查看此查询 - Access将拒绝在图形显示中使用JOIN语法。

考虑修改您的原始查询如下：

SELECT EC.EMPID, E.NAME, 
Format([EC].[SKILL]/MinS*100*([WCSKILL]/100),'Standard') AS SKILL, 
Format([EC].[EXPERIENCE]/MinX*100*([WCEXPERIENCE]/100),'Standard') AS EXPERIENCE, 
Format([EC].[APPEARANCE]/MinA*100*([WCAPPEARANCE]/100),'Standard') AS APPEARANCE, 
Format([EC].[EDUCATION]/MinE*100*([WCEDUCATION]/100),'Standard') AS EDUCATION, 
Val([Skill])+Val([Experience])+Val([Appearance])+Val([Education]) AS TotalCPI
FROM
    (SELECT Min(Skill) AS MinS, Min(Experience) AS MinX, Min(Appearance) AS MinA, Min(Education) AS MinE 
     FROM tblEmployeeCriteria) AS MinData, 
tblWeightingCriteria, 
tblEmployeeCriteria AS EC INNER JOIN tblemployee AS E ON EC.EMPID = E.EMPID
ORDER BY 7 DESC;
英文:

Object names with space must be enclosed in brackets ([ ]). So your original query should not work because of alias field name TOTAL CPI not using brackets. Advise not to use spaces in naming convention.

Complication because RANGECPI values are text strings, not numbers. If range limits were in separate fields, SQL could use https://stackoverflow.com/questions/12604146/sql-join-on-table-a-value-within-table-b-range, so you need to parse RANGECPI string to number values. Also, TotalCPI of 430.03 will not match any range because it falls between begin and end of ranges. Resolving this in query is another complication.

Pulling RangeSalary values into your original query would require repeating entire TotalCPI calculation in JOIN or WHERE clause. Use your query in another query:

SELECT EmpID, [Name], TotalCPI, RangeSalary FROM (your original query here) AS E 
INNER JOIN
    (SELECT Left(RangeCPI,3) AS Start, Mid(RangeCPI,5,3) + 1 AS End, RangeSalary 
     FROM tblPayrollDecision) AS PD
ON E.TotalCPI &gt;= PD.Start AND E.TotalCPI &lt; PD.End;

Be aware, cannot build/view this query in Design View - Access will reject the JOIN syntax in the graphical display.

Consider this modification of your original query:

SELECT EC.EMPID, E.NAME, 
Format([EC].[SKILL]/MinS*100*([WCSKILL]/100),&#39;Standard&#39;) AS SKILL, 
Format([EC].[EXPERIENCE]/MinX*100*([WCEXPERIENCE]/100),&#39;Standard&#39;) AS EXPERIENCE, 
Format([EC].[APPEARANCE]/MinA*100*([WCAPPEARANCE]/100),&#39;Standard&#39;) AS APPEARANCE, 
Format([EC].[EDUCATION]/MinE*100*([WCEDUCATION]/100),&#39;Standard&#39;) AS EDUCATION, 
Val([Skill])+Val([Experience])+Val([Appearance])+Val([Education]) AS TotalCPI
FROM
    (SELECT Min(Skill) AS MinS, Min(Experience) AS MinX, Min(Appearance) AS MinA, Min(Education) AS MinE 
     FROM tblEmployeeCriteria) AS MinData, 
tblWeightingCriteria, 
tblEmployeeCriteria AS EC INNER JOIN tblemployee AS E ON EC.EMPID = E.EMPID
ORDER BY 7 DESC;

huangapple
  • 本文由 发表于 2023年6月26日 21:46:58
  • 转载请务必保留本文链接：https://go.coder-hub.com/76557293.html
  • datagridview
  • ms-access
  • sql
  • subquery
  • vb.net
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定