Field Subtraction Problem

Jun 1, 2005

I have financial table which contains list of End of year revenue titles. Since this combo box was suppose to be part of the master table so I made a joint query. Now there is one problem arising. Table and query and even the form is working fine. But in that combo list there are several titles and 3 of them are Assets, Liabilities and Equity. Equity has to be equal to Assets-Liabilities. And the values are being fed into Borrower In house column. I tried everything, but for some reason its not working for me. It wont subtract the values. Any suggestions. I hope I didn't confuse you guys. Thanx in adv.

View Replies


ADVERTISEMENT

Subtraction Within Queries

May 19, 2005

Hi all,

I was wondering if somebody could help me with a query I'm struggling to write. I have written a query which shows customers who have booked capacity of my company's product. I have written another query which shows customers who have bought volumes of the product. I want to write a query which shows the remaining capacity which can be purchased by customers (e.g. capacity - purchased) but the query I have written for this excludes any customers who haven't purchased any product. Note I do not want to update the tables, I just want to take a view of the capacity position of our customers.

Example:

Booked Capacity
Customer A = 1000
Customer B = 4000
Customer C = 3000

Product Purchased
Customer B = 1000

At the moment my query gives following result:
Customer B = 3000

What I want it to do is:
Customer A = 1000
Customer B = 3000
Customer C = 3000

Any help would be much appreciated!!!!

Ste

View 1 Replies View Related

Subtraction In Query?

Jan 3, 2006

Hi !
I have big problem in putting subtract function in sql query
i have 2 tables
In the first table i make "sum of amount" in each "category_name". In the second table i have 2 columns with the "category_name" and "available_amount".
I make query with join on "category_name" and i want to get like this:
"category_name", "sum of amount", "available_amount","available_amount"-"sum of amount"
I've tried to use "sum of amount" AS a1, "available_amount"-"sum of amount" AS a2 GROUP BY a2

I get "null as boolean in statement" or "column not found a1" errors....

Can anyone help me with this?
Thanks in advance!
i have to make this in a query.

View 1 Replies View Related

Query Subtraction

Mar 19, 2008

Ok, I always post at the end of the day because by now I've fried my brain thinking and I have simple problems that I can't wrap my mind around.

I need some light shed on this for me,

2 Tables

Table1:
ItemID
NumberSold

Table2:
ItemID
InventoryAmt

I have a Select query, I have a relationship drawn between the ID's. I forget now, Straight line with an arrow head from Table2 to Table1.

All I need is every item in Table 2 to have it's Amt subtracted by NumberSold.

My expression [InventoryAmt] - [NumberSold] works for as long as I have an entry for it in Table1. If there is no record of this item ever being sold it gives a blank value. I would like that value to be the InventoryAmt, since X - 0 = X.

How do I explain to my query that if no record is present in Table1 to Subtract from Table2 just use Table2's value?

This has to be easy and I'm sorry to start a thread over it. It's almost quitting time and I'm banging my head off this, heh.

View 1 Replies View Related

Subtraction Issuse 4 Feilds

Dec 2, 2004

I have four feilds and im trying to subtract them in a query


This is what the SQL looks liek for two feilds. but i have 4, i was gettign and error when i added other feilds
Format(Val([CFR_ALLOWED])-Val([CFR_DEDUCT]),"0000000000") AS [Form Total],
im trying to subtract these feilds
Here are my feilds
Allowed-deduct-other-Coins-Cob


Much help needed thanks

View 1 Replies View Related

Modules & VBA :: Subtraction From Last Record

Nov 25, 2014

What i am looking for is i am having a field in query "DEP" which is showing the asset current depreciation as per formula given. I have a problem that the depreciaition is continued on initial cost for all years which i dont want like if it depreciate asset of cost 1000 @ 10% so the figure is 900 for the first year and on second year it should start depreciating from 900 @ 10% so it would be 90 then so on..What i need is to start subtraction last year depreciation

View 4 Replies View Related

Formatting Subtraction Of Two Numbers

