Different results to same query

[sigava77 via AccessMonster.com]

Hi
what to do when a database gives diferent results to same query? what can be
cousing this?

thanks,
Carla

 

[Allen Browne]

Insufficient information, Carla.

Switch the query to SQL View (View menu, in query design.)
Copy the SQL statement.
Paste as a reply to this thread.

Tell us anything we may need to know about the tables, e.g. are they Access
tables? attached Excel or text files? other?

Hopefully someone will then be able to comment.

 

[sigava77 via AccessMonster.com]

Hi,

Sorry... It's a Access database. the query is as follow:

SELECT t_resultadoslaboratorio.*
FROM ((t_paciente LEFT JOIN t_adulto ON t_paciente.nid = t_adulto.nid) LEFT
JOIN t_crianca ON t_paciente.nid = t_crianca.nid) INNER JOIN
t_resultadoslaboratorio ON t_paciente.nid = t_resultadoslaboratorio.nid
WHERE (((t_resultadoslaboratorio.nid) Is Not Null) AND (
(t_resultadoslaboratorio.dataresultado)=(select min(dataresultado) from
t_resultadoslaboratorio r where t_paciente.nid = r.nid and r.codexame =
'CD4' and r.codparametro = 'Cd4 Absoluto')) AND ((t_paciente.emtarv)=True)
AND ((t_resultadoslaboratorio.codexame)='CD4') AND ((t_resultadoslaboratorio.
codparametro)='Cd4 Absoluto'));

The table t_paciente has a one to one relationship with t_adulto e t_crianca,
and a one to many with t_resultadoslaboratorio.

Thanks,
Carla

Insufficient information, Carla.

Switch the query to SQL View (View menu, in query design.)
Copy the SQL statement.
Paste as a reply to this thread.

Tell us anything we may need to know about the tables, e.g. are they Access
tables? attached Excel or text files? other?

Hopefully someone will then be able to comment.

Hi
what to do when a database gives diferent results to same query? what can

[quoted text clipped - 3 lines]

thanks,
Carla

 

[Stefan Hoffmann]

hi Carla,

SELECT t_resultadoslaboratorio.*
FROM ((t_paciente LEFT JOIN t_adulto ON t_paciente.nid = t_adulto.nid) LEFT
JOIN t_crianca ON t_paciente.nid = t_crianca.nid) INNER JOIN
t_resultadoslaboratorio ON t_paciente.nid = t_resultadoslaboratorio.nid
WHERE (((t_resultadoslaboratorio.nid) Is Not Null) AND (
(t_resultadoslaboratorio.dataresultado)=(select min(dataresultado) from
t_resultadoslaboratorio r where t_paciente.nid = r.nid and r.codexame =
'CD4' and r.codparametro = 'Cd4 Absoluto')) AND ((t_paciente.emtarv)=True)
AND ((t_resultadoslaboratorio.codexame)='CD4') AND ((t_resultadoslaboratorio.
codparametro)='Cd4 Absoluto'));

You don't need to LEFT JOIN t_adulto, cause you don't select values from
it or use it in any criteria.

Also use INNER JOINs instead of the LEFT JOIN.



mfG
--> stefan <--

 

[Allen Browne]

Okay, we can now see the query.

How is it giving different results:
- returning different records?
- returning a different number of records?

If you run it:
- on the same computer, twice in a row?
- if you run it on different computers?

I don't see anything in the query itself or its subquery that looks
ambiguous.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,

Sorry... It's a Access database. the query is as follow:

