Hello all...
I have a problem with a single query I have build in Access 2000. Basically, I have a normalised database structure to 3NF. I have a table called Data, a table called Reports and a table called DataReports which cotains the primary keys from both data and reports which are named Data_ID and Report_Id.
Basically, what is happening is I am building a query that returns a record from DataReports using the Data_Id field as criteria as this is held on a form. I create a one to many relationship from the Reports table to the DataReports table using a link from one Report_Id to the other. I save the table and it works fine...once only!! The query is used to feed a list box on a form. The form loads correctly but when I go back into design view to make a change to something else on the form and then go back to form view, I get Data Type Mismatch error. When I look at the query, Access has changed the link from the Report_Id --> Report_Id to Report_Id --> Report_Title which is the next field down in the Reports table. Because all ID's in my database are Autonum's it is trying to link a number to text so I understand why this would not work but I dont understand why on earth access is changing my links. It is very frustrating. Could anyone help me please.
Thank you kindly..
Gareth
I have a problem with a single query I have build in Access 2000. Basically, I have a normalised database structure to 3NF. I have a table called Data, a table called Reports and a table called DataReports which cotains the primary keys from both data and reports which are named Data_ID and Report_Id.
Basically, what is happening is I am building a query that returns a record from DataReports using the Data_Id field as criteria as this is held on a form. I create a one to many relationship from the Reports table to the DataReports table using a link from one Report_Id to the other. I save the table and it works fine...once only!! The query is used to feed a list box on a form. The form loads correctly but when I go back into design view to make a change to something else on the form and then go back to form view, I get Data Type Mismatch error. When I look at the query, Access has changed the link from the Report_Id --> Report_Id to Report_Id --> Report_Title which is the next field down in the Reports table. Because all ID's in my database are Autonum's it is trying to link a number to text so I understand why this would not work but I dont understand why on earth access is changing my links. It is very frustrating. Could anyone help me please.
Thank you kindly..
Gareth
