Queries :: IIF Statement That Would Change Value To Multiple Fields

Oct 15, 2013

I have a problem that I can't seem to solve in SQL for my access 2010 query.

Let's say I have a the price of and for every year.

In the tables I have A building number, a building type, and electricity and water predictions for this year and many upcoming years (up to 40 years)

I need to apply a change to any building with the building type BRT to show only 10 percent of the electricity and water for ALL years (up to year 2052). So building 5 would show 8.5, 5.4, 9.5, 7.4, and so on.

View Replies


ADVERTISEMENT

Change Data Type For Multiple Fields At Once?

Mar 11, 2014

I imported a big table from excel with many columns. Access when I brought it in determined that they should be "text" format. I don't want to sit and change each field to a "number-double". Is there a way to quickly change data type for multiple fields at once?

View 1 Replies View Related

Multiple Queries, One Statement?

Aug 15, 2005

Hi all,

I thought the whole point of the ; sign at the end of a sql statement in access was so that you can
run more than one query at a time?
if this is the case, could you tell me why this doesn't work?
Code: sqlStr = "UPDATE tabControlFeeType SET FeeAmount = " & txtFirst.Text & " WHERE KeyFeeType = 1;" & _ "UPDATE tabControlFeeType SET FeeAmount = " & txtSecond.Text & " WHERE KeyFeeType = 2;" & _ "UPDATE tabControlFeeType SET FeeAmount = " & txtThird.Text & " WHERE KeyFeeType = 3;" Dim comm As New OleDbCommand(sqlStr, conn) comm.ExecuteNonQuery()
the query doesn't work if I put it straight into access either?

do I just have this wrong?

Thanx

View 2 Replies View Related

Queries :: SQL Where Statement Linked To Fields When Field Is Empty

May 23, 2013

I'm having trouble with using a where statement linked to fields when the field is empty. I need a way to say if field is null then 'do nothing'/'select all' else use the text from the box.

I have a form (ServicesRCSSearch) which has 3 combo boxes (Location1, Location2 and Location3). These fields are linked to a query. The button on the form generates the query.

My SQL for the query is currently:

Select Services.Key, Services.Location, Services_1.Location, Services_2.Location
From Services, Services_1, Services_2 (copies of the same table all left joined)

Where
((IIf(forms!ServicesRCSSearch!Location1 Is Null,"",services.Location=forms!ServicesRCSSearch! Location1))<>False)

And ((Services_1.Location)=IIf(forms!ServicesRCSSearch !Location2 Is Null,forms!ServicesRCSSearch!Location1,forms!Servi cesRCSSearch!Location2))

And ((Services_2.Location)=IIf(forms!ServicesRCSSearch !Location3 Is Null,forms!ServicesRCSSearch!Location1,forms!Servi cesRCSSearch!Location3))

This works in that it uses the fields to filter the query but when Location1 is empty there are no results as you can see from the code.

View 6 Replies View Related

Queries :: Multiple Query Criteria IIF Statement - True Condition

Feb 24, 2015

I'm trying to have a single or multiple query criteria based on what the user checks on a form.

I can't get the True condition to work at all, I get no records. Here is what I'm using

