Queries :: Calculate Multiple Fields For One Result - Callsign

Feb 21, 2015

I have never created a single query that is this complicated.

I have a time card database that I created back in 2003 and over the years more and more new task have been added to track volunteer hours has been incorporated into it.

The current task at hand is to determine if each member has achieved or meet certain requirements each year.
They are attend 8 or more meeting and check into 5 or more nets and be Net Control Operator for at least 1 radio net and be involved with 1 or more Public Service events. Sounds simple right?

I have a table that contains all the information that is need to get the result I want but am having a problem getting that result with a single SQL query. Everyone you talk to says use SQL query to obtain calculated vales and never store/save them.

So from the work table below meetings consist of 4,5,6,8,9 and nets consist of 10,11,14,15 and events consist of 2, 13 and the NCOflag is T/F.

So those are all the parts, count the number of meetings, nets, events and NCO and set the meets requirement flag T/F
I keep getting "You tried to execute a query that does not include the in specified expression <name> as part of an aggregate function. (Error 3122)"

In this case it keeps complaining Callsign.

You did not enter an aggregate function in the TRANSFORM statement.

Here's the statement I created:

SELECT [Work Hours].Callsign, [Work Hours].[Work Code], [Work Hours].NCOflag,[Work Hours].SubMtg, [Work Hours].SubNet, [Work Hours].SubNCO, [Work Hours].SubEvnt, [Work Hours].ReqSubTotal
FROM [Work Hours]
WHERE (((Year([Date Worked]))=(Year(Date()))))

[Code] .....

