How To Subtract Values In A Query

I have a table that has two fields; odometer reading and reading date. I want to able input the reading dates along with the odometer readings. Then I want my query to calculate the milage difference between each date.

Example:
DATE READING DIFFERENCE
9/1/04 35000
10/1/04 40000 5000
11/1/04 42000 2000

And so on.............

Thanks in Advance - John

View Replies


ADVERTISEMENT

Subtract Values From Dates - Version: 2003 (11.0)

I was hoping someone might be able to help me here. I have a table which has amongst this,two fields which are date values. I need to perform a function to subtract Date (A) from Date (B) an produce a further field showing the difference in the date values.

In a nutshell

Field (A) = 02/01/2006
Field (B) = 07/01/2006
Field (C) = 5

Many Thanks

ASHAY

View Replies View Related

Subtract Query?

If there is such an animal...

My employer uses a third party employee tracking system and I do back end work to massage the raw data for ad hoc reports using tables created by this third party software.

Is there a simple means to have a query compare Table A and Table B where Table A is a list of all jobs in a company - both filled and vacant - and Table B is a list of all employees in the company? The table containing jobs data has no field designating whether a job is filled or not. The only way to determine vacancies is to compare the two tables and select only those job numbers where there is no matching employee.

So, using examples from an earlier question - one that KeithG answered perfectly - let me see if I can demonstrate what I'm trying to do...

The Jobs table has two fields; JobNum and JobDesc.
The Employee table has three fields; EmpNum, EmpName, JobNum.

Here's Table A, Jobs;

100, Boss
105, Aide
200, Manager
201, Worker
202, Worker

Here's Table B, Employees;

111, Montgomery Burns, 100
112, Waylon Smithers, 105
121, Homer Simpson, 201
122, Lenny Leonard, 202

Jobnum is the link between the two tables in the query and the query's fields are;

JobNum and JobDesc

The result I need is;

200, Manager

The only vacant job.

So, how do I do a subtract query, if that's even a proper term? How do I have a result that is all of Table A that does not have a match in Table B?

Thanx!

View Replies View Related

Subtract 7 Hours From The Query

4/11/2007 1:01:28 AM
4/12/2007 2:02:52 AM
4/13/2007 4:21:30 AM
4/14/2007 2:22:21 AM
4/15/2007 3:34:53 AM
4/16/2007 4:02:37 AM
4/17/2007 5:03:15 AM
4/18/2007 7:17:34 AM

I need helps again
How can I subtract 7 hour from the query

Thanks

View Replies View Related

Query Aggregate Value Won't Subtract From Form Total

Here's a sticky one? (maybe...) I have a number of aggregate totals from several queries all linked to the Input Query and all Nz'd as 0's

From the image enclosed the totaling text box (top right left) is calculating the value in the properties box. The value should be 3.52 + 5 - 8 = 0.52 the value showing is -4.475 (which is 3.52 - 8) so I see it's ignoring the 5 value but I can't see why.

The ACDays and ALDays values are both generated by the same module, but with different Leave types. and as I mentioned previously all Nz 0'd

Any thoughts appreciated, I'm baffled:confused:

View Replies View Related

Add And Subtract

Hi

I want to make a button which when clicked will bring up a box which the user will enter a number which will then be subtracted from a number in a field.

I have tried an update query however not been able to getting it working.

Any ideas?

View Replies View Related

Subtract

Hi,
I have a query with two fields. One has the "MEASUREMENT" and the second one is the month containing the values. In the MEASUREMENTs I have gross revenues and discounts, how can I create a new MEASUREMENT (net revenue) that is calculated by subtracting the discounts from the gross revenue.

Regards
Giannis

View Replies View Related

Subtract One Table From Another?

Hi, I hope someone can help me out with this problem:

I have 2 tables with identical stucture, and no unique ID number.
Both had identical data, and the databse was then copied onto 2 laptops. Now further data was added onto each.

I want to now merge the three databses (ie original one, and 2 laptops) into 1, without creating duplicates- so I need to identify which are the new fields added to the databases. I figured that by somehow subtracting the original table from the new ones, i would be left with the new data, but dont know how to do this. Or is their another way?