SELECT t_resultadoslaboratorio.*
FROM ((t_paciente
LEFT JOIN t_adulto ON t_paciente.nid = t_adulto.nid)
LEFT JOIN t_crianca ON t_paciente.nid = t_crianca.nid)
INNER JOIN t_resultadoslaboratorio ON t_paciente.nid
= t_resultadoslaboratorio.nid
WHERE ((t_resultadoslaboratorio.nid Is Not Null)
AND (t_resultadoslaboratorio.dataresultado =
(select min(dataresultado)
from t_resultadoslaboratorio r
where t_paciente.nid = r.nid
and r.codexame = 'CD4'
and r.codparametro = 'Cd4 Absoluto'))
AND (t_paciente.emtarv = True)
AND (t_resultadoslaboratorio.codexame = 'CD4')
AND (t_resultadoslaboratorio.codparametro = 'Cd4 Absoluto'));

The table t_paciente has a one to one relationship with t_adulto e
t_crianca,
and a one to many with t_resultadoslaboratorio.

Thanks,
Carla

Insufficient information, Carla.

Switch the query to SQL View (View menu, in query design.)
Copy the SQL statement.
Paste as a reply to this thread.

Tell us anything we may need to know about the tables, e.g. are they
Access
tables? attached Excel or text files? other?

Hopefully someone will then be able to comment.

Hi
what to do when a database gives diferent results to same query? what
can

[quoted text clipped - 3 lines]

thanks,
Carla

 

[sigava77 via AccessMonster.com]

Hi,
it returns a different number of records. I’m running it at the same computer.

thanks,
Carla

Okay, we can now see the query.

How is it giving different results:
- returning different records?
- returning a different number of records?

If you run it:
- on the same computer, twice in a row?
- if you run it on different computers?

I don't see anything in the query itself or its subquery that looks
ambiguous.

Hi,

Sorry... It's a Access database. the query is as follow:

SELECT t_resultadoslaboratorio.*
FROM ((t_paciente
LEFT JOIN t_adulto ON t_paciente.nid = t_adulto.nid)
LEFT JOIN t_crianca ON t_paciente.nid = t_crianca.nid)
INNER JOIN t_resultadoslaboratorio ON t_paciente.nid
= t_resultadoslaboratorio.nid
WHERE ((t_resultadoslaboratorio.nid Is Not Null)
AND (t_resultadoslaboratorio.dataresultado =
(select min(dataresultado)
from t_resultadoslaboratorio r
where t_paciente.nid = r.nid
and r.codexame = 'CD4'
and r.codparametro = 'Cd4 Absoluto'))
AND (t_paciente.emtarv = True)
AND (t_resultadoslaboratorio.codexame = 'CD4')
AND (t_resultadoslaboratorio.codparametro = 'Cd4 Absoluto'));

The table t_paciente has a one to one relationship with t_adulto e
t_crianca,

[quoted text clipped - 21 lines]

 

[Allen Browne]

Is there anything identifiable about the records it returns sometimes and
not others?

1.
What is the data type of the fields involved in the JOINs and the WHERE
clause? For example, if you open t_adulto in design view, what is the data
type of nid? And in t_paciente, what is the data type of nid?

2.
What version of Access is this? And what service pack? (See Help | About)

3.
Locate msjet40.dll (typically in c:\windows\system32.)
Right-click, and choose Properties.
On the Version tab, what is version is it?

4.
Is there any chance that someone/something could be changing the data
between the times when this gives different results?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
it returns a different number of records. I'm running it at the same
computer.

thanks,
Carla

Okay, we can now see the query.

How is it giving different results:
- returning different records?
- returning a different number of records?

If you run it:
- on the same computer, twice in a row?
- if you run it on different computers?

I don't see anything in the query itself or its subquery that looks
ambiguous.

Hi,

Sorry... It's a Access database. the query is as follow:

SELECT t_resultadoslaboratorio.*
FROM ((t_paciente
LEFT JOIN t_adulto ON t_paciente.nid = t_adulto.nid)
LEFT JOIN t_crianca ON t_paciente.nid = t_crianca.nid)
INNER JOIN t_resultadoslaboratorio ON t_paciente.nid
= t_resultadoslaboratorio.nid
WHERE ((t_resultadoslaboratorio.nid Is Not Null)
AND (t_resultadoslaboratorio.dataresultado =
(select min(dataresultado)
from t_resultadoslaboratorio r
where t_paciente.nid = r.nid
and r.codexame = 'CD4'
and r.codparametro = 'Cd4 Absoluto'))
AND (t_paciente.emtarv = True)
AND (t_resultadoslaboratorio.codexame = 'CD4')
AND (t_resultadoslaboratorio.codparametro = 'Cd4 Absoluto'));

