MS ACCESS :: Sorting Query Results

Sorting Results Problem...
Working with Access 97 (willing to try your ideas even if your not sure! )

I have a query that calculates the distance between a Zip code entered and a list of cities. What I need is to sort by closest cities to the Zip.

Here is my code. I'll explain what I've tried below it.

SELECT CommunityClasses.Community, (69.1*(CommunityClasses.Latitude-Zip.Lat)) AS X, ((69.1*(CommunityClasses.Longitude-Zip.Lng))*Zip.keviekev) AS Y, Sqr((X^2)+(Y^2)) AS Distance
FROM CommunityClasses, Zip
WHERE (((Zip.[Zip Code])=[Please enter a ZIP Code]));

When I try to sort, it asks for a value for X and Y. If I don't ask to sort, it calculates the values as it should do.

If I ask it to ORDER BY Sqr((X^2)+(Y^2)) it again asks for values of X and Y.

Do I need to do ORDER BY .... then tell it WHERE for the ORDER BY?

Thanks for any input!

View Replies !

Query Results Minus Query Results = New Query?
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items.
How can i make 3rd query which will give me all but taken items from query1?
(of course items from query 1 are in query2)
thx in advance

View Replies !

Help With Sorting A Query?
I have a job database where I sort jobs by upcoming, inactive, closed and active. Each has a corresponding number 1-4 respectively. The main table I update the jobs in is the JOB TABLE. I print a weekly report for our weekly meeting but I only want the active jobs in the report. My question is: how do I perform a sort function in the query, so I will have only the active jobs in the database table show in the query? Thanks in advance! If more info is needed I wil post additonal.

View Replies !

Sorting In A Query
I'd like to sort in my query - used to populate a combo box - first by name, and then by ID.
Is there a way to force Access to sort in a particular order? It seems like it starts sorting with the first field that's to be sorted, but I want it to start with field 2 and then sort by field 1.

View Replies !

Sorting A Query
I am fairly new at relying heavily on Access for my programming. Problem: I have a telephone database with call detail for every call. I only want specific records, which I am pulling by selecting sort (in query design mode) for that particular field. I need to sort like you would in Excel (Column A first, Column B second, and etc). Can someone help direct me in the right path as to how I need to run a mulitple sort? Your help is much appreciated.

View Replies !

Sorting A Table, Apply The Sorting To A Form
Hi all.
I've created a database which contains information about stores. I want to have the forms automaticly sorted by the department number.
I've tried to sort the table by department, but when I try to add a department, the sorting doesn't seem to affect the form at all.

Lets say I have department 1,2,3,6,7,8 in the form, and I add department 4, it will be the last post in the form. I want it to be the fourth, and so on..

I'd apreciate some help with this :) Thanks

Here's the database (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=12934&stc=1&d=1142018915&PHPSESSID=f730b7f11f6983965698faeacbe5a1ee)

View Replies !

Sorting Further From A Duplicates Query
I have a Duplicates Query that extract the following information from my Table

Name Surname DOB Joined

The query then extracts info such as...

Bill Smith 25:12:70 1999
Bill Smith 25:12:70 2005

Most of the time the 'Joined' field has the same year. But I need to find the ones where the year differs but the rest of the information is exactly the same. Because there is 15000 odd records to check, I thought that there would be a quicker way to check.

Could someone please assist me with this query if it is possible?

Thank you in advance.

View Replies !

Sorting Crosstab Query
I have a crosstab query where my column headings are the names of the months based on invoice dates.
what i currently have:
Month: MonthName(Month([AnnualIncome]![InvoiceDate]))
so this spits out April May and June in the column headings.
The problem is that it is sorting it alphabetically, not by the numerical value - so my column headings are coming out as april, june, then may. (I assume once I have july that will come in between april and june.)

Any help would be REALLY appreciated.

Thanks in advance.

View Replies !

Sorting Records In A Query
I am having a problem sorting records in a query. I have about 5, 000 entries in a table and when I build my query based on certain fields, the numbers in certain columns are not in order although I use the sorting feature. ( e.g.) I have 5 columns with numbering data contained in the columns, and I have it set up to sort in ascending order, which it did, but I noticed that the Volume # column sorts the Volume numbers differently (see example below):

V. 1
V. 10
V. 11
V. 12
then it goes back to
V. 2
V. 3
V. 4
V. 5
V. 6
V. 7
V. 8
V. 9