It is failing on the first HAVING (([Work Hours].SubMtg) =Sum(). I have tried many variations and changes but don't see what is wrong with it.

View Replies


ADVERTISEMENT

Queries :: Search With Multiple Fields Result From Query

Dec 26, 2013

I want to make a form which will allow me to get data from a query and search using 3 fields parallel.

Attached the database in 2010 format.

Password of the db is "nolimit".

The query PTM & Equipment should be bound with the form and

If I enter month, PTM and equipment need rest 2 fields displayed.

Like choosing the above 3 fields from the drop down , the next 2 text box should display the rest two fields.

View 3 Replies View Related

Queries :: Calculate Ratio And Then Arrange Result Based On Facility Types

Mar 20, 2013

I have a simple query like below.

Code:
SELECT [score_admit] / SELECT [score_discharge] AS Ratio, facility_type
FROM tbl_test
group by facility_type;

It's really just based on one table, but what I'm trying to do is to calculate the ratio for the scores, then arrange the result based on facility types.

The error I get is a syntax error the SELECT statements.

View 3 Replies View Related

Queries :: Calculate Time Difference And Show Result Under Certain Criteria In Query

Jul 9, 2013

ID Up Time Down Time
John 18:00 15:00
Kelvin 08:00 08:05
Melisa 23:00 02:00

This is the Table , i need the Query result show IF [Up Time]-[Down Time] is >=3 hrs

Like Below :

ID Up Time Down Time
John 18:00 15:00
Melisa 23:00 02:00

IDUp TimeDown TimeJohn18:0015:00Melisa23:0002:00

I tried DateDiff("h",[Up Time],[Down Time]) in Field but POP out with error "Syntax error (comma) in query expression 'Table1.[DateDiff("h",[Up Time],[Down Time])]' " ...

View 9 Replies View Related

How To Calculate The Fields From Two Queries?

May 23, 2005

I need to pull 2 fields from 2 different queries, then calculate them. Those 2 queries has the same structure, just one is last year's data, the other is this year's. :rolleyes:

I tried using the 3rd queries to combine them, then calculate from there, but then I had no clue where to go from there. I don't sql...I was wondering is there anything to do with sql? :confused:

I'm stucked so far, how can I solved it? :mad:

View 5 Replies View Related

Consolidating Multiple Queries Into One Single Result

Dec 17, 2006

I couldn't find a similar question already answered, so here it goes. I have multiple tables used to store information on different research materials:

table Books:
- BookID
- Author
- Title
- Year
- Cabinet

table Papers:
- PaperId
- Author
- Title
- Year
- Cabinet

table Catalog:
- CatalogID
- Author
- Title
- Year
- Cabinet

And I have a query for each of those tables that helps me find information. What I'm trying to do is to consolidate the result of those individual queries into one single query that later I can insert into a form to display a search result.

So the resulting query would look like:

Type.....ID....Author.....Title....Year....Cabinet
Book.....001...John.....Good book....2005....C01
Book....002.....Mary....Other book...2006...C01
Paper....001....Albert...PaperABC....1987....C01
Paper...002....John.....PaperXYZ.....2006....C02
Catalog...001...Mark....Cat00A....1989.....C02
Catalog...002...Bill......Cat00B.....2004.....C03
etc.

Is this possible?

Any help is appreciated!

Thanks.
Daniel

View 4 Replies View Related

Queries :: Calculate Fields Within Table

Dec 18, 2013

I am trying to calculate 5 fields within my table in a query, but for some reason some rows do not calculate as not all the fields contain a value (blank field), is there anyway that I could bypass this issue?

View 1 Replies View Related

Queries :: Calculate Multiple Values Same Field

Feb 22, 2014

I have a table where there are multiple vehicles, each identified by their vehiclenumber. Each record holds the vehiclenumber, date and odometer reading. I need to figure out how to calculate records in this table per each vehiclenumber.

Below is a code that works, but only when i have each vehicle with the same vehiclenumber.

SELECT tblOdometer.VehicleNum, tblOdometer.ODate, tblOdometer.Odometer, tblOdometer.Odometer AS OdomAlias,
Nz(DLast("Odometer","tblOdometer","[Odometer] < " & [OdomAlias]),0) AS Previous, [Odometer]-[Previous] AS Difference
FROM tblOdometer;

View 4 Replies View Related

Queries :: Display Multiple Rows Into One Row Query Result

Mar 25, 2013

I have a result for a query made from four different tables for which I would like to refine the result of this query grouping multiple rows into columns.

Attached is a pdf file showing the results being obtained by my query and underneath is how the result would like it be after running the query.

I am currently using Access 2010.

View 5 Replies View Related

Queries :: DateDiff Function - Calculate Difference In Dates Between Two Fields

Apr 22, 2013

I am trying to calculate the difference in dates between two fields. How do I find the difference in days between field one which contains the date 04/12/2011 and field two which contains the date 04/12/2013? I have tried to use the datediff function, but it keeps telling me it doesn't recognize the field name, even though the spellings correct.

View 3 Replies View Related

Queries :: Calculate Difference Between Two Date Fields To Get Total Time

Oct 26, 2014

I have to create a query in access that will calculate two Date and time fields [Date & Time Left]/ [Date Returned], need to figure out between the two fields. Trying to identify when the rep returned the call and the number of business hours (6:00am - 4:30pm) it takes to return a message in Ms Access 2010.

The only issue is the calculation has to be done by time and so I have to calculate what time they left the message(so the difference between [Date & Time Left] and [Date Returned) [Date & Time Left] and when the rep returned the message which is suppose to be [Date Returned] but the problem with this field is the data entry is in date format (10/9/2014, 00/00/0000) of Date and not Date and Time like the [Date & Time Left] field, so I don't know what to do now. Not sure what to do now not a database that create or have allot of control over.

View 4 Replies View Related

Queries :: Filter Query To Calculate Sum Of Fields In Date Range

Nov 18, 2014

I have a table, tblDailyCalls, that contains agent_name, date, calls_ answered, and talk_time. Ideally on a form, the user will select an agent, enter the date range in txtStartDate and txtEndDate and a report opens to show what the total amount of calls and talk time is for that date range.

All I've managed so far is doing a simple expression on the report itself to sum the fields I want. But my method returns every date in the range. I would like to only display the total.

I've been trying with Totals in the query and crosstab queries but am not familiar with them.

View 4 Replies View Related

Queries :: Calculate New Fields Based On Current And Prior Year-end Numbers

May 6, 2013

I am creating a Make Table Query and calculating new fields based on current and prior year-end numbers. If the prior year-end number does not exist (Is Null), I want the use the current rate or calculate the change in rate. I have typed the below in the Field Box:

CHG_IN_PGM_RATE: IIf(([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE]) Is Null,([MASTER LIST CURRENT]![PTD_PM_PGM_RATE]),(([MASTER LIST CURRENT]![PTD_PM_PGM_RATE])-([P0_10 Tbl - AM PYE PTD Detail w Rates]![PTD_PYE_PGM_RATE])))

However, when running the query, I get the attached error message.

The screenshot will also show how the two tables are joined.

View 2 Replies View Related

Calculate Query's Field With Two Result

Sep 28, 2005

Hi All
I try to create a query based on Table1 and fields Date and Result. Is it posible to get a query that calculate two amount of Result field when that field is null and not null?
That two values of Result field I will use to create monthly Pivot report in which each bar will display amonts Completed and NonCompleted result.
Thanks.

View 1 Replies View Related

Calculate Last Record Value Plus 1 / Display Result On Form?

Nov 18, 2014

I'm trying to use the job number field, which is my primary key in my table, to auto assign the new job number on my Forms. Currently, I have to enter a new job number manually, and it has to be unique because that is the way I have it set. I can't use autonumber because Access does not allow you to select what number you would like to start from, which would not play well with my current job numbers.

Basically I need Access to get the job number from the last record and add 1 to it, or just find the last / highest current job number and add 1 to it.

I created a query (qryFindJob#s) that list all of the Job Numbers, but I'm not sure how to add the query results to my Form to display the Job number + 1. I created a text box, typed a simple expression in the control box to see if I was on the right path (=[qryFindJob#s]) but I keep getting a #Name? error in the txt field.

View 7 Replies View Related

Forms :: Calculate Integer Difference Between Due And Result Date

Sep 20, 2014

Trying to calculate the integer difference between Due_Date and Result_Date excluding weekends and holidays.

I have a table (Holidays) with the dates of the holidays in it. The table looks like:

ID Description Holiday
1 New Years 1/1/2014
2 New Years 1/1/2015

So, if Date_Due: 9/25/2014 and Result_Date: 9/29/2014, then TAT = 2

Since 9/27/2014 and 9/28/2014 are weekends they are excluded from the calculation and only that Thursday and Friday are used in the calculation.

Code:
Private Sub Result_Date_AfterUpdate()
[TAT] = NETWORKDAYS(Due_Date, Result_Date, tblHolidays)
End Sub

View 7 Replies View Related

Queries :: Conditional Query To Post Result In Field And Filter Result Records?

Mar 5, 2014

I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg

In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.

View 1 Replies View Related

Queries :: If There Is No Result In Query Need To Have Default Result Zero

Oct 12, 2013

I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.

View 5 Replies View Related

Queries :: Method For Combining Crosstab Queries With Same Criteria From Multiple Fields?

Dec 2, 2014

I have 3 cross tab queries that are completely identical with the exception of the field that they pivot. Each field is searching for the same values just in different columns, with the end goal being to get the sum of the values for each pivoted column. I'm wondering rather than having 3 almost identical queries is there a way to use a crosstab to sum the values from each of the three fields rather that having 3 queries which then have to be aggregated in a fourth?

QUERY1

Code:
TRANSFORM
IIf(Count(PT_LEVEL.UNIT) Is Null,0,Count(PT_LEVEL.UNIT)) AS CountOfUNIT
SELECT
PT_LEVEL.INF_YEAR,
PT_LEVEL.INF_MONTH,
PT_LEVEL.UNIT

[code]...

View 2 Replies View Related

Access Queries With Multiple Yes/no Fields

Nov 7, 2004

I have created a table with multiple Yes/No fields. I wish to create a
query where the user can select from a form that displays all the Yes/No fields
Is it possible for this to be done

Thanks for your help

REFINEMENT OF PROBLEM
I have created a table for a small library at work. The items in the table are item_number, author, title etc. Also I wanted to classify each item into various categories. In the Table I have set the categories as Yes/No fields. An item can belong to a number of categories eg admin, finance,resources etc. What I would like to do is for a user to be able to interegrate the table via a query to find the articles that meet the criteria they want to search on. Ideally this would be done via a form that the user can tick the applicable categories (Yes/No fields). I tried setting up as parameter queries however this is very messy. Is there a way it can be done. Sorry for confusion in first posting.

View 4 Replies View Related

Queries :: Multiple Dates Fields

Nov 15, 2014

I have a DB that consists of Movie titles and the multiple dates ( as many as 10) on which they will be used in the coming year.I built a flat table with 10 date fields.Then tried to build a relational table with just movie title and dates linked to ID.I cannot work out an ability to SEARCH the Database for a SPECIFIC DATE and get returned a listing of ALL Movie titles that will air on that date,

View 9 Replies View Related

Queries :: One Parameter For Multiple Fields

May 20, 2013

Is there a way to have one parameter in a query effect multiple fields?I have a table that details service calls on our equipment. There are columns in that table for each part of the equipment that get marked, depending on what needed serviced (i.e. software, printer, keypad, monitor, etc). I would like to be able to run a query that will show all the service calls where one part of the equipment was serviced multiple times without have to enter multiple parameters or write a union query (there would need to be 11 unions or 11 parameters for each part to be captured). For example, I would want to know how many pieces of equipment had 3 or more service calls on their software. I want to run the query without having to say that I don't care how many service calls were performed on the printer, monitor, or keypad. I would like to be able to say "Software >3" and have the query run on that information.

View 1 Replies View Related

Queries :: Sum A Value Based Off Multiple Fields

Oct 22, 2013

I'm trying to build a query that uses the information pulled from controls on a form called "CharacterCreation" - 2 of the controls are "Race" and "Class", and the form stores this data in a "Characters" table.The query needs to refer to the Characters table, and another table called "Modifiers".In this second table I have several numerical fields such as hp, str, etc, and I have two other fields called "Type_Modifier" and "Type" Among the Type_Modifiers are Race and Class, where their corresponding "Type" could be for instance Demon and Magician..

So the idea is that when I mark on the form (and thus creating the record in the first table) a Race of "Demon", and a class of "Magician" I want the query to refer to both of these fields, and sum the values in the second table where the "Type" is either "Demon" or "Magician" or whatever I decided to choose. I feel like I should be able to figure this out but I'm having a hard time..Here's an example of the SQL I've tried that shows nothing.

Code:
SELECT Characters.Char_Name, Sum(Modifiers.hp) AS SumOfhp
FROM Modifiers INNER JOIN Characters ON (Modifiers.Type = Characters.Class) AND (Modifiers.Type = Characters.Color2) AND (Modifiers.Type = Characters.Color1) AND (Modifiers.Type = Characters.Species) AND (Modifiers.Type = Characters.Race)
GROUP BY Characters.Char_Name, Modifiers.Type
HAVING (((Modifiers.Type)=[Characters].[Race]));

I also tried this, which I thought would work but it displays nothing as well.

Code:
SELECT Characters.Char_Name, Sum(Modifiers.hp) AS SumOfhp
FROM Modifiers INNER JOIN Characters ON (Modifiers.Type = Characters.Class) AND (Modifiers.Type = Characters.Color2) AND (Modifiers.Type = Characters.Color1) AND (Modifiers.Type = Characters.Species) AND (Modifiers.Type = Characters.Race)
GROUP BY Characters.Char_Name, [Type]=[Characters].[Race];

View 12 Replies View Related

Queries :: Lookup Using Multiple Fields

Jun 5, 2013

I have a query that based on certain codes in a record does multiple joins to pull in other codes... I want the query (based on these pulled in codes) to do a lookup in another table to extract 1 field that has a description...

Can I do this in one query??? Do I need to do a Make Table to first pull in the codes and then do a second query with a DLookup based on those fields???

View 3 Replies View Related

Queries :: Counting Multiple Yes / No Fields

Apr 26, 2013

I have a table, which is being populated by a number of users. The table has 211 rows (customer field - this is a fixed amount) and there are 5 other columns (information which might or might not be available for that customer) which are all yes/no fields. Each row may could have any combination of ticks in those 5 columns.

I have a form to fill out this information, but now I need to use the data.

I'm trying to create one query which counts how many of each field are populated. I need it to give me the total of each of the "information" columns, and another to give me a list of all of the customers which have at least one of the information columns ticked.

I will also need to be able to see how many customers have all of the information columns ticked, and how many have none.

View 1 Replies View Related

Queries :: Check Multiple Fields And Return One Value?

Sep 16, 2014

lets say I have a table with five tasks for five employees and each employee's data type is with 'Yes or No'.

I need to have a desired result to check

1.if all the employees have completed that particular task then the result should be 'YES'

2.if anyone employee has not completed that particular task then the result should be 'NO'

so, for each task there must be a check on all the five employee's data type.

View 1 Replies View Related







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