Query Calculation Help Required Please

May 17, 2007

Hi

I am struggling with something im sure is quite simple.

I have two tables - the first has employee name and each record represents 1 telephone call answered. The second table also has the employee name but this table has a field with calls answered.

What I need to do is crosscheck the calls answered by employee in both tables. For example:

Table 1 has 3 records for John. (This means 3 calls answered).

Table 2 has 1 record for John with a calls answered field which is populated with the number 4. (This means 4 calls answered).

What I need is a report with both these sets of data and a new field with shows the the difference between the two.

Can someone please help me.

Many Thanks

View Replies


ADVERTISEMENT

Forms :: Fields Are Required To Be Filled Out Even Though Not Set As Required

Nov 3, 2014

I have made a form based on related tables. it requires me to fill out every field, which I don't want. I didn't make them required. Why does it do that?

View 3 Replies View Related

Help Required For Query

Sep 13, 2007

I need to see the records of a specific date very often. I have designed a simple query (Field : My date field; Criteria : Enter the Date) and it is giving info I want. However, sometimes it shows no records making me wonder where all the data had vanished. On checking up further, I discover that the date I specified was a Sunday, a holiday, when no data entry takes place. What I now want is that if the query returns no records, it should also give a message that "the date you specified could be a holiday. please check" so that I would not panic. Is it possible?
Alternatively, the query can look up the day of the specified date and return the message. I shall be grateful for help for any of these query designs.

View 2 Replies View Related

Query Question Or Maybe VB Required

Sep 12, 2005

Hi All,

I am relatively new to Access having created my first database a few months ago. I have hit a problem and I am not sure if I can solve this with queries or I need to move to Visual Basic.

I want to create a table containing the stock a product for each day for the next sixty days. I have the current stock in one table, a monthly forecast of sales in another and purchase orders in another.

I want to use the monthly forecast divided by how many days in that month and move forward day by day allowing for purchases coming in.

The tables (simplyfied) are :-

Stock Table.
eg.
Code Stock Qty Date
101 10 11/9/05
102 200 11/9/05


Forecast Table.
eg.
Code Month Fcast Qty
101 P09 30
102 P09 90

Purchase Order Table.
eg.
Code Due_Date PO Qty
101 13/9/05 100

The table that I would like to create would contain Code, Date and Stock.

eg.

Code Date Stock
101 12/09/05 9
101 13/09/05 108
101 14/09/05 107 ...........

102 12/9/05 197 ...........

Any ideas/direction would be welcome.

Carl

View 4 Replies View Related

Query Search Help Required

Dec 10, 2006

Hi,

I have my Query looking at multiple colums on a single table.

The table has approximately 2000 records.

I would like to create a Query that searches for criteria in one colum only. I have created a Query and providing i search for exactly what is written then it shows results, e.g. when i seach for Horse Furniture then i get many results but if i search for Horse i get no results?

I would like the search to be dynamic, so when the end user runs the query they are presented with a dialog box asking what they wish to search for (this i have no problems with) however i need to know how to get my query to be dynamic and provide results based upon single information.

is this possible and if so then how?

Thank you in advance for your help, i have been banging my head for a while on this one and am getting no where.

Bev:mad:

View 2 Replies View Related

Query Advice Required

Mar 28, 2007

Hi All

Im after some help with a query im building.

I have a table called QuizResults which has 10 Yes/No fields. The table also has a field called Site.

Each site will take a quiz and the problem I am having is with the statistics side of things.

What I need is a query that will work out the percentage correct for each site. All the data is stored in this 1 table. There are no relational fields as they are not required. I have tried a few different ways but each method I use involves me making many many queries to work this out.

To summarise I need 10 percentage correct fields, broken down by site.

Please can anyone help?
Many Thanks
Dazstarr

View 5 Replies View Related

Table / Query Help Required

Jan 29, 2008

I have a table set up for training with the following fields. Date, Employee Name, Class Name.

I have entered all the current data. I want the database to tell me who doesnt have a class completed.

I also would like a code to put in my query that will show recurrent date, although there is different recurrent dates dependent on the class.

Any help would be appreciated!

Thanks in advance.

View 4 Replies View Related

Query Problem - Greater Value Required

Jan 11, 2008

Hi everyone.

What I currently have is 2 columns in one table 1 is required and 1 isnt.

However what i need to do is work out the sum value of the greater amount from each line.

IE
A B
1 2
1 0
2 1

which would give a total of 5 as its adding B1+A2+A3

