Solved Calculated feild using values from multipe tables (1 Viewer)

[MDKay]

Good afternoon all,

I have two tables, the first is 4 colums:
Type
Width
Projection
Cost
This is effectively a row for every possible type, width and projection combination and how much it would cost.

My second table is for the entire order with costs, customer details and all calculations. What I am trying to do is have the cost brought through from the first table based on the type, width and projection entered into the second table. I know I can do this at form level but I want it to do this at table level. Is this possible?

 

[theDBguy]

Hi. Welcome to AWF!

Not a good idea and not possible using a simple Calculated Column. It may be possible using a data macro. It's easier to just use a Query to get and display the calculated value.

 

[namliam]

Use a query as per the suggestion of @theDBguy suggested.

ps. I realize it is only your first four columns, but where is your PK? It is customary to have this as your first column.

 

[MDKay]

Thanks for the help both, i have gone with a cascading query within the form.
@namliam the table with 4 columns is only a reference table, there will be no data added or removed from this table. I did not see the need for a PK here. I have a PK on my master table that utilizes this table.
The way it is now set up, my master table has the same four fields with select queries to the relevant fields on my price table.

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[MDKay]

Not sure if this should be a new thread or nut but i am having an issue with the same fields and cant find a solution.

These on my main table are set a basic Text, Number and Currency fields to match those in the reference tables.

On my forms i have them set up as Cascading Combo Boxes with select queries to get values from the reference table.

When i set this up it works perfectly, however, every time i close my database it breaks when i open it and try using it again. When i do this i get the pop up asking me to specify what the previous box(es) values are. This also affects the other Cascading combo box i have on this form, however when i set the Width box (the first true cascading combo box that filters only on Type) to text and then back to Combo and reset the select query all of them work again.

The select queries are SQL not Queries and each combo box is requeried by the others.

The part that is most confusing is this works on one of my forms and keeps working after i close and open my DB. However if i create a copy of this form (and redirect my select queries to look at the fields on the copied form) it has the same issue.

 

[The_Doc_Man]

When you get pop-ups like that, Access thinks you have built a parameter query and need to tell it values. Which means that something in one of the combo box .Recordsource declarations calls for something that must not be defined correctly. This is common behavior for either spelling a field name wrong or naming the wrong source table.

 

[MDKay]

It appears to have been resolved by amending the Name Autocorrect settings on current database.

Thank you all again for the support