Need Meaning of Make-Table Query Code

E

EarlCPhillips

I have taken over an old Nutrition Services tracking system developed by
someone else. One of the old Make-Table queries has code for a field as
follows:

BC: (Q_GenericpullKiKPrePostData![4]+Q_GenericpullKiKPrePostData![5])*-1

where Q_GenericpullKiKPrePostData is a query extracting data from files of
test data recorded for the "Kids in the Kitchen" program Pre or Post test
results to teach nutrition and cooking skills to young disadvantaged kids.

The file contains: ID, SessNbr, Name, bln1a (boolean answer to question 1a),
bln1b, bln1c, bln1d, bln1e, bln2, bln3, bln4, bln5, bln6, bln7a, bln7b,
bln7c, bln7d, Comments, PrePost (whether it is a "pre" or "post" class test),
Date.

The query it is drawing from has the following field order: Date, Host (org.
that sponsored class), Session, Name, bln1a, bln1b, bln1c, bln1d, bln1e,
bln2, bln3, bln4, bln5, bln6, bln7a, Ques8Comments, PrePost, bln7b, bln7c,
bln7d, Comments.

The objective is to measure progress (BC or Behavior Change) in nutrition
knowledge to justify grant money for future programs.

What does the construct "Q_GenericpullKiIPrePostData![4]" or
"Q_GenericpullKiIPrePostData![5]" refer to? What would be the expected value
in BC?

EarlCPhillips
Ex-Mainframer Learning Access To Help
Harvesters Food Network feed more
hungry people with the same staff
 
J

John Spencer

As a guess they are referrng to fields named 4 and 5 in a query named
Q_GenericpullKiKPrePostData. The other possibility is that this is
referring to the 5th and 6th field in the query.

If you could post the entire SQL for the make table query and
Q_GenericpullKiKPrePostData it might be easier to decipher.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
E

EarlCPhillips

The SQL is:
SELECT Q_GenericpullKiKPrePostData.Date AS Expr1,
Q_GenericpullKiKPrePostData.Host AS Expr2, Q_GenericpullKiKPrePostData.Name
AS Expr3, Q_GenericpullKiKPrePostData.PrePost AS Expr4,
(Q_GenericpullKiKPrePostData![4]+Q_GenericpullKiKPrePostData![5])*-1 AS BC
INTO PrePostMilk
FROM Q_GenericpullKiKPrePostData;

and the SQL for Q_GenericpullKiKPrePostData is:

SELECT tblOldKIKPrePostTest.dtmDate AS [Date],
tblSessAttendance.chrPgmHostName AS Host, tblOldKIKPrePostTest.chrSessNbr AS
Sess, tblOldKIKPrePostTest.chrParticiName AS Name, tblOldKIKPrePostTest.bln1a
AS bln1a, tblOldKIKPrePostTest.bln1b AS bln1b, tblOldKIKPrePostTest.bln1c AS
bln1c, tblOldKIKPrePostTest.bln1d AS bln1d, tblOldKIKPrePostTest.bln1e AS
bln1e, tblOldKIKPrePostTest.bln2 AS bln2, tblOldKIKPrePostTest.bln3 AS bln3,
tblOldKIKPrePostTest.bln4 AS bln4, tblOldKIKPrePostTest.bln5 AS bln5,
tblOldKIKPrePostTest.bln6 AS bln6, tblOldKIKPrePostTest.bln7a AS bln7a,
tblOldKIKPrePostTest.chrQues8Comments AS Ques8Comments,
tblOldKIKPrePostTest.chrPrePost AS PrePost, tblOldKIKPrePostTest.bln7b AS
bln7b, tblOldKIKPrePostTest.bln7c AS bln7c, tblOldKIKPrePostTest.bln7d AS
bln7d, tblOldKIKPrePostTest.chrComments AS Comments
FROM tblOldKIKPrePostTest INNER JOIN tblSessAttendance ON
(tblOldKIKPrePostTest.chrSessNbr = tblSessAttendance.chrSessNbr) AND
(tblOldKIKPrePostTest.chrParticiName = tblSessAttendance.chrParticiName)
GROUP BY tblOldKIKPrePostTest.dtmDate, tblSessAttendance.chrPgmHostName,
tblOldKIKPrePostTest.chrSessNbr, tblOldKIKPrePostTest.chrParticiName,
tblOldKIKPrePostTest.bln1a, tblOldKIKPrePostTest.bln1b,
tblOldKIKPrePostTest.bln1c, tblOldKIKPrePostTest.bln1d,
tblOldKIKPrePostTest.bln1e, tblOldKIKPrePostTest.bln2,
tblOldKIKPrePostTest.bln3, tblOldKIKPrePostTest.bln4,
tblOldKIKPrePostTest.bln5, tblOldKIKPrePostTest.bln6,
tblOldKIKPrePostTest.bln7a, tblOldKIKPrePostTest.chrQues8Comments,
tblOldKIKPrePostTest.chrPrePost, tblOldKIKPrePostTest.bln7b,
tblOldKIKPrePostTest.bln7c, tblOldKIKPrePostTest.bln7d,
tblOldKIKPrePostTest.chrComments
HAVING (((tblOldKIKPrePostTest.dtmDate) Between
[Forms]![frmReportsPage]![txtStartDate] And
[Forms]![frmReportsPage]![txtStopDate]));

