Basic Query Criteria Problem

[ormsk]

I am using Access 2000 on XP.

I am either losing my mind or i am doing something so basically wrong.

I have a table (1900 records). I was going to build a query with a series of tables, but have encountered a problem.

I am expecting 15 records to show, but only one record is.

To find the error (or logic error), I have one table that I know has 15 records with the same value in a particular field, but the query only returns one record when I enter that value as the criteria and run the query.

I check edthe SQL text that Access generates automatically and there is no DISTINCT or DISTINCTROW there.

I am not a novice, but i am flumoxed by this.

I have recently swapped computers, but can't see anything I may have done or not done to cause this.

Can anyone help?

**********************************
There is more than one way to skin a cat...but who wants a skinned cat?

 

[SkipVought]

Hi,

My crystal ball is kinda cloudy this morning, to it might help a tad if you supplied the...

SQL

and...

sample rows form your table.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[SkipVought]

Hi,

My crystal ball is kinda cloudy this morning, to it might help a tad if you supplied the...

SQL

and...

sample rows from your table.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[ormsk]

Hi

SQL statement =

SELECT FRAMES.PLAYER_ID
FROM FRAMES
WHERE (((FRAMES.PLAYER_ID)=1201));

When I view the results without any criteria, there are 15 records with 1201 in this field.

Also, when i open up the table, the 'Filter By Selection' only brings up one record (again i can see 15 records in there when the filter is turned off).

This table is linked (dBASE IV), but i have been using these tables for some times before and can only think there is some setting in Access that is only showing one record.


**********************************
There is more than one way to skin a cat...but who wants a skinned cat?

 

[PHV]

To be sure that all 15 records have same value:
SELECT PLAYER_ID, Count(*) As ShouldBe15
FROM FRAMES
GROUP BY PLAYER_ID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ormsk]

I think i have found the problem:

Even though I can't change the structure of the file (because it is linked to a .dbf file), I can view the structure and the field in question has an index, but disallowing duplicates. Dbase IV does not have such an indexing capability so i will have to either modify the dbase file structure or find some other work-around.

**********************************
There is more than one way to skin a cat...but who wants a skinned cat?

 

[ormsk]

To PHV

Thanks, it does show 15, but I have discovered why Access is only showing 1 record, i now have to work out how i can get it to interpret the dbase IV index as I would like.

I am going to try to re-link the file.

**********************************
There is more than one way to skin a cat...but who wants a skinned cat?