It looks like it sorts anything with a 1 first then the rest later. How do I fix this?

Thanks.

View Replies !

Sorting Query & PivotChart
Hey everybody,

I know this question maybe be very lame but its been ages since i've touched on my sql knowledge. I've set out my database and i have 1 table which is holding the info i need to extract so that i may put it into my report. I wish to count the different values in a specific row:

Example:

Whats the problem
Behavioural
Physical
Physical
Behavioural

I wana be able to count these seperately as in:

SELECT COUNT(Client.[Whats the problem] LIKE 'Physical') AS Phy AND COUNT(Client.[Whats the problem] LIKE 'Behavioural') AS Beh
FROM Client;

Result:

Phy Beh
2 2

I havent got a clue if i'm doing it right but i need to get it to work in access 07. Also one more question i have created a Pivotchart in a form but when a create a button on my main startup form and click into the Pivotchart form all i get is the Datasheet view, how can i get it to open up the Pivotchart through the form.

Any info would be greatly appreciated Thank You.

View Replies !

Genaral Month Sorting Query - Help?!
Hi i have a general query and im not sure which section to put it in yet

Basically i need to have a date choosing system which allows me to enter dates once into the form and then print out monthly reports on which of our clients is to be sent a report,

some clients are half yearly, some quarterly, some yearly, at present i have a drop down box for these choices and a drop down box for month

is there a way of combining the half yearly and month ie May to come up with nov so that When i want to print out may's report, Mr X (who has a half yearly report) can have half yearly chosen and May chosen but still get on both the novembr and May print out along with mr Y who only has a yearly report in May

I do not really want to have to change the values in the drop down boxs as i do not think employees would do it and the system would be inaccurate

If a query could be made which somehow combined annual may and half Annual May, AND would also work out that half annual may would become Nov? ie: Annual Nov and Half Annual May

Im confused about the best way to do this so any suggestions would be v much appreciated :)
I dont know if the drop down system is the best way or how i would make a query to make this work and what the best method of making the reports is? any help much appreciated

H

View Replies !

Sorting Query By Date & Employee Name
I created a query which at first was simply to run a query which asks you for a drivers name, however we have decided that now we want to sort by the employee name as well as by a specified date range. I originaly had it set up to select the driver name with this in the criteria field in the Driver Name column:

[Enter Driver Name]

Then I tried to enter the following in to the criteria field in the date column:

