I had a calculated field set up in one of my tables, and it was working ok. (don't say it, yes, I really do need a stored calculated field) However, I decided that since this field has to do with payroll, it should be on a table my other users don't have access to. I created a new Table, but now Access says that it can't create the field because the calculation refers to another table. That much is correct, I do refer to one field that needs to stay where it is on the old table. Is there some way around this?

Hi,

Please don't tell anyone that I told you this, but try adding a Lookup Field in your restricted table that looks up the value in the other table so that you can use it for your calculated field.

(untested)
Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

(IMG:style_emoticons/default/evilgrin.gif) A lookup field used to store a calculated field... I like it! (IMG:style_emoticons/default/evilgrin.gif)

Hi,

I don't usually recommend using calculated and lookup fields but if you're going to break the rules, why not go all the way... (IMG:style_emoticons/default/grin.gif)

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

The way that I set up a lookup (using the wizard) creates a pulldown list. I didn't like that, so I got the idea to set up a one to one relationship. One to one because this table has a one to one relationship on the IDs. one job, one commission. It seemed to work while I was in the back end, but when I went to the front end, it started giving errors. I should have wrote them down, but didn't. I got the error even if I just clicked TblCommissions directly, vs. a form or whatever else. So I deleted that relationship, but am now wondering if I set up the original lookup wrong..

I sat staring at it for a while... and came up with this idea.. but I don't know what the ultimate outcome may be, and I wonder if it might be very bad..
The current setup between these two tables is a 1 to 1 between the two IDs. then there was the attempt to also connect the other field I need. ( ContractPrice ) in addition to the errors it gave me, it wouldn't allow referential integrity..

But... What if... I were to just make the singular 1 to 1 relationship between TblJobs and TblCommissions, using that ContractPrice field? I'm a little afraid, because what if there are two jobs with exactly the same contract price? Couldn't those commissions get mixed up, or assigned to the wrong job or something?

Hi,

I'm happy to just listen as you try to work this out on your own in case you just need a sounding board. But if you need any comment from us, it would help to know the context of the issue.

To that end, we will need to know something about the database you're trying to create. Can you describe its purpose and post the table structures?

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Sorry.. I thought that I had mentioned all the info about what I'm doing. But I guess it's helpful to have it all in one spot. The DB has several other tables, but the only two I am working with for this particular project are TblJobs, and TblCommissions. 1 to 1 relationship currently exists between the IDs. Each Job gets a commission calculated and paid. Hence the reason for the stored calculated field. What I paid won't change.

Most of the info required for my calculations is in TblCommissions. But I need one piece of info from TblJobs. The ContractPrice. And from there we move into the various semi-failed attempts to get that piece of information into my calculation, without taking it off of TblJobs.

forgot the attachment

Hi,

Thanks for the additional info. It helps to have it in the same location for other readers but you can also provide a link to the background info if you like.

So, once a job gets a commision, it will never get another one? If so, why even have a separate table for it?

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

About the background info, I meant it was somewhat spread amongst my previous posts in this particular thread. The reason for the separate table is in the original post. I need this data, but my other users don't. So due to the sensitive nature, I made it a separate table. (TblCommissions)

I'm not too concerned with security beyond that, as my other two users don't know how to even find and link a table, much less use queries and forms to put it into cohesive data.

(I'm the closest we have to an IT guy, but also the manager who processes commissions. 2 of my several hats)

Hi,

Understood! You know, you can do a lot using queries. You don't have to have everything in the table. What you can do in a table, you can also do in a query, and more.

So, I guess what I am saying is that when it's time for you to do your computations, maybe you could just use a query that links both your tables and then you will have all the data you need in one place as if you only had one table to begin with.

In fact, you can also do that the other way too. Let's say you store all the data in one table but you don't want your users to see some of the fields from that table. One solution, make them use queries that do not include the restricted columns from the table.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

I think I understand, but I won't truly until I try it. The specific calculation I am trying to set up and store in TblCommissions.FrontEndAmount, is TblJobs.ContractPrice*.05
There are several other calculations, but this is the one that spans two tables, which access doesn't seem to like.

So if I am reading/understanding what you say, I can do that calculation in a query, and the query will save the data to the table?

My understanding/usage of calculations in queries thus far has been that it would display, but not store. I thought to store the result, the calculation has to be in the table itself. Am I wrong? I'd love to be, it would make this so much easier.

Hi,


No, you are correct. Query calculations will not be automatically stored in the table. But, you may have misunderstood what "calculated fields" actually are. They too, do not "save" the result of the calculation in the table. They only display the result, just like a query does.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

That I did misunderstand.. I tried prior to the original post to play with putting a calculation in the Default Value of some of my fields.. Because I knew I could override that result if needed and I was (relatively) sure that if one of my users or myself accidentally changed one of the fields used in a calculation that it would not change the value of what had been paid. I couldn't seem to get that to work. I don't know if I entered something wrong, or if Access just doesn't like you doing that in default values.

Anyway, moving forward.. I can't risk the values that I pay being changed on accident. So, do I need to calculate it in one spot (query or table), then manually type the result into another, non-calculated field? There is still some risk there, but it's a reduction from just using the calculation and leaving it.

I would highly recommend performing the calculation at the form level and assigning the result in a textbox bound to the table field where you want to store that value.

If you use a form for data entry, you can also trap (or prevent) accidental changes to the value.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)