sorting

[Guest]

Kinda of new sor sorry for my term., but is there a way to dictate the sort
order within a field.

Example I have a several fund #'s 01, 02, 33, 34, 35, 90 91 95. I would
like my sort to put 01, 90, 91 and 95 first and the sort the rest out in
numberic order.

 

[Guest]

Add a new column to your table. Make it a number / integer field. Manually
populate it with the sort order that you want to see and sort on this field.

 

[kingston via AccessMonster.com]

Create a field called SortOrder (or whatever) that is tied to the fund
numbers. I suggest making it an integer field with a default value of 99 for
example. Then set the order for the four funds and return any results based

 

[Guest]

Is there any examples of this for me to view. I'm such a visual person.

 

[kingston via AccessMonster.com]

Go into design view of the table and add the field. If you cannot go into
design view (e.g. the database doesn't belong to you), create a new table
with two fields: SortOrder and Fund#. Then link this new table to the
existing one. Basically, all you're doing is adding a column like in a
spreadsheet and entering the sort order for the rows of the spreadsheet.
Sorry, I don't know of any examples on the web that you could view.

Is there any examples of this for me to view. I'm such a visual person.

Create a field called SortOrder (or whatever) that is tied to the fund
numbers. I suggest making it an integer field with a default value of 99 for

[quoted text clipped - 7 lines]

 

[Guest]

Perfect. Thanks so much. I thought about that on the way home last night
and completed the additional table. Now it works. I have an additional
question. The detail I am working off of is a excel spreadsheet that I
imported to access. In saying this is there a way to auto populate records
within a field that do not have data.

Example:

Table consists of Payments and adjustments for individuals. If a payment is
received a dollar amount reflects in my Total Payment field, however if an
adjustment is reflected the total Payment field is blank. I would like to
possibly auto populate it with zeros. Right now I am manually adding this
data.

Any assistance would be great. Thanks again.

Go into design view of the table and add the field. If you cannot go into
design view (e.g. the database doesn't belong to you), create a new table
with two fields: SortOrder and Fund#. Then link this new table to the
existing one. Basically, all you're doing is adding a column like in a
spreadsheet and entering the sort order for the rows of the spreadsheet.
Sorry, I don't know of any examples on the web that you could view.

Is there any examples of this for me to view. I'm such a visual person.

Create a field called SortOrder (or whatever) that is tied to the fund
numbers. I suggest making it an integer field with a default value of 99 for

[quoted text clipped - 7 lines]

like my sort to put 01, 90, 91 and 95 first and the sort the rest out in
numberic order.

 

[kingston via AccessMonster.com]

If this is in a table, set default values for the fields in design mode. If
this is a linked datasource, create a query with the following and use the
query to feed your form, report, etc.:

Field: IIF(IsNull([LinkedField]),YourDefault,[LinkedField])

Perfect. Thanks so much. I thought about that on the way home last night
and completed the additional table. Now it works. I have an additional
question. The detail I am working off of is a excel spreadsheet that I
imported to access. In saying this is there a way to auto populate records
within a field that do not have data.

Example:

Table consists of Payments and adjustments for individuals. If a payment is
received a dollar amount reflects in my Total Payment field, however if an
adjustment is reflected the total Payment field is blank. I would like to
possibly auto populate it with zeros. Right now I am manually adding this
data.

Any assistance would be great. Thanks again.

Go into design view of the table and add the field. If you cannot go into
design view (e.g. the database doesn't belong to you), create a new table

[quoted text clipped - 10 lines]

 

[Guest]

I used Expr1: IIf(IsNull([Total Payment]),"0",[Total Payment]) but it didn't
work. It is a table I am working off of, but it would be over written each
week so I created a query as you suggested not included the Linked verbage.
What am I doing wrong?

If this is in a table, set default values for the fields in design mode. If
this is a linked datasource, create a query with the following and use the
query to feed your form, report, etc.:

Field: IIF(IsNull([LinkedField]),YourDefault,[LinkedField])

Perfect. Thanks so much. I thought about that on the way home last night
and completed the additional table. Now it works. I have an additional
question. The detail I am working off of is a excel spreadsheet that I
imported to access. In saying this is there a way to auto populate records
within a field that do not have data.

Example:

Table consists of Payments and adjustments for individuals. If a payment is
received a dollar amount reflects in my Total Payment field, however if an
adjustment is reflected the total Payment field is blank. I would like to
possibly auto populate it with zeros. Right now I am manually adding this
data.

Any assistance would be great. Thanks again.

Go into design view of the table and add the field. If you cannot go into
design view (e.g. the database doesn't belong to you), create a new table

[quoted text clipped - 10 lines]

like my sort to put 01, 90, 91 and 95 first and the sort the rest out in
numberic order.

 

[kingston via AccessMonster.com]

It looks like the field is numeric so "0" should be just plain 0.

I used Expr1: IIf(IsNull([Total Payment]),"0",[Total Payment]) but it didn't
work. It is a table I am working off of, but it would be over written each
week so I created a query as you suggested not included the Linked verbage.
What am I doing wrong?

If this is in a table, set default values for the fields in design mode. If
this is a linked datasource, create a query with the following and use the

[quoted text clipped - 23 lines]

 

[Guest]

Sorry I did use 0 and still nothing. Any idea why this would be?

It looks like the field is numeric so "0" should be just plain 0.

I used Expr1: IIf(IsNull([Total Payment]),"0",[Total Payment]) but it didn't
work. It is a table I am working off of, but it would be over written each
week so I created a query as you suggested not included the Linked verbage.
What am I doing wrong?

If this is in a table, set default values for the fields in design mode. If
this is a linked datasource, create a query with the following and use the

[quoted text clipped - 23 lines]

like my sort to put 01, 90, 91 and 95 first and the sort the rest out in
numberic order.

 

[kingston via AccessMonster.com]

What are the properties of the field in question? If this is a table that
can be cleared and refreshed (i.e. you can keep the data structure), set the
default value to 0 (if it is a numeric field) and then refresh the data.
Otherwise, it seems like the field [Total Payment] cannot be a Null so you
can test for an empty field instead:

Expr1: IIf(([Total Payment]=""),"0",[Total Payment])

Sorry I did use 0 and still nothing. Any idea why this would be?

It looks like the field is numeric so "0" should be just plain 0.

[quoted text clipped - 8 lines]

 

[Guest]

Thank. I have another question for you. regarding the same query.

This query is sorting by fund for a report. Made a separate fund table
listing sort order and linked it to the orig. table for sorting and
calculating. Pretty simple! I have sub totals added to the report.

Now, within the org. table is list "account #’s" and within the "account
#’s" are sub funds – 1st 3 digits nested in the 9 digit "account #’s". I
need to break my query down further for only one of the orig. funds 84 based
on this criteria and sub total for each sub fund.

Can you help me?

What are the properties of the field in question? If this is a table that
can be cleared and refreshed (i.e. you can keep the data structure), set the
default value to 0 (if it is a numeric field) and then refresh the data.
Otherwise, it seems like the field [Total Payment] cannot be a Null so you
can test for an empty field instead:

Expr1: IIf(([Total Payment]=""),"0",[Total Payment])

Sorry I did use 0 and still nothing. Any idea why this would be?

It looks like the field is numeric so "0" should be just plain 0.

[quoted text clipped - 8 lines]

like my sort to put 01, 90, 91 and 95 first and the sort the rest out in
numberic order.

 

[kingston via AccessMonster.com]

Create another grouping based on the first three digits of the account number:

Left([AcctNo],3)
You can do this in your report or in your query. It's probably easier to do
this in the query by creating a calculated field so that all you need to do
is pick it as a group for your report.

Thank. I have another question for you. regarding the same query.

This query is sorting by fund for a report. Made a separate fund table
listing sort order and linked it to the orig. table for sorting and
calculating. Pretty simple! I have sub totals added to the report.

Now, within the org. table is list "account #’s" and within the "account
#’s" are sub funds – 1st 3 digits nested in the 9 digit "account #’s". I
need to break my query down further for only one of the orig. funds 84 based
on this criteria and sub total for each sub fund.

Can you help me?

What are the properties of the field in question? If this is a table that
can be cleared and refreshed (i.e. you can keep the data structure), set the

[quoted text clipped - 11 lines]

 

[Guest]

great! Now How can I sort this info. Is there a query I can impletment
(note several transactions within each fund/sub-fund, which is the reason for
the sorting requst):

fund 84 by 001
002
003
004
005

Create another grouping based on the first three digits of the account number:

Left([AcctNo],3)
You can do this in your report or in your query. It's probably easier to do
this in the query by creating a calculated field so that all you need to do
is pick it as a group for your report.

Thank. I have another question for you. regarding the same query.

This query is sorting by fund for a report. Made a separate fund table
listing sort order and linked it to the orig. table for sorting and
calculating. Pretty simple! I have sub totals added to the report.

Now, within the org. table is list "account #’s" and within the "account
#’s" are sub funds – 1st 3 digits nested in the 9 digit "account #’s". I
need to break my query down further for only one of the orig. funds 84 based
on this criteria and sub total for each sub fund.

Can you help me?

What are the properties of the field in question? If this is a table that
can be cleared and refreshed (i.e. you can keep the data structure), set the

[quoted text clipped - 11 lines]

like my sort to put 01, 90, 91 and 95 first and the sort the rest out in
numberic order.

 

[Guest]

Here's my SQL Statement:
SELECT Fund_tbl.Fund, Fund_tbl.[Fund Sort], Fund_tbl.[Sort Order],
EData_tbl.Fund, EData_tbl.[Loan Number], EData_tbl.[Transaction Type],
EData_tbl.Principal, EData_tbl.Interest, EData_tbl.[Total Payment],
IIf(([Total Payment]=""),"0.00",[Total Payment]) AS Expr1, Left([Loan
Number],3) AS Expr2
FROM Fund_tbl LEFT JOIN EData_tbl ON Fund_tbl.Fund = EData_tbl.Fund
GROUP BY Fund_tbl.Fund, Fund_tbl.[Fund Sort], Fund_tbl.[Sort Order],
EData_tbl.Fund, EData_tbl.[Loan Number], EData_tbl.[Transaction Type],
EData_tbl.Principal, EData_tbl.Interest, EData_tbl.[Total Payment],
IIf(([Total Payment]=""),"0.00",[Total Payment]), Left([Loan Number],3)
HAVING (((EData_tbl.[Transaction Type])="Cash Payment" Or
(EData_tbl.[Transaction Type])="Adjustment" Or (EData_tbl.[Transaction
Type])="cash chargeback"))
ORDER BY Fund_tbl.[Sort Order], EData_tbl.Fund;

great! Now How can I sort this info. Is there a query I can impletment
(note several transactions within each fund/sub-fund, which is the reason for
the sorting requst):

fund 84 by 001
002
003
004
005

Create another grouping based on the first three digits of the account number:

Left([AcctNo],3)
You can do this in your report or in your query. It's probably easier to do
this in the query by creating a calculated field so that all you need to do
is pick it as a group for your report.

Thank. I have another question for you. regarding the same query.

This query is sorting by fund for a report. Made a separate fund table
listing sort order and linked it to the orig. table for sorting and
calculating. Pretty simple! I have sub totals added to the report.

Now, within the org. table is list "account #’s" and within the "account
#’s" are sub funds – 1st 3 digits nested in the 9 digit "account #’s". I
need to break my query down further for only one of the orig. funds 84 based
on this criteria and sub total for each sub fund.

Can you help me?

What are the properties of the field in question? If this is a table that
can be cleared and refreshed (i.e. you can keep the data structure), set the
[quoted text clipped - 11 lines]
like my sort to put 01, 90, 91 and 95 first and the sort the rest out in
numberic order.

 

[kingston via AccessMonster.com]

You can add another grouping in your report by fund and specify that you want
to sort by Expr2 within the group. You might have to include Expr2 in your
report. Or within the query, add the new field to the end of the ORDER BY
statement after Fund.

Here's my SQL Statement:
SELECT Fund_tbl.Fund, Fund_tbl.[Fund Sort], Fund_tbl.[Sort Order],
EData_tbl.Fund, EData_tbl.[Loan Number], EData_tbl.[Transaction Type],
EData_tbl.Principal, EData_tbl.Interest, EData_tbl.[Total Payment],
IIf(([Total Payment]=""),"0.00",[Total Payment]) AS Expr1, Left([Loan
Number],3) AS Expr2
FROM Fund_tbl LEFT JOIN EData_tbl ON Fund_tbl.Fund = EData_tbl.Fund
GROUP BY Fund_tbl.Fund, Fund_tbl.[Fund Sort], Fund_tbl.[Sort Order],
EData_tbl.Fund, EData_tbl.[Loan Number], EData_tbl.[Transaction Type],
EData_tbl.Principal, EData_tbl.Interest, EData_tbl.[Total Payment],
IIf(([Total Payment]=""),"0.00",[Total Payment]), Left([Loan Number],3)
HAVING (((EData_tbl.[Transaction Type])="Cash Payment" Or
(EData_tbl.[Transaction Type])="Adjustment" Or (EData_tbl.[Transaction
Type])="cash chargeback"))
ORDER BY Fund_tbl.[Sort Order], EData_tbl.Fund;

great! Now How can I sort this info. Is there a query I can impletment
(note several transactions within each fund/sub-fund, which is the reason for

[quoted text clipped - 31 lines]

 

[Guest]

Hi, please know that I do appreciate all your assistance. The more you
explain the more I understand. Anyway, I did a sort as you indicated in the
report. One problem though it sorts all funds and there sub funds. I need
to only the sub funds for only one of the funds 84. The rest of the data
needs to stay as is. Is there a query I can do to accomplish this task and
if so. Can you show me the formula?

Create another grouping based on the first three digits of the account number:

Left([AcctNo],3)
You can do this in your report or in your query. It's probably easier to do
this in the query by creating a calculated field so that all you need to do
is pick it as a group for your report.

Thank. I have another question for you. regarding the same query.

This query is sorting by fund for a report. Made a separate fund table
listing sort order and linked it to the orig. table for sorting and
calculating. Pretty simple! I have sub totals added to the report.

Now, within the org. table is list "account #’s" and within the "account
#’s" are sub funds – 1st 3 digits nested in the 9 digit "account #’s". I
need to break my query down further for only one of the orig. funds 84 based
on this criteria and sub total for each sub fund.

Can you help me?

What are the properties of the field in question? If this is a table that
can be cleared and refreshed (i.e. you can keep the data structure), set the

[quoted text clipped - 11 lines]

like my sort to put 01, 90, 91 and 95 first and the sort the rest out in
numberic order.