I am inclined to believe it is the fifth and sixth fields added together and
multiplied by -1. Since the fields are boolean, the checked value is -1, so
two added together would be -2 if both were checked, times -1 would make it a
value of 2.
--
Trying To Feed The Hungry


John Spencer said:
As a guess they are referrng to fields named 4 and 5 in a query named
Q_GenericpullKiKPrePostData. The other possibility is that this is
referring to the 5th and 6th field in the query.

If you could post the entire SQL for the make table query and
Q_GenericpullKiKPrePostData it might be easier to decipher.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

EarlCPhillips said:
I have taken over an old Nutrition Services tracking system developed by
someone else. One of the old Make-Table queries has code for a field as
follows:

BC: (Q_GenericpullKiKPrePostData![4]+Q_GenericpullKiKPrePostData![5])*-1

where Q_GenericpullKiKPrePostData is a query extracting data from files of
test data recorded for the "Kids in the Kitchen" program Pre or Post test
results to teach nutrition and cooking skills to young disadvantaged kids.

The file contains: ID, SessNbr, Name, bln1a (boolean answer to question
1a),
bln1b, bln1c, bln1d, bln1e, bln2, bln3, bln4, bln5, bln6, bln7a, bln7b,
bln7c, bln7d, Comments, PrePost (whether it is a "pre" or "post" class
test),
Date.

The query it is drawing from has the following field order: Date, Host
(org.
that sponsored class), Session, Name, bln1a, bln1b, bln1c, bln1d, bln1e,
bln2, bln3, bln4, bln5, bln6, bln7a, Ques8Comments, PrePost, bln7b, bln7c,
bln7d, Comments.

The objective is to measure progress (BC or Behavior Change) in nutrition
knowledge to justify grant money for future programs.

What does the construct "Q_GenericpullKiIPrePostData![4]" or
"Q_GenericpullKiIPrePostData![5]" refer to? What would be the expected
value
in BC?

EarlCPhillips
Ex-Mainframer Learning Access To Help
Harvesters Food Network feed more
hungry people with the same staff
Click to expand...
Click to expand...
 
H

HatesIT

I think you need to restore your db and try and get a look at that SQL before
the designer mangles it. As a "feature" it probably saved it mangled already.

after a restore (!! to a different folder for testing !!) in the debug
window type
?currentdb.QueryDefs(" Your query ").SQL and press enter. I'll bet you get
something different from your post.

Looks like Q_GenericpullKiKPrePostData is missing or pulls from a deleted
linked table. The reason I think this is happening is all fields from that
object are getting re-labeled as Expr1, Expr2 etc. this would prove/disprove
that...

hope this helps
EarlCPhillips said:
The SQL is:
SELECT Q_GenericpullKiKPrePostData.Date AS Expr1,
Q_GenericpullKiKPrePostData.Host AS Expr2, Q_GenericpullKiKPrePostData.Name
AS Expr3, Q_GenericpullKiKPrePostData.PrePost AS Expr4,
(Q_GenericpullKiKPrePostData![4]+Q_GenericpullKiKPrePostData![5])*-1 AS BC
INTO PrePostMilk
FROM Q_GenericpullKiKPrePostData;

and the SQL for Q_GenericpullKiKPrePostData is:

