Prev: Possible Report Issue
Next: Acess DB - list of worldwide contacts - can I show them on a m
From: Steve on 11 Dec 2009 12:02
You ought to consider making some small changes to your database and then
you would not have this problem. To start, you need tables that look
something like:
TblCaseHandler
CaseHandlerID
FirstName
LastName
CaseHandlerIDNumber (101, 103, 104)
etc

TblClient
ClientID
etc

TblCase
CaseID
CaseHandlerID
ClientID
CaseDate

In TblCaseHandler, you could consider using CaseHandlerID as the number
assigned to an individual person who will handle the case. The advantage to
this is that it is automatically generated by Access and is guaranteed to be
unique for each case handler. If this is acceptable, you don't need
CaseHandlerIDNumber.

It is not clear what is meant by sequential number for the total number of
reports or cases for the year. Assuming that it is an unique number assigned
to each case, you could consider using CaseID as the number assigned to each
case. The advantage to this is that it is automatically generated by Access
and is guaranteed to be unique for each case.

Now you can create a query that includes the "hyphenated" number. The query
needs to include TblCaseHandler and TblCase and perhaps TblClient. The first
field in the query needs to be:
CaseYear = Year(CaseDate)
and the second field in the query needs to be:
CaseMonth = Month(CaseDate)
Assuming you use CaseHandlerID as the number assigned to an individual
person who will handle the case, CaseHandlerID needs to be the third field
in the query. Assuming you use CaseID as the number assigned to each case,
CaseID needs to be the fourth field in the query. The fifth field in the
query can then be the hyphenated field:
CaseIDNumber = CaseYear & "-" & CaseMonth & "-" & CaseHandlerID & "-" &
CaseID

Now you can easily sort the records in the query the way you want by sorting
CaseYear ascending, sorting CaseMonth ascending sorting CaseID ascending.

Steve
santus(a)penn.com





"David Leonard" <DavidLeonard(a)discussions.microsoft.com> wrote in message
news:2B146CD7-401F-4CB2-AE68-602924ED3350(a)microsoft.com...
>A little assistance please. I not sure that "hyphenated" is actually a
>word,
> but anyway....
>
> In my database (Access 2007) I have a field that contains a "hyphenated"
> number, i.e.: 09-12-108-1234 for a report or case number. The first two
> sections represent the current year and the current month. The third
> section
> is a number assigned to an individual person who will handle the case.
> The
> fourth section is a sequential number for the total number of reports or
> cases for the year.
>
> My problem is I do not know how to sort this number so that it will be
> sequential for the year, month and sequential number. The number in the
> third section will vary with the individual completing the report, i.e.:
> it
> could be for 101 (Jim) or 103 (Bob) or 104 (John), etc.
>
> Can I do a sort based on the three other sections and ignore the second
> section and still have the cases sorted sequentially? If I can do this,
> HOW
> can I do this?
>
> Your assistance will be greatly appreciated.


From: Linq Adams via AccessMonster.com on 11 Dec 2009 20:17
How about a calculated field in the query

SortField: Replace([HyphenatedField],"-","")

then sort on the field SortField?

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200912/1

From: Steve on 12 Dec 2009 12:12
Good thought but that won't work! The sort needs to be on Year, Month and
CaseID. SortField would give a different sort result because CaseHandlerID
is in the middle.

Steve
santus(a)penn.com


"Linq Adams via AccessMonster.com" <u28780(a)uwe> wrote in message
news:a07430045d204(a)uwe...
> How about a calculated field in the query
>
> SortField: Replace([HyphenatedField],"-","")
>
> then sort on the field SortField?
>
> --
> There's ALWAYS more than one way to skin a cat!
>
> Answers/posts based on Access 2000/2003
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200912/1
>


From: David Leonard on 15 Dec 2009 15:01
Thanks to all that responded..... I really appreciate your generous
assistance..... now I just have to experiment with my new ffound knowledge
and my database.

Again, my sincere appreciation !!!

"David Leonard" wrote:

> A little assistance please. I not sure that "hyphenated" is actually a word,
> but anyway....
>
> In my database (Access 2007) I have a field that contains a "hyphenated"
> number, i.e.: 09-12-108-1234 for a report or case number. The first two
> sections represent the current year and the current month. The third section
> is a number assigned to an individual person who will handle the case. The
> fourth section is a sequential number for the total number of reports or
> cases for the year.
>
> My problem is I do not know how to sort this number so that it will be
> sequential for the year, month and sequential number. The number in the
> third section will vary with the individual completing the report, i.e.: it
> could be for 101 (Jim) or 103 (Bob) or 104 (John), etc.
>
> Can I do a sort based on the three other sections and ignore the second
> section and still have the cases sorted sequentially? If I can do this, HOW
> can I do this?
>
> Your assistance will be greatly appreciated.
First  |  Prev  | 
Pages: 1 2
Prev: Possible Report Issue
Next: Acess DB - list of worldwide contacts - can I show them on a m