Access 2010 calculated field in relationship

[AlexaS]

Ok, so I've spent the majority of this morning, and a good portion of last Friday looking through forum posts related to calculated fields. Here is where I'm having a problem:

I have a claims database. My three main tables are: Claims, Payments, and Corrections. On Claims I have a field called CLAIM# which is a calculated field taking the Plant abbreviation and the AutoNumber Primary Key. Payments and Corrections have a similar field taking the CLAIM# (text in these tables) and adding a 'P' or 'C' respectively. These tables also have an AutoNumber primary key.

The reason I want the Claim# to be calculated is because I don't trust end users. My goal is to automate any process that I create for an end user as much as possible. This Claim# will then be saved to the Payment and Correction tables as their Foreign key in order to link those records back to the claim table.

How can I set a relationship between the Claim# calculated on the Claim table and the Claim# on the Payment and Correction tables?

 

[xenou]

How can I set a relationship between the Claim# calculated on the Claim table and the Claim# on the Payment and Correction tables?

You have to have a common field. You need to put one of the claim numbers in one of the other tables as a foreign key. You do this by using a form/subform data entry form, where the subform has a parent-child relationship to the main form. That's the simplest way - Access does all the work for you once you say that the subform is related to the main form and provide the related fields (Access will fill in the field for you in the subform). Otherwise you have to work up a similar mechanism of your own - for instance, using VBA to populate the foreign key, or updating the field in some other way.


P.S. You don't need calculated fields and it seems very suspicious putting a plant abbreviation onto an autonumber key. The autonumber key will uniquely identify the record already. So there's no need to do anything else to it. Leave the plant abbreviation in its own field by itself.

 

[AlexaS]

You have to have a common field. You need to put one of the claim numbers in one of the other tables as a foreign key.

The Claim# field is on all three tables. On the Claims table it is calculated. On Payment and Correction it is a text field. That is because I need it to match to the Claim table. The problem is that I can't create a relationship between these three tables based on the Claim#, it keeps erring.

You do this by using a form/subform data entry form, where the subform has a parent-child relationship to the main form. That's the simplest way - Access does all the work for you once you say that the subform is related to the main form and provide the related fields (Access will fill in the field for you in the subform).

It's been a while since I worked with forms, but I figured that was how that was supposed to work.

P.S. You don't need calculated fields and it seems very suspicious putting a plant abbreviation onto an autonumber key. The autonumber key will uniquely identify the record already. So there's no need to do anything else to it. Leave the plant abbreviation in its own field by itself.

Why is that suspicious?

I DO need a calculated field. I need a Claim# field so that they can put that identifier on the print out forms and use it as a reference. They want the Plant abbreviation and then a sequential number - hence Plant abbreviation and the AutoNumber. I want it calculated because I don't want them putting in random numbers (which is what they are currently doing by plant). Plus I need a field that will attach to the Payment and Correction tables in order to link the records.

 

[xenou]

The Claim# field is on all three tables. On the Claims table it is calculated. On Payment and Correction it is a text field. That is because I need it to match to the Claim table. The problem is that I can't create a relationship between these three tables based on the Claim#, it keeps erring.

I have never used a calculated field. It is possible it cannot be used as a primary key. Allen Browne shares my dim view of these things: Microsoft Access tips: Calculated Fields I recommend not using calculated fields especially where primary keys are concerned. Your experience (getting an error here) suggests that it is not possible anyway. I can't find any confirmation of this, however, so it is possible the error is for some other reason.

I DO need a calculated field. I need a Claim# field so that they can put that identifier on the print out forms and use it as a reference. They want the Plant abbreviation and then a sequential number - hence Plant abbreviation and the AutoNumber. I want it calculated because I don't want them putting in random numbers (which is what they are currently doing by plant). Plus I need a field that will attach to the Payment and Correction tables in order to link the records.

The autonumber already uniquely identifies the record. It already is a primary key. If they want the plant number to show up on the forms and reports then no problem - put it on the forms and reports. The Plant Number is still part of the record, and the record is still uniquely identified by the claim number. So all you need to do is show both values as a concatenated string: [ClaimNumber] & "-" & [PlantNumber]. If you want to be sure you always have a plant number, make it a required field. This is all very easy without any complications such as using calculated fields! Plant number would need to be part of the primary key only if you could have the same "claim number" on two records, one for one plant and one for the other. But even then we don't need calculated fields. You just use a composite primary key (two fields, claim number and plant number, as the primary key). I am not sure if you can make an autonumber field part of a composite key, so again it's simplest to just use the claim number as the primary key if you can.

ξ

 

[AlexaS]

I have never used a calculated field. It is possible it cannot be used as a primary key. Allen Browne shares my dim view of these things: Microsoft Access tips: Calculated Fields I recommend not using calculated fields especially where primary keys are concerned. Your experience (getting an error here) suggests that it is not possible anyway. I can't find any confirmation of this, however, so it is possible the error is for some other reason.