Oct 23, 2014

How can I format the output of subtracting two times as a time value. For instance 10:00 am - 9:00 AM = 1 (where I want to show the output as "1").

View 7 Replies View Related

Date/time Subtraction Using Previous Row.

Aug 18, 2006

Hi All,

I am on a mission to get this out but having muchos problemos! Hope someone can hand me a little tip or pointer.

My table has two coloms:
Date_stamp(datetime), product_code(text(25))

I need to show this data in a report with a third colum that shows the difference in seconds between each record. IE how long it took for the next date-stamp to occur.

Can anyone help.

Thanks in advace

View 1 Replies View Related

Reports :: Running Difference Of Several Numbers - Subtraction?

Oct 25, 2013

Is there such a calculation in REPORT wherein I can have the Running Difference of several numbers?

Example is several Kilometer reading of a vehicle:

18716
18731
18742

difference of red color numbers: 15
difference of 2nd red color number and blue color numbers: 11

total: 26

I don't even know if my calculation is correct ...

View 2 Replies View Related

Forms :: Copy Value From Field In Subform To Field In Main Form During Data Entry

Jul 18, 2013

How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.

In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.

So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.

View 10 Replies View Related

Modules & VBA :: Filter Subform Data - Only Show Records Where Field A Is Higher Value Than Field B

Oct 24, 2013

I want to filter my subform data, to only show records where field A is a higher value than field B.

Code:
Me.MySubform.Form.Filter = "A > B"
Me.MySubform.Form.FilterOn = True

This way it doesn't find field B.

Code:
Me.MySubform.Form.Filter = "A > " & MySubform.Form!B
Me.MySubform.Form.FilterOn = True

This way it seems to filter all record to the field B value of the first record.

View 5 Replies View Related

Inserting Time Into Access 2010 Query Field When Character Is Entered In That Field

Mar 4, 2015

Here is what I am trying to do. I have a query with 2 fields. "Time In" & "Time Out". What I would like to happen is this. Whenever a character, let's say a "t", is entered into that field I would like the current time to populate that field. Right now we are actually typing in the time. I have the fields set up as DateTime fields currently.

View 10 Replies View Related

Limit A Field To User Defied Values Depending On The Input Of Another Field.

Nov 23, 2005

Hey all,

I have two fields 1 & 2

field 1 is a simple combo list of user defined values ie A, B, C or D

Field 2 relates to a attribute of the data in field 1 and is not always the same for A, B, C and D. i.e

A could have a,b,c,d or e
B could have c,e,f,g or h
C could have a,g,h,i or j
D could have v,w,x,y or z

I would field 2 to have a combo box which only displays a,b,c,d, or e when A is chosen in field 1; c,e,f,g or h when B is chosen; etc

How do I do this?

Cheers all,

Matt :confused:

View 2 Replies View Related

Forms :: Can A Field Auto-fill From Previous Entry In Separate Field

Mar 21, 2013

I've just returned to work after kids and started managing a large Access database related to health, back-tracking over many years.

Currently in filling a form we physically enter:
Apples 2.2
red apple 2.4
red apple cut 2.45
Oranges 5.6
Cucumbers 8.5

Is it possible to get field 2 to automatically fill with a number code due to the text typed in field 1?

FWIW, I'm confident at more basic Access e.g making follow on default value = Dlast("field""table") type stuff but the more complex stuff I haven't touched since Uni over a decade ago and you will need to be gentle while I blow away the cobwebs

View 3 Replies View Related

Tables :: Auto Populate Field Based On Answer Of Another Field In Same Table

Feb 28, 2013

Within my table if Field 1 has an answer of Self (from drop down), then, I would like Fields 6-12 to auto populate; however, if Field 1 does not have an answer of Self, then leave Fields 6-12 blank.

I am not quite sure how to lay this out. I am using Access 2010.

View 8 Replies View Related

Forms :: Move Focus From Last Field Of Subform To Another Field On Main Form

Aug 19, 2015

