Newbie Calculated Field Question

[mikeymay]

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

 

[ranman256]

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

 

[mikeymay]

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]

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]

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:

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.

 

[mikeymay]

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]

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.