Using The IIF Statement To Sort
May 2, 2006
Hi I have very basic SQL knowledge and would appreciate any help.
I have a query which asks users to enter an ID - based on that ID, a Report is generated that gathers student information and produces a subject report for that student. The ID refers to a subject that a particular student is enrolled in. The table that is returned contains a list of every student currently enrolled in that subject (which is what I want), however it is sorted alphabetically.
I want the result set to be sorted alphabetically, but within classes, where a class is a set of about 20 or 30 kids enrolled in a particular subject. So for example - If the ID for Maths 101 is 50. The user types in 50 at the message box promt, and the query returns 150 kids enrolled in Maths 101. But there are 5 class groups in Maths 101. So I need it to be sorted into Maths A, Maths B, Maths C, Maths D and Maths E, and then Alphabetically within each group.
The original student data is stored in a table that has about ten "lines" where a maths class may fall on the school time table. So it searches through Line A, and if ID=50 is not in Line A it searches Line B and so on.
How can I say in SQL "If the ID is in Line A, sort alphabetically by Line A, else If the ID is in Line B, sort alphabetically by Line B else...for 10 possible Lines"
I'd appreciate any help. I also need actual Sytax for how to put the IFF into my SQL window as I am not sure how to do this...
thanks heaps!!!!
Bella
View Replies
ADVERTISEMENT
Oct 1, 2013
I have a Union Query (that works perfectly fine) with the following code:
Code:
SELECT * FROM sbqryUseBattery
UNION
SELECT * FROM sbqryUseBeltsDeck;
UNION
SELECT * FROM sbqryUseBeltsHydro;
UNION
SELECT * FROM sbqryUseBeltsPTO;
UNION
SELECT * FROM sbqryUseFiltersAir
UNION
SELECT * FROM sbqryUseFiltersFuel
UNION SELECT * FROM sbqryUseFiltersOil;
I am using this information on a Report.
The problem is that the Report shows the data in random order. Is there a way to filter either the Union Query or the Report?
View 14 Replies
View Related
Mar 3, 2006
i made a report based on a query. when i run the query, my results are sorted by Job Time Ascending, like i want...
when i made my report i chose job id as the main group priority in the wizard..
now in my report it displays the jobs in ID ascending, rather than Job time ascending as my query says..
i dont want to re-do the report it took ages.. how can i fix this?
View 3 Replies
View Related
Oct 16, 2006
Hi all,
I have a form in Access 2K that properly displays city, state and zip codes. As I type in each letter of a city's name into the combo box, I get the first city in the sorted list displayed (autofills). That's fine when there is only one zip code for a city, but when I enter "chic" for chicago I still get the first chicago zip code in my table.
It sure would be nice if I could do a "sort within a sort" so that if there were two or more records with the same city name then that whole group would drop down so I could choose one of the other zip codes. If this is doable, kindly point me to an example as I need all the help I can get.
Thanks in advance for any responses.
D
View 6 Replies
View Related
Jan 9, 2005
I have a query that sorts players batting averages from highest to lowest. When I run the query just by itself it sorts it right however when I open the report based on that query it does not sort in descending order like I asked. What am I missing??
View 3 Replies
View Related
Feb 12, 2014
I have the following Select Statement:
SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],
[code]....
Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?
View 2 Replies
View Related
Oct 31, 2014
I have a query with the following criteria in one of the fields:
>=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom())
fom is a function for first of the current month. I need this query to be specific to what month it is when its ran so i want to only have this criteria if the month is > = october. If it isnt October or greater, i want the criteria to reflect this.
>=DateAdd("m",-12,fom()) And <=fom()
Which also works by itself. But when i add it to an iif statement it always produces no results. Below is the iif statement.
Iif(month(date())>=10, >=DateAdd("m",-12,fom()) And <=DateAdd("m",1,fom()),>=DateAdd("m",-12,fom()) And <=fom())
I have also added the column name to each expression and it still doesnt produce any results.
View 4 Replies
View Related
Apr 28, 2015
I am creating a multi-search form for a student database, where after I enter my search criteria I hit a "Run Query" command button and then it opens a query form with all of my criteria.So far I can search using last name, first name, and middle name. When I try to search with a start date and end date I am have issues.The start date and end date is for the class date. In the query form under the field, class date, for criteria I wrote:
Between IIf([Forms]![Search Form]![Start Date]="",1/1/10,[Forms]![Search Form]![Start Date]) And IIf([Forms]![Search Form]![End Date]="",4/25/15,[Forms]![Search Form]![End Date])
I want it when I write a date in the start date and end date I want it to give me a list of all the students who took the course between those dates. Also, if I leave the dates blank I want it to search all dates. The dates 1/1/10 and 4/25/15 are just the dates I gave because that is far back as my database goes.
View 11 Replies
View Related
Aug 8, 2005
I was just wondering if it was possible to run a query by a specific value first, then the remainder of the following values in ascending order.
For example, the data would appear in the table as
Smith
Bloggs
Jones
Taylor
What I would like to do is have all the Taylors at the top, followed by Bloggs,Jones then Taylor
I have looked at the OrderBy function, but believe that you can only sort Ascending and Descending
Checked search, cant find anything
Many Thanks
Andy
View 3 Replies
View Related
Mar 25, 2007
Hi,
I try to do something but I don't know how.
I have 1 table with 3 rows (title, year, genre) and 1 Form.
Is it possible to create combobox or something with 3 criterium, when I selected
criterium to re-sort columns in Forms (by title, by year, by genre)?
thank you
View 2 Replies
View Related
Jan 13, 2006
Continuous form:
The standard menu / toolbar commands sort ASC and DEC work just fine.
However, if I define a button with the VBCode
"DoCmd.RunCommand acCmdSortAscending", I get an alarm message pop-up
saying "the command or action "SortAscending" isn't available now".
I made sure that the standard menu items "Sort asc / dec" are not grayed out(disabled) at the time I click the button in runtime. The mouse cursor points to the desired sort criteria field.
Any advice?
Regards from Belgium (pretty cold here)
View 2 Replies
View Related
Aug 29, 2005
I am completely stumped on this one. I have a database where we track IP Addresses and I just want to sort by IP...should be simple, I'd think.
With these 5 IP's, I'd want them in this order:
1.2.3.1
1.2.3.4
1.2.3.7
1.2.3.9
1.2.3.10
1.2.3.17
1.2.3.21
But it sorts in this order:
1.2.3.1
1.2.3.10
1.2.3.17
1.2.3.21
1.2.3.4
1.2.3.7
1.2.3.9
Is there anything I can do to fix this? I want it to see each number as a whole, for example "10" instead of "1" and "0"
Much thanks.
View 5 Replies
View Related
Oct 10, 2005
How do you change a file's sort order from ascii to international?
View 3 Replies
View Related
Dec 22, 2005
Hello all...
I have a table which includes month, day & date. I want to sort this table by day, and then by month & then by year... does anyone know how to do this?
Thank you...:)
View 4 Replies
View Related
Jan 16, 2006
Hello all
Been looking at this forum for a few weeks and think it's great!!
Now for my first post :rolleyes:
We have a DB at work (attached) and it was put together by a consultant would you believe. Anyway I have been given the task to fix this. It now work i.e. it records what I want. The only problem is that it's written with more macros than i've ever seen.
I want some advice on the DB to make it function better. It is used to record how long a member of staff spend on a piece of work, apologies for the interface.
1. The user must first input a date , start date and end date into a table. Now the start date will always be a Monday and the end date a Sunday. Is there anyway to automate that process? i.e if a user goes in on a Monday the prevous weeks date is there but if the user went in before it would not , get the idea.
Anyway, please rip it to shreads and give me some advice
Thanks
View 11 Replies
View Related
Jan 25, 2006
I have a table already set up in a Subform of a main form
The Table has values like
-Date
-Time
-Name
-SchBy
I want to have the subforms table to automatically re-sort ascending by Time, how would I go about doing this so the user doesn't have to click the field then sort ascending button...?:confused:
View 1 Replies
View Related
Oct 17, 2006
hello ladies and gents!
I've been kind of lurking around.
I love this site! thanks for having me :)
View 1 Replies
View Related
Jan 15, 2007
When i sort values, for some reason the negatives are at the top, followed by the highest positive values. Anyway to turn this off?
Tried google and searching these forums.
View 5 Replies
View Related
Dec 9, 2007
Hi all.
I imported a text file into a new table.
I sorted the table on one field, then exported table to another text file.
But, the sort did not export. The new text file has the same sort as the original.
What am I doing wrong?
Thanks.
View 3 Replies
View Related
Dec 13, 2007
Hey all, I looked throught a couple of threads for sorting, but could not find exactly what I need.
Basically, I have a list of dates:
10-Aug-07
20-Oct-07
13-Nov-07
etc...
and when I try and sort these dates from earliest to latest, it only reads the number and not the month, like:
10-Aug-07
13-Nov-07
20-Oct-07
etc...
How would I make it sort by date and month?
Thanks,
RR
View 4 Replies
View Related
Jun 21, 2005
Hello, I have the following query which gives me a count by day.
However, it sorts by day instead of day/month.
For example:
01/01/05
01/02/05
01/03/05
02/01/05
02/02/05 and so on...
Can I sort so it will show me all of January, then all of Febuary etc?
Thanks.
J.
View 5 Replies
View Related
Jan 19, 2006
Open the database – then open the Report: rep_Billing
I have a query that is doing some sort and count functions. The column [TYPE] is sorted then counted. Instead of sorting by the full name can the query sort based off of the first 2 characters?
Example:
On the report the first company is: AMERICAN POSTCARDS
First it separates the colors, which is good.
Then separates the [TYPE] then counts the total amount that color was that type.
However I do not want the full type to be separated out, I want it based on the first 2 characters so that the “N-“ will all stay together as a group.
Final count should say: AMERICAN POSTCARDS
GREEN RA 3
RED N- 14
RED PR 16
RED RA 12
BLUE NC 1
I know I can get this info from the report, but it has to be done at the query level because it is going to be exported into another system at that point. Thanks.
Let me know if I need to make this clearer. Thank you all.
Travis
View 2 Replies
View Related
Feb 3, 2006
Hello,
Is there any way to sort a query by last entry? Unfortunately, this would be the only solution to a design error. Thanks much.
Pat
View 1 Replies
View Related
Feb 3, 2006
Is there any way to sort a query by last entry? Unfortunately, this would be the only solution to a design error.
View 5 Replies
View Related
Feb 10, 2006
I need to sort an address field by street name only. The address info is stored as: 123 Main Street, etc. Is there a way to omit the number portion of the field during a sort function in Design View?
View 1 Replies
View Related
Jun 14, 2007
Hello,
Quick question,
I am bringing in two fields into a table which looks like this:
Field 1 Field 2
A 2006-07-25
A 2006-08-21
A 2006-09-21
A 2006-10-21
This is how it's sorted and do not forget that field 2 is a text field.
Now i made two different queries pulling in both Field 1 and Field 2, however in one query i'm pulling in First of Field 2 and in the second query Last of Field 2. For some reason, i'm getting 2006-10-21 as a first and 2006-07-25 as last.
Can anyone explain that?
Thank you!
View 1 Replies
View Related
