Mismatch in query and relationship problem

[setis]

Dear all,

I have striped down the DB as much as possible. I hope that it still make sense.

I have a problem getting results for this query. As far as I've read, it might be a problem with the relationships.

I would like to know why I can't enforce data integrity on the "Service Subtype" The message I get is:
"No unique index found for the referenced field of the primary table"

I don't know if this is the reason why the query is not working. The message I'm getting in the query is "Type mismatch in expression"

I would appreciate if somebody could point me out in the right direction here.

 

[sahh1419]

database version please

 

[setis]

database version please

access 2016

 

[isladogs]

The first issue is that the ServiceSubType field is a Number datatype in one table & a text datatype in another
Fix that then try again.

I expect you will have more issues after that.
The ServiceSubType field isn't a PK field so the linking data may not be unique

 

[setis]

The first issue is that the ServiceSubType field is a Number datatype in one table & a text datatype in another
Fix that then try again.

I expect you will have more issues after that.
The ServiceSubType field isn't a PK field so the linking data may not be unique

How should it be? In the main table ClaimDataSheet, both ServiceType and ServiceSubtype should be as Number data type since it's their PK's being saved, is that correct?

Should I change the field datetype in the actual tables Service and ServiceSubType to "number" or is it the fields in the CDS to short text?

Apologies for the rookie questions.. but I struggle with this

 

[isladogs]

You just need to link identical fields

Does this give the correct results?

 

[setis]

Thanks. Yes, it does. How are the data types for the 3 tables?

 

[isladogs]

Sorry didn't look at the other fields & have now deleted your db.

 

[setis]

Sorry didn't look at the other fields & have now deleted your db.

Thanks anyways.

If I change the datatype in tblService and tblServiceSubtype to Number, the fields get deleted and I can't retype them since it only allows numbers.

If I change the datatype in the main table to "short text" all the existing records fields get deleted and I believe that the main table is as it should, with the PKs.

 

[isladogs]

You don't need to change any datatypes if you just link the tables as I suggested. Keep what you had originally

If you want to make it clearer that fields are the same then rename fields in your tblServiceSubtype as follows:

ServiceSubType=> ServiceSubTypeDetail
ServiceSubTypeID => ServiceSubType

It isn't necessary for a PK field to have ID as part of its name

 

[setis]

Thanks anyways.

If I change the datatype in tblService and tblServiceSubtype to Number, the fields get deleted and I can't retype them since it only allows numbers.

If I change the datatype in the main table to "short text" all the existing records fields get deleted and I believe that the main table is as it should, with the PKs.

I got it now. Apologies.

I can see noe that I wasn't linking to the SubType PK. My bad.

Thank you so much!