Strange Access SQL problem

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
 
G

Guest

That is very strange. I've been looking for a typo or something but can't see
one.

One thought: Group is a reserved word. In the SQL that works, it's
surrounded by ( ). In the one that doesn't it's not. Try this:

SELECT ECNDBASE.[ECN NO],
ECNDBASE.[TAGGED],
ECNDBASE.[GROUP]
FROM ECNDBASE
WHERE ECNDBASE.[TAGGED]="X"
AND ECNDBASE.[GROUP]="CONTROLS";

If that doesn't help, I'd remove the TAGGED out of the WHERE clause then
manually look for X's to see if there are any.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


[email protected] said:
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
Click to expand...
 
J

jliebman

That is very strange. I've been looking for a typo or something but can't see
one.

One thought: Group is a reserved word. In the SQL that works, it's
surrounded by ( ). In the one that doesn't it's not. Try this:

SELECT ECNDBASE.[ECN NO],
ECNDBASE.[TAGGED],
ECNDBASE.[GROUP]
FROM ECNDBASE
WHERE ECNDBASE.[TAGGED]="X"
AND ECNDBASE.[GROUP]="CONTROLS";

If that doesn't help, I'd remove the TAGGED out of the WHERE clause then
manually look for X's to see if there are any.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.



[email protected] said:
All,
Click to expand...
I am a non-Access programmer drawn into a problem in Access which I
have never seen before.
Click to expand...
I have a database that includes the following 2 tables (format is
Field Name, Field type, Field size, Required):
Click to expand...
Table ECNDBASE:
Click to expand...
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
Click to expand...
Table ECNDBASE1:
Click to expand...
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
Click to expand...
ECNDBASE1.ECR NO and ECNDBASE. ECN NO are related keys.
Click to expand...
Here's the problem: the TAGGED field in ECNDBASE contains values which
are "hidden" from certain queries. For example, the following query
returns data:
Click to expand...
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];
Click to expand...
The following query which is just on the ECNDBASE table, also looking
for X in the TAGGED field, returns no rows:
Click to expand...
SELECT ECNDBASE.[ECN NO], ECNDBASE.TAGGED, ECNDBASE.GROUP
FROM ECNDBASE WHERE ECNDBASE.TAGGED="X" AND ECNDBASE.GROUP="CONTROLS";
Click to expand...
Can anyone see the problem? There are no relationships defined between
the tables.

JL- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Hi Jerry,

Thx for your reply.

I tried your q
 
J

jliebman

That is very strange. I've been looking for a typo or something but can't see
one.

One thought: Group is a reserved word. In the SQL that works, it's
surrounded by ( ). In the one that doesn't it's not. Try this:

SELECT ECNDBASE.[ECN NO],
ECNDBASE.[TAGGED],
ECNDBASE.[GROUP]
FROM ECNDBASE
WHERE ECNDBASE.[TAGGED]="X"
AND ECNDBASE.[GROUP]="CONTROLS";

If that doesn't help, I'd remove the TAGGED out of the WHERE clause then
manually look for X's to see if there are any.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.



[email protected] said:
All,
Click to expand...
I am a non-Access programmer drawn into a problem in Access which I
have never seen before.
Click to expand...
I have a database that includes the following 2 tables (format is
Field Name, Field type, Field size, Required):
Click to expand...
Table ECNDBASE:
Click to expand...
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
Click to expand...
Table ECNDBASE1:
Click to expand...
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
Click to expand...
ECNDBASE1.ECR NO and ECNDBASE. ECN NO are related keys.
Click to expand...
Here's the problem: the TAGGED field in ECNDBASE contains values which
are "hidden" from certain queries. For example, the following query
returns data:
Click to expand...
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];
Click to expand...
The following query which is just on the ECNDBASE table, also looking
for X in the TAGGED field, returns no rows:
Click to expand...
SELECT ECNDBASE.[ECN NO], ECNDBASE.TAGGED, ECNDBASE.GROUP
FROM ECNDBASE WHERE ECNDBASE.TAGGED="X" AND ECNDBASE.GROUP="CONTROLS";
Click to expand...
Can anyone see the problem? There are no relationships defined between
the tables.

JL- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Hi Jerry,

I tried your query. I'm afraid it didnt work.

I know there are records in there with Tagged = X because I did in
fact look "manually" through the table and identified them. Do you
have any other ideas? In 25 years of database work I have never run
accross a problem like this.

Rgds

Jory
 
J

John W. Vinson

I know there are records in there with Tagged = X because I did in
fact look "manually" through the table and identified them. Do you
have any other ideas? In 25 years of database work I have never run
accross a problem like this.
Click to expand...

I wonder if you might have a corrupt Index. Try dropping the index on Tagged
(or perhaps all the indexes); compact the database; recreate the indexes.

Another possibility is that there is some non-printing character in with the X
- e.g X<tab> or X<Word space>.

John W. Vinson [MVP]
 
J

jliebman

I wonder if you might have a corrupt Index. Try dropping the index on Tagged
(or perhaps all the indexes); compact the database; recreate the indexes.

Another possibility is that there is some non-printing character in with the X
- e.g X<tab> or X<Word space>.

John W. Vinson [MVP]
Click to expand...

John,

I'm not sure whether or not you are correct about the indexes but
undoubtedly some form of corruption was involved. The problem was
rectified when I created a blank database and imported the old
database into it wholesale. Not only was the new database able to find
the records with TAGGED = X, but the old one started working again
properly, without any other action on my part.

I'm certainly not sure of what happened, but I do know that I dont
really like working with such a quirky product.

Thanks to all who tried to help

Jory
 
A

Aaron Kempf

yeah I don't like working with a Quirky Product either

that is why I moved to 'Access Datas Projects' a decade ago

I'm so f'in glad that I finally found someone else that is fed up with
Access!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel Excel Macro works on one computer but errors out on other computers 2
Linked Table Query Problems (SQL Server ODBC) 4
Customer, Prospect, Service Table design 4
Only one record with yes/no 1
Table Design Review of Fields and Tables 9
Problem w/vlookup 5
form date using javascript and sql (firefox, safare drops last dig 3
Enable / Disable User Form Fields Based on Input. 1

Top