I tried creating a 3rd columb and entered this IF Formula but apparently access doesnt support If formulas

=If([claims]![Mileage1 IR]>[claims]![Mileage1],[claims]![Mileage1 IR],[claims]![Mileage1])

my plan was to get the "IF column to show me the higher amounts which could then be totalled up.

Any ideas where I may be going wrong?

Kev

View 12 Replies View Related

Query Design To Retrieve Required Input

Apr 27, 2007

I have been asked to create a database in Access 2000 that will hold 1.6 million postcodes. There will be four fields within the a table one holding the postcodes, and three fields holding information as to whether or not the post code is classed as good, neutral or bad. The requirements are to allow a user to run a query that asks thenm for the required postcode and then displays the relevant information (good,bad,neutral).

There requirement is that the search is done as fast as possible returning the required results. Has anybody got any ideas as to the best way of doing this.

View 2 Replies View Related

Referenced Crosstab Query Does Not Always Have Information Required

May 11, 2007

My database is built using Access 97.

I have the following table and 2 queries:

Table: “Materials Master Sheet”
field: “Material”
field: “Re-Order Level”
field: “Re-Order Quantity”

Query (crosstab): “Most Recent Count Numbers”
field: “Material Name” (joined to table’s “Material”)
field: “Count Quantity”

Query (crosstab): “Orders Pending Delivery”
field: “Material” (joined to table’s “Material”)
field: “Total Orders”


I am attempting to create a query called "Count vs ReOrder" with the following fields:

1. “Material Name” - comes from “Most Recent Count Numbers” query

2. “Order” - if the inventory count has reached the reorder level point, then 1, else 0; criteria: 1
expression: IIf([Most Recent Count Numbers]![Count Quantity]<=[Materials Master Sheet]![Re-Order Level],1,0)

3. "Suggested Order" - reorder quantity - count of any orders pending delivery; criteria: > 0
expression: [Materials Master Sheet]![Re-Order Quantity]-[Orders Pending Delivery]![Total Order]


This works great if all materials have orders pending delivery. However, if there are no orders pending (material not displayed in crosstab query), the material is not displayed (even though, in reality, the suggested order should be >0)

Currently does this:
material 1 - count = 1, re-order level = 2, re-order quantity = 8, orders pending = 4: shows material and suggests ordering 4 (correct)

material 2 - count = 2, re-order level = 2, re-order quantity = 4, orders pending = 4: material not shown (correct, as suggested order would be 0)

material 3 - count = 2, re-order level = 2, re-order quantity = 4, orders pending = 0: material not shown (incorrect! should be shows material and suggests ordering 4)

How can I go about addressing the missing “Orders Pending Delivery” numbers?

View 2 Replies View Related

Queries :: Getting Required Data Using Query For Calculations To Be Used Later?

Nov 7, 2014

I've been trying unsuccessfully for weeks to try and query a usable data set that would later be used for calculated values in a report.

I'm building a Hospital Acquired Infections Database for my facility to record data and process reports automatically. Previously, I built the system in Excel using VBA and userforms, but the size of the file has become too cumbersome, and now takes too long to process my reports.

So here's the issue.

UNIT_DATA TABLE:
UNIT
INF_MONTH
INF_YEAR
PT_DAYS
CVC_DAYS
IUC_DAYS
VENT_DAYS
APV_DAYS

PT_LEVEL TABLE (There are many fields in this table for recording purposes but I will only list the ones I need for reporting purposes):

UNIT
INF_STE_MAJ
SPECIF_SITE
INF_MONTH
INF_YEAR

So the important numbers I can't seem to collect are the total number infections per month from the PT_LEVEL Table for only select SPECIF_SITE infections.

the Tables are joined Left to Right as Follows:

UNIT_DATA.UNIT - PT_LEVEL.UNIT
UNIT_DATA.INF_MONTH - PT_LEVEL.INF_MONTH
UNIT_DATA.INF_YEAR - PT_LEVEL.INF_YEAR

The UNIT_DATA Table is a monthly collection for the 5 metrics (PT_DAYS, CVC_DAYS, IUC_DAYS, VENT_DAYS, APV_DAYS) regardless of whether or not the number for each is zero. Every unit in the database will have a record for each month and year.

The PT_LEVEL Table will only have records if and when a patient develops an infection. This means there is no cumulative monthly data for the PT_LEVEL Table Data based on SPECIF_SITE infection type.

