Multiple records when there should only be one

[N3w2access]

I have 2 tables - Table1 has personal data, table2 has error types with comments. I am using the policy reference to join the tables from table1 to table2 but when there is more than 1 error it is only showing the one error twice.

For Example: In table2 there can be pol ref aaa1 with Error1 next row aaa1 error2. some policy refs in table1 will not be in Table2 as table only holds the error information.

Can you advise how I can join these as what I have done so far is joined the tables together and put in the form
table1.polref,
table2.error_type put into the control source =IIF(Error_Type="error1",Error_Type, Null)


table2.error_type put into the control source =IIF(Error_Type="error2",Error_Type, Null)
But this does not work. Do I need to create an Event for all of this?
Many thanks.

[amrut]

If table2 holds all the error types and corresponding comment for that error type then
you will need a third table with polycyREF and error types asscociated with that policy. join this table to Policy in table1
If comments keep on changing for each policy then
table2 should have only error types and
a third table will hold polycyREF, comments and error types asscociated with that policy. join this table to Policy in table1

Post a screenshot of your table relationships for more help from experts on the forum.

[JoeM]

Can you post the SQL code of your query where you join these two tables so we can see what it looks like?

[N3w2access]

Can you post the SQL code of your query where you join these two tables so we can see what it looks like?

This is not in SQL its in Access, I have not written any SQL code.

[N3w2access]

Sorry if I am not understanding but if table2 already has the policy ref and error type I dont understand why I would need a third table? I thought joining the policy ref from table 1 to policy ref in table 2 should work. All that I want to happen is when the analyst enters the comment to Error1 it stores it in table 2 if and again if there is another error and so on - Every policy can have multiple errors. Don't know why but when ever I try and add a image it doesnt seem to attach but I am more than happy to email the image if that helps.

[JoeM]

This is not in SQL its in Access, I have not written any SQL code.

You "actually" are writing a form of SQL, you probably just don't realize it!

Go into your Query in Access, and change it to SQL view. There's the SQL code for your query. Copy and paste it here.

[amrut]

I think either one of us is not understanding the table relationships you require/have.
Post a screenshot of your table relationships for more help from experts on the forum.

[N3w2access]

I keep trying to insert image or use the go advanced settings to upload the png file but it shows that its attached but hasnt. To simplify I have created a new table with 3 records and 3 fields:
[policyRef]= AAAA1 [ErrorType]= "ErrorType1", [Comments]="No Location"
[policyRef]= AAAA1 [ErrorType]= "ErrorType2", [Comments]="Missing Data"
[policyRef]= AAAA2 [ErrorType]= "ErrorType3", [Comments]="Not Approved"
On the form I want to see only 2 records.
Form layout [Pol Ref], [ErrorType] ,[Comments],
[ErrorType] ,[Comments] (for the other error).
In the Control Source I am doing IIF([ErrorType]="Error1",ErrorType,Null). Sorry for not being able to send a screenshot but hopefully the above is clear.

[JoeM]

Still looking for that SQL code (see post #6 above for instructions)...

Or are you not basing your Form on a Query in the first place (which might be the cause of your problem!)?

[N3w2access]

I am not using a query sorry for not advising sooner.

[JoeM]

I am not using a query sorry for not advising sooner.

I think that may be the cause of much of your grief, you are making things a lot harder for yourself than they have to be.

Use a query to join your records, select the records you want to return, return the fields you want, and perform any calculations.
Then, use this query as the Record Source of your form.

The only other ways to return data from two different tables on a single form is to use Subforms or Lookups.