Deleting primary key leads to strange query results ?!?

[Guest]

Does anyone have knowledge of whether deleting the primary key of a table
could cause strange results from queries?

I have a GROUP BY (on a date column) query that returns results grouped
correctly up to a certain date and ungrouped after this date. The date is
around the time that I removed the primary key of the table (which was an
autonumber key).

 

[Ken Snell \(MVP\)]

Do you have an ORDER BY clause in the query's SQL statement? If not, then
the query is not sorting the data at all, but rather is defaulting to a sort
order "from the table" (which often is the primary key field in the table,
if there is one). Always use an ORDER BY clause to ensure that you get
proper sorting.

 

[Guest]

No, because this query is feeding a report and I have Sorting and Grouping
set so I thought it wasn't necessary.
The problem went away when I created a new table and copied all the data
into it.

 

[Ken Snell \(MVP\)]

No, because this query is feeding a report and I have Sorting and Grouping
set so I thought it wasn't necessary.

You're correct, you do not need an ORDER BY clause in the query if the
report will be sorting and grouping -- it just adds extra processing time
for the query. But, if this is the use of the query, why does it matter to
you in which order the query returns the records?

The problem went away when I created a new table and copied all the data
into it.

It may or may not stay this way as you add more data to the table. There is
no need in this case to create a new table as you did. If you want the query
to sort in a specific order, use an ORDER BY clause. Else, let the report do
it.

 

[Guest]

The order does not matter, the issue was the grouping specified by the GROUP
BY clause.