Query question

[WayneRyan]

Bruce,

I'm in on this thread a little bit late, some vital piece is out of place.

In the last post:

[0-Combined] is the left-side table.
[0-Combined_City_Filter] is the right-side table
Join on Left.City = Right.City
Remove undesired --> Where Right.City Is Not Null

Then sort by Left.City

That sure seems logically right.

Can you post a sample DB?

Wayne

 

[InvGrp]

Not sure what you mean by sample DB?

The DB is pretty big: the front end is 43,432 kb and back end is 190,096.

I appreciate all your help.

Bruce

 

[WayneRyan]

OK,

Then I guess you can't post it.

If you can't make a "small" DB that demonstrates the problem, then I
fear that we'll all remain confused.

If you can then it can probably be resolved quite easily.

Wayne

 

[raskew]

Picture #1: is the query where sale date is sorted
There is no sort in that query.

Picture #2 is the sale date results
Sale date is not sorted. Look at the last 3 records.

Picture #3 is the query where city is sorted
Sorted on City. Criteria for City: Is Null

Picture #4 is the city results.
City is most obviously not sorted.

Bob

 

[InvGrp]

Bob:

If you can, could you look at the pictures I posted in post #18. They are the most current.

If you notice picture #3 that is how the query is set up and #4 is the results.

As you can see #4 the results are not sorted by city and that's what I'm trying to accomplish.

I'm sorry if I'm not answering your questions properly but this Access stuff is way over my head.

Thanks

Bruce

 

[WayneRyan]

Bruce,

The MIDDLE column in #3 should be from [0-Combined] Not [0-Combined_City_Filter] !!!

Wayne

 

[InvGrp]

I tried uploading the DB but its too big (its 854 kb).

I can email mail it if anyone would like to see it.

Thanks again.

Bruce

 

[InvGrp]

Bruce,

The MIDDLE column in #3 should be from [0-Combined] Not [0-Combined_City_Filter] !!!

Wayne

Wayne:

I tried using 0-combined and its sorts by city just fine BUT it shows all of the cities (there is a table that is supposed to show only certain cities).

see post #20

Thanks.

Bruce

 

[raskew]

Bruce -

Look at your Picture #3. All of the displayed data is coming from table O-Combined (note the checkmarks/lack of same). The other fields shown are there to construct the Where and Order By statements.

You need to:
1) Remove the Ascending sort from [0-Combined_City_Filter].City. It does absolutely nothing. (How many ways can you sort a Null field?)
2) Pull field [O-Combined].City into the query, click on Sort Ascending and remove the checkmark from the Show box.

Although you haven't provided the query SQL, which is what's needed to see what's really going on (not pictures), believe that this will give you a proper sort, returning only those cities not listed (Is NULL) in [0-Combined_City_Filter].City.

Bob

 

[raskew]

Bruce -

Have gotta ask -- are you trying any of the suggestions you've been provided or do you just want to argue about them? We're up to post #30 (not a world's record, but we're getting close) and in reviewing them, can't find any indication that you've tried any of them.

Check back on Wayne's recent posts. His suggestions should work, and actually (I just discovered) my previous post mostly duplicates what he's already offered.

Bob

 

[Brianwarnock]

When I started to read this again today I realised that he is using an unmatched query to filter out the cities he does not want, then I reached Wayne's posts and realised that Wayne had solved the problem, it only needs the InvGrp to see that.

InvGrp note the order of your sorts affects the final sort order
from post 5
ORDER BY [0-Combined].[SALE_DATE], [0-Combined].[CITY];
will give a diff order to
ORDER BY [0-Combined].[CITY],[0-Combined].[SALE_DATE];

Brian

 

[InvGrp]

Bob:

I tried each and every suggestions and they didn't work.

I then decided to compact/repair the database and rebooted my computer.

I then started over trying each suggestions.

It now works (I believe what you suggested did the trick).

I truly appreciate everyone's help.

Thanks again.

Bruce

 

[georgedwilkinson]

I'm awestruck.

 

[WayneRyan]

I'm not sure exactly what ... BUT there is a lesson to be learned here.

Wayne

 

[InvGrp]

I certainly learned a few lessons.

I need to be more precise in how I explain my problem.

I need to be more precise when I respond to posts.

I need to remember to reboot my computer every once in awhile.

Again, thanks for everyone's help.

Bruce

 

[WayneRyan]

Bruce,

I hope that means that you got it working correctly now.

btw,

To attach a DB:
Remove forms/reports/queries/data not pertinent to the problem.
Tools --> Compact/Repair
Then ZIP

It is WAY easier to assist with a tangible DB to look at.

Hope to see you again,
Wayne

 

[georgedwilkinson]

I'm not sure exactly what ... BUT there is a lesson to be learned here.

Wayne

I guess it would be nice if there were a list of problems caused by a database that needs to be compacted and repaired. It's not the kind of thing I'm likely to try to fix a problem and certainly not the first thing I'm gonna tell a poster who is having a problem.

 

[Brianwarnock]

I have to admit to missing this first time through

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.

but you know George had the correct sort field way back on post 5
I guess we all need to read more carefully.
To be quite honest Bruce seemed to ignore everybody but Bob.

Brian

 

[InvGrp]

To be quite honest Bruce seemed to ignore everybody but Bob.

I actually did try everyone's suggestions but they didn't work (I guess I wasn't very clear on this).

It wasn't until I compacted/repaired the DB and rebooted the computer when they worked.

Thanks again.

Bruce

 

[raskew]

Hi -

I then decided to compact/repair the database and rebooted my computer.

Hmm, that is a definite 'eye-opener'!! Who would've thought? I'm going to try to remember to add that to my paltry bag-of-tricks!

Thanks - Bob