Joining Status Query Problem

[Jim]

In a table I have a column with status letters F J or I which denotes
Members current Position.
In the Query I want to sort alphabetically all the F people first, then the
J people and then the I
people.
The F people where easy in the criteria box, or the J, or the I, but I have
been unable to
get the J and I people to join together and produce a complete list.
Help appreciated.
Jim

 

[Ken Snell]

Easiest approach is to add a calculated field to your query:
SortField:
Switch([StatusField]="F",1,[StatusField]="J",2,[StatusField]="I",3)

Then sort ascending on this SortField field.

 

[John Vinson]

In the Query I want to sort alphabetically all the F people first, then the
J people and then the I
people.
The F people where easy in the criteria box, or the J, or the I, but I have
been unable to
get the J and I people to join together and produce a complete list.

You may be making the common mistake of confusing *sorting* - taking a
set of records and putting it into a chosen sequence - with
*searching*, applying criteria in a query to limit which records are
selected. These are two independent operations in Access. If you put
criteria in the criteria box, you are doing the latter: selecting
which records you want to see. Sorting, as discussed elsewhere in this
thread, is a different operation.

If you want to search for all J, I and F people (excluding those
records with other values in Status, or no value at all) you can use a
criterion of

IN ("J", "I", "F")

 

[Jim]

Hi Ken
Not sure I've got this right.
My query field at present is JoiningStatus,
and the sort is set to ascending.
So creating a new sort field it goes [SortField]
Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF
ield]="I",3)
Table: is set to Main Data Table
Sort: ascending
Criteria: nothing
But I am getting error msgs like invalid syntax!!
Does this look to you as if it will or should work?
Thanks for your help so far.
Jim

Easiest approach is to add a calculated field to your query:
SortField:
Switch([StatusField]="F",1,[StatusField]="J",2,[StatusField]="I",3)

Then sort ascending on this SortField field.

--
Ken Snell
<MS ACCESS MVP>

In a table I have a column with status letters F J or I which denotes
Members current Position.
In the Query I want to sort alphabetically all the F people first, then the
J people and then the I
people.
The F people where easy in the criteria box, or the J, or the I, but I have
been unable to
get the J and I people to join together and produce a complete list.
Help appreciated.
Jim

 

[Ken Snell]

You forgot the : after the name of the calculated field.

[SortField]:
Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF
ield]="I",3)



--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Not sure I've got this right.
My query field at present is JoiningStatus,
and the sort is set to ascending.
So creating a new sort field it goes [SortField]
Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF
ield]="I",3)
Table: is set to Main Data Table
Sort: ascending
Criteria: nothing
But I am getting error msgs like invalid syntax!!
Does this look to you as if it will or should work?
Thanks for your help so far.
Jim

Easiest approach is to add a calculated field to your query:
SortField:
Switch([StatusField]="F",1,[StatusField]="J",2,[StatusField]="I",3)

Then sort ascending on this SortField field.

--
Ken Snell
<MS ACCESS MVP>

In a table I have a column with status letters F J or I which denotes
Members current Position.
In the Query I want to sort alphabetically all the F people first,

then
the

J people and then the I
people.
The F people where easy in the criteria box, or the J, or the I, but I have
been unable to
get the J and I people to join together and produce a complete list.
Help appreciated.
Jim

 

[Jim]

Hi Ken
Yes, thanks, sadly the sort only shows the F members.
Any ideas?
Thanks
Jim

You forgot the : after the name of the calculated field.

[SortField]:
Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF
ield]="I",3)



--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Not sure I've got this right.
My query field at present is JoiningStatus,
and the sort is set to ascending.
So creating a new sort field it goes [SortField]

Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF

ield]="I",3)
Table: is set to Main Data Table
Sort: ascending
Criteria: nothing
But I am getting error msgs like invalid syntax!!
Does this look to you as if it will or should work?
Thanks for your help so far.
Jim

Easiest approach is to add a calculated field to your query:
SortField:
Switch([StatusField]="F",1,[StatusField]="J",2,[StatusField]="I",3)

Then sort ascending on this SortField field.

--
Ken Snell
<MS ACCESS MVP>

In a table I have a column with status letters F J or I which denotes
Members current Position.
In the Query I want to sort alphabetically all the F people first, then
the
J people and then the I
people.
The F people where easy in the criteria box, or the J, or the I, but I
have
been unable to
get the J and I people to join together and produce a complete list.
Help appreciated.
Jim

 

