92
A while ago i had the problem of backing up a Access 2002 table with unique data that changed in some tables but not others, resulting in restore failure. This was sorted by NeoPa with the (Modified) SQL code:
Expand|Select|Wrap|Line Numbers
- INSERT INTO tblBookings ([Outing ID],
- [Child ID],
- [Amount Paid],
- [Deposit Paid],
- Transport )
- SELECT tblOutingDetails.[Outing ID],
- tblPersonalDetails.[Child ID],
- tblBookingsAppend.[Amount Paid],
- tblBookingsAppend.[Deposit Paid],
- tblBookingsAppend.Transport
- FROM (tblBookingsAppend INNER JOIN (tblOutingDetailsAppend INNER JOIN tblOutingDetails
- ON tblOutingDetailsAppend.[Outing Title] = tblOutingDetails.[Outing Title])
- ON tblBookingsAppend.[Outing ID] = tblOutingDetailsAppend.[Outing ID]) INNER JOIN (tblPersonalDetailsAppend INNER JOIN tblPersonalDetails
- ON (tblPersonalDetailsAppend.Forename = tblPersonalDetails.Forename) AND (tblPersonalDetailsAppend.Surname = tblPersonalDetails.Surname) AND (tblPersonalDetailsAppend.[Date of Birth] = tblPersonalDetails.[Date of Birth]) AND (tblPersonalDetailsAppend.[Post Code] = tblPersonalDetails.[Post Code]))
- ON tblBookingsAppend.[Child ID] = tblPersonalDetailsAppend.[Child ID]
The Table tblWaitingList is a list of all people on the waiting list for an Outing, consisting of:
Waiting List ID - Autonumber - PK
Child ID - Text - FK
Outing ID - Text - FK
Date Added - Date/Time
which will be restored from a backup table tblWaitingListAppend, with the Child IDs and Outing IDs coming from tblPersonalDetails and tblOutingDetails as in the code above.
The current code im trying to use looks like:
Expand|Select|Wrap|Line Numbers
- INSERT INTO tblWaitingList ([Outing ID],
- [Child ID],
- [Date Added])
- SELECT tblOutingDetails.[Outing ID],
- tblPersonalDetails.[Child ID],
- tblWaitingListAppend.[Date Added]
- FROM (tblOutingDetailsAppend INNER JOIN tblOutingDetails
- ON tblOutingDetailsAppend.[Outing Title] = tblOutingDetails.[Outing Title]) INNER JOIN ((tblPersonalDetails INNER JOIN tblPersonalDetailsAppend
- ON (tblPersonalDetails.[Post Code] = tblPersonalDetailsAppend.[Post Code]) AND (tblPersonalDetails.[Date of Birth] = tblPersonalDetailsAppend.[Date of Birth]) AND (tblPersonalDetailsAppend.Forename = tblPersonalDetails.Forename) AND (tblPersonalDetails.Surname = tblPersonalDetailsAppend.Surname)) INNER JOIN tblWaitingListAppend
- ON tblPersonalDetailsAppend.[Child ID] = tblWaitingListAppend.[Child ID])
- ON tblOutingDetailsAppend.[Outing ID] = tblWaitingListAppend.[Outing ID]
and although, to me, exactly the same as the previous SQL produces a "Type Missmatch in Expression" error.
The HELP button wasnt too much help, saying that this meant that two related fields were of a different data type, for example: and AutoNumber and Text.
This is what confuses me, as there was no problem when Autonumber and text fields were linked in the other restores.
What on earth is going on?
NDayave