I am not trying to use it as PRIMARY KEY. I am trying to connect the claim number to a related field on the other tables. Since the Claim table is the parent table and the Payment and Correction tables are the child tables, I don't understand why I can't link the calculated field to a field on the other tables. This is my question. Why can I not link these items.

And yes, I know, I've read that link twice now.

The autonumber already uniquely identifies the record. It already is a primary key. If they want the plant number to show up on the forms and reports then no problem - put it on the forms and reports. The Plant Number is still part of the record, and the record is still uniquely identified by the claim number. So all you need to do is show both values as a concatenated string: [ClaimNumber] & "-" & [PlantNumber]. If you want to be sure you always have a plant number, make it a required field.

I think we're getting confused on terminology here. There is no plant number. The record is being uniquely identified by the AutoNumber. The Plant is being identified by an abbreviation (generally two characters). The claim number does not exist without the calculated field [PLANT]&[ID] (i.e. PLANT = DE and ID = 1. DE + 1 = CLAIM# DE1). Without a claim number being stored, I don't know how to link the Payment and Correction tables to the Claim table.

This is all very easy without any complications such as using calculated fields! Plant number would need to be part of the primary key only if you could have the same "claim number" on two records, one for one plant and one for the other. But even then we don't need calculated fields. You just use a composite primary key (two fields, claim number and plant number, as the primary key). I am not sure if you can make an autonumber field part of a composite key, so again it's simplest to just use the claim number as the primary key if you can.

How can I use the claim number as the primary key if calculated fields are not possible to be used as a primary key!? Claim number is the calculated field comprised of Plant and the AutoNumber. It does not exist without a calculation!

Also - PLANT cannot be a primary key since the PLANT abbreviation will be used more than once in the database.


Here is my question: CAN I CREATE A JOIN BETWEEN A CALCULATED FIELD ON MY PARENT TABLE AND A TEXT FIELD ON A CHILD TABLE? HOW DO I DO THIS? I would rather have the calculated field than trust that the end user is going to input the correct sequential number to a record into the CLAIM# field on a form.

 

[xenou]

Here is my question: CAN I CREATE A JOIN BETWEEN A CALCULATED FIELD ON MY PARENT TABLE AND A TEXT FIELD ON A CHILD TABLE? HOW DO I DO THIS? I would rather have the calculated field than trust that the end user is going to input the correct sequential number to a record into the CLAIM# field on a form.

as far as I know this is not possible - I can find no documentation to definitively say one way or the other. If it's not working and you've already tried it then I suppose that's your answer.
ξ

 

[AlexaS]

as far as I know this is not possible - I can find no documentation to definitively say one way or the other. If it's not working and you've already tried it then I suppose that's your answer.
ξ

Thank you for your time. Although, if I assumed every time I wasn't able to do something that it wasn't possible, I wouldn't have come up with half the code that I've mangled together from other pieces. However, last night I did get a book that I ordered, titled Access 2010: The Missing Manual. Upon perusal I found that when linking fields (with the exception of the AutoNumber field), they do need to be the same data type. I managed to work up a relationship by having the Claim# in the Payment and Correction tables be a lookup, but I'm not sure it will work once I go to the form entry. We'll have to see.

Thanks again.

 

[xenou]

Okey dokey. Please do consider not using calculated fields, especially not for any of your keys, primary or otherwise. You do not need them - you can work the two fields as easily (or more easily) than a single calculated field. It will also enhance the portability of your database, since you can upgrade to SQL server or interface with other systems. Not to mention all the other concerns noted by Allen Browne. You seem to have boxed yourself into a corner saying "I have to do it this way". But most likely you don't.

ξ

 

[Sharkey71]

I understand what AlexaS is trying to do. I was also trying to use a calculated field in a table and then using it to create a relationship between two table. I didn't want a user to enter the text that was long, complicated, and prone to errors. By entering the data in small chunks in a data entry form, it eliminated a lot of errors. The problem came along, like AlexaS, when I tried to create the relationship. I scoured the internet and forums to see if a simple fix was available. I couldn't find much. My eventual fix or work around was to create a "make table" query, joined the fields together like I did in the calculated field, added all the other fields, and ran the query. I used an alias:[field1]&"aaa"&[field2]&"bbb"&[field3] ..etc. Make sure you don't have the calculated field you created in the first table in the query because it will create a pop-up error message. The table that is made has the combined data as I want it and is a text field. The only issue is that this is not dynamic. My data is entered and not changing, no new data is entered. That may not be helpful if you continually enter data and have to keep running the "make table" query. But maybe there is a work around for that. Hopefully this will help anyone that lands here looking for a solution.