Count Expression/ Calculation

Oct 30, 2007

I'm a little newbie here so please bare with me.

I'm doing a query for a call centre that is to calculate the number of calls for locations over 100k Square foot. However, I want to normalize the calculation by 100k SF. So the calculation should count the call centre work orders and then multiply it by 100k and divide it by the location Area.

I have no clue where to start, below is where I'm at. the new expression that i've added "wo" is where I want to count the total ([CallID]*100000)/[Area]. this isn't working.

http://www.audiolabmusic.com/Upload_Bin/Access%20Query.bmp

View Replies


ADVERTISEMENT

Data Calculation Expression - New User

Jun 20, 2006

I am just learning how to use Access and have created a basic form. I am stuck though on getting my form to calculate the Age (from Date of Birth) in Years and Months.

The info on this forum I have tried the following Expression:

=DateDiff("yyyy",[DoB],Date()) & " years and " & DateDiff("m",[DoB],Date())-(DateDiff("yyyy",[DoB],Date())+Int(Format(Date(),"mmdd") & lt;Format([DoB],"mmdd")))*12 & " months "

But my problem is that it tells me:

''The expression you entered contains invalid syntax
You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounung it in quotation marks''

Can anyone let me know what error I've made and how to fix it?
It would be much appreciated.
Thanks

View 1 Replies View Related

Data Calculation Expression - New User

Jun 20, 2006

I am just learning how to use Access and have created a basic form. I am stuck though on getting my form to calculate the Age (from Date of Birth) in Years and Months.

The info on this forum I have tried the following Expression:

=DateDiff("yyyy",[DoB],Date()) & " years and " & DateDiff("m",[DoB],Date())-(DateDiff("yyyy",[DoB],Date())+Int(Format(Date(),"mmdd") & lt;Format([DoB],"mmdd")))*12 & " months "

But my problem is that it tells me:

''The expression you entered contains invalid syntax
You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounung it in quotation marks''

Can anyone let me know what error I've made and how to fix it?
It would be much appreciated.
Thanks

View 3 Replies View Related

Convert Expression Calculation Into Data Table?

Jan 1, 2006

Hello all, I'm quite new to Access. I've read many Access tutorials and site but I have yet to found the solution for my problem. So here it is (pardon my english):

Field AmountTotal is
=(Nz([AmountSubTotal])+Nz([AmountSH])-Nz([Discounts]))

I manually input AmountSubTotal, AmountSH and Discounts. I expected an automatic calculation for AmountTotal.

In "Form view" I get the result of AmountTotal that I wanted. But the calculation result doesn't get recorded in my field data table AmountTotal. It just shows blank.

How do I record this automated results into my original table?

Thanks in advance!

View 4 Replies View Related

Date Difference Calculation In Expression Builder

Oct 8, 2015

I'm trying to add a calculated field which will be the time difference between two other date/time fields.

When I try to make a Calculated Field it does not recognize the DateDiff command.

Is there a preferred method to create a calculated field based on date/time differences? One would think this would be an easy thing to do.

View 1 Replies View Related

Tables :: Field With Calculation Expression - Round Function

Jul 28, 2014

I create e field with calculation expression and I need to round the results. I insert the following expression:

Round(IIf([A]="N";0;(IIf([B]>0;([B]-[C]-[D]-[E]-[F]+[G]);([H]-[C]-[D]-[E]-[F]+[G]))));2)

I have back the following error message: Could not find field 'Round'.

I do not understand why, because Round is not a field, but a function.

View 6 Replies View Related

Record Count For Calculation In Access

Dec 17, 2004

I need to be able to count the number of records in a report table in order to perform a calculation. For example record count/ total.
Thanks

View 1 Replies View Related

Forms :: Count In Expression

Sep 18, 2013

I want to put an expression that count all Debt in a field named "Payment". What would be an expression for this: =Count([Order].[Payment], "debt")?

View 1 Replies View Related

Calculation Across Fields - Show Total Count For Each Record

Nov 14, 2014

I need a solution for the following problem:

For example:

............F1 F2 F3
Rec1 ....4 ..2 ..5
Rec2 ....3 ..3 ..1
Rec3 ....1 ..2 ..1

Now, I want to know how many 1s are there in Rec3 the answer is obviously 2 but I want a field(F4, for e.g) that calculates F1+F2+F3 showing all their 1s 2s and 3s.

A solution I found was...... F4=IIf([F1],1,0)+IIf([F2],1,0)+IIf([F3],1,0)

But I have over 50 Fields that contain 1s and 2s and I can't count them all by using this formula as it will be tremendously long and access will reject it.

So I need another formula(or function) that will look up for the 1s across multiple fields and show the total count for each record in a new field.

Tried 'look up' function but achieved nothing ....

View 3 Replies View Related

Queries :: Expression Or SQL To Count Query Columns?

Jan 7, 2014

I'm looking for an expression or SQL for use in a query that will count the number of columns in another query. I do not need to count the records, I just need to know how many columns. I can't seem to locate a reference to a column count - everything points me to record count.

View 13 Replies View Related

