By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,960 Members | 1,009 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,960 IT Pros & Developers. It's quick & easy.

Those backups just have no consistency

P: 92
How do,

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
  1. INSERT INTO tblBookings ([Outing ID],
  2.                             [Child ID],
  3.                             [Amount Paid],
  4.                             [Deposit Paid],
  5.                             Transport ) 
  6.  
  7. SELECT tblOutingDetails.[Outing ID],
  8.               tblPersonalDetails.[Child ID],
  9.               tblBookingsAppend.[Amount Paid],
  10.               tblBookingsAppend.[Deposit Paid],
  11.               tblBookingsAppend.Transport 
  12.  
  13. FROM (tblBookingsAppend INNER JOIN (tblOutingDetailsAppend INNER JOIN tblOutingDetails 
  14. ON tblOutingDetailsAppend.[Outing Title] = tblOutingDetails.[Outing Title]) 
  15. ON tblBookingsAppend.[Outing ID] = tblOutingDetailsAppend.[Outing ID]) INNER JOIN (tblPersonalDetailsAppend INNER JOIN tblPersonalDetails 
  16. 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]))
  17. ON tblBookingsAppend.[Child ID] = tblPersonalDetailsAppend.[Child ID]
which worked and was very much appreciated. I later altered this to help in the restore of another table in the same situation, and again it worked a treat. i now try to implement it on a third table and disaster strikes.

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
  1. INSERT INTO tblWaitingList ([Outing ID],
  2.                             [Child ID],
  3.                             [Date Added]) 
  4.  
  5. SELECT tblOutingDetails.[Outing ID],
  6.               tblPersonalDetails.[Child ID],
  7.               tblWaitingListAppend.[Date Added]
  8.  
  9. FROM (tblOutingDetailsAppend INNER JOIN tblOutingDetails
  10. ON tblOutingDetailsAppend.[Outing Title] = tblOutingDetails.[Outing Title]) INNER JOIN ((tblPersonalDetails INNER JOIN tblPersonalDetailsAppend
  11. 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
  12. ON tblPersonalDetailsAppend.[Child ID] = tblWaitingListAppend.[Child ID])
  13. 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
Mar 12 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,347
The joins could be the cause. You may be joining a text field on a numeric field.
Mar 17 '07 #2

Denburt
Expert 100+
P: 1,356
Rabbit is right AutoNumber is just that a number field, number fields joined to text fields just don't work and if somehow you have managed to make it work like this then CHANGE it quickly to prevent any data loss.

Good luck :)
Mar 19 '07 #3

NeoPa
Expert Mod 15k+
P: 31,343
Is this still an outstanding problem.
I know you posted another one recently which you managed to resolve yourself. Is this another of those or is an answer still required?
Mar 20 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.