Queries :: Calculated Field - Top 50 Queries Setting Not Working

Sep 24, 2013

I have a query which returns a calculated percent. I have ordered that in descending order, and now want to see the top 50. So (In Access 2010) I entered 50 into 'Return'. But it returns all of the records!

Is this because pct is a calculated field? How can I correct this? The SQL seems to be correct.

Code:

SELECT TOP 50 HeciFail1.POHECI, HeciFail1.POQTY, HeciFail1.FAILQTY, IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) AS PCT
FROM HeciFail1
ORDER BY IIf(Nz([FAILQTY])>0,Round(100/([POQTY]/[FAILQTY]),0),0) DESC;

View Replies


ADVERTISEMENT

Queries :: Changing Query In VBA Not Working With Calculated Dates?

Sep 9, 2013

I am trying to run a change an existing query in real time to allow date filtering for 4 different categories. I can get two of them to work. Calibration Date and Icepoint Date. But for The two calculated fields Calibration due date and Icepoint date I cannot get it to filter properly e.g. for 2013 dates it also includes 2014 dates and just does not work properly. I am thinking its due to it being a calculated field but don't have a clue how to fix it. see pasted code for calibration due date filter where Todate and Fromdate are the 2 dates used. I also have the on current code and the exit code to reset the query to its original status.

Public Sub SetDate1()
'Apply date filter and rebuild query in real time
On Error GoTo Err_SetDate1
If IsNull(Me!ToDate) And IsNull(Me!FromDate) Then
MsgBox ("Please Enter Date First"), vbExclamation
GoTo Exit_SetDate1

[code]....

View 3 Replies View Related

Queries :: Setting Temp Var As The Field

Aug 16, 2013

I have a table [Employees] which has the following Fields

- TxtEmpID
- StrEmpName
- StrEmpPassword

Which I use for my login table with the following script:

If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[TxtEmpID]=" & Me.cboEmployee.Value) Then
TxtMyEmpID = Me.cboEmployee.Value

On my form the drop down to select the employee is

SELECT tblEmployees.[TxtEmpID], [tblEmployees].[strEmpName] FROM tblEmployees;

(Seemingly all this is based on the TxtEmpID)

When I create a Temporary Variable

[SetTempVar screen.activecontrol with the name SetUserID]

And then want to place the variable in a form or whatever else, I can only see the TxtEmpID. I would like to set the TempVar as the field StrEmpName.

View 11 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 :: Setting Field Width To Zero In Select Statement

Jun 11, 2015

I am building a form that uses list box selections on the form to make a temporary query and open the results in Excel.

It mostly works in just trying to make it more functional.