I am currently stuck on set focus property. I have a main from with nested subform. I am trying to move the focus from last field of the subform to another field on the main form.

Customers(mfrm)....>Addresses(sfrm)...>Orders(sfrm Add)......>OrdDetails(sfrmOrders)

Now I have a field name [Securedesign] in frmOrderdetails and I want the tab order to navigate to field [CustomerID] in frmAddresses which is a subform to frmCustomers.

View 2 Replies View Related

Forms :: Autofill Field Based On Related Field In Previous Form

Jun 30, 2015

I have my Assets form and the primary key is the ChargerID, in this form I have an "Add New Job For This Asset" button, which opens up the Jobs form at a new record.

How do I make it so that the ChargerID field is automatically filled with whatever the previous record was instead of being blank.

For example if I have Charger12345 open in the Asset form, I'd like to click the Add New Job button and it automatically have Charger12345 in the ChargerID field of the Jobs form.

View 5 Replies View Related

Tables :: Linking Multiple Field Values To A Field Selected From Combo Box

Feb 16, 2014

I'm pretty good with setting up a very simple database such as inventory, profiles, etc.. However I'm creating a database to keep track of a football (soccer) team's players and match statistics.What I have so farsample attached)

Tables:
* Players - PlayerID, Fname, Lname, position, goals, assists, etc (all details regarding a player)
* Position - Positons (Table containing positions eg: defender. Data is selected in player's form as a combo-box)
* Competition - Competition types (Cup, League, Friendly. Data is selected in Match's form as a combo-box
* Venue - similar to Competition table
* Opponent - Similar to above two tables
* Match - MatchID, Competition, Venue, etc (form corresponding to table attached)

Forms:
* Player form
* Match form

Now as shown in the sample, I choose players using the combo-box. Then whatever stats they had during the match are entered on the fields provided. How to link the player (selected using combo box) to the stat fields (goals, assist, YC, etc).

View 1 Replies View Related

Modules & VBA :: Unable To Extract Day Number From Date Field And Write It To Day Field

Jan 10, 2014

I am trying to do some simple table operations. I have a field (Date) containing dates, and an empty field called Day.

I want to extract the day number from the Date field, and write it to the Day field.

I didn't get very far until I ran into trouble when setting my recordset. I get the error "Too few parameters, expected 1". Clicking "Debug", will highlight the code line "Set rs = db.OpenRecordset(sqlString, dbOpenDynaset)".

So far, my code looks as follows:

Code:
Private Sub Command16_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sqlString As String
Dim dataDay As Byte
'Open connection to current Access database
Set db = CurrentDb()

[Code]...

I am not very familiar with the various types of recordset settings. I just want to be able to read data from the Date field, and write data to the Day field.

View 12 Replies View Related

General :: Update Main Form Field Value After Updating Field Value In SubForm?

Feb 12, 2015

I have a main form and a subform.

Both forms have the field called JobID in common.

Both forms have a field called JobStatus.

Any easiest solution so that After I Update the field called JobStatus in the subform, it changes the field called JobStatus in the main form to the value which was selected from the subform?.

View 3 Replies View Related

Hotkey For Toggling Between Field Row In Table Design View And Its Field Properties?

Feb 15, 2013

I am making a new Record in the design view of a table (creating a new field in the table), and I am assigning it a date type, is there a hotkey that will select the field properties sheet so that I can select the format of the field without having to move my mouse?

View 1 Replies View Related

Some (expert) Advice Needed Please (linking Field With String In Other Field

Oct 3, 2005

Hi,

I hope someone can help me, I have a database compiled from different sources which means that information in fields that need to be linked are written differently, meaning that I can't just simply make a relationship between them.

The two tables I would like to link are,
Table 1 has the fields OCCUPATION and AMOUNT and contains over 740,000 records.
Table 2 has the fields COMPANY_NAME and TICKER and has 500 records.

I need to find a way for all COMPANY_NAME fields in table 2 to be cross-referenced with the OCCUPATION field, so if COMPANY_NAME is part of the string in the OCCUPATION field then the TICKER (of that company) can be attached to the record in table 1 (specifically to AMOUNT).

The problem is that the OCCUPATION field is not written in a standard form and can include either only the occupation, only the company name, or both in either order.

I can make a seperate query for each company by using as criteria "like "*[COMPANY_NAME]*", but then I would have to do this 500 times!!! Is there a way to automate this?

The final purpose is to link the AMOUNT to TICKER so as to find the sum of all the amount associated with a company.

I really hope you can help, I have little programming knowledge and it will save me the time of making 500 seperate queries. The final use is for my thesis studying private contributions in the american elections.

Thanks in advance (I hope),

Onur

View 1 Replies View Related

Convert Text Field To Number Field But Keep Leading Zeros

Oct 3, 2006

I need to convert my text data to a number but when I convert using the VALUE function or use "format cells" to the numbers category, I loose the leading zeros. I need to keep them for sorting purposes.

What formula do I use?

View 6 Replies View Related

Make Field Auto Populate Based On Value Of Field In Another Table

Mar 4, 2008

I'm creating a database that keeps a track of questions and scores.

The questions in the database need to be dynamic and are changed frequently.

I have a scorecard table which keeps a record of scores and the applicable question at the time the record was saved. I need to do this because in 6 months time we may want to provide feedback. As the question may have changed we need to be able to refer back to what the question was.

I want the question field in my scorecard table to populate with the value in my question table.

I have tried a number of things including setting the question field in the scorecard table to a lookup based on the following query:

SELECT tblQ1.Q1
FROM tblQ1;

This works however only as a list or combo box. I don't want the user to have to select the question. I want it to auto populate, is this possible?

View 1 Replies View Related

Field Update Using DSUM: Field Joins Are Not Working Correctly

Sep 11, 2007

I know there are numerous threads regarding dsum() on the forum, but I wasn't able to find the exact answer to my problem.

The root of my problem is that I'm trying to update a field on a table using dsum, which references another query to update the table. Although I have all of the correct keys from the physical table joined to the query in the dsum function, the code/ms access seems to ignore the joins. As a result, all payees are having their "vol" field set instead of a select subset.

Query (GetTxnVolAmtTR"):

SELECT p.payee_id, sum(txn_volume) AS vol, t.market, t.period_id
FROM ft_payees AS p, ft_txn_summary AS t
WHERE p.payee_id=t.payee_id And p.market=t.market
GROUP BY t.payee_id, t.period_id, t.market, p.payee_id;


Update statement (references the query above):

UPDATE tmp_ft_component AS rc
SET rc.volume = Dsum("vol","GetTxnVolAmtTR","GetTxnVolAmtTR.payee_id= " & [rc.payee_id] And "GetTxnVolAmtTR.market= " & [rc.market] And "GetTxnVolAmtTR.period_id= " & [rc.period_id] & "")
WHERE rc.component_name='Total Revenue';

as you can see, I have all of the fields I want joined, but the code seems to ignore this. I've tried looking at this site: http://www.mvps.org/access/general/gen0018.htm , but haven't found my answer. Any help would be much appreciated!

View 3 Replies View Related

Lock A Field On Main Form If A Field On A Subform Is Complete?

Oct 24, 2005

hi, i would like to be able to prevent a user from being able to change a field (lock a field) in a main form if a field in a subform is complete. (the field in the subform is named: "new_weekly_base" if this is complete then i would like the field: "weekly base" to be locked on the main form. is this possible?, please help.

the main form is named: "SCREEN-MAIN"
the subform is named: "SCREEN-SUBFORM"
(the main form has a button on it which loads the subform.)

ive tried the below code but it doesnt work, any help would be excellent.

-------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
If [NEW_WEEKLY_BASE] >= 0 Then

With Me.WEEKLY_BASE
.Visible = True
.Enabled = False
End With
End If
End Sub
------------------------------------------------

View 4 Replies View Related







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