Query question

[InvGrp]

I have a query that I can't get to sort correctly.

I'd like it to be based upon a start date (which is fine) BUT then sorted based upon city.

If I leave all fields "unsorted" it sorts by date but if I check the city field "sort" it kind of sorts.

I've attached a few screen shots.

I really could use some help with this (I just can't figure it out).

Thanks in advance.

Bruce

 

[InvGrp]

I don't see images

I uploaded the images but I don't see them.

So, I'll try again.

Thanks.

Bruce

 

[georgedwilkinson]

Open your query in SQL mode. If you can't figure out how to fix it there, post your SQL here and somebody'll tell you what to type.

 

[InvGrp]

I opened the query in design view and then went view, SQL view and here is what showed up (not sure this is what I was supposed to do).

SELECT [0-Combined].*
FROM [0-Combined] LEFT JOIN [0-Combined_City_Filter] ON [0-Combined].[CITY]=[0-Combined_City_Filter].[City]
WHERE ((([0-Combined_City_Filter].[City]) Is Null) And (([0-Combined].[SALE_DATE])>GetDateCrit()) And (([0-Combined].[T_S_NO]) Is Not Null) And (([0-Combined].[USE]) Like "r"));

Any help would be appreciated.

Bruce

P.S. I just want it to start from a certain date then sort by city (currently, it sorts by date).

 

[georgedwilkinson]

Try:

SELECT [0-Combined].*
FROM [0-Combined] LEFT JOIN [0-Combined_City_Filter] ON [0-Combined].[CITY]=[0-Combined_City_Filter].[City]
WHERE ((([0-Combined_City_Filter].[City]) Is Null) And (([0-Combined].[SALE_DATE])>GetDateCrit()) And (([0-Combined].[T_S_NO]) Is Not Null) And (([0-Combined].[USE]) Like "r"))
ORDER BY [0-Combined].[SALE_DATE], [0-Combined].[CITY];

If those are the fields you want to sort on. If not, just put the correct fields in.

Just a note, you should avoid using special characters and spaces in your object/column names. The special character "-" looks too much like a minus operator. You'd probably be better off not to name things with numbers, for the most part.

 

[Brianwarnock]

I know that I'm getting old and slow but is there any point on sorting on a field where the only value is null?

ON [0-Combined].[CITY]=[0-Combined_City_Filter].[City]

WHERE ((([0-Combined_City_Filter].[City]) Is Null)


Brian

 

[georgedwilkinson]

I wondered about that. My guess was that this is a first pass to clean up the data (i.e. supply a city where it is missing) before using this query for...whatever the heck it is they're using it for.

 

[InvGrp]

The database was set up to only search for certain cities and if I remove the "is null" it shows every city.

Thanks.

 

[Brianwarnock]

But how does it show any with the code you have, its only looking for Null.

Brian

 

[georgedwilkinson]

And you're sorting on city, a value that will always be null.

 

[InvGrp]

I'm not really sure how it actually works but if I remember correctly, it was designed to have a separate table where the cities are kept that I DON'T want to see data on.

If I run the query and the "is null" is used these cities do not show up.

If I remove the "is null" every city shows up.

Thanks for looking at the problem.

Bruce

 

[georgedwilkinson]

Is it working correctly now?

 

[InvGrp]

No, its still the same. It will sort by date but I want it to sort by city and that's where the problem is.

Bruce

 

[georgedwilkinson]

That's because city is null. How can you possibly even see that there is a problem, since city is null:

FROM [0-Combined] LEFT JOIN [0-Combined_City_Filter] ON [0-Combined].[CITY]=[0-Combined_City_Filter].[City]
WHERE ((([0-Combined_City_Filter].[City]) Is Null)

In the code you gave us, both things called "City" must be null in order to get anything from the query. You can sort on a null column but the result will always be the same...nothing. And since "City" will always be set to null using your query (or my query based on the same) it will essentially do...nothing.

How can you tell it's not sorting on City? Can you show us sample output along with the SQL used to get that output?

 

[InvGrp]

If you look at post #2 I attached some pics of what I'm seeing.

Here is the code when I have:

no fields sorting
SELECT [0-Combined].*
FROM [0-Combined] LEFT JOIN [0-Combined_City_Filter] ON [0-Combined].CITY = [0-Combined_City_Filter].City
WHERE ((([0-Combined].SALE_DATE)>GetDateCrit()) AND (([0-Combined_City_Filter].City) Is Null) AND (([0-Combined].T_S_NO) Is Not Null) AND (([0-Combined].USE) Like "r"));

sale_date sorting:
SELECT [0-Combined].*
FROM [0-Combined] LEFT JOIN [0-Combined_City_Filter] ON [0-Combined].CITY = [0-Combined_City_Filter].City
WHERE ((([0-Combined].SALE_DATE)>GetDateCrit()) AND (([0-Combined_City_Filter].City) Is Null) AND (([0-Combined].T_S_NO) Is Not Null) AND (([0-Combined].USE) Like "r"))
ORDER BY [0-Combined].SALE_DATE;

sale_date sorting:
SELECT [0-Combined].*
FROM [0-Combined] LEFT JOIN [0-Combined_City_Filter] ON [0-Combined].CITY = [0-Combined_City_Filter].City
WHERE ((([0-Combined].SALE_DATE)>GetDateCrit()) AND (([0-Combined_City_Filter].City) Is Null) AND (([0-Combined].T_S_NO) Is Not Null) AND (([0-Combined].USE) Like "r"))
ORDER BY [0-Combined_City_Filter].City;

It sorts fine when I have sale_date sorting but doesn't work when I have city sorting.

Thanks.

Bruce

 

[georgedwilkinson]

I could barely see the picture you posted and they were not indexed to the problem you say you are having. Ignore all the stuff that works and just talk about what doesn't work (and show samples for same).

The problem with the city sort is that you're sorting on a field that is null, as pointed out in posts 6, 10, and 14. If you think that is inaccurate, post ONLY the SQL you think is giving you problems and sample data for ONLY the SQL that is not working so we can try to help. Say what they are (accurately) and how they're related (accurately).

 

[raskew]

How many times does it have to be pointed out that you're attempting to sort on a field that you've declared, in your Where statement, that the field must be null?

Bob

 

[InvGrp]

I'm trying to understand the "is null" comments BUT the query displays data when "is null" is in the city field.

Picture #1: is the query where sale date is sorted

Picture #2 is the sale date results

Picture #3 is the query where city is sorted

Picture #4 is the city results.

As you can see I'm getting data.

I really do appreciate all your help.

Bruce

 

[WayneRyan]

InvGrp,

If you want it to sort by city, use the City in the Left-Hand table,
which is the one WITHOUT Nulls:

SELECT [0-Combined].*
FROM [0-Combined] LEFT JOIN [0-Combined_City_Filter] ON 
        [0-Combined].CITY = [0-Combined_City_Filter].City
WHERE [0-Combined].SALE_DATE > GetDateCrit() AND 
      [0-Combined_City_Filter].City Is Null AND
      [0-Combined].T_S_NO) Is Not Null AND 
      [0-Combined].USE) Like "r"
ORDER BY [B][SIZE="3"][0-Combined].[/SIZE][/B]City;

hth,
Wayne

 

[InvGrp]

The way the current query is set up I have a table where I input the cities I don't want to see data displayed.

When I use the city in the other table it displays every city including the ones I don't want data displayed on.

Thanks for your help.

Bruce