IIf([Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1,[TempVars]![EID] Or 86,[TempVars]![EID])

If I just put
[TempVars]![EID] Or 86
in the Criteria it works just fine.

View 14 Replies View Related

Queries :: SQL Crosstab Query With Multiple Grouping Levels - Transform Statement?

Mar 24, 2015

I'm trying to construct an SQL crosstab query that will output data like the picture I've attached in the .zip file.

The four variables from the data table would be [Client Accounting].[Marketer] (the left vertical column), [Client Accounting].[Closing Date] (the higher level horizontal column grouped by month), [Client Accounting].[Write Off] and [Client Accounting].[Refund] (the lower level horizontal columns as sums)

The totals column at the bottom and the two vertical columns at the right would be made in the report and wouldn't need to be in the query.

This is what I have so far but I don't know how to add a second TRANSFORM statement to be included and grouped by month!

Code:
TRANSFORM Sum([Client Accounting].[Refund]) AS SumOfRefund
SELECT [Client Accounting].[Marketer]
FROM [Client Accounting]
GROUP BY [Client Accounting].[Marketer]
PIVOT Format([Closing Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

View 2 Replies View Related

Queries :: IIF Statement Expression - Create Alias Column Based On Data From Two Fields

Jun 20, 2013

I am writing a very complex 'if statement' query expression. I need to create an alias column based on data from two data fields.

Here's the logic:

True: If [PP] = "WG" or "WS" or "WL" and If [GR] > 10 Then "Skilled" or
[GR] Between 6-9 Then "Semi - Skilled" or [GR] <6 Then "Unskilled"

False: "GS"

View 2 Replies View Related

Queries :: WHERE Statement - Enter Dates Into Date Reported Fields For Results To Show

Sep 11, 2013

I use this

'WHERE ((OperationalRiskEventTable.DateReported)>=Forms!U pdateForm!UDateBegin And (OperationalRiskEventTable.DateReported)<=Forms!Up dateForm!UDateEnd)'

in a query by form.

The problem is that you have to enter a date in the between values for results to show. If I don't enter information into a different field such as Full Name but I enter in 40 into Age then everyone that is 40 years old will show. On the other hand if I enter 40 into the Age field but I leave the Date Reported fields empty then no results will show.

How can I change it so that I don't have to enter dates into the date reported fields for results to show?

View 4 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 :: Update Statement For A OUTER JOIN Select Statement

Feb 12, 2014

I have the following Select Statement:

SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],

[code]....

Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?

View 2 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

Queries :: Merging Multiple Fields Into One Field

May 15, 2015

I have a table which has each claim as a single record. A client wishes us to provide the data in a format that lists the each procedure on its own line. I have attached a highlevel example of what the data currently looks like and what the query results should look like.So I need to create a query that would repeat the Claim Number and place all the Procedure fields into one field.

View 1 Replies View Related

Queries :: Matching Fields From Multiple Columns

Jun 26, 2013

I have a series of numbers in 30 columns. I want to identify where either of two numbers appear in the same record regardless of which of the 30 columns they are in.

OTHCPT2OTHCPT3OTHCPT4OTHCPT5OTHCPT6OTHCPT7
00300C1778C1820J0690 J2250 J2270
00300C1778C1820J0690 J2250 J2270
00300C1778C1820J0690 #NULL!#NULL!
2318095978C176788307J0131 J2270

For instance if 23180 and J2270 are in the same record I want to identify them for all of the records.

View 7 Replies View Related

Queries :: Subtract Two Fields From Multiple Records

Sep 5, 2014

I have the following source data:

Code:
Item Revision Start End
332 B 9/2/11 9/15/11
332 C 9/21/11 9/22/11
332 E 11/2/12 11/29/12
2A9 A 1/13/13 1/14/13
2A9 C 1/16/13 1/18/13

I'm trying to make a select query to provide the following output (RevisionStart-PreviousRevisionEnd):

Code:
Item Revision Span
332 C 6
332 E 407
2A9 C 2

I've struck out with union queries and aggregating/sorting letters and don't know where else to look. Is there a specific name for this type of operation anyway?

View 4 Replies View Related

Queries :: Minimum Of Multiple Fields In Expression

Aug 26, 2013

I am trying to set up a calculated field in one of my form querries, using expression builder.

In one of my source tables I have 4 date fields called Inspection date 1 to 4

I need the lowest date among those inspectinos which are in the future (next inspection) If all 4 dates are in the past or Nulls, the function should return the current date. The logic of the expression could be:

MIN(MAX(D1,Date()),MAX(D2,Date()),MAX(D3,Date()),M AX(D4,Date()))

How can I do this in expression builder?

The built-in functions DMin and Dmax work with single field arrays, witch would be perfect if the database were properly designed, but now I dont have the power to change this.

View 1 Replies View Related

Queries :: Find Difference Between Multiple Fields In Two Tables

May 8, 2013

I have a database with two tables, one for the amount that was estimated in each cost section, and one for the actual amount billed for each cost section. The tables have the same number of fields, all with the same names. They can be linked together with event ID. Each table has over 100 fields and I would like to find the difference between what was estimated and what the actual was for each event. I would also like to see which cost section has the most and least variance. I am trying to do this without going through each cost and putting [tEst].[CostName]-[tActual].[CostName].

View 2 Replies View Related

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 2 Replies View Related

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 :: Date Range Search Of Multiple Fields

Nov 24, 2013

I am building Access (2013) database for 100+ employees to keep track of their expiry dates of visa/passport/work permits, for that to work I need to be able to search between date range to generate a report to work on. please check out the image attached as it will explain better where I am stuck, with current settings access keeps giving me empty query table as I think it searches for exact same result in all 3 fields at the same time. But I want it to search between all 3 fields and display if any of the 3 fields falls into that range.

View 3 Replies View Related

Queries :: Update Fields Of One Table With Multiple Criteria

Aug 12, 2015

How can i update fields of one table with update query where creteria are more than one 1

Example:

DoCmd.RunSQL "UPDATE Salary2009toUpdate SET " & Me.Combo2 & "='" & Me.Text4 & "'
Where Empid='" & Me.Combo0 & "'" and where bank='" & Bankname & "'"

What is the syntax error ...

View 5 Replies View Related







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