Currently the listbox that contains the first and last names of the personnel also has a unique shorttext 'number' as a primary key for the table (bad choice in my opinion but I didn't design the database I just have to work with it).

The short text primary key is hidden by an option given in the listbox wizard that let me set that field width to zero but still search on it. This is how I currently build the query's where clause when it involves names. I search the primary key.

I would like to update the names list box based upon which cities and locations are selected (both are multi-select).

Do I need to change how I make the "where" clause to use first and last names or an I use the same query and just hide the USER_ID

The query looks like this

Code:

SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID
FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO
ORDER BY t_asset_personnel.LAST_NAME;

And the list box wizard hides USER_PHY_ID

By setting the width to zero

If I make my own query in the City_After_Update()

Can I do something like this

Code:

SELECT DISTINCT t_asset_personnel.LAST_NAME, t_asset_personnel.FIRST_NAME, t_asset_personnel.USER_PHY_ID
.fieldWidth(0)FROM t_asset_personnel INNER JOIN t_asset_master ON t_asset_personnel.USER_PHY_ID = t_asset_master.ASSIGNED_TO
WHERE [forms]![myform]![citylist].[itemsSelected]'obviously more code is needed here this is just for conceptual illustration
ORDER BY t_asset_personnel.LAST_NAME;

View 7 Replies View Related

Setting Up Calculated Field In A Report?

Jul 8, 2013

I am "OK" when it comes to figuring out a formula in Excel, but Access is much different for me. I am looking to set up a calculated field in a report.

The result is find the Client-to-Staff ratio but there are two possible conditions:

CONDITION 1: If VacantFTEE = 0
ActiveClients / (DirectFTEE + DetailedFTEE + ProvidedFTEE)

CONDITION 2: If VacantFTEE > 0
ActiveClients / (DirectFTEE + VacantFTEE + DetailedFTEE)

I am presuming I need some sort of IF/OR statement to make this as 1 formula, but I can't seem to make it work.

View 11 Replies View Related

Queries :: Calculated Field In A Query

Mar 23, 2015

This is not the normal calculated fields in query's. What I want is different, I want it to be like

IFF(ShowID="A",[TotalSales],0)

Thing I have is, I want to know all the Total sales in on row that has Show A in it and then Total sales for Show B. Problem is, I have over 130 shows. How would do that?

View 6 Replies View Related

Queries :: DCount With Calculated Field

Dec 8, 2013

I am working with DCount in a query based on a calculated value [MyCalValue] that is a status.

The status can be: Complete, On Going, Past Due, Additional Information, No Date.

In the DCount, I want to return 0 if null.

Sample that works fine

Code:
Green: DCount("[MyCalValue]","Open Issues","[MyCalValue] = 'COMPLETE")

But I want combine values in an OR statement but can't get it to work.

Code:
Green: DCount("[MyCalValue]","Open Issues","[MyCalValue] = 'COMPLETE OR On Going")

Also not working is when I am trying to get a count based on the month like this:

Code:
LastMonth: DCount(Month("[RequestDate]","Open Issues","[RequestDate] = fLastMonth(Now())",0)

View 7 Replies View Related

Queries :: Calculated Field Error

Nov 4, 2014

I have a several fields, calculated, that break apart a string of text at every open and closed parentheses. They work like they are supposed to with no errors. However, I want to use the result of this particular field in a combo box to populate another combo box with the other half of the string that will be split. The problem, I think, is that since the field is calculated, it will not allow me to select it in the combo box, saying that the field can't be edited.

So, I added the same field names to the table that the query was built on, however when I run the query, it says that there is a syntax error with a comma in the code. If I take out the table in the query, (not using the SQL side, just the query builder), it works again with no problem.The text I am breaking apart looks like this: 1.234(a)(1)(A)(2)(b)(i)(-a-)

I need this field to break the text up at the (1) mark, so the code I am using looks like this:

Code:
PrinSubChap1: IIf([Sub2] Is Null,[PrincipleNumber],Left([PrincipleNumber],InStr(InStr(1,[PrincipleNumber],"(")+3,[PrincipleNumber],")")-0))

The other fields I have do the exact same thing, written the same way, with adjustments made to the position (+3,0).I have stared at this for awhile and can't figure out why it is giving me an error and research on the web doesn't appear to cover my question.

This is the error message: Syntax error (comma) in query expression '[mytable].[IIf([Sub2] Is Null,[PrincipleNumber],Left([PrincipleNumber],InStr(InStr(1,[PrincipleNumber],"(")+3,[PrincipleNumber],")")-0))]

View 2 Replies View Related

Queries :: Calculated Field In A Query

Nov 25, 2013

I Have some calculated fields in a query and want to update it in the table .So,is there anyway to store these fields.Otherwise can i store this Query data every month in database so that when i re-run the query the previous month data will not be affected.

View 1 Replies View Related

Tables :: Setting Primary Key On A Calculated Field?

Apr 18, 2013

Is there anyway to set a calculated field as the primary key? Or how to duplicate the value of a calculated field into a new field and set the new field as the primary key?

View 7 Replies View Related

Queries :: Calculated Date Field In Query

Sep 10, 2014

I am having a problem with calculating a date field in a query. Prior to this posting I've done some research and made several changes to my query. This only resulted in fixing one problem but then creating another problem. Original problem was I had 2 fields, arrived (23:36) and stemi (0:07). I use the following calculation AT_ST: DateDiff("n",[arrived],[stemi]) which resulted in -1409. So my research showed me I had a problem with the date whenever the time went past midnight and trying to calculate a zero hour number. I changed my calculation to

AT_ST: IIf([stemi]>=#11:59:00 PM#,(DateDiff("n",[arrived],[stemi])),(DateDiff("n",[arrived],[stemi]+1440) Mod 1440))

This works fine and gives me the result of 31 minutes which is what I want, however the problems comes in when I change to this calculation any where there was a negative time now has a 1400+ plus value. Such as arrived (7:37) and 1st_eck (7:18) = 1426 where as before it would report -14 (yes, negatives are acceptable for my reporting because sometimes a call to the hospital is placed before the patient arrives so we want to report on the negative splits). I've tried using a nested IIF to calculate for stemi time being less than arrived time, this didn't work when I tried to use it on the calculated query field. I was wondering if I could write something to check the value of the calculated field if it is greater than 1440 and if yes - subtract 1440 from it. So in the example above 1426-1440 = -14. Is it possible to do this within the query or do I need to do it using VBA

View 14 Replies View Related

Queries :: Can Place Criteria In Calculated Field?

Jan 27, 2015

Can I place a criteria in a calculated field?

[SellingWgt]*[SellingPrice] is ok but only
if [SellingUnits] = "lbs" or [tblSellingUnits].[SellingUnitsID] = 1

View 7 Replies View Related

Queries :: Running Total In Calculated Field

Oct 5, 2013

I want to calculate running total and find out the date when that total is greater than a number.

My initial plan was to use Dsum and then use dlookup to find when that Dsum value > [Fixednum].

But when I try Dsum and use Totals in query, access shuts down. maybe because of 15000 rows.

I have attached a sample database that shows what Im working with and what I would like.

View 2 Replies View Related

Queries :: Filter Calculated Query Field

Nov 13, 2014

So I have the following query field which calculates another field.

How do I Filter the records in this calculated field to only return TRUE, as if I put "TRUE" in the Criteria for this field (or anything at all) then a parameter message box pops up asking for [Balance].

Code:
Balance1: IIf([Balance]<>0,"TRUE","FALSE")

View 8 Replies View Related

Queries :: Selecting A New Calculated Field In Same Query

Aug 30, 2013

I want to calculate a field that is Sales*6+Salary, then in that same query I want to select ONLY the greatest Salary per employee. So for example

SELECT EmpId, MonthDate, Sales, Salary, [Sales]*6+[Salary] AS SalTot
FROM EmpTable S1
WHERE SalTot = (SELECT MAX(SalTot) FROM EmpTable S2 WHERE S1.EmpId = S2.EmpId);

Can I not select a value that has been calculated this query?

View 5 Replies View Related

Queries :: IIF Statements - Rounding Value In Calculated Field

Mar 24, 2014

I am using access 2010. I have a query with an iif statement on a calculated field im trying to round up. ex.

Code:
TotalDiscount: Round(iif([field1]="Piece",[int]-[decr],[field2]*[field3]/[field4],3))

I get an error something like "wrong number of arguments or expression on comma.

View 2 Replies View Related

Queries :: Calculated Field To Display Value Of One Field Based On Another

Jul 23, 2015

I have a query with a Date field for EndDate (the dates for end-of-week, Fridays in our case) and another field for Sales (number of sales, not dollars).I want to add 4 calculated fields that represent weeks and have the Sales appear in the correct column (field) for that date.So I will have columns for 10 July 15, 17 July 15, 24 July 15 and 31 July 15 and I want the Sales for each record to land in the correct date column, based on the EndDate column. (The 4 fields is just for the sake of the example, I will actually be having dozens of these calculated date fields).I tried to do it by setting up the 4 calculated fields like:

10Jul15: Sales
and then adding Criteria like:
EndDate = #10/07/2015#
It doesnt work.

View 3 Replies View Related

Queries :: Rounding Value In Calculated Field In Crosstab Query

Aug 4, 2015

I have a very basic query with the following calculated field in it:

ProjRevNRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentNRC]*[Rev Flow Through],0)

I went into the properties if that field inside the query design and changed it to Standard with 0 decimal places and it worked fine.

But when I based a crosstab query off the query that contained the above calculated field, I cannot seem to get the numbers to format correctly. 1231313.424 is shown instead 1,231,313 and I don't have a line in the Properties window to even change the decimal places. It doesn't recognize when I change the format to Standard. I have tried using Round([ProjRevNRC]) which gets rid of the decimal places as desired but does not show commas.

I'm sure it's a simple part of the Round expression that I am missing but nothing has worked.

View 4 Replies View Related

Queries :: Put Sum Of All Records In Calculated Field And Divide It By Predefined Value

Feb 6, 2014

I want to take the sum of all records 'cost' i.e.
record 1 - 2
record 2 - 4
record 3 - 3

I want to take the sum and put it in a calculated field and use it to divide it by a predefined value 'no of responses.'

So I make the field in the query -> Calcfield: (Sum([cost])/[responses])

I am pretty new to this and i don't understand why this won't work? Is it something to do with the row Group by (total.)

View 3 Replies View Related

Queries :: How To Filter Calculated Field In Query Using A Criteria

Jul 17, 2014

I have query with a calculate field to finds the next service due date but I'm having a problem getting it to only show services due dates in the next 30 days.for some reason I cant add a criteria date()-30...I have to calculate the next service it takes service intervals from maskservicemonths field then find the last service date and generates the next service due date NextService: DateAdd("m",[MaskServiceMonths],[FindLast]).

View 14 Replies View Related

Queries :: Calculated Field Is Blank For Records With Even Primary Key

Jun 17, 2013

I have a calculated field in a query. The field name is TotatPt (this is to calculate the total points students have earned during the term). The expression is as follows:

Code:

TotalPt: [Att1Pt]+[Att2Pt]+[Att3Pt]+[Att4Pt]+[Att5Pt]+[Att6Pt]+[Att7Pt]+[Att8Pt]+[Att9Pt]+[Att10Pt]+[Att11Pt]+[Att12Pt]+[Att13Pt]+[Att14Pt]+[Att15Pt]+[Att16Pt]+[Att17Pt]+[Att18Pt]+[Att19Pt]+[Att20Pt]+[Att21Pt]+[Quiz1Pt]+[Quiz2Pt]+[Quiz3Pt]+[Quiz4Pt]+[Quiz5Pt]+[Quiz6Pt]+[Quiz7Pt]+[Quiz8Pt]+[Quiz9Pt]+[Quiz10Pt]+[MidtermWritPt]+[FinalWritPt]+[Proc1Pt]+[Proc2Pt]+[Proc3Pt]+[Proc4Pt]+[Proc5Pt]+[Proc6Pt]+[Proc7Pt]+[Proc8Pt]+[Proc9Pt]+[Proc10Pt]+[Proc11Pt]+[Proc12Pt]+[Proc13Pt]+[Proc14Pt]+[ProcPracPt]

I think this should be no complicated expression (though a bit long) and should just add the fields together. But what I get is that the calculated field appears as expected for records with an odd primary key (1, 3, 5, 7, ...) and turns out blank for records with an even primary key (2, 4, 6, 8, ...)!

I've attached a screenshot.

View 6 Replies View Related

Queries :: Access 2003 - Join Via Calculated Field

Apr 28, 2013

I'm stuck in Access 2003 - the group I am working with is unwilling to upgrade due to costs, and I've inherited this database (or I should say, a glorified spreadsheet), so unfortunately I am stuck with the tools that I have to use.

So I have Table A:MemberID (autonumber, Primary Key)

NameLast (Text)
NameFirst (Text)
TLBeginner (Yes/No) - Training level
TLIntermediate (Yes/No) - Training level
TLContinuing (Yes/No) - Training level
TLAdvanced (Yes/No) - Training level

I have created Query1 with the above table and added in a calculated field which essentially takes the training levels and converts it to a decimal number (basing it off a binary number of the 4-bits/Yes-no fields) with the calculated field below:

TrLevelTxtID: IIf([TLFundamental]=True,1,0)*1 + IIf([TLIntermediate]=True,1,0)*2 + IIf([TLContinuing]=True,1,0)*4 + IIf([TLAdvanced]=True,1,0)*8

So the calculated field works great - Depending what training levels are selected or not selected, I get a range between 0 through 15.

Table B: I want a list of "friendly name" for the different training levels.

TLID (Number, No duplicates)
TLFriendlyText (Text)

So what I want to be able to do, is to have Query1, that will pull the training level text (TLFriendlyText) within the query based on the calculated field (TrLevelTxtID). The catch is, I can't seem to create a join between the two tables with the calculated field.

Ideally, I want the final query to pull, [NameFirst], [NameLast] from TableA and [TrLevelTxtID] (from TableB) based on the queries calculated field [TrLevelTxtID].

How best to achieve this with the Access 2003 limitations. Also, this query will only be used for reporting/mail merging so there are no update requirements or concerns.

View 4 Replies View Related

Queries :: Calculating Bonus - Expression Using Sum Of Another Calculated Field In Query

Mar 28, 2015

I've can do this on excel but don't know how in Access. I'm calculating bonuses. My table has salaries, and my query simply multiples each salary amount by a % to get the bonus amount. But I need to calculate adjustments to the bonuses using the sum total of the bonuses my query calculated:

Salary (from table) Bonus (calc'd by query) Adjustment (to be calced)

100,000 1,000 Sum of total bonuses/salary*4%
90,000 900

How do I capture the total of my calculated bonus column to use to calculate the adjustments in my right-most column?

View 13 Replies View Related

Queries :: Calculated Field To Find Latest Date For Each Record

Jul 11, 2015

If I have four date Fields in a query, Astart, Bstart, Cstart, and Dstart and want to have a calculated field to find the latest date for each record how would I do that? I have tried things like:

LatestDate: MAX(Astart, Bstart, Cstart, Dstart).

View 2 Replies View Related

Queries :: Calculated Field - Converting Percentage Marks With Grades

Mar 11, 2014

The calculated field converts all percentage marks perfectly fine with grades except 100 which returns to a stupid "E" . I've been trying all sorts and now give up.

Code:
Grade:IIf([Percentage]<20,"U",IIf([Percentage]<30,"G",IIf([Percentage]<40,"F",IIf([Percentage]<"50","E",IIf([Percentage]<60,"D",IIf([Percentage]<70,"C",IIf([Percentage]<80,"B",IIf([Percentage]<90,"A",IIf([Percentage]<100,"A+")))))))))

View 1 Replies View Related







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