Queries :: Simple Sum Of Two Values

Aug 20, 2013

I use three tables to pull my data.

WorkTracker - each record is a date, worker name, and chore name completed
Chores - list of chores and the allowance received for each
Transactions - date, child, and money spent

I have a query that pulls the sum of transactions and the sum of allowance given but I don't know how to add them together.

Code:
SELECT DISTINCTROW WorkTracker.Worker, Sum([Allowance]+[MoneyTransfered]) AS Balance
FROM Transactions INNER JOIN (Chores INNER JOIN WorkTracker ON Chores.[Chore] = WorkTracker.[Chore]) ON Transactions.Customer = WorkTracker.Worker
GROUP BY WorkTracker.Worker;

View Replies


ADVERTISEMENT

Queries :: Calculation Based On Values Selected In Table By Looking Up Corresponding Values In Other Tables

Aug 29, 2014

I'm using Access 2010. I need to calculate a score based on values selected in a table by looking up corresponding values in other tables. I have a "Project" form to create new entries into the Project table (see Table 1). When I create a new project record, I will select values for the Payback and Need fields by selecting options from a list. The Payback list is pointed at Table 2 and the Need list is pointed at Table 3. In the below example, I created the "ABC" project and selected "1 year" for the Payback field and "Repair" for the Need field. Pretty simple.

Now that I have the "ABC" project loaded to my Project table, I'd like to create a report that will show a "score" for this project. The score should be calculated as follows: Payback Impact + Need Impact. In this example, the score should be 30 (Payback Impact of 20 + Need Impact of 10).

View 5 Replies View Related

Queries :: ORDER BY - Update Values In One Table With The Values In Another Using Join

Sep 18, 2013

I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:

UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;

View 2 Replies View Related

Queries :: Query Records With Both Duplicate Values And Different Values?

Jun 18, 2013

I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:

Code:

AcctNum FoodType FoodClass
------- -------- ---------
A123 Apple Fruit
A123 Apple Fruit
A123 Grape Fruit
A456 Potato Vegetable
A456 Potato Perishable
A789 Carrot Vegetable
A001 Banana Fruit

For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:

Code:

AcctNum FoodType FoodClass
------- -------- ---------
A456 Potato Vegetable
A456 Potato Perishable

It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').

View 5 Replies View Related

Queries :: Rename Field Values With Values From Another Table

Mar 2, 2015

I have two tables.descriptions I'd like to relate and use to find/replace in bulk.

[Checking].[Description] (with the source data)
[Rename].[NewDescription] (with the correct data)

I'd like the values in [Checking].[Description] to be replaced with the values in [Rename].[NewDescription], including those that are "Like".

Examples:
[Checking].[Description] = Geico 12345
[Rename].[NewDescription] = Geico

[Checking].[Description] = Geic
[Rename].[NewDescription] = Geico

View 4 Replies View Related

Queries :: How To Show Row Values As Column Values

Mar 8, 2015

I have a table with following two columns

TechName Version
SQLServer 2000
SQLServer 2003
SQLServer 2005
SQLServer 2008
SQLServer 2008R2
Oracle 11G
MSOffice 2000
MSOffice 2003
MSOffice 2007
MSOffice 2010

How can i show this data as components eqch version for a techname to be one component order need not follow

TechName Component1 Component2 Component3 Component4 Component5
SqlServer 2000 2003 2005 2008 2008R2
Oralce 11G NULL NULL NULL NULL
MSOffice 2000 2003 2007 2010 NULL

View 4 Replies View Related

Simple Q On Multiple Queries

Aug 20, 2006

I'm used to doing a data analysis but never had much cause to use Access previously so I'm trying to quickly pick up the essentials for a specific work project:

I need to extract a subset of data from an Access 2000 database and then perform some category operations on that subset - all interactively. So 2 questions please:

1. I'd prefer to do this with two successive queries (just so the logic is plainer to me) the second operating on the results of the first. But I can't immediately see how to submit the results of one query to a second query interactively.

2. If I were to combine both queries into one can I safely assume that the columns will be processed from left to right (as shown in the query designer)? To explain: I want first of all to select a subset of the data and then to do some stats (ie as a 'total' field) on the subset. But I need to ensure that the stats are done on the subset and not the full original table.

TIA
John Dann

View 3 Replies View Related

This Should Be Simple (marge 2 Queries)

Jul 11, 2007

I have one query that returns a list of all debits in a given year/month/cost element: e.g.

Year Month CE Debit
2007 6 111 100.00
2007 6 153 150.00
2007 6 157
2007 6 294 75.00

and another that returns a list of cost element budgets: e.g.

Year Month CE Budget
2007 6 111 500.00
2007 6 120 75.00
2007 6 153 200.00
2007 6 157 120.00

So you can see there could be budgets with no debits against them, and there could be debits with no budget (either could be zero or null). What i want is to merge the two so that I return ALL possibilities: e.g.

Year Month CE Debits Budget
2007 6 111 100.00 500.00
2007 6 120 75.00
2007 6 153 150.00 200.00
2007 6 157 120.00
2007 6 294 75.00

