Rob4077
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.PayCompon entId, tblTimeSheetComponents.fkP ayComponen tId
FROM tblPayComponents INNER JOIN tblTimeSheetComponents
ON tblPayComponents.PayCompon entId = tblTimeSheetComponents.fkP ayComponen tId;
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.PayCompon
FROM tblPayComponents INNER JOIN tblTimeSheetComponents
ON tblPayComponents.PayCompon
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
/gustav
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
So rebuild your database.
/gustav