Re: compound primary key using calculated field

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

This is beyond my current abilities, but I'll see if I can make sense of it
nonetheless.
Thanks for your help.

Jamie Collins wrote:
On Dec 13, 3:02 am, "quaddawg via AccessMonster.com" <u21767@uwe>
wrote:
Look-up tables as you call them are nothing untoward.

You've given me a lot to chew on. I'll report back.

I agree with the 'nothing untoward' assessment.

Here's a quick example (ANSI-92 Query Mode SQL DDL syntax):

CREATE TABLE Grades (
grade_description VARCHAR(12) NOT NULL,
age_limit_lower INTEGER NOT NULL,
age_limit_upper INTEGER NOT NULL,
grade_limit_lower DECIMAL(5, 2) NOT NULL,
grade_limit_upper DECIMAL(5, 2) NOT NULL
)
;
CREATE TABLE People (
person_name VARCHAR(35) NOT NULL,
age INTEGER NOT NULL
)
;
CREATE TABLE Scores (
person_name VARCHAR(35) NOT NULL,
score DECIMAL(5, 2) NOT NULL
)
;

INSERT INTO Grades (grade_description, age_limit_lower,
age_limit_upper, grade_limit_lower, grade_limit_upper) VALUES ('Pass',
6, 13, 0.00, 39.99)
;
INSERT INTO Grades (grade_description, age_limit_lower,
age_limit_upper, grade_limit_lower, grade_limit_upper) VALUES
('Distinction', 6, 13, 40.00, 100.00)
;
INSERT INTO Grades (grade_description, age_limit_lower,
age_limit_upper, grade_limit_lower, grade_limit_upper) VALUES ('Fail',
14, 999, 0.00, 39.99)
;
INSERT INTO Grades (grade_description, age_limit_lower,
age_limit_upper, grade_limit_lower, grade_limit_upper) VALUES ('Pass',
14, 999, 40.00, 64.99)
;
INSERT INTO Grades (grade_description, age_limit_lower,
age_limit_upper, grade_limit_lower, grade_limit_upper) VALUES ('Gold',
14, 999, 65.00, 74.99)
;
INSERT INTO Grades (grade_description, age_limit_lower,
age_limit_upper, grade_limit_lower, grade_limit_upper) VALUES
('Platinum', 14, 999, 75.00, 100.99)
;
INSERT INTO People (person_name, age) VALUES ('ChildOne', 8)
;
INSERT INTO People (person_name, age) VALUES ('ChildTwo', 13)
;
INSERT INTO People (person_name, age) VALUES ('AdultOne', 22)
;
INSERT INTO People (person_name, age) VALUES ('AdultTwo', 55)
;
INSERT INTO Scores (person_name, score) VALUES ('ChildOne', 23.45)
;
INSERT INTO Scores (person_name, score) VALUES ('ChildTwo', 67.89)
;
INSERT INTO Scores (person_name, score) VALUES ('AdultOne', 34.56)
;
INSERT INTO Scores (person_name, score) VALUES ('AdultTwo', 78.90)
;
SELECT P1.person_name, S1.score, G1.grade_description
FROM (Scores AS S1
INNER JOIN People AS P1
ON P1.person_name = S1.person_name), Grades AS G1
WHERE S1.score BETWEEN G1.grade_limit_lower AND G1.grade_limit_upper
AND P1.age BETWEEN G1.age_limit_lower AND G1.age_limit_upper
;

And here it is as VBA to create a new mdb, tables and data:

Sub LookupMultiCriteria()
' Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim sql As String
sql = _
"CREATE TABLE Grades (" & vbCr & "grade_description" & _
" VARCHAR(12) NOT NULL, " & vbCr & "age_limit_lower" & _
" INTEGER NOT NULL, " & vbCr & "age_limit_upper" & _
" INTEGER NOT NULL, " & vbCr & "grade_limit_lower" & _
" DECIMAL(5, 2) NOT NULL, " & vbCr & "grade_limit_upper" & _
" DECIMAL(5, 2) NOT NULL" & vbCr & ")"
.Execute sql
sql = _
"CREATE TABLE People (" & vbCr & "person_name" & _
" VARCHAR(35) NOT NULL, " & vbCr & "age INTEGER" & _
" NOT NULL" & vbCr & ")"
.Execute sql
sql = _
"CREATE TABLE Scores (" & vbCr & "person_name" & _
" VARCHAR(35) NOT NULL, " & vbCr & "score" & _
" DECIMAL(5, 2) NOT NULL" & vbCr & ")"
.Execute sql
sql = _
"INSERT INTO Grades (grade_description," & _
" age_limit_lower, age_limit_upper," & _
" grade_limit_lower, grade_limit_upper)" & _
" VALUES ('Pass', 6, 13, 0.00," & _
" 39.99)"
.Execute sql
sql = _
"INSERT INTO Grades (grade_description," & _
" age_limit_lower, age_limit_upper," & _
" grade_limit_lower, grade_limit_upper)" & _
" VALUES ('Distinction', 6, 13," & _
" 40.00, 100.00)"
.Execute sql
sql = _
"INSERT INTO Grades (grade_description," & _
" age_limit_lower, age_limit_upper," & _
" grade_limit_lower, grade_limit_upper)" & _
" VALUES ('Fail', 14, 999, 0.00," & _
" 39.99)"
.Execute sql
sql = _
"INSERT INTO Grades (grade_description," & _
" age_limit_lower, age_limit_upper," & _
" grade_limit_lower, grade_limit_upper)" & _
" VALUES ('Pass', 14, 999, 40.00," & _
" 64.99)"
.Execute sql
sql = _
"INSERT INTO Grades (grade_description," & _
" age_limit_lower, age_limit_upper," & _
" grade_limit_lower, grade_limit_upper)" & _
" VALUES ('Gold', 14, 999, 65.00," & _
" 74.99)"
.Execute sql
sql = _
"INSERT INTO Grades (grade_description," & _
" age_limit_lower, age_limit_upper," & _
" grade_limit_lower, grade_limit_upper)" & _
" VALUES ('Platinum', 14, 999," & _
" 75.00, 100.99)"
.Execute sql
sql = _
"INSERT INTO People (person_name," & _
" age) VALUES ('ChildOne', 8)"
.Execute sql
sql = _
"INSERT INTO People (person_name," & _
" age) VALUES ('ChildTwo', 13)"
.Execute sql
sql = _
"INSERT INTO People (person_name," & _
" age) VALUES ('AdultOne', 22)"
.Execute sql
sql = _
"INSERT INTO People (person_name," & _
" age) VALUES ('AdultTwo', 55)"
.Execute sql
sql = _
"INSERT INTO Scores (person_name," & _
" score) VALUES ('ChildOne', 23.45)"
.Execute sql
sql = _
"INSERT INTO Scores (person_name," & _
" score) VALUES ('ChildTwo', 67.89)"
.Execute sql
sql = _
"INSERT INTO Scores (person_name," & _
" score) VALUES ('AdultOne', 34.56)"
.Execute sql
sql = _
"INSERT INTO Scores (person_name," & _
" score) VALUES ('AdultTwo', 78.90)"
.Execute sql
sql = _
"CREATE VIEW PersonGrades AS" & _
" SELECT P1.person_name, S1.score," & _
" G1.grade_description " & vbCr & "FROM (Scores" & _
" AS S1 " & vbCr & "INNER JOIN People AS P1" & _
" " & vbCr & "ON P1.person_name = S1.person_name)," & _
" Grades AS G1 " & vbCr & "WHERE S1.score" & _
" BETWEEN G1.grade_limit_lower" & _
" AND G1.grade_limit_upper" & vbCr & "AND" & _
" P1.age BETWEEN G1.age_limit_lower" & _
" AND G1.age_limit_upper"
.Execute sql
sql = _
"SELECT V1.person_name, V1.score," & _
" V1.grade_description " & vbCr & "FROM PersonGrades" & _
" AS V1"
Dim rs
Set rs = .Execute(sql)
MsgBox rs.GetString
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

--
Message posted via http://www.accessmonster.com

.

Relevant Pages

  • Re: Design Question
    ... note that a typical Calendar table has one row per day. ... ALTER TABLE EarningsHistory ADD ... Dim Sql As String ... ..Execute Sql ...
    (microsoft.public.access.tablesdbdesign)
  • Re: "Do until .EOF" doesnt update all the records
    ... > database goes into endless loop. ... It puzzles me why I have seen at least two posts today which execute SQL ... by using the .Edit method, changing Field value, and then using the ...
    (microsoft.public.access.modulesdaovba)
  • Re: compound primary key using calculated field
    ... INSERT INTO Grades (grade_description, age_limit_lower, ... INSERT INTO People (person_name, age) VALUES ... Dim sql As String ... ..Execute sql ...
    (microsoft.public.access.tablesdbdesign)
  • RE: Passing Parameters to an Execute SQL Task in SSIS
    ... you can get and use the return value of SQL ... Set the task properties in SSIS development environment: ... Execute SQL Task1 Properties: ... Update PCTEST Set sname='TEST1' WHERE id=? ...
    (microsoft.public.sqlserver.dts)
  • Re: Dynamic DTS
    ... The two Execute SQL are still working. ... >> Can you point me to a sample active script that would perform step three? ... >>> So you have some variables inside your package that you set from outside ...
    (microsoft.public.sqlserver.dts)