J
jliebman
All,
I am a non-Access programmer drawn into a problem in Access which I
have never seen before.
I have a database that includes the following 2 tables (format is
Field Name, Field type, Field size, Required):
Table ECNDBASE:
ECN NO,Number,8,True
REQUESTOR,Text,255,False
LOG IN DATE,Date/Time,8,False
CBA,Text,255,False
DRAFTING DATE,Date/Time,8,False
DRAFT PERSON,Text,28,False
DIST DATE,Date/Time,8,False
APPROVAL AGENCY,Text,28,False
ECN STATUS,Text,12,False
ENG APPROVAL,Text,50,False
QC APPROVAL,Text,50,False
MFG APPROVAL,Text,50,False
SALES APPROVAL,Text,50,False
REGULATORY APPROVAL,Yes/No,1,False
finalApproval,Text,50,False
ENGR COMMENT,Text,255,False
DESCRIPTION OF CHANGE,Text,255,False
EXTENDED DESCRIPTION,Text,255,False
CHECKED/APPROVED BY,Yes/No,1,False
DIR OF ENG,Yes/No,1,False
TAGGED,Text,50,False
GROUP,Text,15,True
EngRepApproval,Text,25,False
QCRepApproval,Text,25,False
SalesRepApproval,Text,25,False
MfgRepApproval,Text,25,False
chkApproval,Text,25,False
dirEngr,Text,12,False
Table ECNDBASE1:
ECR NO,,8,True
ID,Text,255,False
ORIGINATOR,Text,255,False
DEPARTMENT,Text,50,False
MODEL,Text,15,False
DWG NO,Text,15,False
REC,Date/Time,8,False
TO CB,Date/Time,8,False
CBA,Text,255,False
DRAFTING,Date/Time,8,False
DRAFT,Text,255,False
DIST DATE,Date/Time,8,False
AGENCY APPROVAL,Text,255,False
ECR STATUS,Text,255,False
ECNDBASE1.ECR NO and ECNDBASE. ECN NO are related keys.
Here's the problem: the TAGGED field in ECNDBASE contains values which
are "hidden" from certain queries. For example, the following query
returns data:
SELECT DISTINCTROW ECNDBASE1.[ECR NO], ECNDBASE.[ECN NO],
ECNDBASE.TAGGED, ECNDBASE.GROUP
FROM (ECNDBASE INNER JOIN ECNDBASE1 ON ECNDBASE.[ECN NO] = ECNDBASE1.
[ECR NO])
WHERE (((ECNDBASE.TAGGED)="X") AND ((ECNDBASE.GROUP)="CONTROLS"))
ORDER BY ECNDBASE1.[ECR NO];
The following query which is just on the ECNDBASE table, also looking
for X in the TAGGED field, returns no rows:
SELECT ECNDBASE.[ECN NO], ECNDBASE.TAGGED, ECNDBASE.GROUP
FROM ECNDBASE WHERE ECNDBASE.TAGGED="X" AND ECNDBASE.GROUP="CONTROLS";
Can anyone see the problem? There are no relationships defined between
the tables.
TIA
JL
I am a non-Access programmer drawn into a problem in Access which I
have never seen before.
I have a database that includes the following 2 tables (format is
Field Name, Field type, Field size, Required):
Table ECNDBASE:
ECN NO,Number,8,True
REQUESTOR,Text,255,False
LOG IN DATE,Date/Time,8,False
CBA,Text,255,False
DRAFTING DATE,Date/Time,8,False
DRAFT PERSON,Text,28,False
DIST DATE,Date/Time,8,False
APPROVAL AGENCY,Text,28,False
ECN STATUS,Text,12,False
ENG APPROVAL,Text,50,False
QC APPROVAL,Text,50,False
MFG APPROVAL,Text,50,False
SALES APPROVAL,Text,50,False
REGULATORY APPROVAL,Yes/No,1,False
finalApproval,Text,50,False
ENGR COMMENT,Text,255,False
DESCRIPTION OF CHANGE,Text,255,False
EXTENDED DESCRIPTION,Text,255,False
CHECKED/APPROVED BY,Yes/No,1,False
DIR OF ENG,Yes/No,1,False
TAGGED,Text,50,False
GROUP,Text,15,True
EngRepApproval,Text,25,False
QCRepApproval,Text,25,False
SalesRepApproval,Text,25,False
MfgRepApproval,Text,25,False
chkApproval,Text,25,False
dirEngr,Text,12,False
Table ECNDBASE1:
ECR NO,,8,True
ID,Text,255,False
ORIGINATOR,Text,255,False
DEPARTMENT,Text,50,False
MODEL,Text,15,False
DWG NO,Text,15,False
REC,Date/Time,8,False
TO CB,Date/Time,8,False
CBA,Text,255,False
DRAFTING,Date/Time,8,False
DRAFT,Text,255,False
DIST DATE,Date/Time,8,False
AGENCY APPROVAL,Text,255,False
ECR STATUS,Text,255,False
ECNDBASE1.ECR NO and ECNDBASE. ECN NO are related keys.
Here's the problem: the TAGGED field in ECNDBASE contains values which
are "hidden" from certain queries. For example, the following query
returns data:
SELECT DISTINCTROW ECNDBASE1.[ECR NO], ECNDBASE.[ECN NO],
ECNDBASE.TAGGED, ECNDBASE.GROUP
FROM (ECNDBASE INNER JOIN ECNDBASE1 ON ECNDBASE.[ECN NO] = ECNDBASE1.
[ECR NO])
WHERE (((ECNDBASE.TAGGED)="X") AND ((ECNDBASE.GROUP)="CONTROLS"))
ORDER BY ECNDBASE1.[ECR NO];
The following query which is just on the ECNDBASE table, also looking
for X in the TAGGED field, returns no rows:
SELECT ECNDBASE.[ECN NO], ECNDBASE.TAGGED, ECNDBASE.GROUP
FROM ECNDBASE WHERE ECNDBASE.TAGGED="X" AND ECNDBASE.GROUP="CONTROLS";
Can anyone see the problem? There are no relationships defined between
the tables.
TIA
JL