I need to first do a roll up count for every month and year in the database, for which I was initially trying to use the UNIT_DATA table for since it contains every month and year. The problem is when I try to query the SPECIF_SITE from PT_LEVEL, I can get the number of Infections for months where infections where present for each unit and null values, for each month, and each year in UNIT_DATA, but when I include a where condition to narrow the view to only selected SPECIF_SITE's it only shows data for that SPECIF_SITE for months where they occured.

I need a cummulative monthly aggregation of the totals for a selection of SPECIF_SITE infections, for every unit, for every month, and every year, since July 2010.

View 14 Replies View Related

A Query That Returns Values That Total Less Than A Required Amount?

Dec 10, 2007

Hi

Can anyone suggest a method for doing what the title asks. I basically have a single table with several fields. One of the fields is the length of music tracks in seconds. What i want to do is to set criteria so that when a query is run the records to not add up to more than 900 seconds.
1stly) Is this "do-able" using queries or do i need to start implementing sql statements which i have limited experience of?
2ndly) Can anyone recommend a suitable method to do so provided it isn't very complex.

My knowledge of Access is Intermediate.

Kind regards

View 14 Replies View Related

General :: Update Query Has Error 424 Object Required

Jul 10, 2013

I am trying to create an update query. I am trying to update a field in a table with the current date as a request.

I have a table named tblTest and a field named Date2 that I am trying to update with the current date, the button that the VBA is applied to is in a form name frmTest. This is my code:

Private Sub Command39_Click()
Dim t1 As Date
t1 = Date
db.Execute("update tblTest set tblTest.Date2") = t1
End Sub

But when I press the button I get:
Run time error '424'
Object Required

It highlights the 4th of code....

View 8 Replies View Related

Query Calculation

May 16, 2005

Hello, I have the following criteria for my Query >=0.8*[Projects]![EstimatedHours]. This tells me when we've used 80% of our actual hours. How would I go about the criteria line if I wanted it to return above >= 50% hour use, but below 80%? Thanks

View 1 Replies View Related

Calculation In A Query

Sep 8, 2005

I have a query that is built up of 5 fields with only the 2 below being visible. I am wanting to be able to calculate the total price for each pole no i.e. Pole no 01-02 total price would be £1441.79, What is the best way to do this? Sample data is shown below


New Pole NoTotal Cost
01-02.....£437.08
01-02.....£476.37
01-02.....£36.21
01-02.....£70.27
01-02.....£51.56
01-02.....£370.30
01-1.....£437.08
01-1.....£476.37
01-1.....£36.21
01-10367.....£493.43
01-10367.....£70.15
01-10367.....£76.51
01-10367.....£102.67
01-10367.....£87.54
01-10367.....£36.21
01-10367.....£51.56
01-10367.....£39.54
01-11448.....£437.08
01-11448.....£463.25
01-11448.....£493.43
01-11448.....£476.37
01-11448.....£70.15
01-11448.....£76.51

View 4 Replies View Related

Query Calculation

Dec 6, 2005

In qryAddticket, I am trying to calculate two conditions (see Condition 1 & 2)However, I am having a problem on the first condition because it is adding 8 hours when it shouldn't. Can someone help to either fix my code or write a new one?

Condition 1: If the day_of_wk is 1, calculate time-reg_time/60, If the day_of_wk is 1 and time is less than 0, calculate time-reg_time/60+24

Condition 2: If the day_of_wk is 2, calculate time/60, If the day_of_wk is 2 and time is less than 0, calculate time/60+24

My code: pre: IIf([day_of_wk]="2",[time]/60,IIf([time]<0,([time]-[reg_time])/60+24,([time]-[reg_time])/60))

Thank you very much!

View 2 Replies View Related

Calculation Query

Feb 26, 2007

I need to calculate the total cost of the order and enter this into the "CostOfOrder" field which you can see in the picture...

http://i13.photobucket.com/albums/a298/steve01/untitled2.jpg

I need to read all the orderlines for an order into a record set and calculate the cost for each orderline. I need to use a loop in VBA to add them all together.

Here is the query for inclusion in VBA...

Dim StrSQL As String

StrSQL = "SELECT orderline.qty*sandwich.price"
StrSQL = StrSQL + "FROM sandwich INNER JOIN orderline ON
sandwich.sno=orderline.sno"
StrSQL = StrSQL + "WHERE (((orderline.orderno)="
StrSQL = StrSQL & orderno
StrSQL = StrSQL + "));"

Do Until recordSetData.EOF
Total = Total + recordSetData.Fields(0).Value
recordSetData.MoveNext
Loop

