How Do I Create A Table That Contains Figures Calculated From Other Tables?

Feb 18, 2008

Hi,

I have two tables:

Table A provides total sales volume of the UK shoe market from 2000-2007.

Table B provides the sales volume of different shoe manufacturers from 2000-07.

I want a third table created, called Table C. This should look exactly like Table B but instead of sales volume it shows percentage sales that are calculated by using the figures in Table A and B (i.e. [sales volume from Table B/total sales volume from Table A] * 100).

Could someone point me in the right direction please (assuming that such a table can be created, based on a calculation of figures in other existing tables).

Thank you.

View Replies


ADVERTISEMENT

Tables :: Creating PK With Calculated Autonumber To Create Relationships

Sep 24, 2012

I am trying to create four tables: Company, Contact, Activities, and Opportunities.

I want them to relate hierarchically. A Company can have many contacts, contacts can have multiple Activities and Opportunities. But you can't have contacts without a company and you can't have Activities and Opportunities without having a contact. I want all PK's in all tables to link to one another, that you cannot create one without the other.

How I can do this in Access 2010?

YYMM00000-000000-A0000
CompanyID-ContactID-ActivityID
or
YYMM00000-000000-O0000
CompanyID-ContactID-OpportunityID

View 1 Replies View Related

General :: 2 Fields From Separate Tables Needed To Create Calculated Field?

Aug 19, 2013

The interface being used is a main form with various tabs and a subform on each of these tabs.

There is one field ('max power density') in my database that is calculated using 'Max Rated Power' and 'Cylinder Capacity' however these are in different tables and subforms. The 'max power density' and 'max rated power' are in table and subform 1 but 'cylinder capacity' is in table and subform 2. Is it possible to keep them in separate tables/subforms and still calculate the field?

View 7 Replies View Related

Create Calculated Autonumber In A Table

Mar 17, 2008

I have a table where one field needs to be an autonumber, however, that autonumber needs to be calculated. This field is not based on any other information in the database, but there's a very complicated mathematical process behind it, which I'm figuring out...I just need to know once I get that code complete, how do I tie it into the table?

View 7 Replies View Related

Setting Up A Table For Sales Figures

May 6, 2005

Dear All,
My boss wants me to create a budget report as follows:

Rep Cust ActM$ BudgetM$ VarM$ ActMProfit BudgetMProfit VarMProfit

ActYTD$ BudgetYTD$ VarYTD$ ActYTDProfit BudgetYTDProfit VarYTDProfit


I have a table with the following headings:

Date State Product Prod Code Customer Cust Code Category Jan Feb Mar etc

State has the domain Vic, NSW, Qld
Category has the domain Sales GP

Question 1:

To the field Category, should I either

A: add to the domain actual sales and actual profit? I can past these into the table at the end of each month.

OR

B: set up a separate table for the actual sales and actual profit for the month?

I think A.

Question 2:

Instead of having a separate column for each month, should I either:

A: just have a heading Month and put the figures in that column

OR

B: Have the sales figures in separate columns for each month?

I think A

Question 3:

When I have set up my table correctly, and assuming the answers to my questions above are all "A", I am now unsure how to create a query which will give me the data for the report my boss wants.


Can someone please give me some help?

Thanks
Bon

View 4 Replies View Related

Tables :: Calculated Fields From Two Tables / Based On Relationship In Third Table

May 29, 2014

I have two tables of data, each relating to three business branches (branches A, B and C).

Table 1 shows the expenditure of each branch (by fuel, premises and wages).

Table 2 shows a number of units for each branch (mileage, floorspace and sales).

What I would like to do is calculate unit costs, based on the expenditure in Table 1, divided by a relevant unit in Table 2. The catch is that I want to have a third table which allows the user to specify which expenditure (from Table 1) is combined with which unit (from Table 2) to generate the calculated unit costs. I've been able to do this in Excel, and have attached an example. I've also attached an incomplete Access version with the first two tables. Given the complexity of my actual data, I feel this could be better handled in Access than Excel.

View 6 Replies View Related

Tables :: Make Calculated Field In One Table That Calculates Values From Another Table Linked By Order ID

May 13, 2015

I have what is probably a stupid question but I've been struggling with this one for a while. I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc. At the moment, I have the order line table as a subform within the Order form (run from the Orders table). The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table. In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?

View 3 Replies View Related

Tables :: Get Rid Of Calculated Field In A Table?

Sep 13, 2013

I've learned the hard way about using calculated fields in a table. I used on while building in Access 2010 for users that will be using Access 2007. Once my testers pointed out the error message, I did some research and realized my calculated field was the problem.

So, I created a query to replicate the calculation, deleted the offending field from the original table and redirected everything to point at the new query vice the original table.

However, my testers are still receiving the error about needing version 14 to access that table. There isn't a calculated field there any longer, but they still can't access that table. Did that calculated field put some sort of taint on my database that can't be removed? I'm not sure what to do. Do I need to start over, so that a calculated field was never part of the database to begin with? If so, how much of my existing one can I reuse?