Between [Enter Start Date: (Format MM-DD-YY)] And [Enter End Date: (Format MM-DD-YY]

However when I run the query it asks me for the date about 4 times, and then it goes to a blank report. I am sure I am not doing this properly somehow, anyone care to help out?

View Replies !

Sorting Of Query In Subform Doesn't Happen
Hi,

I have a query for a subform which is displayed in datasheet mode (see attachment). I've created a form based on a query and used it as a subform.
The "casenumbers" per clients are displayed from 1 and upwards, I would like to have them sorted Descending. So in the query I sorted them that way. When I run the query they are displayed in descending order. When I open the subform based on that query however it is not. I can sort descending anyway by clicking the column on which I want to sort and do it that way, but that becomes bothersome and it doesn't need to be that way.

Record source is ok:
SELECT KlantNAW.ClientNumber, CaseInfoTable.Casenumber, CaseInfoTable.DateInitialCall, CaseInfoTable.DateCaseCreation, CaseInfoTable.CaseAccepted, CaseInfoTable.[1stAppointmentDate], CaseInfoTable.DateCaseClosed, CaseInfoTable.About1 FROM KlantNAW INNER JOIN CaseInfoTable ON KlantNAW.ClientNumber=CaseInfoTable.ClientNumber;

Order by:
Casenumber DESC

I thought, well, lets create a new form based on the query to see if that changes anything, maybe I've changed something I shouldn't have.

But nope, same result.

How can make the form sort descending in datasheet mode?

View Replies !

Stopping A Crosstab Query Sorting Fields Alphabetically
HI all,
In my database i have a crosstab query that has a text field as a row heading and a number field as a column heading. The query is sorting the row heading on the left hand side alphabetically. I dont want it to do this, i want it to show the fields as they are in the table.

Is there anyway of stopping the query automatically sorting alphabetically?

Thanks in advance

View Replies !

Self Generating Query String Based On Query Results?
Here's my problem. I need to generate a report that says how much of each individual product was produced and as well as the total produced for a specified category in a time period. Something like the following:

05 Catagory A: 02 Product AA, 01 Product AB, 02 Product AC
10 Category B: 07 Product BA, 03 Product BB
04 Category C: 01 Product CA, 01 Product CB, 01 Product CC, 01 Product CD

etc...

I currently have a query that queries a database and pulls out all products that were produced in a specified period and the categories they belong to and dump them into a local access table. Now what I need to do is search through the query results and count up how many of each product were produced (02 AA, 01 AB, etc...) and the totals for each category. The number of categories is pretty limited (6), but there are hundreds of product codes, so I need a way to do this without having to type in each induvidual product code as the requirement by which the query searches. Also, the product codes that get returned are different every day.

I was thinking something along the lines of take the product code of the first row and check for any others in the results that match and write that into another table. Then move onto row 2 and use its product code as a search parameter and search through the query results for any matches. Then continue that until the end of the query results. Can I do that? Is there a better way to achieve what I need?

View Replies !

Query Results In Form, But What If Query Is Null?
I have a query that displays results in a form, but if the query is null, I want to display a different form, or just an error message that says something like "your query returned no results" (right now it will display the form with no fields)

I am a beginning Access/VBA user and have searched and browsed the forum for combinations of null/query/form, but haven't found what I need. Can anyone point me in the right direction?

Thanks for any help.

View Replies !

Query Results
I am wanting my query results to appear in my main form so that i can edit them, rather than a table the query is just a look up of my form that stores about 500 records and stores all the same fields

any help would be appreciated

thanks

View Replies !

Query With No Results
Right my query is working fine and creating a report this is how it works,

User clicks button , it runs the query report and prints it, it then updates the records so it doesnt print these again. here the script is

On Error GoTo Err_Command45_Click

Dim stDocName As String

stDocName = "Goods In Today"
DoCmd.OpenReport stDocName, acNormal
DoCmd.SetWarnings False
DoCmd.OpenQuery "Goods In Today (Make Printed)"
DoCmd.SetWarnings True

Exit_Command45_Click:
Exit Sub

Err_Command45_Click:
MsgBox Err.Description
Resume Exit_Command45_Click

Now what i`d like it to do is when the query runs and doesnt find a match it comes up with a message "No Results". How would i go about doing this

Thanks

View Replies !

One Query W/many Different Results
Is there a way to write one query that will work for different criteria and is run by different buttons to produce bar graphs or a basic report depending on the button the user clicks??

View Replies !

Query Results?
Hi all :o

I am working on a query but can manage to get it to do what I want.
I have a main table with a job_ID and a faults table which documents faults that occur related to that Job_ID the tables are related with a one to many relationship. I want the query to bring up all the jobs even those with no faults, at present it is only bring up those jobs that have faults, how do I include details of all the jobs and if there are no faults then that field is left blank, probably very simple to do but after working on this db for hours my brain is no longer functioning!! :eek:

Thanks

Jackie

View Replies !

Help With Query Results
Hello all,

I have a table called tblODF and within that table, I have these fields:
- ODFNumber
- ODFScanDate
- Status (Combo box: Pending, Complete, Licensing)
- LastFollowup

In my query, I have the same fields, except I added a calculated field.
I added DayCount to

Codecalculate:(Date()-[ODFScanDate]

This should calculate the number we've had the ODF.

What I want to happen is, let's say the status is Maturity, I want the DayCount to say 0. If it's pending, I want it to calculate the actual days.

Is this possible?

Thanks

View Replies !

Query Results
I've just started using Access 2007, but I've been an Access 2003 user for some years and have a couple of databases, both of which I've now brought over to 2007.

Something I've noticed and is probably very simple. In 2007, after I entered information in a table via one of my forms, I queried on that information but it wasn't included in the results. But everything else - from 2003 - was. I then went thru each field in the underlying table to "match" fields there were filled in for a 2003 record and now the new entry does appear in the query results.

This hadn't happened before in 2003 - it didn't matter if all or even particular fields were filled in. But now it needs for at least some particular fields to be filled in. Don't know if it's something different in 2007, or probably coincidental. I haven't changed anything in the table, query or entry form.

It's not a crisis (now that I see my results), but I'm wondering what is going on - now, that apparently wasn't occurring before?

Thanks!

View Replies !