Hi,
This seems like such a simple/common need that I'm reluctant to ask, but I just don't see a good generic solution for constructing an SQL query-string.
The user will supply "QQuery" fields which will select records from "Tests". I don't know which fields the user will supply. If they supply none, then "QResults" should contain all records from "Tests". That query might look (loosely/remotely) like:
SELECT Tests.TestID, PartTypes.PartNumber, DUTs.SerialNumber, Operators.Operator, Tests.ResultCode
FROM Operators INNER JOIN ((Stations INNER JOIN (DUTs INNER JOIN (PartTypes INNER JOIN (Tests INNER JOIN QueryTests ON Tests.TestID = QueryTests.TestID) ON PartTypes.PartNumber = QueryTests.PartNumber) ON (DUTs.DUTNdx = Tests.DUTNdx) AND (PartTypes.PartTypeNdx = DUTs.PartTypeNdx)) ON Stations.StationNdx = Tests.StationNdx)
If they supply SerialNumber and ResultCode, then only records matching the specific SerialNumber and ResultCode should be returned.
I wonder if all that's required is a correctly constructed "WHERE" clause(?) - need to try that. (still trying to get my mind around "INNER JOINS")
Sorry to bother!
Jadn
------------------------------
"QQuery", "QResults" (QQuery fields are inputs)
(
TestID int,
PartNumber char(),
SerialNumber char(),
Operator char(),
ResultCode int
)
"Tests" table has the following columns:
(
TestID int Primary Key,
DUTNdx int Foreign Key,
OperatorNdx int Foreign Key,
ResultCode int
)
"PartTypes"
(
PartTypeNdx int Primary Key,
PartNumber char()
)
"DUTs"
(
DUTNdx int Primary Key,
PartTypeNdx int Foreign Key,
SerialNumber char()
)
"Operators"
(
OperatorNdx int Primary Key,
Operator char()
)
This seems like such a simple/common need that I'm reluctant to ask, but I just don't see a good generic solution for constructing an SQL query-string.
The user will supply "QQuery" fields which will select records from "Tests". I don't know which fields the user will supply. If they supply none, then "QResults" should contain all records from "Tests". That query might look (loosely/remotely) like:
SELECT Tests.TestID, PartTypes.PartNumber, DUTs.SerialNumber, Operators.Operator, Tests.ResultCode
FROM Operators INNER JOIN ((Stations INNER JOIN (DUTs INNER JOIN (PartTypes INNER JOIN (Tests INNER JOIN QueryTests ON Tests.TestID = QueryTests.TestID) ON PartTypes.PartNumber = QueryTests.PartNumber) ON (DUTs.DUTNdx = Tests.DUTNdx) AND (PartTypes.PartTypeNdx = DUTs.PartTypeNdx)) ON Stations.StationNdx = Tests.StationNdx)
If they supply SerialNumber and ResultCode, then only records matching the specific SerialNumber and ResultCode should be returned.
I wonder if all that's required is a correctly constructed "WHERE" clause(?) - need to try that. (still trying to get my mind around "INNER JOINS")
Sorry to bother!
Jadn
------------------------------
"QQuery", "QResults" (QQuery fields are inputs)
(
TestID int,
PartNumber char(),
SerialNumber char(),
Operator char(),
ResultCode int
)
"Tests" table has the following columns:
(
TestID int Primary Key,
DUTNdx int Foreign Key,
OperatorNdx int Foreign Key,
ResultCode int
)
"PartTypes"
(
PartTypeNdx int Primary Key,
PartNumber char()
)
"DUTs"
(
DUTNdx int Primary Key,
PartTypeNdx int Foreign Key,
SerialNumber char()
)
"Operators"
(
OperatorNdx int Primary Key,
Operator char()
)