2023年6月26日 21:46:58go评论73阅读模式

英文:

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;

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

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

问题

答案1

如何在联接的Table2中排除Table1中出现的行？

SQL Server COALESCE() 函数

VBA代码不返回任何值，也不会报错；用户定义的

SQL选择最大值和平均值

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论