View 6 Replies View Related

Tables :: Calculated Column In Same Table

May 19, 2014

I have a table with columns as

1- Name

2-Marks in Subject 1

3-Marks in Subject 2

4-Marks in Subject 3

I want to create more columns in same table as

5-Total Obtained Marks

6-Maximum Marks

7-%age of Obtained Marks

How can I do it in one table

View 3 Replies View Related

Tables :: Calculated Fields In A Table

Sep 27, 2012

I have three columns where A is a number, B is a number and I want to make C = to the greater of the two. i.e.

Col A = 10, Col B = 12 therefore Column C = Col B or Col A = 12, Col B = 10 therefore Col C = Col A

View 4 Replies View Related

Tables :: Using NZ In A Table Calculated Field

Jul 22, 2013

Access 2013

I am trying to add some numbers together for a number of fields, of which at least one field will either be 0 or empty. I am trying to use the following formula in a calculated field in a table:

IIf(IsNull([Price Increase %]),[Total],([Total]*[Price Increase %])+Nz([LumpSumAnnualIncrease]))

Access returns an error statement: The expression XXX cannot be used in a calculated column.

Is there a different formula I should be using, or a work around, other than using the formula in a query.

View 5 Replies View Related

Tables :: Storing Calculated Values In Table

Jan 8, 2014

I know that as a general rule one should not store calculated values in a table, but if I need to include such a value on a number of forms, and calculating that value involves manipulating an ADO Recordset, does there come a point where the performance hit outweighs the design "quality"?

View 1 Replies View Related

Tables :: Need Multiple Calculated Fields In One Table

Aug 8, 2014

I am great with Excel but not soo much with Access 2010. I Excel, what I needed to do was very simple but duplicating what I did in Access is not soo easy.I am trying to calculate billings for FSA & HRA. I have set up one table with all the data. I want to add columns to the table to calculate:

1. Is there an account balance-excel formula:
2. If the account is still active
3. If the plan year run out is "active runout" or "runout over"
4. calculate each account type with a rate *count of FSA accounts = total to bill

Here are my excel formulas that for the life of me I can't get to work.

Account balance==IF(V2="HCRA",(IF(AB2-AD2>0,"Available Balance",IF(AB2-AD2=0,"Zero Balance","Negative Balance"))),((IF(AC2-AD2>0,"Available Balance",IF(AC2-AD2=0,"Zero Balance","Negative Balance")))))
Active runout/runout over=
=IF(AG2="YES","Active Runout",(IF(S2=W2,(IF(AF2>$AJ$1,(IF(AH2="Zero Balance","Runout Over","Active Runout")),"Runout Over")),(IF(AE2>$AJ$1,(IF(AH2="Zero Balance","Runout Over","Active Runout")),"Runout Over")))))
Active account=
=IF(D2<$AJ$1,"NO",IF(W2>$AJ$1,IF(S2=W2,"YES","NO") ,"NO"))

Can I have calculated fields that refer back to a calculated field?

View 10 Replies View Related

Tables :: Calculated Field In Table Can Only Sum Whole Numbers

Oct 1, 2014

Access 2010.

Have a table with columns of data which I want to total at the end of each row. The Calculated Field does this simply enough but it only seems to be able to add whole integers? How do I get it to accept and add values to 2 decimal places?

View 14 Replies View Related

Tables :: Calculated Form Field To Be Inserted Into Table

Jan 3, 2013

i want to insert a calculated value of a e.g field6 (field4 * field5) in forms into the database table. by using expression builders in my forms. i have made that feild and on click it shows the calculated value but on filling in the rest of the form and clicking on Insert button does not update this calculated field but iserts all other fields.

View 3 Replies View Related

Tables :: Calculated Field In Table - Display Week Number

Jan 13, 2014

I am trying to create a calculated field in my table, I am trying to use the following code:

DatePart("ww",[Gas]![Date Opened])

I receive the following error message: "The expression DatePart("ww",[Gas]![Date Opened]) cannot be used in a calculated column."

Access 2007 - I really need to be able to display the week number in the table, based on the Date Opened field.

View 6 Replies View Related

Tables :: Pull Field From Main Table And Create A New Table With Date Stamp

Nov 4, 2014

I have a make-table query that pulls all the fields from 1 table (MainTable), and creates a new table with a date stamp based apon a form value entered (New Table = MainTableWithDate).

Currently, I setup the query to pull info from the form field like this:

DateField: [Forms]![frmmain]![DateField]

