Queries :: Summing Records From Two Queries?

Apr 17, 2014

I have two queries which pull fuel uplift figures by airport from a master table, each query for a separate airline.

Each record is made up of Airport, Fuel Uplift, Month and Airline. I want to create a query that will show each Airport per month with the total fuel from both airlines. To do this I've created a new field titled AirportMonth and then applied a JOIN command. This works to a point but initially only returns records where the same AirportMonth value appears in both tables. I can alter the join properties to show all records from one of the tables but I need all records from both tables. Below is the SQL I have so far.

SELECT JAFyr.Airport, JAFyr.Month, [JAFyr]![Fuel Uplift USG]+[TFLyr]![Fuel Uplift USG] AS Expr2
FROM JAFyr LEFT JOIN TFLyr ON JAFyr.AirportMonth = TFLyr.AirportMonth;

View Replies


ADVERTISEMENT

Queries :: Summing Records In A Query?

Jul 24, 2013

I have a query which displays sales and cash deposits for the day. I want a sum of all cash deposits.

So it looks like this..

Date________________Amount_______________SumOfAmou nt____________Exp1
17/7/2013___________$55___________________$55__________ __________$55
17/7/2013___________$22___________________$22__________ __________$22

I tried doing it by two methods.. neither of which are working.

In the Amount field, I put it as a Total: Sum. I also tried creating a new field which is an expression which sums up the Amount field. Neither are summing it and just displays the value.

View 1 Replies View Related

Modules & VBA :: Summing Multiple Queries And Summing Time

Oct 4, 2013

I have a sales form. The sales form has many Call, Meetings and Emails linked to each sales record. I want to total all the number of calls, meeting and email records related to the sales record to give a total- Touches.I've created 3 queries;

1 - Counts Calls
2 - Counts Emails
3 - Counts Meetings

These all work fine however when I combine them to attempt to count the results it doesn't produce the correct results.I have a second query as well (no pun intended).

I am trying to sum together a column that has values in Time. The results displays as a decimal. How can I have the result display as a Time i.e. 1:20 (1 hour 20 minutes).

View 1 Replies View Related

Summing Queries.

Jul 14, 2005

i'm going to start this by telling what i am working toward in the end as it is where the problem obviously stems from.

i am trying to create a report which will tell me how many times 'hcapp' is in a table. there are three different fields where 'hcapp' can be placed...i can easily create three different queries which return how many times 'hcapp' is mentioned in one field, but i have yet to figure out how to look through all three fields in one query...is it possible? if not, how can i have a report call all three of these queries and then sum the total?

View 4 Replies View Related

Summing Queries.

Aug 3, 2005

i've a question that hopefully has a simple answer.

i have a query that returns three different numbers (each in its own column)...anyway, how can i make it sum the number of these three numbers...the best case scenario would be if i could have a fourth column appear in the results of the query with the sum...

it would look something like this

num1 num2 num3 sum
1 2 3 6

View 1 Replies View Related

Queries :: Summing A Field In A Sub-database?

Mar 27, 2013

I have a table called Products which has a field called "Product ID" and a few others. I have another table called "Sales", which has a field called "Sales Date".

There will be different dates where the products are sold. Here's my problem: I'm trying to make a query to find out which products make the most sales. However, doing so will also display the products separately by their sales dates. So for example, it'll show...

Product #10 / Jan 1, 2013 / 100 Sales
Product #10 / Jan 14, 2013 / 21 Sales
Product #10 / Feb 11, 2013 / 44 Sales
Product #11 / Jan 5, 2013 / 201 Sales
Product #11 / Mar 13, 2013 / 444 Sales

I'd like the products to be together so product #10 would give 165 sales for example. In the Query, Product ID from both tables are connected.

View 3 Replies View Related

Queries :: Summing Query Totals Together

Mar 17, 2014

I have a number of querys which search by month and year, I want to be able to sum up 3 of the querys together and display this in a text box on a form... I have tried dsum etc but I can't seem to do it...

So basically I want the totals which are dsumed into the same form to calculate together in a different query if required... This would be simple except one of the months if from the previous year...

View 2 Replies View Related

Queries :: Summing Data In Query

Jan 28, 2014

Creating a Query that would give me results showing in the attached file column D. I am trying to build volume incentive database to track how much we are going to save once we hit certain volume and rate drops.

View 2 Replies View Related

Queries :: Dataset - Group By Query Without Summing

Jan 15, 2015

I have a a dataset that has recorded encounters with a number of Banded (identified) animals. It is currently setup so that each encounter is its own row; with all the information, including "Band_Num" repeated, but "Encounter_Date" being unique. I wanted to query the table and get it to display the results as "Band_Num" on a row, followed by x columns for with each unique "Encounter_Date" represented. I thought a "Group By" function would work, but that requires a sum, which is not useful.

Complicating this is the fact that not every individual is seen a set number of times, so Animal 1 might be seen 3 different dates, but Animal 2 only on 1 date.

View 4 Replies View Related

Queries :: Expression Summing From Fields With Dates

Aug 3, 2015

I am involved in centrally managing a project , a part of which involves employees of certain companies being given a weekly subsidy for study on academic courses. I have successfully set up a table for all the learners and a related table for companies.

Each learner will receive a fixed weekly rate subsidy in the first year, and a lower fixed weekly rate subsidy in the second year. Somebody (not me) will need to check their paychecks to ensure that employees have been paid and all is above board. The learners/employees can start on the programme at any time. I need a way to track this (total claimed amounts, where they're up to etc).

My theoretical solution, is to have a field for each week a learner is on the programme (52 first year, 52 second), and instead of entering a currency amount in the field, the check-person can enter the date they saw the evidence and are happy with it. I thought then if I create a query that searches any dates that fall within the current claim period (eg >01/01/2015, <=Date()), and multiplies field count that fall within those parameters by first/second year subsidy rate if they're in that table. I think my logic is sound, I am just not sure how to write the query/expression, or if it's possible, or if there is a much simpler way to do it.

View 4 Replies View Related

Queries :: Summing Columns Into Multiple Rows?

May 13, 2013

I have a linked table. The table looks like:

Course 1 Name | Course 1 Grade | Course 2 Name | Course 2 Grade |
Student 1
Student 2
Student 3

I need to write a query that will consolidate all the courses (15 total) into multiple rows like this:

Course name | Course Grade
Student 1
Student 1
Student 2
Student 2

What is the best way to do this?

View 8 Replies View Related

Queries :: Summing Days Late Through Multiple Columns

Aug 29, 2014

I have 4 revised ship date columns, an est ship date, and an actual ship date. I have a column summing days late for est ship date and one summing revised days late. The expression I am having problems with is to calculate the total days late for the revised. I want it to check the 4th revised and if it has a date calculate from that date. If not I want it to go to the 3rd and calculate from that, and so on. I have pasted the code I was trying to use. It just goes all the way to the bottom of the code and calulates from the 1st revised ship date. I have also attached a jpg of the layout of the columns. I also want to do this in the query using expression builder.

Days Late Revised: IIf( [4th Revised Ship Date] <>null,(DateDiff("d", [4th Revised Ship Date] ,
[Actual Ship Date] )-DateDiff("ww", [4th Revised Ship Date] ,
[Actual Ship Date] ,1)-DateDiff("ww", [4th Revised Ship Date] ,
[Actual Ship Date] ,7)),IIf( [3rd Revised Ship Date]<>null ,(DateDiff("d", [3rd Revised Ship Date] ,

[Code] ......

View 1 Replies View Related

Queries :: Summing Quantities Of Same Product In Multiple Locations?

Sep 30, 2014

I have a transactions table which records a qty number (- or +) and references particular location details (location, room, rack, shelf etc). My question is how do I sum the qty for the same product at different locations. So my user sees that product A has a qty of X in location C and qty Y in location D. I can sum the transactions table qty which does give me a sum based on a single location but each location has multiple racks, shelves etc. I need to know the sum of transactions for the same product for exactly the same location, room rack etc

View 3 Replies View Related

Queries :: Complex Query Sorting And Summing Amounts Breakdown

Jan 28, 2015

I have a shell of my database in the below link for reference. I am trying to create a query for a report that will allow me to track charges and payments per client. Each charge has a ChargeID and each payment has a PaymentID. Multiple payments can be applied to one charge so the same ChargeID can show up multiple times with a different PaymentID. Payments that are catagorized as Third Party Payments will have not only a PaymentID, but a TPPaymentID.

What I need to do in the query and final report is track the total charges (even though the ChargeID may show up multiple times, I only want the charge itself to be calculated once and the charge to only be listed once for each client) and track the total payments as well as sum the total payments for each charge for each client.

View 9 Replies View Related

Queries :: Quotations Database - Queries Showing Too Many Records

Jun 25, 2015

I have a database that is used to create Quotations. After all of the information is entered the queries that hold the calculations must be run. I have lots of calculated that rely on other calculated fields. When I need to Sum all of the calculated fields in one field I must create a new query. I currently have a QuotationID, PartID, and MetalID all linked together. The first of the calculations are done per Metal, and these are working fine. I run into a problem when the calculations need to be done by part. My Queries are creating a record for every Metal and this is throwing all of my numbers off.

View 1 Replies View Related

Queries :: Identify Missing Records Between Queries

Jul 14, 2015

I have a query that will draw down student details who have completed a course in a given month (May for example), i would like to use this data to identify those learners who are not enrolled on a course in the next Month (June for example). There is no field that denotes whether a student has left only that a course assosciated with their ID has a completion date within that Month. There are approx 250 records.

In my head it should work something like this

1) Identify all learners who finsihed a course in May (Identify learner ID, must have a course end date in that month)

2)Cross reference these against all those who started a course in June and identify the students that have completed in May but did not start a course in June.

Is it possible to store all those who completed (May) in a table/query and cross reference those who started in June and identify of the May completions who did not start in June?

View 3 Replies View Related

Summing Records Where Field Is Between

Jul 31, 2006

I am trying to create a sum of a field called ec2mo where mopp is between 0 and 2 and then another sum when mopp is between 2 and 4, etc... I've tried a number of different things, but it doesn't seem to work - instead i get a single value for each field (i'm assuming because there are different values for mopp and many other fields)... I've attached the db with just that table.
Thanks very much for your help!
P.S. I'm not really at all good with using SQL so anything that can be done in a regular query would be most helpful!

View 3 Replies View Related

Summing Records Based On Criteria...

Mar 25, 2006

I am not sure if this possible, the methods I have looked up on the Internet take ages for the query to open but I'll give it a shot.

What I need to do is count (and increment) how many records are returned in a query for each person.

So if 1 person returns 4 unique records, in these records it shows 1,2,3,4 in each record.

The way i have tried is by using DLookup to check the next record and evaluate it using IIF but this takes about 25 seconds to return circa 50 records.

View 5 Replies View Related

Plz Help! Query For Summing Individual Records On Their Own

Jun 27, 2006

Hello Everyone,I am trying to perform a sum of records for the table belowSeatType| Ext rent | |Internal rent | Work Transfer 2 2VendorOther 2 3LOB BRF GTI BRF 2 5Displacement so that when I run the query I get the following followingSeatType| Ext rent| |Internal rent| | Total |Work Transfer 2 2 4VendorOther 2 3 5LOB BRF GTI BRF 2 5 7Displaceme What I mean is that from the first table how is it possible to get the results below. The query would generate a new column and place the sum of the data in the each row(record) in the same row in the total column. I would be really grateful. Have been bashing my head all day over this. PS I dont know any VBA. Is it possible without VBA?Thank you.

View 5 Replies View Related

Summing/Counting Records In Access

Dec 29, 2004

Hello & thank you in advance.
I am fairly new to using Access and would appreciate any help.

I have a file with 50,000 records
Example:
NAME CATEGORY
Karen Shoes
Karen Sports
Karen Collectibles
Karen Cars
Bob Music
Bob Sports
Bob Cars


The results that I am looking for are a sum of the total category purchases by each name.
EXAMPLE:
NAME CATEGORY
Karen 4
Bob 3

Is that something that I can do via Acces?

Thank you

View 4 Replies View Related

Grouping And Summing Mltpl Records To One Record

Jun 19, 2006

:confused: Hi everyone,

I have one table that I want to query. It's a basic table that contains:

MYKEY, AMOUNT
1234430, 100
1234435, 200
1234435, 100

1264435, 300
1264430, 200

1154435, 100
1154435, 219


1294430, 983
2010, 845
........ so on and so forth


Now, I simply want to GROUP MyKEY ENDING with 4430 and 4435 into one line and SUM the AMOUNT, thus showing like this:

MYKEY, AMOUNT
1234430and1234435, 100+200+100 --> (123 being the first common group)

1264430and1264435, 300+200

1154435and1154435, 100+219

etc etc.

i tried using simple sum and group by but does NOT work:

SELECT MYKEY, SUM(AMOUNT)
FROM MYTABLE
WHERE MYKEY LIKE ('*4430') OR ('*4435')
GROUP BY MYKEY

It does NOT ROLL-UP the 4430 and 4435 together.

QS: do i need to use VBA for this?

QS: do i need to have subqueries?

QS: please help?

Thanks!

View 4 Replies View Related

Summing Number Of Records In A Group On Report

Mar 28, 2013

I have a report I am grouping by a category, and I want to put the total number of records in that category in a textbox in the group footer.

How would I go about doing this? I have searched high and low with no avail...

View 2 Replies View Related

Queries :: Mass Duplicate Main Records And Related Subform Records

May 29, 2014

In my simple database (attached), I need to mass duplicate Tasks and their Notes.

I have three tables: tbTasks (PK: Task_ID), tbNotes (PK: Note_ID), jtbTaskNotes (FKs: Task_ID and Note_ID). jtbTaskNotes is my many-to-many junction table that ties Tasks to Notes.

The main form (fmTasks), bound to tbTasks, has a subform (sbfm_TaskNotes) that displays notes associated with each Task. On themain form,you select which Tasks you want duplicated via a checkbox. The append query (quCopyTasks) will duplicate all tasks that have the checkbox checked. All good there. However, I can't figure out how to also duplicate each task's Notes.

I found Allen Browne's solution [URL] ....., but that only handles duplication of one record at a time, whereas I need to duplicate many records at a time (sometimes 10+ records). How do I go about duplicating multiple Tasks and their associated Notes?

Before you ask "why are you duplicating records?": There are times when tasks need to be re-accomplished and therefore need to have a new record. It's easier to duplicate records than it is to hand-jam everything again.

View 5 Replies View Related

Using Queries But Need To Add New Records

Sep 17, 2007

Hey,

I currently have an Access front end backed up by SQL server.

I have 3 subforms that need to use a Access query that is based on a linked SQL table. It does everything I need but let me add new records. Upon checking the linked table has an enabled new rec button but the query doesn't.

Does anyone know what I need to change to get the queries to allow new records to be added since each subform has its own query?

View 3 Replies View Related

Combining Records From Two Queries

Dec 10, 2007

Can I use left and right outer join at a time? Means I want all records from both the table, and it should put 'null' whenever value is absent.

View 1 Replies View Related

Queries That Return No Records

Jan 31, 2006

Hi All,

I am wandering if it possible to check programatically whether a query returns a NULL result?

I am writing a program(VB) that involves an IFF statement where if there are records in the query it does a specific calculation & if there are no records returned in the query it does something else. Is this possible to achieve?

Thanks in advance:D

Cheers,

Karv

View 2 Replies View Related







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