For the lif of me I can't get this to work - I can get all budgets, or all debits, but not a merge of both. Can someone please suggest a solution or point me in the right direction?

View 2 Replies View Related

Queries :: Simple Query Gives No Results

May 3, 2013

Have created a simple data collecting database with a simple query to narrow down some of the data , the DB all works fine and some queries are ok, but one the simple query where I want to look a one single set of data.Using the Like "*"&[Enter Search Parameter]&"*" gives me no results.

View 9 Replies View Related

Queries :: Simple Loop Through Excel Cells

Feb 25, 2014

I have been trying to understand which method to use for looping through excel cells and storing these into access tables. However, I am having difficulty with so little knowledge in vba.

I have lets say two tables (rows for each table are not fixed) in a worksheet and I want to loop through these rows and store each tables (PROJECT PLAN 1 and PROJECT PLAN 2) in a separate table in access.how to loop through the PROJECT PLAN 1 and PROJECT PLAN 2 in excel and store these in table1 and table2 in access

I have these in excel

B3 I have "PROJECT PLAN 1"
B4 COMPANY | C4 DESCRIPTION | D4 TIME
B5 Google | C5 aaa | D5 10
B6 Microsoft | C6 bbb | D6 11
B7 IBM | C7 ccc | D7 12

next row 8 is blank row and columns

B9 I have "PROJECT PLAN 2"
B10 COMPANY | C10 DESCRIPTION | D10 TIME
B11 Google | C11 aaa | D11 10
B12 Microsoft | C12 bbb | D12 11
B13 B14(merge cell) IBM | C13 ccc | D13 12
B13 B14(merge cell) IBM | C14 ccc | D14 12

This is the code I have so far:

Code:
Dim objXL As Object
Dim xlSht As Object
Dim xlWB As Object

[code]....

View 6 Replies View Related

Queries :: Setting To Only Show Field In Simple Query Once

Sep 19, 2013

I have a simple query between two tables joined together by common fields. In my first table (Table 1 - tblLocations) I have information about a building i.e. Location Code, address and total sqft. . In my second table (Table 2 - tblAllocatedSpace), this contains details (Location Code, Room ID, SqFt assigned, etc.) of the space allocated in each building. The two tables are joined together when the “Location Code” in both table match.

In my query, I show the location detail from (Table 1 - tblLocations) and related records from (Table 2 - tblAllocatedSpace). My result looks like the following:

Location Code Sqft Address Assigned Sqft
106067 1,000 600 March Rd 10
106067 1,000 600 March Rd 15
106067 1,000 600 March Rd 12
106067 1,000 600 March Rd 20

The location code, Address and Sqft is rebated each time a space is assigned in (Table 2 – tblAllocatedSpace). When a build a report and need to sum the location Sqft, the number is multiplied by the number of related records in (Table 2 – tblAllocatedSpace). In this example by building total space is 4,000 sqft when I only it to show 1,000.

How do I set to only show the location code and sqft once?

View 1 Replies View Related

Queries :: Simple Query To Find Records Which Are Not Numbers

Jun 1, 2015

I have an table that contains StudentID, Name and Age. I have imported the data from Excel sheet and there are some records which contains Null Value and some "h", "n/a" etc. I would like to design query which finds the records that are non numeric.

View 2 Replies View Related

Queries :: Combo Box On Form That Passes Criteria To Simple Select Query

Sep 22, 2014

I have a combo box on my form that passes criteria to a simple select query. There are four possible selections to make from the combo box. For some reason, when I select the first option on the list the query runs perfectly. However, if I select the second, third or fourth option from the combo box, the query returns no records, even though I know there are records in my table which should be returned.

View 5 Replies View Related

Queries :: Combining Queries Without Matching ID Values

Feb 2, 2015

I have two queries:

1. a sum of payments by individuals
2. a sum of charges of individuals.

I want to combine these queries to create a report that shows the sums for each person of the charges and payments and calculate the balance. However, it is only showing me those individuals that have both a charge sum and a payment sum, while some individuals will only have a charge sum. How do I get those individuals to show and show with a zero payment balance if they are in my charge sum query but not my payment sum query and vice versa (some may have made a prepayment even though they have not been charged yet).

View 5 Replies View Related

Queries :: Run A Simple Update Query To Copy Data From One Column To Another Column

Sep 24, 2013

I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.

View 7 Replies View Related

Nz - Null Values In Queries

Sep 12, 2007

i have checked the forums and i know they kind of answer this question, but im not really sure where i am ment to write it!

I have two queries, which a third query subtracts the 1st query and 2nd query values and gives the end value. If the 2nd value is null, i get a null value at the end, as i need to change this to zero instead. i know i need to use nz (as seen in microsoft access help, and other places on this forum) but im not sure exactly where to put this on my query, and in which query.

Basically:

(qryCountpartNo.CountPartCode)-(qryTotalquantityallocated.sumofquantity) = qryTotalFree

both countpartcode and sumofquantity may be null at any time