I think the Query is correct, but where do I put this in the form (or where do I actually put it as I don't know!) to make it work?

Thanks in advance!
Steve :)

View 1 Replies View Related

I Need Help With A Calculation Query

Oct 19, 2004

Im building a database for a car dealership. what do i need to do to take how many times someones name comes up in a certain field in a table and show it as a number. I need to include this calculation in a subfrom on a form which displays "employee info" which is already stored. Basically i just need to take how many times there name shows in the salesman field and show it as a number.

thanks

please respond, URGENT!

View 2 Replies View Related

Problem With Calculation In A Query

Oct 15, 2005

Hello

Can any body advise me.

I am running a simple sum

sum ([SUBTOTAL]/1000)

it does not give me the results I am after for example:

8.32 /1000 = 0.0832 - This is the desired result

I am getting

8.32/1000 = 0

It does note help if I change the decimal places.

What am I missing?

Thanks

View 7 Replies View Related

Query Calculation Expressions

Nov 28, 2005

Is there a way or what is the expression used if I want to do a running total in a query under a field called "Amount Donated". In addition, I would like to filter by selection using the DonorID as the primary key to show the total.

Thanks.

View 1 Replies View Related

Date Calculation In Query

Apr 20, 2007

YearsOfService: DateDiff("d",[Employee]![SIV Date],Date())/365

First I'm calculating the current Years of Service. SIV Date = Hired Date

Then I would like to calculate the years of service as of 12/31/"Current Year", where the Current Year would not require updating every year and 12/31 would be constant.

I'm just not sure how to specify the Month/Day and use Current Year not hard coded. I could set up a user defined date for that half of the calc but my users are not really bright and allowing them to simply click a button is my only option there.

Ultimately I will compare the 2 years of Service numbers to determine if there will be a change in the level of vacation accrual an individual receives during the year. The only part I have issue with is coding that date. Thanks for the help.

View 2 Replies View Related

Calculation In Query Not Working

Jun 29, 2007

I have a query that needs a calculation field for a report.
This is my field in the query.

Gross: [TBLinvamt]-[TBLinvqty]*67-[TBLfrtamt]-[TBLocamt]

Here lies the problem

example: 3210.81 - 25 * 67 - 0.00 - 0.00

If the frtamt and ocamt fields are 0.00 then my Gross field does not display any value

But if i put 1000.00 for frtamt and 500.00 for ocamt then the gross field returns a value.

Help is greatly appreciated

View 1 Replies View Related

Query Calculation Incorrect

Nov 23, 2007

Hi all

I have a query field doing a simple calculation: 874*(18,3/55,65*0,0592)
Access calculates this to: 17,0144948838454
Excel as well as my own calculator gets: 17,0144948787062
All table fields is defined as double.

If anyone can help me with an explanation as to why access doesn't seem to get this simple calculation right, I would be most thankful.

View 4 Replies View Related

Append Query Calculation

Dec 14, 2007

I have a query that ends with a final calculation, giving a typical answer of 0.033333333.
I have created an append query, which works but doesnt pull the result of the calculation through, just displays 0.

Any ideas?

View 1 Replies View Related

Query Calculation Problem

Mar 11, 2008

Hi to all,

In my report, there is a calculated column called Cost that display as $13.13. The orginal value is 13.125. This column is used to calculate another field called leftover.

The expected result is $16.00 - $13.13 = $2.87
The actual result is $16.00 - $13.125 = $2.88. So there is a increase of $0.01.

The results in my report is run using a query. In the query, I set the format of the calculated field, Cost as $#,##0.00;($#,##0.00). How can I ensure that whatever it calculate is what's displayed? Meaning to say if it display 13.13, use it instead of using 13.125 to calculate the leftover value.

Thx!

View 5 Replies View Related

Calculation Within Query Using Combo

Mar 28, 2008

hi, i have a query which retrieves data on products.

my problem is how to display a specific total i need.

there are three fields;
[itemprice],[itemlabour],[itemdiscount]

the item price and labour columns are in currency format, but the discount column allows the user to select a discount between 0 and 50%.

now in a form, when i want to calculate the total price to be paid i have a simple code like this:
[itemtotal] = ([itemprice]+[itemlabour])/[itemdiscount].column(1)
the idea being, when a discount is selected, there is a hidden column in the combo which contains a specific number to divide the total of [itemprice]+[itemlabour], thus giving a total.

now i tried to use this code within a query, but it doesnt work.

any ideas?

jared james

View 1 Replies View Related







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