View Replies View Related

Subtract Previous Value From Next Value

:confused: Need help building a query that will subtract the previous value from the next value at each change in date

Date Tier 1
1/6/2006 4.22
1/13/2006 4.27
1/20/2006 4.27
1/27/2006 4.314
2/3/2006 4.314

for example, 4.22-4.27 = -0.05

View Replies View Related

Different Add/subtract Value Each Month

I have a report based on a query where I get results sorted by month after i have chosen 'from date' and 'to date' in the beginning. I also want a set number for each month to be subtracted from the total sum, and then added to the next month (even if it is a negative number). Ex. Total in january is 100, then it is supposed to subtract 50 from that number, report the result of 50 in one own field at the end of january, then send this to february where the total sum is ex. 200, then the total sum for february is supposed to be 250, and then again a different number, like 60 is automaticly subtracted from the total sum and displayed in one field as the result 190. And so on.. Does anyone know if this is possible, and how? I am a noob, both in access and in the english language :P

View Replies View Related

IIF Number Exists, Then Subtract

(hard being a rookie)

On my Form, I want to have a query look at one specific field for a number (1). If the number is found then I would like to have the query do basic subtraction on two other fields. (one from each table)

What I have so far is one unbound text box with the following,
=IIf(tblDaStrategy!DaNumber=1,(tblDaStrategy!DaTar get-tblDaReport!DaActual1))

Simply put, if one field has the number one in it, then take the other two fields and subtract (ex. 100-75) to display 25.

It's not working. I keep getting the #Name?

Update: I think I got it. I went back to square one and made a master query that contains the data from both tables. Then I bound one of the forms to the query. I updated my IIF statement and poof. It seems to be working. Now, where's the Asprin and my pillow?

Update 2: Bummer, now my magical 25 is in place but now my form is locked tight. Guess I need to consider abandoning this concept and simply producing a report with the 25 on it. Unless someone out there has a better idea.

View Replies View Related

Subtract Day From User Input

Hi,
Working on a query, what I have is a user will select a date on a form and hit submit.

I need the query that it runs, to pull all data -1 day from what they select.

So if they select 10/31 on the form, it needs to pull everything for 10/30 only.

I have this in my query now,
[Forms]![frm_change_report]![txt_Date]

Which pulls the data for the date they selected, but not sure how to subtract a day.

View Replies View Related

Subtract TIME From DATETIME

I'm sure this is simple but my brain is mushy this morning!

I have two datetime fields ADMISSION and DISCHARGE with values such as 03/02/2008 15:00. How do I convert these to dates only? I don't want to just *format* them as dates, which I can do. In this example I would want to convert that date to 03/02/2008 (or 03/02/2008 00:00)

It's an idiosyncrasy of the way the NHS calculates hospital length of stay. Our previous IT system had dates and times in separate fields so I could just ignore the time field and work out length of stay based on dates only. However, our new system returns a combined datetime.

Patient A 03/02/2008 00:15 to 03/02/2008 23:45 = 0 days
but
Patient B 03/02/2008 23:45 to 04/02/2008 00:15 = 1 day

Basically, you count a day if there's a date change, even if that was only half an hour (or even a few minutes) either side of midnight, but you DON'T count a day if there was no date change, even if that was almost 24 hours.

So formatting them as dd/mm/yyyy wont work ,as I will still have partial days when I subtract one from the other and INT([DISCHARGE]-[ADMISSION]) wont always work either as it would calculate 0 for patient B.

Help!

View Replies View Related

Subtract From Value In A Field - Version: 2000 (9.0)

Hi everyone, hope you are all well.....