Queries :: COUNT With Select Distinct On Expression

Dec 9, 2014

I have the following SQL which returns rows of distinct numbers that are calculated from a field.

SELECT Distinct (Left([ProjectID],4)) AS NumberOfCalls
FROM tblProject;

ProjectID looks something like this : 1307-IND-001 and NumberOfCalls looks like : 1307

I need to count the number of unique "NumberOfCalls" entries that there are in a list of about 50 rows. Currently the SQL returns a list of numbers like this:

1307
1311
1401
etc...

I just want NumberOfCalls to show "3". Is there an easy way to do this?

View 5 Replies View Related

Reports :: Expression To Count Values In A Range

Sep 3, 2013

I have been stuck trying to write an expression that will calculate the Yes values in a range of 16 Yes/No fields.

I have attached a screen print of the report I am working on.

View 5 Replies View Related

Queries :: Count Distinct Error - Missing Operator In Query Expression

Jan 23, 2015

I am trying to get a count of the unique customers in an access 2010 database

After some research it seems i should be using

SELECT Count(Distinct [Customer]) FROM [tblMain];

But when i use this i get

Syntax error (missing operator) in query expression 'Count(Distinct Customer)'.

I have tried leaving out the square brackets but this does not work....

View 3 Replies View Related

Writing An Expression In The Expression Builder

Jun 8, 2005

Good day all

Heres what im trying to do:

in one textfield i have =Count(*)

this returns all the records in my db.

now i want to split it up. i want to count all the records for each month.
my field name is datein_now but its just a normal textfield and not a date field.
example in field: 05/02/02 10:24:31 AM.
also got a datein textfield:05/02/02
i had
Count(*) where datein_now between 05/05/01 and 05/05/31

please help

lee

View 1 Replies View Related

Count Records Problem. Display Field Even When Count Is Zero.

Apr 13, 2006

I have a table tblBookings.

In this table it has a bookingID, CustomerID and some other none relevant details.

The CustomerID comes from table tblCustomer. i.e a customerID must exist in the customer table to be allowed in the bookings table tblBookings

A customer can exist in tblCustomer without existing in the booking table.

I am trying to write a query that will list each and every customer ID in the tblCustomer and count the number of bookings that that customer has (even if it is zero).

I have a query that will count the bookings if they exist in the booking table and display the number of times that a customer appears in the bookings table.

SELECT tblBookings.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings
FROM tblBookings
GROUP BY tblBookings.CustomerID;


How do I create a query that will do this but list all customers even if they don't exist in the bookings table (but obviously occur in the customers table)

I am trying to create a similar query where all bookings per hotel are listed even if no bookings are made for that hotel. I am guessing the answer is the same as above.

The Ritz. Bookings 0
The Hilton. Bookings 3
The Carlton. Bookings 0
The Lowry. Bookings 2

For every hotel.

That kind of thing.

If you need more information please shout.

View 3 Replies View Related

Queries :: Using Count And MIN Together To Retrieve Only MIN Record With Count

Aug 16, 2015

I have a table that has 5M+ accounting line entries. Below is an example of one accounting journal in the table.

BUSN_UNIT_IJRNL_DJRNL_ICNCY_CMONY_A
CB0014/07/20140002888269323AUD16797
CB0014/07/20140002888269323AUD-16797
CB0017/07/20140002888269323AUD16797
CB0017/07/20140002888269323AUD-16797

The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.

Right now this is what i get

BUSN_UNIT_I JRNL_I CNCY_C SumOfMONY_A CountOfJRNL_I MinOfJRNL_D
CB001 0002888269 AUD 0 4 4/07/2014

This is the output i would like

BUSN_UNIT_I JRNL_I CNCY_C SumofMONY_A CountofJRNL_I MinOfJRNL_D
CB0010002888269323 AUD16797 2 4/07/2014

Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?

current SQL
SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A
FROM [One Year Data Lines]
GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I
HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));

View 9 Replies View Related

Count Of A Count Or Sum Of A Count? Newbie Needs Some Help.

Sep 20, 2005

Hi,

Just spent the past hour in here trying to nut this one out, but not sure I've found something quite the same...though I know the answer will be painfully simple.

I have a customer table and a product table, and a query that groups customer first and last names along with a count of products per customer e.g. 1,1,3,2,3,4,2,1 indicates customer A buys qty 1 of product z, customer B buys qty 1 of product x, cust C buys qty 3 of product y and so on.

All I need to do now is do something to also output the total number of products. ie as per example above, 1+1+3+2+3+4+2+1 to get 17.

Can I do a count of the count or do I do some sort of sum of the count results?

I've tried everything I'm capable of as a newbie, and I'm not having any progress.

Any help appreciated.

View 2 Replies View Related

IF Then Calculation

Oct 11, 2005

Trying to calculate a few fields. 1st field is [delays] and 2nd field is [Planned]
The Plan scheduled for 60 and the Delays are an entered value. In the on update command button on the form I want code that will take the value above 60 and add then to the value in the [Delays] So if delays is 10 and the planned is 70 then planned will change to 60 and delays will = 20

any help

Jon

