# Newbie Calculated Field Question

#### mikeymay

##### Well-known Member
Apologies for how simple this probably is but I can't find anything on the web that helps...

I have a table named Clients. This consists of two fields - Client (text) and Benchmark (Value)

I have another table where various fields are selected from drop down, one of the being a Client and another being a value that is entered named Qty.

I then want another field that takes the Benchmark for the selected Client and multiplies by the Qty value.

I can see how to add a calculated field but I can't seem to be able to select the Benchmark field to multiply by the Qty entered.

I am using Access 2013.

Thanks

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### ranman256

##### Well-known Member
You put the calculated field in a query.
If you query tClients, then:
select [name], BM, CalcBM: [BM]*Qty

#### mikeymay

##### Well-known Member
Thanks ranman

I am still a little fuzzy with this as I literally started learning Access this week so could I just clarify my problem -

I have 2 tables
Projected
Clients

In Projected I have 3 fields
Client (text)
Qty (number)
Hours

In Clients I have 2 fields
Client
Benchmark

In the Projected table I want the Hours field to be the result of the following
Qty * Client Benchmark

E.g.
Client 1 entered in Projected table where their Benchmark is 0.50
10 entered in Qty in Projected Table
Hours = 5

I have attempted to build a query but I just can't seem to get it to work and how do I then get that query into my Projected table?

Apologies for the basic level of the problem but I have tried resources on the web and the Dummies book and after several hours and attempts still no joy.....

A step through what I need to do would be hugely appreciated.

Thanks

#### ranman256

##### Well-known Member
you dont put queries IN tables. They are 2 diffent things. You run queries ON tables to pull data out.
Your query would pull the fields from the table and do the calculations.

#### xenou

##### MrExcel MVP

As noted, in general calculated fields do not belong in tables. You would not open a table to view your data, you would open a query. The query is relatively simple but it needs to obey the laws of relational database design, which means you should have a primary key in each table. The query then displays the table data with the calculated data:
Code:
``````SELECT
Clients.Client,
Qty,
Qty * Benchmarks As Hours
FROM
Clients LEFT JOIN Projected
ON Clients.Client = Projected.Client``````

Be careful. There are many gotchas with databases. You really should get a basic course in databases under your belt before you start working with them (either reading a book or taking an intro class).

The most recent versions of Access have some trigger features that may work in the way you originally described but that's something I don't have any experience with. You could also potentially use a form with code in it to update data on the fly as it is entered - that would be a more advanced solution. Using a query as above is really the simplest way to go.

Last edited:

#### mikeymay

##### Well-known Member
Thanks xenou

The SQL you provided kind of gives me what I need as in the query returns all the clients and the total Qty for each one for all the entries in the Projected table.

I'm aware I should really go on a course but I find that on the job training works much better for me as I can implement learnings into what I am working on, although I am using the Dummies book to help as well.

I suppose the next question is - is Access the best platform for what I am trying to achieve? The DB is something that is up and running in Excel and I wanted to migrate to Access to basically learn how to build databases.

If Access is a viable platform then I need to work out how get this to work.

As each entry in the 'Projected' table is effectively a 'job' that needs to produce a 'Hours' figure from the 'Qty' and the @Benchmark' for the selected 'Client', what is the best way to do this? The entries in the 'Projected' table will be added to a number of times each day.

Hope you don't mind me asking for advice and any you can give will be hugely appreciated!

Thanks

#### xenou

##### MrExcel MVP
Access is certainly a viable platform. As a general rule, you will never store "hours" since that can be generated (calculated) from the other fields. Also it is usually advisable not to use tables directly but to create forms for interacting with the data (in fact, a form in datasheet view can be made to *look* exactly like a table does, but you have more options for filtering and displaying data, as well as validating inputs and using form events to interact with the data as it is being entered.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,108
Messages
5,857,431
Members
431,879
Latest member
KiwDaWabbit

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back