Link to home
Start Free TrialLog in
Avatar of Rob4077
Rob4077Flag for Australia

asked on

Fields don't match on a query

I have tblPayComponents that has, as a Primary Key, an autonumber field named PayComponent
I have another table called tblPayComponents that has a field called fkPayComponentId that is defined as long integer Indexed Duplicates OK

When I run the following query the columns differ. I am pulling the two fields that are linked by the inner join where the two linked fields equal one another. I have never seen this behavior before - how is this possible? Shouldn't they be identical? This seems to imply that something is corrupt but I don't know what or how to fix. Any suggestions?


SELECT tblPayComponents.PayComponentId, tblTimeSheetComponents.fkPayComponentId
FROM tblPayComponents INNER JOIN tblTimeSheetComponents
ON tblPayComponents.PayComponentId = tblTimeSheetComponents.fkPayComponentId;
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Values can't both differ and be identical.
So rebuild your database.

/gustav
Avatar of Rob4077

ASKER

Does that mean all my data is corrupt and I need to rebuild historical data somehow?
First try a repair and compact. Do make a backup first.

/gustav
Avatar of Rob4077

ASKER

The database is closed/repaired every night and I had a look at some recent backups and they're all the same. I've never seen this before and it's going to mean a lot of work trying to rebuild. Great! No other words of wisdom?
SOLUTION
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rob4077

ASKER

Pat, I think you were right. I created an extra field in the table and copied the join key from the other table into it. I then renamed the new field and it appears to work fine. I have never seen anything like this before.
Avatar of Rob4077

ASKER

Points are shared for input. Gustav and Scott both made valid points and proffered suggestions to repair. I think Pat's comment is the correct cause of the fault