However, when the make-table query is done - all date fields are blank (all other fields are correctly created), and when I look at the new created table (mainTableWIthDate), the typeassigned to the date field is "Binary" (in the form, I've specified LongDate).

View 6 Replies View Related

Create Table Of Different Columns In Three Tables Join To Form A Master Table?

Jun 4, 2015

I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.

If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.

Note: except the Name column, none of these tables have any other columns in common How do I go about this?

View 7 Replies View Related

Create Tables From Another Table

Oct 17, 2005

Hi All

I am trying to create a database for estimating manhours on projects. There is a setup table with two columns: "Project" and "Time Period". The database is supposed to allocate manhours to tasks on projects, which is used in estimating man hour costs and also forecasting labour resource requirements.

For example, say the database is used for "Project X" and "Project Y" projects, with Project X running from Jan to March and Project Y running from Aug to Dec. The setup table would look like this:

PROJECT TIME PERIOD
Project X Jan
Project X Feb
Project X March
Project Y Aug
Project Y Sept
Project Y Oct
Project Y Nov
Project Y Dec

From this table, I need to automatically create a new table for each unique project that allows man power to be allocated to each time period. Following on with the example, there are two unique projects, so two tables
need to be automatically created with column headings as per below:

Project X table:
FUNCTION PERSON JAN FEB MARCH

Project Y table:
FUNCTION PERSON AUG SEPT OCT NOV DEC

"Function" is a description of a role, for example engineer. "Person" is the individual undertaking that role, for example Bob Jane. Then in each time period a number between 0 and 1 would be entered corresponding to how much time (0 is no time, 1 is full time) that person would be spending on that function for that time period. Then the table would be populated with many functions and people in this manner.

So the number of tables automatically created depends on how many unique projects there are in the "Project" field of the setup table. The number of columns in these tables will be atleast two: "Function" and "Person",
plus another column for each time period defined for that project.

Perhaps something can be done with a make-table query, but all I can get that to do is copy data from one table straight into another table.

Any help would be greatly appreciated.

Thankyou in advance.

Joey

View 14 Replies View Related

Create A Table From 3 Other Tables

Jul 17, 2007

Hi, i currently have three tables.

Policy table: Policy number(key),date and fund(4 possible strings of four letters)
Last Price: date, fund, last price
Next price: date, fund, next price

What would i have to do to make a table that has a policy number, date, fund, last price and next price?

Thanks

View 1 Replies View Related

Tables :: Create Table With Dates And Day Name

Apr 26, 2015

I am very new with access database. Want to create table with Dates & DayName. For example, tblYear2015 with all dates from 1-Jan-2015 to 31-Dec-2015 in FirstColumn and DayName in second column as per date of First Column. DayName Should be entered automatically as per date entered in first column.

View 6 Replies View Related

Tables :: Create One To Many Table Entries?

May 8, 2013

I'm new to MS Access, indeed database design as a whole.

I have been tasked with creating an issues logging database and am having some issues...

I have a table/form that has all the fields I want, including a unique ID, called "Issues". I want to be able to log multiple entries in another table called "emails". The trouble is I am not having much luck. I can create an entry in the issues Form which then shows in the emails but I can't then add another email under the same ID in the "Issues" table.

View 2 Replies View Related

Tables :: Auto-populate Table Field From Calculated Field In Another Table?

Jan 15, 2015

I am creating a database to keep up with the expenses for my art business. I have a table for Vendors with a field (Mileage) for one-way mileage that I enter manually (EX: 11.8 miles). Then I have a calculated field that basically just multiplies the Mileage field times two to get total mileage to and from my house to vendor location. (Total Miles) {The reason I want to do this this way right now is because I didn't keep up with my mileage this year and I am having to look up the distance from my home to each location)

Then I have a "supplies" table where I show the date, the vendor, the item, cost, etc, and I want to add a Total Mileage field based on the calculated Total Miles field in the Vendors table that will automatically populate based on the vendor of each transaction. I just can't remember how to make this work and I know just enough SQL to be dangerous.

View 2 Replies View Related

Create A Table Based On 2 Tables With Different Fields

Apr 17, 2007

Hello,

I have attached a zipped excel workbook to best describe what I'm trying to do. I have table1 and table2 and I'm trying to write a query in access to get the output as shown in the workbook. I'm having hard time getting this right. I would appreciate your help if possible. Tks

Richard

View 4 Replies View Related

Tables :: Create Log For Table To Capture Changes By Users

Mar 2, 2015

Is there a way of creating a log to capture changes in a table by users?

In a current database that I'm taking over, there was a lot of records deleted from the main table and now I need to figure out what happened.

So, if I would be able to have a log that records all the actions taken by users, I could at least see who did what.

View 13 Replies View Related

Query That Will Select All Tables In The Database And Create One Table

Sep 16, 2007

I have a rotating number of tables that are created from excel spreadsheets that are imported. The Tables will change, but when they are there I need to be able to create a query that will merge them all together so I can run one query against all the tables. In SQL I know you can use a * to say Select *
From Table_1

Is there a syntax for the From portion so I can say:

Select *
From * (AKA all the tables in the Database)?

I have searched for a wildcard for the FROM statement that works like in the select statement but have been unsuccessful at finding an answer. Can anybody help? I'd list the Tables in the from Statement but there are 266 of them. Unless someone knows how to say:

Select *
From All tables in a folder with 266 excel spreadsheets

Thanks for your help
:)

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved