Access SQL Order By Putting Null Values Last In Query?

Dec 19, 2012

NOTE: Im working in MS Access 2003, only results in this SQL/database (not mysyl, MSSQL etc)

I want to be able to ORDER an SQL query and put the NULL values last.

I have a basic databse:

Table name: PeopleTable
Field names: TableID, PersonField, PersonID

The aim is to order by PersonID and put the NULL values last

See pic1.jpg;

The picture (pic1.jpg) shows the database as it is without a query.

See pic2.jpg;

Picture 2 (pic2.jpg) shows the query results when I use the orderby statement (SELECT * FROM PeopleTable ORDER BY PersonID As you can see it shows the results ordered by the PersonID however the NULL values are first.

See pic3.jpg

Desired results, it is ordered by the PeronID and the NULL values are last (NOTE this is an editied screenshot).How can i achieve this is MS Access 2003?

View Replies


ADVERTISEMENT

Sort Order With Null Values

Aug 22, 2012

I have a Form which presents the results from a query. Upon clicking the header of each column of data the records are sorted A-Z and Z-A using the code below. This works fine when all records have data in the relevant field but not when several records have a value of null. It appears that when null values are present the "If Me.OrderBy = "property" & " DESC"" always evaluates as not true, even though all the null values are on top and all the filled in values are in appropriate order.

A second issue is one of my fields is not sorting properly at all. I have two date fields, one sorts the other does not. The one that sorts is a user entered date (8/22/2012) which defaults to "9/9/9999" if the user does not enter a different value. The other date field which does not sort properly at all is a calculated date field which chooses the earlier of two possible dates (both associated with the same record) using

IIf([Senior Mortgage Maturity]>[Mezz Maturity],[Mezz Maturity],[Senior Mortgage Maturity])

This code is repeated for each of the six fields with the relevant field name in place of 'property'.

Private Sub Property_Label_DblClick(Cancel As Integer)
Me.OrderByOn = True
If Me.OrderBy Like "Property" Then
Me.OrderBy = "property" & " DESC"
Else
Me.OrderBy = "property"
End If
End Sub

View 14 Replies View Related

Queries :: Access 2007 Query With Null Values

Apr 17, 2013

I am currently creating an Access 2007 database for calculating salesperson commissions. I have a table with 5 fields I'm working with: SalesRep, SOWRep, TerritoryRep, Period and Commission. I am trying to build a query that will calculate commission for a salesperson for each record where their name appears in one of the first three fields. Each time their name appears, they get the commission listed in the Commission field for the stated Period. I have managed to do this part successfully. My problem now is that I am creating a query that will sum all of their commissions by Period. I have run into a situation where sometimes a salesperson will not appear in one of the rep fields, resulting in NULL values in the previous queries. How can I create a final query that will sum correctly even if there are NULL values returned on the previous queries?

Here is what I have so far:

November: Avg((SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin1] WHERE [Period] Like "November*")+(SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin2] WHERE [Period] Like "November*")+(SELECT [SumOf1/3GM$s] FROM [BaxterActualMargin3] WHERE [Period] Like "November*"))

View 14 Replies View Related

Putting Row Values Into Columns

Nov 17, 2006

Hi,

I have been trying to figure this out for a long time. Any tips/helps is much appreciated it.

I have a table with 3 columns(SSN,DeductionType,DeductionAmount). Let say I have 100 employees. There are 3 records per employee, because each have 3 different deductionType(TypeA,TypeB,TypeC).

I want to create a queryTable that will result with 1 record per individual, with TypeA,TypeB,and TypeC as column names and the DeductionAmount as their values. Make sense? Below is an example of what the new table should look like:

SSN--------------TypeA-------TypeB-------TypeC
022-58-5898------ $10.35-----$25------------$14
036-89-5487-------$5----------$45.69---------$47.33
...
..
.

Thank you VERY much!

By the way, if QueryTable is not the method to do this, please let me know how.
Joe

View 4 Replies View Related

Access Slow To Sum Or Join On Null Values?

Oct 5, 2006

Is Access slower at summing null records than SQL server?
I have a query which takes less than 1second in SQL server but takes about 5-10 in Access but can't think why there is such a lag in processing speeds.

View 7 Replies View Related

Adding Values In A Form Then Putting It In A Table HELP

Sep 1, 2005

Hey there, Im currently making a form so users can enter project information. For this particular project, the user must choose from a variety of options, and each option has a number value assigned to it for a rating. Now at the end of this form, I want a sum of the ratings, and then entered into the table. I am using a text box for the sum of ratings, and can get the sum of ratings to work on the form, but this data is entered as a 0 in the table. If anyone could help me find a way to put the actual sum in the table, that would be excellent.. thx a lot.

If it would be any help, here are the names of text boxes that I'm adding:

Health and Safety Rating, Maintenance Rating, Equipment Rating, School Size Rating, Student Enrollment Rating, SD Priority Rating, Project Requested Previously Rating

Those ratings must be added into "Total Rating"

Thankyou!

View 1 Replies View Related

Modules & VBA :: Putting Values Into Temporary Table?

Jun 28, 2013

I'm trying to put values into a temporary table for the user to view the work behind a returned calculated value.

Here is my code:

Code:

Sub SearchPartNumber_Entered()
Dim txtPartNumber As Variant
Dim rst As Recordset
Dim rstt As Recordset
Dim u As Variant

[code].....

How do I put all values under all 'u' 's into one table?

View 13 Replies View Related

Null Values In Query

Dec 4, 2005

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

Query On Tables With Null Values

Nov 9, 2005

Hi,
I have a problem with one of my query, the query has 2 tables, the secondary field from Table2 is linked to the primary field of table1.
The primary field Doesn't allow null Value
The Secondary Field does allow null value which means that the Data in table 1 only concern Some of Table2's Data.
If I run the query with the two tables I can only see the records with the secondary field with no Null Value.
Is it possible to get the query Showing All the records of table1 2 and Table1 even if Table1 has no values?

View 1 Replies View Related

Append Query & Null Values

Sep 7, 2007

Have the following scenario with perhaps inbuilt fatal flaw

Building an Inventory Management db and in the process of constructing the tools to move some inventory from 1 location in the warehouse to another (purely an internal transfer)

Part of my table design includes an allocated quantity field to record any outstanding orders for this inventory unit (I am planning on a built in LIFO system, which I haven't got around to contemplating yet)

All went well, the stock was decreased from the existing location using an update query. but failed to materialise in the new location using an append query

After much yelling and cursing at the offending query, finally did a search and found the following:

Update queries will not work if you trying to append a primary key value (knew that one) OR you are trying to append a null value into a field

In the real world, it may well be possible that I will have 0 allocations for a given inventory unit

Does anybody know a way around this seeming impass, or am I faited to rebuild my tables again?

Thanks in advance for any enlightenment

View 14 Replies View Related

Exclude Null Values In Query

Dec 21, 2004

I have made a query from a table to count something.
Now, I dont know how to exclude the null value to the new query.

example:
Query Name: School
Field: Sch_No, Sch_Name, Subject1, Sum1, Subject2, Sum2, Subject3, Sum3, Subject4, Sum4, Subject5, Sum5

After run this query, there is a field(Subject4) has a null value.
Before I make a nice report, I have to exclude Subject4 & Sum4. And this will make Subject5 the fourth subject without displaying Subject4 & Sum4.

I hope anybody can help me..i dont know how

View 2 Replies View Related

Query Not Returning Null Values

Oct 10, 2011

2007 version - my child query is not returning null values. I have 2 fields, both pulled from a parent query. One is "DaysSinceRecd", the other is "AmountPaying". The parent query returns multiple results, which is fine. For example, the parent query might have 10 different records for "0" "DaysSinceRecd", with different "AmountPaying" for each of those records.

In the child query, I am trying to condense that down, so that I only have one "AmountPaying" for each "DaysSinceRecd". It's working fabulously, except that there are records in the parent query that have a value for "AmountPaying" but a null value for "DaysSinceRecd". The null value for "DaysSinceRecd" cannot be changed to another value, it needs to stay null. My child query is not returning the null records. I tried adding to the criteria: IsNull Or IsNotNull, >=0 or <=0. That's not working.

View 5 Replies View Related

Query Return 0 For Null Values

Feb 22, 2012

I have two tables: tblStudents and tblEnrollments. The query I have designed shows the StudentID and counts the number of Enrollments that each student has. I want the students who don't have any enrollments to still show up and have a 0 by their ID. Right now, only the students with enrollments show up in the query results.

Here is my SQL Statement:

SELECT tblStudents.StudentID, Count(tblEnrollments.EnrollmentID) AS CountOfEnrollmentID
FROM tblStudents INNER JOIN tblEnrollments ON tblStudents.StudentID = tblEnrollments.StudentID
GROUP BY tblStudents.StudentID;

What do I need to do to it to have null values display as zero?

View 4 Replies View Related

Ignoring Null Values From Form In Query

Mar 21, 2006

I have a form with 4 different search fields used to query a table and limit results: Policy#, SSN, Credit_Amt, Debit_Amt. Any matching rows based on the search criteria are returned in a subform.

My problem is how to handle any fields where the user doesn't specify a search value.

In my query, I have the Criteria set to:
Field: Policy_No
Criteria: =[Forms]![form name]![Policy#]

Field: SSN
Criteria: =[Forms]![form name]![SSN]

and so on....

If I place my criteria on different OR lines, the query runs, but if a user specifies more than 1 criteria, I get all the data for one criteria(such as policy# matches) and all the data for the second criteria(such as all the records for a certain credit_amt, regardless of Policy #).

If I place my criteria on the same criteria line, effectively making it AND, I get no results because I assume the database is looking for any blank search field values.

So, how do I make the query an "AND" query, but actually get results by ignoring any NULL search fields? So if a user enters a Policy# and Credit_Amt, the results will be only matching rows by Policy# AND Credit_Amt, and doesn't look at SSN and Debit_Amt.

I've tried different iterations using IIF(Not IsNull(.... to no avail.

Thanks!

View 3 Replies View Related

Changing Query To Show Null Values

Jun 26, 2006

Hi,
This query will allow me to view payments that are made between 2 dates. I would like to know how to flip the query around so that it gives me the payments that have not been made. I think this would be described as returning the null values?

The SQL code i have at the momnet is:
SELECT download20060602.Date, download20060602.Description, download20060602.Amount, Members.FirstName, Members.[Mid Name], Members.Surname, Members.[Memb No]
FROM Members LEFT JOIN download20060602 ON Members.description = download20060602.Description
WHERE (((download20060602.Date) Between [Enter Start Date] And [Enter End Date]));


Any help or ideas would be fantastic.

Cheers
Phill

View 3 Replies View Related

Null / Non Existent Values And Query Woes.

Aug 12, 2006

Hi there.

I've attached my db in the hope someone can help my head scratching.

I have got a Sales summary table with several other related table. Most notably, a table with the items in the sale and one with the costs. Because each sale might contain many different items and many different costs, I thought seperate tables were the way to go.

All I'm trying to do is make another query that gets the total sales (That's adding each line item * quantity) - (Each cost line item*it's quantity) and then finally the margin made on the deal.

However, if there are no costs or sales involved in the sale the query ignores it. (Sounds odd, but some transactions might be cost free, and some may not involve any revenue - so I have to bear it in mind).

I tried the Nz function, but It's either not what I need or I'm doing it wrong.

How do I get this to work?

Many, many thanks!

View 2 Replies View Related

Modules & VBA :: Invalid Use Of Null With No (obvious) Null Values

Jul 5, 2013

It might be an easy one but I just wasted the past hour deciphering through my code in order to solve the run-time error '94' that I'm getting when trying to execute the following code:

Code:
Private Sub cmdUpdateDates_Click()
'###################################
'This sub aims at combining the timesheet date and the start and end time into the fields [Start Time] and [End Time].
'###################################
Dim intCounter As Integer
intCounter = 0
Dim rs As ADODB.Recordset

[Code] ....

View 1 Replies View Related

Tables :: Append Query Not Working / Null Values?

Sep 24, 2014

I have an append query created to add files to a table from an imported excel file. The table to which I am appending (SubTBL) has a field names Observation. This field is not required. There is a relationship to another table (ObservationTBL) which has 3 records. The information I am trying to append includes 7000 records, about 4000 have observations assigned and they match the information in the ObservationTBL. The remaining 3000 have blank records for observation. When I run the append query, only those with observations are appended. The rest are not added because of "key violations". I removed the relationship between the ObservationTBL and the SubTBL and the append query runs. Then when I try to re-do the relationship it fails "Violates referential integrity rules".To be clear:

1. The records without an observation are blank. (There are other fields in this append that have blank fields also and they are not causing any problems)
2. The field observation is NOT set to required.

Is it not allowing Null records?

View 12 Replies View Related

Queries :: ORDER BY - Update Values In One Table With The Values In Another Using Join

Sep 18, 2013

I have a query with an INNER JOIN and ORDER BY that is working great. Now, using the same JOIN, I need to update values in one table with the values in another. I thought it would be simple until I learned you can't do an ORDER BY with an UPDATE. Is there another way to achieve the same result? If you remove the 'ORDER BY', the statement below doesn't produce an error but the results are not correct:

UPDATE TableA INNER JOIN TableB ON (Left(TableA.CDN,6))=(TableB.CDN)
SET TableA.HCC = TableB.HCC
WHERE TableB.HCC Like '241*' AND TableB.BBB = 'X' AND TableA.CCC = "1234" AND TableA.HCC IS NOT NULL
ORDER BY TableB.HCC, TableA.CDN;

View 2 Replies View Related

Queries :: Query Filter Based On Dates - Excludes Null Values

Apr 17, 2014

how to return all values in a query when a form critieria is left blank. I have made some progress, the combo box criteria queries were fairly simple, but i'm getting stuck with my date criteria. My query doesn't return null values when I want it to.

I want it to return all records (including null values) if the form OpenFrom and OpenTo dates are blank, and just the values between the selected dates (excluding null values) if the form is completed.

Code:
SELECT qryReportSelector2_Authority.*, qryReportSelector2_Authority.ApplicationDate AS ApplicationDateFilter
FROM qryReportSelector2_Authority
WHERE (((qryReportSelector2_Authority.ApplicationDate) Between Nz([Forms]![frmReportSelector]![OpenFromDate],DMin("[ApplicationDate]","[qryAllCases]")) And Nz([Forms]![frmReportSelector]![OpenToDate],DMax("[ApplicationDate]","[qryAllCases]"))));

View 8 Replies View Related

Help With Putting A List In Access

Mar 20, 2006

i have started to try putting the concrete records i keep on excel into microsoft acces. for each record, i need to make note of the vehicle registration numbers. what is the best way of doing this? if it is possible

Thanks

View 3 Replies View Related

Field Criteria: Is Null; There Are Null Values In That Field; No Records Are Returned

Nov 16, 2007

I think the title pretty much sums it up....

I have a query where data is first sorted by user input; first field's criteria: [fieldname], then by another field's criteria: Is Null.

I know there are records containing null values in the second field, as I have run a select query with the criteria: Like "*", to make sure they are null, and not zero-length-strings.

The query is refusing to return any results...

Any ideas?

View 10 Replies View Related

Access Query Sort Order Every Other One

Sep 29, 2014

I am trying to create a work schedule, some customers are scheduled for morning and others in the afternoon. I am trying to figure out how to sort AMs and PMs so I get a line with every other one.

View 2 Replies View Related

Putting Code Behind Tables In MS Access 2007?

Dec 2, 2013

Using an example:I have three tables; tbl_weight , tbl_height , tbl_bmi...How do I make tbl_bmi automatically use the weight and height values (height/ weight) to determine the tbl_bmi values? Is there a way to store code behind the tables as if I was in MS excel?

View 2 Replies View Related

Tables :: How To Make Constraint When Putting Records Into Access Table

Sep 29, 2014

I am developing a simple database for students to register courses using Microsoft Access 2010. I have 4 tables: Student, Register, Course, and Section (1 Course can have many Sections).

Then, there is a criteria that a student can only take up to 6 courses. Hence, may I know that where and how can I include this constraint? I believe that it should be in the Macro (Before Change) of the table Register...

My relationship is: Student -> Register <- Section <- Course

View 1 Replies View Related

Putting A Query Into A Form

Aug 9, 2006

I would like to put a query or some look up device into my form so that a certain part number can be found and its form opened for changes. How should I do this?

View 1 Replies View Related







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