[Jim]

Hi Ken
Sorry the F only sort was my problem.
The result is now that the sort order alphabetically of surnames seems to
overide the F J and I
sort. does it need another query now to sort the sort, so to speak?
Regards
Jim

You forgot the : after the name of the calculated field.

[SortField]:
Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF
ield]="I",3)



--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Not sure I've got this right.
My query field at present is JoiningStatus,
and the sort is set to ascending.
So creating a new sort field it goes [SortField]

Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF

ield]="I",3)
Table: is set to Main Data Table
Sort: ascending
Criteria: nothing
But I am getting error msgs like invalid syntax!!
Does this look to you as if it will or should work?
Thanks for your help so far.
Jim

Easiest approach is to add a calculated field to your query:
SortField:
Switch([StatusField]="F",1,[StatusField]="J",2,[StatusField]="I",3)

Then sort ascending on this SortField field.

--
Ken Snell
<MS ACCESS MVP>

In a table I have a column with status letters F J or I which denotes
Members current Position.
In the Query I want to sort alphabetically all the F people first, then
the
J people and then the I
people.
The F people where easy in the criteria box, or the J, or the I, but I
have
been unable to
get the J and I people to join together and produce a complete list.
Help appreciated.
Jim

 

[Jim]

Hi John
Yes, because sort and criteria are in the same column hence the *confusion*.
I have a column of F, J and I, but are all mixed up, and using Kens Switch
idea is not giving me all the F's, J's and I's group together, let alone
then sorting the names
afterwards!!
I thought this was going to be easier than is turning out to be!!
Is there any other way of approaching this one?
Jim

 

[Ken Snell]

Move the calculated field to be the first field in the query. Queries sort
precendence is from left to right with respect to the fields.

--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Sorry the F only sort was my problem.
The result is now that the sort order alphabetically of surnames seems to
overide the F J and I
sort. does it need another query now to sort the sort, so to speak?
Regards
Jim

You forgot the : after the name of the calculated field.

[SortField]:

Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF

ield]="I",3)



--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Not sure I've got this right.
My query field at present is JoiningStatus,
and the sort is set to ascending.
So creating a new sort field it goes [SortField]

Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF

ield]="I",3)
Table: is set to Main Data Table
Sort: ascending
Criteria: nothing
But I am getting error msgs like invalid syntax!!
Does this look to you as if it will or should work?
Thanks for your help so far.
Jim


Easiest approach is to add a calculated field to your query:
SortField:
Switch([StatusField]="F",1,[StatusField]="J",2,[StatusField]="I",3)

Then sort ascending on this SortField field.

--
Ken Snell
<MS ACCESS MVP>

In a table I have a column with status letters F J or I which denotes
Members current Position.
In the Query I want to sort alphabetically all the F people first, then
the
J people and then the I
people.
The F people where easy in the criteria box, or the J, or the I,

but

I

 

[Jim]

Hi Ken
Right, the orders are now grouped F, I and J and not F, J,and I as expected.
The alphabetical sort is not right either at this stage.
Thanks for your help so far, nearly there I hope.
Jim

Move the calculated field to be the first field in the query. Queries sort
precendence is from left to right with respect to the fields.

--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Sorry the F only sort was my problem.
The result is now that the sort order alphabetically of surnames seems to
overide the F J and I
sort. does it need another query now to sort the sort, so to speak?
Regards
Jim

You forgot the : after the name of the calculated field.

[SortField]:

Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF

ield]="I",3)



--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Not sure I've got this right.
My query field at present is JoiningStatus,
and the sort is set to ascending.
So creating a new sort field it goes [SortField]

Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF

ield]="I",3)
Table: is set to Main Data Table
Sort: ascending
Criteria: nothing
But I am getting error msgs like invalid syntax!!
Does this look to you as if it will or should work?
Thanks for your help so far.
Jim


Easiest approach is to add a calculated field to your query:
SortField:
Switch([StatusField]="F",1,[StatusField]="J",2,[StatusField]="I",3)

Then sort ascending on this SortField field.

--
Ken Snell
<MS ACCESS MVP>

In a table I have a column with status letters F J or I which denotes
Members current Position.
In the Query I want to sort alphabetically all the F people first,
then
the
J people and then the I
people.
The F people where easy in the criteria box, or the J, or the I,