SELECT tblOldKIKPrePostTest.dtmDate AS [Date],
tblSessAttendance.chrPgmHostName AS Host, tblOldKIKPrePostTest.chrSessNbr AS
Sess, tblOldKIKPrePostTest.chrParticiName AS Name, tblOldKIKPrePostTest.bln1a
AS bln1a, tblOldKIKPrePostTest.bln1b AS bln1b, tblOldKIKPrePostTest.bln1c AS
bln1c, tblOldKIKPrePostTest.bln1d AS bln1d, tblOldKIKPrePostTest.bln1e AS
bln1e, tblOldKIKPrePostTest.bln2 AS bln2, tblOldKIKPrePostTest.bln3 AS bln3,
tblOldKIKPrePostTest.bln4 AS bln4, tblOldKIKPrePostTest.bln5 AS bln5,
tblOldKIKPrePostTest.bln6 AS bln6, tblOldKIKPrePostTest.bln7a AS bln7a,
tblOldKIKPrePostTest.chrQues8Comments AS Ques8Comments,
tblOldKIKPrePostTest.chrPrePost AS PrePost, tblOldKIKPrePostTest.bln7b AS
bln7b, tblOldKIKPrePostTest.bln7c AS bln7c, tblOldKIKPrePostTest.bln7d AS
bln7d, tblOldKIKPrePostTest.chrComments AS Comments
FROM tblOldKIKPrePostTest INNER JOIN tblSessAttendance ON
(tblOldKIKPrePostTest.chrSessNbr = tblSessAttendance.chrSessNbr) AND
(tblOldKIKPrePostTest.chrParticiName = tblSessAttendance.chrParticiName)
GROUP BY tblOldKIKPrePostTest.dtmDate, tblSessAttendance.chrPgmHostName,
tblOldKIKPrePostTest.chrSessNbr, tblOldKIKPrePostTest.chrParticiName,
tblOldKIKPrePostTest.bln1a, tblOldKIKPrePostTest.bln1b,
tblOldKIKPrePostTest.bln1c, tblOldKIKPrePostTest.bln1d,
tblOldKIKPrePostTest.bln1e, tblOldKIKPrePostTest.bln2,
tblOldKIKPrePostTest.bln3, tblOldKIKPrePostTest.bln4,
tblOldKIKPrePostTest.bln5, tblOldKIKPrePostTest.bln6,
tblOldKIKPrePostTest.bln7a, tblOldKIKPrePostTest.chrQues8Comments,
tblOldKIKPrePostTest.chrPrePost, tblOldKIKPrePostTest.bln7b,
tblOldKIKPrePostTest.bln7c, tblOldKIKPrePostTest.bln7d,
tblOldKIKPrePostTest.chrComments
HAVING (((tblOldKIKPrePostTest.dtmDate) Between
[Forms]![frmReportsPage]![txtStartDate] And
[Forms]![frmReportsPage]![txtStopDate]));

I am inclined to believe it is the fifth and sixth fields added together and
multiplied by -1. Since the fields are boolean, the checked value is -1, so
two added together would be -2 if both were checked, times -1 would make it a
value of 2.
--
Trying To Feed The Hungry


John Spencer said:
As a guess they are referrng to fields named 4 and 5 in a query named
Q_GenericpullKiKPrePostData. The other possibility is that this is
referring to the 5th and 6th field in the query.

If you could post the entire SQL for the make table query and
Q_GenericpullKiKPrePostData it might be easier to decipher.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

EarlCPhillips said:
I have taken over an old Nutrition Services tracking system developed by
someone else. One of the old Make-Table queries has code for a field as
follows:

BC: (Q_GenericpullKiKPrePostData![4]+Q_GenericpullKiKPrePostData![5])*-1

where Q_GenericpullKiKPrePostData is a query extracting data from files of
test data recorded for the "Kids in the Kitchen" program Pre or Post test
results to teach nutrition and cooking skills to young disadvantaged kids.

The file contains: ID, SessNbr, Name, bln1a (boolean answer to question
1a),
bln1b, bln1c, bln1d, bln1e, bln2, bln3, bln4, bln5, bln6, bln7a, bln7b,
bln7c, bln7d, Comments, PrePost (whether it is a "pre" or "post" class
test),
Date.

The query it is drawing from has the following field order: Date, Host
(org.
that sponsored class), Session, Name, bln1a, bln1b, bln1c, bln1d, bln1e,
bln2, bln3, bln4, bln5, bln6, bln7a, Ques8Comments, PrePost, bln7b, bln7c,
bln7d, Comments.

The objective is to measure progress (BC or Behavior Change) in nutrition
knowledge to justify grant money for future programs.

What does the construct "Q_GenericpullKiIPrePostData![4]" or
"Q_GenericpullKiIPrePostData![5]" refer to? What would be the expected
value
in BC?

EarlCPhillips
Ex-Mainframer Learning Access To Help
Harvesters Food Network feed more
hungry people with the same staff
Click to expand...
Click to expand...
Click to expand...
 
Top