The table t_paciente has a one to one relationship with t_adulto e
t_crianca,

[quoted text clipped - 21 lines]

thanks,
Carla

 

[sigava77 via AccessMonster.com]

Sorry for the delay response (I was out):

1. The data type of nid in all tables is the same (text)
2. The version is access 2003 SP2
3. The version of this file is 4.0.8618.0
4. I’m running the query in one computer. The database is in the same
computer and there are no one connected to it. Between the two operations
nothing happens

Thanks,
Carla

Is there anything identifiable about the records it returns sometimes and
not others?

1.
What is the data type of the fields involved in the JOINs and the WHERE
clause? For example, if you open t_adulto in design view, what is the data
type of nid? And in t_paciente, what is the data type of nid?

2.
What version of Access is this? And what service pack? (See Help | About)

3.
Locate msjet40.dll (typically in c:\windows\system32.)
Right-click, and choose Properties.
On the Version tab, what is version is it?

4.
Is there any chance that someone/something could be changing the data
between the times when this gives different results?

Hi,
it returns a different number of records. I'm running it at the same

[quoted text clipped - 42 lines]

 

[Allen Browne]

Hi Carla

Sorry: I have no idea why one query would return different results from your
database when nothing else has changed.

Even if the database were corrupt, I fail to see how it would do this, so
there seems little point in going through the "how to solve corruptions"
routines.

The clue must be in the records that are sometimes returned and other times
not.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sorry for the delay response (I was out):

1. The data type of nid in all tables is the same (text)
2. The version is access 2003 SP2
3. The version of this file is 4.0.8618.0
4. I'm running the query in one computer. The database is in the same
computer and there are no one connected to it. Between the two operations
nothing happens

Thanks,
Carla

Is there anything identifiable about the records it returns sometimes and
not others?

1.
What is the data type of the fields involved in the JOINs and the WHERE
clause? For example, if you open t_adulto in design view, what is the data
type of nid? And in t_paciente, what is the data type of nid?

2.
What version of Access is this? And what service pack? (See Help | About)

3.
Locate msjet40.dll (typically in c:\windows\system32.)
Right-click, and choose Properties.
On the Version tab, what is version is it?

4.
Is there any chance that someone/something could be changing the data
between the times when this gives different results?

Hi,
it returns a different number of records. I'm running it at the same

[quoted text clipped - 42 lines]

thanks,
Carla

 

[sigava77 via AccessMonster.com]

HI Allen,

Can it be related with the number of records to be returned (limited to 1000),
as it has more than 1000 record? I don’t know…

Carla

Hi Carla

Sorry: I have no idea why one query would return different results from your
database when nothing else has changed.

Even if the database were corrupt, I fail to see how it would do this, so
there seems little point in going through the "how to solve corruptions"
routines.

The clue must be in the records that are sometimes returned and other times
not.

Sorry for the delay response (I was out):

[quoted text clipped - 33 lines]

 

[Allen Browne]

If the number is exactly 1000, perhaps you have applied some kind of server
filter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

HI Allen,

Can it be related with the number of records to be returned (limited to
1000),
as it has more than 1000 record? I don't know.

Carla

Hi Carla

Sorry: I have no idea why one query would return different results from
your
database when nothing else has changed.

Even if the database were corrupt, I fail to see how it would do this, so
there seems little point in going through the "how to solve corruptions"
routines.

The clue must be in the records that are sometimes returned and other
times
not.

Sorry for the delay response (I was out):

[quoted text clipped - 33 lines]

thanks,
Carla