OK, I have a DB that is storing tickets data. Basically I want to, in code, subtract from the no_of_tickets field each time someone books a ticket so that when it gets to zero, no more tickets can be booked (i'm ok with the checking the field to see what the value is).

Problem is, never done this before so I don't know what the proper phrase for it is so when I'm trying to search on here and google its not much use. Can someone please help me with what it is i'm trying to do so I can search and hopefully find a solution?

Cheers m'dears in advance....

Linds

View Replies View Related

I'm Very New To Access & Need To Figure Out How To Subtract One Field Form Another Wi

Hi,
I am very new to access. I have designed a db for my employer, but I can't figure out how to subtract one field from another without having it subtract fom all others.
My employer is a counselor and wants to know how many units are left per client. There are several diffent services available per client. (88K, 86H, 81H, ect) For instance I tried to use a query, but when it was subtracting
(number of units)-(number of used) It subtracted from all the different service types.
Then I tried to do it on the form and all I get is #Name?
I can email you a copy of the database with a mock client if i'm not explaing myself enough. I know it's probably something very simple and I would appreciate any help. Also, if you know of any good books or websites where I can learn more I'd appreciate the info. I'm learning from Access 2002 Bible.

View Replies View Related

Add Or Subtract A Number Of Business Days To A Date

Comments

This procedure relies on the existence of a "Holidays" table. See the methods below for the HolidayTableFill() procedure for details on creating, filling, and using this table.



Returns

original date plus the number of business added or subtracted





Example

Sub AddBusinessDays()

' ==============================================

' Example code for AddBusinessDays()

' ----------------------------------------------

' Adds or subtracts business days to a given

' date. Weekends and holidays are not counted.

'

' Relies on a Holidays table listing the

' non-weekend holiday dates. To create the

' Holidays table, see the HolidayTableFill

' procedure.

' ==============================================



' Assuming the Holidays table exists and contains

' Dec. 25, 1997 (Christmas) and Jan. 1, 1998

' (New Year's) this routine calculates the 7

' business days from Dec. 23.



Dim datNewDate As Date



datNewDate = AddBusinessDays(#12/23/97#, 7, "Holidays", "Date")



MsgBox "7 business days after 12/23/97 is " & datNewDate



End Sub







Function AddBusinessDays(datDateIn As Date, intDays As Integer, strHolidayTbl As String, strHolidayField As String) As Date

' Comments : Add or subtract a number of business days to a date

' Weekend dates (Saturday and Sunday) are not counted in adding/subtracting days

' Parameters: datDateIn - starting date

' intDays - number of days to add (negative to subtract)

' strHolidayTbl - name of holiday table

' strHolidayField - field name of holiday dates in the holiday table

' Returns : original date plus the number of business added

'

Const dummyDate = #1/1/100#

Dim db As Database

Dim rstHoliday As Recordset

Dim datNextHoliday As Date

Dim intCounter As Integer

Dim intDirection As Integer

Dim datNewDate As Date

Dim strCriteria As String



Set db = CurrentDb()

Set rstHoliday = db.OpenRecordset(strHolidayTbl)

rstHoliday.Index = "PrimaryKey"



datNewDate = CDate(datDateIn)

If intDays > 0 Then

intDirection = 1

strCriteria = ">="

Else

intDirection = -1

strCriteria = "<="

End If

rstHoliday.Seek strCriteria, datNewDate

If Not rstHoliday.NoMatch Then

datNextHoliday = rstHoliday(strHolidayField)

Else

datNextHoliday = dummyDate

End If



For intCounter = 1 To Abs(intDays)

datNewDate = datNewDate + 1 * intDirection



' Skip weekends

If intDirection > 0 Then ' Increment date

If WeekDay(datNewDate) = 7 Then datNewDate = datNewDate + 2

Else ' Decrement date

If WeekDay(datNewDate) = 1 Then datNewDate = datNewDate - 2

End If



' Skip holidays

While datNewDate = datNextHoliday

datNewDate = datNewDate + 1 * intDirection



' Skip weekends

If intDirection > 0 Then ' Increment date

If WeekDay(datNewDate) = 7 Then datNewDate = datNewDate + 2

Else ' Decrement date

If WeekDay(datNewDate) = 1 Then datNewDate = datNewDate - 2

End If



' Get next holiday

If datNextHoliday <> dummyDate Then

If intDays > 0 Then

rstHoliday.MoveNext

Else

rstHoliday.MovePrevious

End If

If rstHoliday.EOF Or rstHoliday.BOF Then

datNextHoliday = dummyDate

Else

datNextHoliday = rstHoliday(strHolidayField)

End If

End If

Wend

Next



rstHoliday.Close

db.Close



AddBusinessDays = datNewDate



End Function



Sub HolidayTable()

' ==============================================

' Example code for HolidayTableFill()

' ----------------------------------------------

' Creates a table called 'tblHolidays' with the

' the following structure:

' Date Date/Time

' Description Text 30

' and fills it with holidays for the years

' 1996 to 2006.

' ==============================================

Dim strSQL As String



' First create the table

If Not TableExists("", "tblHolidays") Then



' create a SQL Make Table query

strSQL = "CREATE TABLE tblHolidays ([Date] Date, [Description] Text (30), CONSTRAINT [PrimaryKey] PRIMARY KEY ([Date]));"

Else

' Table already exists, so empty it

strSQL = "DELETE * FROM [tblHolidays];"

End If



' Execute the query

CurrentDb.Execute strSQL



' Now fill the table

HolidayTableFill "tblHolidays", "Date", "Description", 1996, 2006



End Sub





Sub HolidayTableFill(strHolidayTbl As String, strHolidayField As String, strDescField As String, intFirstYear As Integer, intLastYear As Integer)

' Comments : Add holiday dates to a holiday table

' Assumes table already exists and does not include the dates being added.

' Parameters: strHolidayTbl - name of holiday table

' strHolidayField - field name of holiday dates in the holiday table

' strDescField - field name of description field (optional)

' intFirstYear - first year

' intLastYear - last year

'

Dim db As Database

Dim rstHoliday As Recordset

Dim intYear As Integer

Dim datHoliday As Date



Set db = CurrentDb()

Set rstHoliday = db.OpenRecordset(strHolidayTbl)



For intYear = intFirstYear To intLastYear



' New Year's Day (January 1)

datHoliday = HolidayDate(CDate("1/1/" & intYear))

Call HolidayTblWrite(datHoliday, rstHoliday, strHolidayField, strDescField, "New Year's Day")



' Martin Luther King, Jr. Day (3rd Monday in January)

datHoliday = NthDayOfMonth(1, intYear, 3, 2)

Call HolidayTblWrite(datHoliday, rstHoliday, strHolidayField, strDescField, "MLK Day")



' President's Day (3rd Monday in February)

datHoliday = NthDayOfMonth(2, intYear, 3, 2)

Call HolidayTblWrite(datHoliday, rstHoliday, strHolidayField, strDescField, "President's Day")



' Memorial Day (last Monday in May)

datHoliday = LastDayOfWeekInMonth(5, intYear, 2)

Call HolidayTblWrite(datHoliday, rstHoliday, strHolidayField, strDescField, "Memorial Day")



' Independence Day (July 4)

datHoliday = HolidayDate(CDate("7/4/" & intYear))

Call HolidayTblWrite(datHoliday, rstHoliday, strHolidayField, strDescField, "Independence Day")



' Labor Day (1st Monday in September)

datHoliday = NthDayOfMonth(9, intYear, 1, 2)

Call HolidayTblWrite(datHoliday, rstHoliday, strHolidayField, strDescField, "Labor Day")



' Columbus Day (2nd Monday in October)

datHoliday = NthDayOfMonth(10, intYear, 2, 2)

Call HolidayTblWrite(datHoliday, rstHoliday, strHolidayField, strDescField, "Columbus Day")



' Veteran's Day (November 11)

datHoliday = HolidayDate(CDate("11/11/" & intYear))

Call HolidayTblWrite(datHoliday, rstHoliday, strHolidayField, strDescField, "Veteran's Day")



' Thanksgiving (4th Thursday in November)

datHoliday = NthDayOfMonth(11, intYear, 4, 5)

Call HolidayTblWrite(datHoliday, rstHoliday, strHolidayField, strDescField, "Thanksgiving")



' Day after Thanksgiving

datHoliday = datHoliday + 1

Call HolidayTblWrite(datHoliday, rstHoliday, strHolidayField, strDescField, "Day After Thanksgiving")



' Christmas (December 25)

datHoliday = HolidayDate(CDate("12/25/" & intYear))

Call HolidayTblWrite(datHoliday, rstHoliday, strHolidayField, strDescField, "Christmas")



Next intYear



rstHoliday.Close

db.Close



End Sub



Function HolidayDate(datDay As Date) As Date

' Comments : For fixed date holidays (New Year's, 4th of July, Christmas),

' returns the celebrated date based on Federal guidelines

' Saturday dates are shifted to Friday.

' Sunday dates are shifted to Monday.

' Parameters: datDay - date to calculate

' Returns : Adjusted holiday date

'

Dim datHoliday As Date



datHoliday = CDate(datDay)

Select Case WeekDay(datDay)

Case 1: datHoliday = datHoliday + 1 ' Shift Sunday to Monday

Case 7: datHoliday = datHoliday - 1 ' Shift Saturday to Friday

End Select



HolidayDate = datHoliday



End Function



Sub HolidayTblWrite(datHoliday As Date, rsHoliday As Recordset, strHolidayField As String, strDescField As String, strDescription As String)

' Comments : Add a record to the holiday table

' Used in an example of filling the holiday table

' Parameters: datHoliday - holiday date to add

' rsHoliday - recordset pointing to the holiday table

' strHolidayField - field name to put the holiday date

' strDescField - field name to enter description (optional)

' NOTE: Does not check to see if the date is already in the table.

'

rsHoliday.AddNew

rsHoliday(strHolidayField) = datHoliday

If strDescField <> "" Then

rsHoliday(strDescField) = strDescription

End If

rsHoliday.Update



End Sub



Function LastDayOfWeekInMonth(intMonth As Integer, intYear As Integer, intDay As Integer) As Date

' Comments : Calculate the last day (Sunday, Monday, etc.) of the month

' Parameters: intMonth - month to check

' intYear - year to check

' intDay - the day of the week to calculate (1 for Sunday, 2 for Monday, etc.)

' Returns : Last day of month

'

Dim datDate As Date

Dim intOccurrence As Integer



intOccurrence = 5

Do While IsNull(NthDayOfMonth(intMonth, intYear, intOccurrence, intDay))

intOccurrence = intOccurrence - 1

Loop



datDate = NthDayOfMonth(intMonth, intYear, intOccurrence, intDay)



LastDayOfWeekInMonth = datDate



End Function



Function NthDayOfMonth(intMonth As Integer, intYear As Integer, intOccurrence As Integer, intDay As Integer) As Variant

' Comments : Returns the date of the Nth day (Monday, Tuesday, etc.) of the month

' Parameters: intMonth - month to check

' intYear - year to check

' intOccurrence - the occurrence number of the day to calculate

' (1 for first, 2 for second, etc.)

' intDay - the day of the week to calculate (1 for Sunday, 2 for Monday, etc.)

' Returns : Nth day of month (null if date does not exist)

'

Dim varTempDate As Variant

Dim intCurrDay As Integer



varTempDate = DateSerial(intYear, intMonth, 1)



If (intDay > 0) And (intDay < 8) And (intOccurrence > 0) Then

' Calculate first intDay of the month.

intCurrDay = WeekDay(varTempDate)

If intCurrDay <> intDay Then

If intCurrDay < intDay Then

varTempDate = varTempDate + (intDay - intCurrDay)

Else

varTempDate = varTempDate + (7 + intDay - intCurrDay)

End If

End If

If intOccurrence > 1 Then

varTempDate = varTempDate + 7 * (intOccurrence - 1)

If Month(varTempDate) <> intMonth Then ' Date goes past month

varTempDate = Null

End If

End If

Else

varTempDate = Null

End If



NthDayOfMonth = varTempDate



End Function

View Replies View Related

Subtract 3 Days From A Date - Version: 2000 (9.0)

Hello I have a form with a control named CourseDate. I also have a text box that will show the user the cancellation date based on the what the course date is. I am looking to have this populate in the on current event of the form, but I am haveing difficult getting it to work. The Date in the text box should be 3 Working days less the course date. Any help would be greatly appreciated.

thanks

Tim

View Replies View Related

Subtract A Value From Inventory Quantity When A Qty Is Added To - Version: 2003 (11.0)

I have a form named "Inventory" which i input inventory items into the "inventory" table. the field name is Quantity that i put the quantity of the inventory item in. There is also a SKU number for the item.
I also have a form "Sales Receipts" with a subform "Receipt Line Items" in which i add products that get sold. it has a SKU number field and Quantity field also for amount of selected item sold (For example qty is 3 for the item). What i want is when the qty in the receipt line item is added i want the inventory item to automatically decrease by this amount(which would be 3 in this example), i want to be able to add more quantity of stock to the inventory item and it only decrease when sales are made. Is this possible?? Thanks for anyone willing to help me on this.

View Replies View Related

Subtract Two Amounts From Two Fields From Two Tables - Version: Any Version

I have two tables Table1 and Table2

Both tables have two fields ID1 / ID1Sum and ID2 / ID2Sum

both ID1 and ID2 are unique fields (with the same names) .

I want to compare the ID1s and subtract the Table1/ID1Sum from Table2/ID2Sum when ID1=ID2

is this possible to make a table or query with this?


thanks,
alex

View Replies View Related

Set Values In A Query

I have a DB where after I close out a month of sales I no longer want to be able to view records in my SalesEntryForm. I have a little check box that is the ClosedOut Check box. When I close out Febuary, I want to change all of the ClosedOut Check boxs to yes if the invoice was made in February. I have the query to get all of the records I want to close. How can I automate changing the values of these check boxes

Your help is appreciated,
JOe K.

View Replies View Related

0 Values Ina Crosstab Query

Hi, I have a crosstab query, but for any product that has no entries on a certain date a NULL value is put in its place. Is there a way to have a 0 display there? Example of NULL:


DatePRODAPRODBPRODCPRODDPRODE
08/01/200513633
08/02/2005112643
Aug Total:216276



See how there is a NULL for 8/1 under PRODB? I would like that to be a 0

View Replies View Related

Minimum Values In A Query

Hi

I have a table (tbl1) with three fields/columns (Primary key, NameID, Number). E.g.:

NameID ... Number
Mike ........ 2
Mike ........ 3
Mike ........ 19
Ronald ...... 1
Ronald ...... 2
Greg ........ 7
...

I would like to make a query that lists each NameID only ONCE, with corresponding MINIMUN Number. So the result from the upper example would be:

NameID ... Number
Mike ......... 2
Ronald ....... 1
Greg ........ 7


How do I do it???


Thank you in advance

m.

View Replies View Related

Decile Values Query

I need to write a query that will let me determine the values to use as input for another query which will calculate results based on deciles:

eg I have a series of data ranging from 0 to 100 and I need to sort by this field and then pull out the value of the 10th, 20th, 30, ....90th and 100th records.

is there any way I can do this? also what if I want to pull out every 23rd record for example?

thanks

edited to add:

The way I would do this manually in excel is to sort by the target value column and then fill down the numbers 1-23 in a new column and then sort by that column, copy the results and load them into access so I can run a query based on them. This is a little time consuming and hardly elegant but gets the job done. Just hoping someone can help me with a niftier way.

View Replies View Related

Adding Values In A Query

can someone help with this. I have 2 fields in a query

tbl1_nbil = 3
tbl2_nbil = 0

however when I add them using

Bill = [tbl1_nbil] + [tbl2_nbil], I only get a few of the added numbers in the calculated field. In some records it works, but in the majority it does not

It onl seems to add when both fields are greater than zero

tbl1_nbil : tbl2_nbil : Bill
1 : 1 : 2
1 : 0 : 0
2 : 1 : 3
0 : 2 : 0
4 : 0 : 0

View Replies View Related

3 Table Values In One Query

Hi
I have looked at the forum to see if I can find my answer, but it doesn't seem to be there.

I have 3 tbl SchoolDetails, School Activities and Keystages

The values from each table SchoolDetails ID & School Name
SchoolActivities: Activity Title, Date, Start Time, No of Workshops
Keystages: Keystage and NoOfPupils

I am trying to send one confirmation to each school. My problem is that when we book out the time it is booked out either by HalfAM or HalfPM, therefore if a school books a full day they get 2 records in the SchoolActivities tbl.

They also get numerous records in the Keystage tbl depending on how many classes are involved.

I am trying to bring together the sum of NoOfPupils - even though this can have two records (AM & PM) and also the numbers can be in numerous records in Keystage.

I just need to know how best to do this.

Any help would be appreciated.

View Replies View Related

Query On A Field That Has Several Values

I am using a form where I select values from combo boxes to enter parameters for a select query. It works fine where the table queried only has one value in the field concerned. However, it does not work on the fields that have several values (entered through a multi-selection box).
Are there any ways to query fields with a multi-selection?
Thanks, Niels

View Replies View Related

Null Values In Query

I need to create a query with the MTD field
I have created the MTD and it works!!! but now I need it to show all of the PartNum fields even if null. do I need to convert all nulls to zeros? if so how do I go about this?

Here are the fields in the query

First Field:
Field: PartNum
Table: Part
Total: Group By

Second Field:
Field: UnitPrice
Table: OrderDetail
Total: Group By

Third Field:
Field: NumOrdered
Table: OrderDetail
Total: Sum

Fourth Field:
Field: MTD: Sum([NumOrdered]*[UnitPrice])
Table: (blank)
Total: Expression

Fifth Field:
Field: CurrentYear: Year([OrderDate])
Table: (blank)
Total: Group By
Criteria: Year(Date())

Sixth Field:
Field: CurrentMonth: Month([OrderDate])
Table: (blank)
Total: Group By
Criteria: Month(Date())

When I run the query I only get 2 parts and their total. but I want all parts to show up even if they have not been ordered in the month. the parts that have not been ordered should say $0.00 in the MTD field.

Any Suggestions???

View Replies View Related

Returning All Values In A Query

Hi.
Can someone help me?
I am trying to make a query return all values (please see attached jpg)
It returns some of the values, but is not returning values that have nothing in the "material" field (Null Values?)
In this example, I would like it to return all values that fulfill the Completion_Date, CW_Department, Handed_Over and Chargable fields, whether they have any data in the Material field or not.

Do I need to use the NZ, IIF or IsNull function? I have seen this is previous threads, but nothing like this example.....I apologise if there is.

Many Thanks.

Frank.

View Replies View Related

Query And Merging Values

SELECT IIf((Count([PO]))>1,1,Count([PO])) AS PO_Num, IIf((Count([PO]))>1,'Multiple',[Category2]) AS Cat
FROM [Final]
GROUP BY [Final].Category2, [Final].[PO];

k so stage 1 seems to work, all the PO's that are multiples for the one category have been grouped and their value set to 1... but there are PO's with the same number pointing to another category.

I need to "simply" merge those PO's to be just one line_item.

1001 cat1
1001 cat1
1001 cat1
1001 cat2
1001 cat2
1001 cat2
1002 cat3
1002 cat3


should just combine down to
1 multiple
1 cat3


It would make more sense on a bigger scale but hopefully i'm clear here. But the ones that are not multiple do need to show the category name.

Can anyone spot the issue here?

View Replies View Related

Approximate Values Query

hello,
I am just wondering whether there is any way I could define a query in such a way to include a record in Table A if field FA is approximately equal to field FB in Table B by no more than, say 12.

Thanks!

Regards,
Anyi

View Replies View Related

Delete Query - No Values

Hi,

I have a condition < 25 in a delete query.

When it is executed, it is not deleting when there is no value in the field.

How can I delete when there is no value?

Thanks in advance.

Cheers

Bud

View Replies View Related

Subtracting Query Values

How do i subtract one value, worked out using a query, from another value, worked out using a query, and display it in a text box?

View Replies View Related

Values Comparison Query

I really have looked everywhere

I have 2 fields and I want a query to pull out the records with a lower or equal to quantity value than the stock.

ReorderValue
3
5

Quantity
2
6

As you can see it shuold pick out the top record because its lower, I just need a way of telling it to look at each records Reorder Value

View Replies View Related







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