but

I
have
been unable to
get the J and I people to join together and produce a complete list.
Help appreciated.
Jim

 

[John Vinson]

Hi John
Yes, because sort and criteria are in the same column hence the *confusion*.
I have a column of F, J and I, but are all mixed up, and using Kens Switch
idea is not giving me all the F's, J's and I's group together, let alone
then sorting the names
afterwards!!
I thought this was going to be easier than is turning out to be!!

That's because you're making it harder than it needs to be.

If you want to see all the records, whether F, I or J, then don't put
ANYTHING AT ALL on the Criteria line. Just use Ken's calculated field
and put Ascending on the Sort line under *that* field.

 

[Ken Snell]

Post your SQL statement for the query. Let's see what you've got now. (Open
the query in design view, then select SQL from the View icon on the toolbar.
Copy the statement there and paste it into a message.)

--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Right, the orders are now grouped F, I and J and not F, J,and I as expected.
The alphabetical sort is not right either at this stage.
Thanks for your help so far, nearly there I hope.
Jim

Move the calculated field to be the first field in the query. Queries sort
precendence is from left to right with respect to the fields.

--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Sorry the F only sort was my problem.
The result is now that the sort order alphabetically of surnames seems to
overide the F J and I
sort. does it need another query now to sort the sort, so to speak?
Regards
Jim


You forgot the : after the name of the calculated field.

[SortField]:

Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF

ield]="I",3)



--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Not sure I've got this right.
My query field at present is JoiningStatus,
and the sort is set to ascending.
So creating a new sort field it goes [SortField]

Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF

ield]="I",3)
Table: is set to Main Data Table
Sort: ascending
Criteria: nothing
But I am getting error msgs like invalid syntax!!
Does this look to you as if it will or should work?
Thanks for your help so far.
Jim


Easiest approach is to add a calculated field to your query:
SortField:
Switch([StatusField]="F",1,[StatusField]="J",2,[StatusField]="I",3)

Then sort ascending on this SortField field.

--
Ken Snell
<MS ACCESS MVP>

In a table I have a column with status letters F J or I which
denotes
Members current Position.
In the Query I want to sort alphabetically all the F people first,
then
the
J people and then the I
people.
The F people where easy in the criteria box, or the J, or the

I,
but

I
have
been unable to
get the J and I people to join together and produce a complete list.
Help appreciated.
Jim

 

[Jim]

Hi Ken
Herewith SQL as asked for.
By putting order in ascending, the F, J and I's are now mixed,
wheras unsorted it produced a group of F then I then J unsorted!
Many thanks for patience.
Jim

SELECT
Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF
ield]="I",3) AS Sort, [Main Data Table].JoiningStatus, [Status] & " " &
[FirstName] & " " & [LastName] & ", " AS FullName, [Address1] & (",
"+[Address2]) & (", "+[Town]) & (", "+[County]) & (". "+[PostCode]) AS
FullAddress, [Honours] & " " & [LGR] & " " & "PM "+[Past Masters of] & " "
AS FullTitles, [Main Data Table].Telephone, [Main Data Table].ActiveMember,
[Main Data Table].email, [Main Data Table].[Present Post], [Main Data
Table].[Present Post 2], [Main Data Table].LastName, [Main Data
Table].Title, [Main Data Table].Status, [Main Data Table].FirstName, [Main
Data Table].Address1, [Main Data Table].Address2, [Main Data Table].Town,
[Main Data Table].County, [Main Data Table].PostCode, [Main Data Table].LGR,
[Main Data Table].Honours, [Main Data Table].[Past Masters of]
FROM [Main Data Table]
WHERE ((([Main Data Table].ActiveMember)=Yes))
ORDER BY
Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF
ield]="I",3);

Post your SQL statement for the query. Let's see what you've got now. (Open
the query in design view, then select SQL from the View icon on the toolbar.
Copy the statement there and paste it into a message.)

--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Right, the orders are now grouped F, I and J and not F, J,and I as expected.
The alphabetical sort is not right either at this stage.
Thanks for your help so far, nearly there I hope.
Jim

Move the calculated field to be the first field in the query. Queries sort
precendence is from left to right with respect to the fields.

--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Sorry the F only sort was my problem.
The result is now that the sort order alphabetically of surnames

seems
to

overide the F J and I
sort. does it need another query now to sort the sort, so to speak?
Regards
Jim


You forgot the : after the name of the calculated field.

[SortField]:

Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF

ield]="I",3)



--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Not sure I've got this right.
My query field at present is JoiningStatus,
and the sort is set to ascending.
So creating a new sort field it goes [SortField]

Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF

ield]="I",3)
Table: is set to Main Data Table
Sort: ascending
Criteria: nothing
But I am getting error msgs like invalid syntax!!
Does this look to you as if it will or should work?
Thanks for your help so far.
Jim


Easiest approach is to add a calculated field to your query:
SortField:
Switch([StatusField]="F",1,[StatusField]="J",2,[StatusField]="I",3)

Then sort ascending on this SortField field.

--
Ken Snell
<MS ACCESS MVP>

In a table I have a column with status letters F J or I which
denotes
Members current Position.
In the Query I want to sort alphabetically all the F people first,
then
the
J people and then the I
people.
The F people where easy in the criteria box, or the J, or

the

I,

 

[Ken Snell]

Nothing jumps out at me as being incorrect in this SQL....

If you can zip up an example of this database and email it to me (remove
this is not real from my reply email address), I'll take a look to see what
may be "under the covers".

--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Herewith SQL as asked for.
By putting order in ascending, the F, J and I's are now mixed,
wheras unsorted it produced a group of F then I then J unsorted!
Many thanks for patience.
Jim

SELECT
Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF
ield]="I",3) AS Sort, [Main Data Table].JoiningStatus, [Status] & " " &
[FirstName] & " " & [LastName] & ", " AS FullName, [Address1] & (",
"+[Address2]) & (", "+[Town]) & (", "+[County]) & (". "+[PostCode]) AS
FullAddress, [Honours] & " " & [LGR] & " " & "PM "+[Past Masters of] & " "
AS FullTitles, [Main Data Table].Telephone, [Main Data Table].ActiveMember,
[Main Data Table].email, [Main Data Table].[Present Post], [Main Data
Table].[Present Post 2], [Main Data Table].LastName, [Main Data
Table].Title, [Main Data Table].Status, [Main Data Table].FirstName, [Main
Data Table].Address1, [Main Data Table].Address2, [Main Data Table].Town,
[Main Data Table].County, [Main Data Table].PostCode, [Main Data Table].LGR,
[Main Data Table].Honours, [Main Data Table].[Past Masters of]
FROM [Main Data Table]
WHERE ((([Main Data Table].ActiveMember)=Yes))
ORDER BY
Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF
ield]="I",3);

Post your SQL statement for the query. Let's see what you've got now. (Open
the query in design view, then select SQL from the View icon on the toolbar.
Copy the statement there and paste it into a message.)

--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Right, the orders are now grouped F, I and J and not F, J,and I as expected.
The alphabetical sort is not right either at this stage.
Thanks for your help so far, nearly there I hope.
Jim


Move the calculated field to be the first field in the query.

Queries
sort

precendence is from left to right with respect to the fields.

--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Sorry the F only sort was my problem.
The result is now that the sort order alphabetically of surnames seems
to
overide the F J and I
sort. does it need another query now to sort the sort, so to speak?
Regards
Jim


You forgot the : after the name of the calculated field.

[SortField]:

Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF

ield]="I",3)



--
Ken Snell
<MS ACCESS MVP>

Hi Ken
Not sure I've got this right.
My query field at present is JoiningStatus,
and the sort is set to ascending.
So creating a new sort field it goes [SortField]

Switch([JoiningStatusField]="F",1,[JoiningStatusField]="J",2,[JoiningStatusF

ield]="I",3)
Table: is set to Main Data Table
Sort: ascending
Criteria: nothing
But I am getting error msgs like invalid syntax!!
Does this look to you as if it will or should work?
Thanks for your help so far.
Jim


Easiest approach is to add a calculated field to your query:
SortField:

Switch([StatusField]="F",1,[StatusField]="J",2,[StatusField]="I",3)

Then sort ascending on this SortField field.

--
Ken Snell
<MS ACCESS MVP>

In a table I have a column with status letters F J or I which
denotes
Members current Position.
In the Query I want to sort alphabetically all the F people
first,
then
the
J people and then the I
people.
The F people where easy in the criteria box, or the J, or

the

I,
but
I
have
been unable to
get the J and I people to join together and produce a complete
list.
Help appreciated.
Jim