Very wierd problem

[Spidey3721]

I am trying to put together a totals query that will sum up job costs
([Cost]), grouped by both [ProjectID] and by [CategoryID] (Cost Category).

I was getting some very high and inaccurate numbers for one of the projects,
so to try and troubleshoot I changed the Total row under the [Cost] field
from SUM to COUNT. I noticed that, for the job that is giving me inaccurate
numbers, the count is extremely high and does not match what I see when I
view the table itself.

(Normally each job has 2 or three cost entries for each category. This one
job is showing over 100 and when I view hte table - there are only the 2 or
3 that I would expect to see...)

Does anyone have any idea where these "phantom" cost entries could be
hiding, and how I might be able to delete them ?

 

[Rebecca Riordan]

Are you sure you've specified links between tables? Unusually high record
counts in a query are often the result of forgetting to do this, in which
case Jet will combine every row in TableA with every row in TableB.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

 

[Spidey3721]

I just cut and pasted all VISIBLE records into a new table and then saved it
over the old one (that had the phantom records in it)...

No more problem - very strange though...

 

[Spidey3721]

Unfortunately - yes I checked that. It was really strange because it only
occurred with one project out of many. I solved it by cutting and pasting
the visible data into a new table and saving over the old one - it now works
fine.

The only thing that I can think of is that it could have happened when I was
designing a form for data entry into this table.

I remember that I kept using this same ProjectID as a test case when I was
designing the form and I remember a number of times when it tried to prevent
me from closing this form because a new record didn't have information for
fields that were required - I would tell it to close anyways. That probably
did something to it.

Even if that was the cause, however, I still would like to know how I could
purge out any bad (invisible) records...

Very strange...

Are you sure you've specified links between tables? Unusually high record
counts in a query are often the result of forgetting to do this, in which
case Jet will combine every row in TableA with every row in TableB.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

I am trying to put together a totals query that will sum up job costs
([Cost]), grouped by both [ProjectID] and by [CategoryID] (Cost Category).

I was getting some very high and inaccurate numbers for one of the projects,
so to try and troubleshoot I changed the Total row under the [Cost] field
from SUM to COUNT. I noticed that, for the job that is giving me inaccurate
numbers, the count is extremely high and does not match what I see when I
view the table itself.

(Normally each job has 2 or three cost entries for each category. This one
job is showing over 100 and when I view hte table - there are only the 2 or
3 that I would expect to see...)

Does anyone have any idea where these "phantom" cost entries could be
hiding, and how I might be able to delete them ?

 

[John Vinson]

Does anyone have any idea where these "phantom" cost entries could be
hiding, and how I might be able to delete them ?

It's probably due to joining the table to another table; if the linked
table has 100 records, you'll get the same cost entry duplicated 100
times. Try opening the Query and turning off the Totals (click the
Greek Sigma icon) and see which records are duplicated; check to see
if the links are correct.

If you wish, you could post the SQL view of the query here, someone
might spot the problem.