Any help would be greatly appreciated!

Thanks in advance,

Emily

View 14 Replies View Related

Queries :: IF Statement With Many Values

Jul 26, 2013

I am trying to create an IFF statement in a query that if the field HRID is populated and the space category field equals the wrong space type than my results should be "Check Occup". I have tried several combinations but I still cannot get this to work.

=IIf([HRID]>"" And [SCat Text]="BRR_Restructuring" Or "Building_Common" Or "Floor_Common" Or "Vacant_Available","Check Occup")

View 9 Replies View Related

Queries :: Sum Of Values With Same ID In Column

Aug 14, 2013

I am having some trouble with a program that contains a query that is suppose to sum a bunch of market values that have the same ID and is from the same portfolio.

The problem I had at first was that some of these IDs and portfolios that should have been summed together had one called counterparty that had different values.

So the problem that occured was the following:

ID Portfolio CounterParty SumOfMarketValue
5224 SPAC Counterparty1 -55
5224 SPAC Counterparty2 -45

What I wanted

ID Portfolio SumOfMarketValue
5224 SPAC -100

So what I did was that I deleted that column, but I am guessing that the table somehow might save that they have this difference. How I can make them appear in the same sum?

View 3 Replies View Related

Null Values In Crosstab Queries

Apr 24, 2006

Hi there, instead of blank fields in my crosstab query, and hence my report, i would like a standard comment, such as 'No Booking'.

My crosstab is something like this:

.........A...........B..............C............. D
1.......X

2.......X..........................X

3...................X............................. .X

(Ignore the dots, obviously)

Currently, when i try to open the report based on this query, it fails unless each column has data in it for at least one record.

I've tried using the Nz function but haven't been able to make it work. Thankyou in advance! :)

View 5 Replies View Related

Excluding Zero Values In A Crosstab Queries

Jun 8, 2006

Hi all, I know this is a real easy one, but I am not the smartest when it comes to access. Can you help me out.

I am running a crosstab query to count and sum records in my database. I have a fied called "Amount". In my form the user is not always required to enter an amount. When I run the query, I would like the results to exclude the records that have a null value or $0.00 in the "Amount" field.

How do I format the query to exclude those records?

Thanks in advance.

View 3 Replies View Related

Combining Calculated Values From Two Queries.

Aug 24, 2006

I have created two queries which calculates a total. I want to add the values of the two queries in a third query to give me a grand total.

When I try to create this third query, it gives me an error saying that the fields cannot be used from two different queries. So I'm just wondering whether this is possible or is there any other way of doing this.

View 1 Replies View Related

Adding Up Values From Multiple Queries

Jan 30, 2005

Query 1:
Field #1: User Name
Field #2: CountOfUserName

Query 2:
Field #1: User Name
Field #2: CountOfUserName

Query 3:
Field #1: User Name
Field #2: CountOfUserName

Query 4:
Field #1: User Name
Field #2: CountOfUserName

I want to create a Query that will add
[Query 1].[CountOfUserName]+[Query 2].[CountOfUserName]+[Query 3].[CountOfUserName]+[Query 4].[CountOfUserName]=
[My Query].[TotalCountOfUserName]Group by UserName

It seems difficult.

View 6 Replies View Related

Queries :: How To Join One Value To List Of Values

Jan 2, 2014

An affiliate sent us a table of email addresses, one per record. We need to find which ones already exist in our master table. Our master table contains an email field but it may contain MULTIPLE email addresses separated by semicolons. How do we create a query (or queries) which tell us which email addresses already exist somewhere in our master table?

View 7 Replies View Related

Queries :: Duplicate Values In Answer Set

Jun 16, 2015

I have a union query. I am pulling in data from two (almost identical) SQL views in two different databases.

My MS_Access application is linked to the two views

The issue is A.GroupId. In view finance_WILPMonthEndReview the field is set to NVARCHAR255 in view iq4bisfinance_3DMonthEndReview the field is set to NCHAR255.

This is my union query.

SELECT A.Dataareaid,A.a_year, A.a_month, A.GroupID, A.dimension, A.accountnum,A.companygroupaccount,A.account_type,
C.groupiddesc,D.costcelldescription, B.PL1assignment, Round([A.SumOfActual_amt],2) AS ActAmt, Round([A.SumOfBudget_amt],2) AS BgtAmt

[Code] .....

When I link my Excel pivot table to the query I get two lines

View 1 Replies View Related

Queries :: Blank Values When Duplicated?

Mar 21, 2013

I'm trying to execute a query that will delete values in a certain column, but I'm getting no positive result.

Quote:

--- Table1 ---

Original:
col1 col2
-----------
text1 Stext1

[Code]...

View 7 Replies View Related

Queries :: Between Parameter With Null Values?

Jan 17, 2014

I am trying to alter this parameter to bring back all records if either beginningsalesrange or ending salesrange is left blank. I can't quite get it right.

Between [forms]![frmState]![BeginningSalesRange] And [Forms]![frmState]![EndingSalesRange]

View 13 Replies View Related







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