Data Mismatch Error

[tangoman66]

I am trying to copy data from one database to another and rebuilding it in a similar way to conserve size. I have rebuilt some of the queries using the same SQL as previously however some of the queries come up with an error message "Error Data Mismatch". How do I avoid this? Does this mean that I cannot copy and paste the data? Will I have to re-enter all the data?

 

[Mile-O]

It means that you are trying to put information into one field from another where their data types do not match.

i.e. putting a string into a date field

 

[tangoman66]

I have checked all the field types and they are the same as I had previously. Could there be anything else producing this error?

Based on what you said does it still mean that it is possible to copy and paste data from one database to another?

 

[Mile-O]

Why not use an APPEND query from one database to the other?

 

[tangoman66]

As you may be aware from my other posts I am creating an Options Database for a secondary school. What I have realised is that in by tblChoice i have entered the values for the drop down box whereas I should have used the data in the tblSubject for the subject names. I have tried to change this but it has stopped the queries from working so I tried to recreate my database.

I have recreated the tables using exactly the same fields and data types as before and this time used the lookup wizard to extract values from tblSubject instead so when a new subject is added/old subject dropped the drop down box is changed accordingly. However I am trying to transfer the SQL across from the other database and I get the data mismatch error already descried.

One think to point out though is that when I looked up values from tblSubject it changed the data type to number (Why?) so I changed it back to text as in the original database. Is this what is causing the problem and if so how do I change it so that the subject names are still shown but the lookup still returns values from tblSubject.

Cheers,

 

[Mile-O]

And you are sure there's nothing like this:

OldTable
Student (text)


NewTable
StudentID (autonumber)
Student (text)

 

[tangoman66]

This is the old table structure

tblSubject

Subject ID - autonumber
PRIMARY KEY Subject Name - text
Max Class Size - text

tblChoice

PRIMARY KEY UPN Number - text
Choice 1 - text (lookup values typed in)
Choice 2 - text (lookup values typed in)
Choice 3 - text (lookup values typed in)
3 Choices Entered - yes/no

tblPupil

PRIMARY KEY UPN Number - text
Forename - text
Surname - text
Form - text (Lookup 3 typed in values)

This is the new table structure

tblSubject

Subject ID - autonumber
PRIMARY KEY Subject Name - text

tblChoice

PRIMARY KEY UPN Number - text
Choice 1 - text (lookup values tblSubject.[Subject Name])
Choice 2 - text (lookup values tblSubject.[Subject Name])
Choice 3 - text (lookup values tblSubject.[Subject Name])
3 Choices Entered - yes/no

tblPupil

PRIMARY KEY UPN Number - text
Forename - text
Surname - text
Form - text (Lookup 3 typed in values)

When I looked up the values from the table the data tyoe changed to number but I changed it back to text

 

[Mile-O]

Text values are not advised as Primary Keys - they take up too much space meaning that querying joined tables will take considerable longer as the Jet Engine struggles to work along the indexes.

I don't understand why SubjectID is not the primary key in the Subject table. It should be.

I still don't like the repeating group.

 

[tangoman66]

Subject ID is not the primary key as I require the relationship between Choice and subject name to be one-many not one-one. I found this was the only way of doing so.(if there is another way i'm all ears).

i have got the repeating group as i believed it was an easier way to show an individual pupils 3 choices rather than have 3 separate choices for one pupil. Would having three separate choices for a pupil be easier to work with for my queries if so i would probably change to your suggestion (which i still have as another document). I would also definitely change if it makes my trial and error scenario easier to generate

 

[Mile-O]

Subject ID is not the primary key as I require the relationship between Choice and subject name to be one-many not one-one. I found this was the only way of doing so.(if there is another way i'm all ears).

In this scenario - Choice1, Choice2, and Choice3 are all number fields. They are foreign keys to the SubjectID autonumber (whicj should be the Primary Key).

 

[tangoman66]

I understand what your saying but I want the choices to be the subject named not the subject ID's

 

[Mile-O]

I understand what your saying but I want the choices to be the subject named not the subject ID's

I don't understand why. And I don't think it's got anything to do with my comprehension.

The reason for using the ID is because queries bring all this together - that's part of their job.

 

[tangoman66]

When I am trying to view a pupils choices it will a lot easier if u could see the actual subject not its ID number. Is there a way to have ID as the primary key but for the subject name to still appear in the drop down box on the form. I am assuming that I would have to create relationships linking Subject ID to choice1 choice2 and choice3 as at the minute I use subject name instead of Subject ID

 

[tangoman66]

???

 

[Mile-O]

When I am trying to view a pupils choices it will a lot easier if u could see the actual subject not its ID number. Is there a way to have ID as the primary key but for the subject name to still appear in the drop down box on the form.

Yes. You select the ID and the field you want to display into the combobox's RowSource.

i.e.

SELECT SubjectID, Subject
FROM tblSubjects
ORDER BY Subject;


Set the combos' ColumCount to 2
Set the combo's ColumnWidths to 0

 

[tangoman66]

At the current time the row source reads...

SELECT tblSubject.[Subject ID Number], tblSubject.[Subject Name] FROM tblSubject;

do you suggest changing it to...

SELECT SubjectID, Subject FROM tblSubjects ORDER BY Subject;

 

[Mile-O]

No, that's fine. You just need to set the ColumnWidth, and ColumnCount.

 

[tangoman66]

I've done as you said and now it asks me to enter a subject name

After entering a name the drop down box appears but it has no data in it.

 

[Mile-O]

On the RowSource put a query that selects the ID and the name of the subject from your subjects table- I don't know their exact names. Don't copy me verbatim.

 

[tangoman66]

My current row source is...

SELECT [Subject ID Number], [Subject Name] FROM tblSubject ORDER BY [Subject Name];