View 2 Replies View Related

Q:Calculation

Oct 24, 2005

Hi All.

I have 3 tables in Access:
tblCurrency (Fields: IDCurrency, CDate, CRate)
tblOrder (Fields: IDOrder, OName, ODate)
tblCalculation (Fields: IDCal, Rate1, Rate2, Rate3, COrder)
IDOrder is related to COrder with "One to Many" connection.

I have Form frmOrder, based on tblOrder. For Example Form has 1 record:
IDCal OName ODate
1 XXXX 2005.10.24

For this record related table has 2 records:
IDCal Rate1 Rate2 Rate3 COrder
1 10 3.5 15 1
2 6 4.2 12 1

tblCurrency for Exapmle have one record too:
IDCurrency CDate CRate
1 2005.10.24 0.2

In frmOrder I have textBox txtRate. Here I need to be calculated:

=Sum (Rate1*(CRate from tblCurrency Where tblCurrency.Cdate = tblOrder.Odate)+Rate2*Rate3 for Each record related to tblOrder).
By Exapmle: (10*0.2+3.5*15)+(6*0.2+4.2*12)=106.1

Please help with expression.
Thank You in advance.

View 3 Replies View Related

Need Help With Calculation

Jun 9, 2007

hey i hope some one can help me...

I am making a database for products and suppliers. Many suppliers can supply each product.

The product details are in a table called products, the suppliers details are stored in a table called suppliers and the suppliers offer for each product is stored in a table called prices.

the structure is as follows:

Table: Products
Product Name
Trade Price

Table: Suppliers
Supplier Name

Table: Prices
Product Name
Supplier Name
Supplier Offer

I have form that shows the product details and a subtable linked to the table:prices.

The problem

I need to add a field in the subtable called "Percent off trade" which is the trade price - supplier price / trade price * 100

i created a query showing the same records as the prices table plus the field "trade off price" and set the Field to:

Percent Off Trade: ([Forms]![Products]![Trade Price]-[Prices]![Supplier Price])/[Products]![Trade Price]*100

But this doesnt give me the correct percent :confused:

I hope i have explained this query and would really appreciate anyone who takes the time to suggest anything that could help

thanks in advance!

View 5 Replies View Related

Calculation

Nov 21, 2007

How can I multiplicate two numbers from two columns in a subform, show their result at the end of the row and then calculate sum of those results on the main form. This is something what I know how to do but the problem is following:

The total sum on the main form should be multiplicated wit 0,5 % and then reduced for a value of a specific row sum. It should be something like this: (Sum of rows sum * 0,5 %) - Sum of the specific row = Total sum on the main form. And all of this should be on a report too.

View 1 Replies View Related

What Calculation To Use?

Jan 2, 2006

What would I use for a formuls to find all product that are in stock.
like I have a price for all products that i stock, but some i don't have in stock. How would I query for the ones that I have in stock?
In my table I have it as UnitsOnHand, I have it as how many, like 1 or 3 or 4 etc. And also a field for Paper or Metal.
How would I query just for paper and not metal?
I am also going to need a query for the lowest price and the highest price.
Please understand I am New and I have allready search through all the post and can't seem to find anything close to what I am trying to do.
Any and all help would be great

Thank You
WackoWolf

View 10 Replies View Related

Need Help In Calculation

Jan 20, 2008

hi,


i'm working in a database and i face some problems in some calculation

this is my table which contain these fields:
NOC number
Company Name
Warning Letter


Note: each company has many NOC

Here what i need from the query

1- create a field which contain a total of warning for each month
2- create a field which contain a total of warning for each NOC
3- create a field which contain the total of warning for the company

View 3 Replies View Related

Help With A Calculation!

Feb 26, 2008

This is probably an easy calcualtion I am overthinking...
I am trying to make a query where I am calculating the "Total Value" of each customer's order of each item for the current week. The item "my company" is selling is fish, sizes small, medium and large all at different prices. I'm not exactly sure what they mean by "Total value", but I assume I take the cost times the quantity...but then what is the difference between Total Value and total ordered (in dollar amount)? Any suggestions?

View 2 Replies View Related

Calculation

May 25, 2005

I wonder if someone can point me in the right direction with a calculation problem. I have never done calculations in access before but want to make a balance sheet for a club account that I am designing. Very much in its trial stages, I have a form with three currency text boxes in it, set up as continuous. MoneyIn and MoneyOut are in the main form whilst Total is in the footer. All I want is to be able to put in a value in MoneyIn or MoneyOut and for the Total to update. Problem! I dont have a clue where to start! :rolleyes:

View 2 Replies View Related

Calculation Help

Jun 20, 2006

Here is a part of my database nicely zipped for you guys to help me with.

whats happening is when i click the totals button whatever the highest cost option that is selected with a "Y" should be displayed in the totals text box.

example:

Installation of heating system - "Y"
Replacement Kitchen and Bathroom - "Y"
Total should be "£90"

The code behind the button works fine with every choice but not the installation of the heating system. When this is selected "Y" the code seems to be ignoring it.

Any help will be much appreciated.

View 4 Replies View Related







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