Expert advice for Year dependant ID

  • Thread starter Thread starter h2fcell
  • Start date Start date
H

h2fcell

Hello,
I’m new to Access 2007 and would like some expert advice on how to
accomplish a task. I have a form I’m using to enter meeting summaries into a
table. I give each meeting a unique ID consisting of the year part of the
date the meeting took place followed by a hyphen and a unique number.
Like
2009-1
2009-2
2009-3
2010-4
2009-5
2010-6

I use the following expression in a query to build that meeting ID
MeetingID: DatePart("yyyy",[MeetingDate]) & "-" & [ID]

I currently have the unique number ID created using the “Default Valueâ€
=Nz(DMax("[ID]","Table1"),0)+1

But, I need to have the ID begin at 1 for every different year. Eventually
my MeetingID list should look like the below:
2009-1
2009-2
2009-3
2008-1
2010-1
2008-2
2009-4
2010-2….

Any suggestions are welcomed.
 
h2fcell said:
I’m new to Access 2007 and would like some expert advice on how to
accomplish a task. I have a form I’m using to enter meeting summaries into a
table. I give each meeting a unique ID consisting of the year part of the
date the meeting took place followed by a hyphen and a unique number.
Like
2009-1
2009-2
2009-3
2010-4
2009-5
2010-6

I use the following expression in a query to build that meeting ID
MeetingID: DatePart("yyyy",[MeetingDate]) & "-" & [ID]

I currently have the unique number ID created using the “Default Value”
=Nz(DMax("[ID]","Table1"),0)+1

But, I need to have the ID begin at 1 for every different year. Eventually
my MeetingID list should look like the below:
2009-1
2009-2
2009-3
2008-1
2010-1
2008-2
2009-4
2010-2….
Click to expand...


That's a violation of the rules of relational database
normalization. The date should be stored in its own field
and the sequence number in a separate field. When you want
to display it in a report (or another form), its easy to do
using the expression =Year(datefield)) & sequencefield

The sequence number should be set in the form's BeforeUpdate
event, but you can do it your way if there is never more
than one user at a time. The code would be like:
Me.sequencefield = Nz(DMax("sequencefield", "thetable",
"Year(datefield) = " & Year(Date)), 0) + 1

If you are too far down your road to make that kind of
change, then you can do what you asked (only for a single
user) with a DefaultValue expression like:

Nz(DMax("[ID]","Table1","Left(ID,4)=Format(Date(),""yyyy""")),0)+1
 
I would change the design to use meetingDateTime as the PK of the Meeting
table, or just meetingDate if IT IS NEVER CONCEIVABLE to have more than one
meeting in a day. Then you can calculate the identifying code you want to
use as yyyy-nnn where yyyy is the 4 digit year and nnn is the meeting number
within that year. The general goal is storing only base data, and computing
anything that can be calculated. If you store something which can be
calculated it means there are two different ways to know the same fact (the
stored value and the calculation), and eventually you will find two
different answers without any way to know which is correct.

Select
M.meetingDateTime
, year(M.meetingDateTime) & "-" & (1 + count(M2.meetingDateTime) as
MeetingCode
From Meeting as M
Left Join Meeting as M2
On year(M.meetingDateTime) = year(M2.meetingDateTime)
And M2.meetingDateTime < M.meetingDateTime
Group By M.meetingDateTime

In this query, M2 is the list of meetings that occured in the same year as
the M meeting we are looking at, with an earlier date.
We use a Left Join because otherwise we would exclude the first meeting of
the year, which has no predecessors.
So (1 + count(M2.meetingDateTime) is the sequence number for each meeting
within the year of that meeting.

However, I have to say I'm not sure Access accepts that SQL for the join
condition, so you may need to work on that query. If necessary, you can
compute the meeting sequence number in a separate query and join that query
to the Meeting table to get this result.

If instead you want to use your scheme of the meetingDate and the
meetingSequenceNumberInYear, you need to manually assign that sequence
number. You can't use an autonumber.
 
Marsh,
Thanks for the assist. The DefaultValue expression you gave me
Nz(DMax("[ID]","Table1","Left(ID,4)=Format(Date(),""yyyy""")),0)+1
returned an error.

I'll try to work with it and see if I can make it work.
Thanks for your help.

Marshall Barton said:
h2fcell said:
I’m new to Access 2007 and would like some expert advice on how to
accomplish a task. I have a form I’m using to enter meeting summaries into a
table. I give each meeting a unique ID consisting of the year part of the
date the meeting took place followed by a hyphen and a unique number.
Like
2009-1
2009-2
2009-3
2010-4
2009-5
2010-6

I use the following expression in a query to build that meeting ID
MeetingID: DatePart("yyyy",[MeetingDate]) & "-" & [ID]

I currently have the unique number ID created using the “Default Valueâ€
=Nz(DMax("[ID]","Table1"),0)+1

But, I need to have the ID begin at 1 for every different year. Eventually
my MeetingID list should look like the below:
2009-1
2009-2
2009-3
2008-1
2010-1
2008-2
2009-4
2010-2….
Click to expand...


That's a violation of the rules of relational database
normalization. The date should be stored in its own field
and the sequence number in a separate field. When you want
to display it in a report (or another form), its easy to do
using the expression =Year(datefield)) & sequencefield

The sequence number should be set in the form's BeforeUpdate
event, but you can do it your way if there is never more
than one user at a time. The code would be like:
Me.sequencefield = Nz(DMax("sequencefield", "thetable",
"Year(datefield) = " & Year(Date)), 0) + 1

If you are too far down your road to make that kind of
change, then you can do what you asked (only for a single
user) with a DefaultValue expression like:

Nz(DMax("[ID]","Table1","Left(ID,4)=Format(Date(),""yyyy""")),0)+1
Click to expand...
 
Describing this simpler.
I have a table “Table1†with the following Field Names - Data Type
ID - Number --- Primary Key Indexed (No Duplicates)
Topic - Text
Date - Date/Time --- With Format = Short Date

I have a query “Table1 Extended†that uses all the fields in “Table1†and
adds an additional field for each record.
DateYear: Year([Date])

Right now “Table1†contains the following 10 records.
ID Topic Date
1 NewHire 2/25/2008
2 NewHire 2/25/2008
3 VacationRec 3/26/2008
4 NewHire 1/14/2009
5 SickLeave 4/15/2008
6 NewHire 2/10/2009
7 Resolution 5/16/2008
8 Resolution 2/3/2009
9 Bid 4/12/2008
10 Bid 2/15/2009

The datasheet view of the query “Table1 Extended†looks as follows.
ID Topic Date DateYear
1 NewHire 2/25/2008 2008
2 NewHire 2/25/2008 2008
3 VacationRec 3/26/2008 2008
4 NewHire 1/14/2009 2009
5 SickLeave 4/15/2008 2008
6 NewHire 2/10/2009 2009
7 Resolution 5/16/2008 2008
8 Resolution 2/3/2009 2009
9 Bid 4/12/2008 2008
10 Bid 2/15/2009 2009

I would like the query “Table1 Extended†to add an additional field that
gives each record a sequence number by year as follows:

ID Topic Date DateYear SeqNum
1 NewHire 2/25/2008 2008 1
2 NewHire 2/25/2008 2008 2
3 VacationRec 3/26/2008 2008 3
4 NewHire 1/14/2009 2009 1
5 SickLeave 4/15/2008 2008 4
6 NewHire 2/10/2009 2009 2
7 Resolution 5/16/2008 2008 5
8 Resolution 2/3/2009 2009 3
9 Bid 4/12/2008 2008 6
10 Bid 2/15/2009 2009 4

I’ve tried SeqNum: Nz(DMax("SeqNum","Table1 Extended","[DateYear] = " &
Year([Date])),0)+1
But that causes an error.

Any help in building the right expression is greatly appreciated.


Paul Shapiro said:
I would change the design to use meetingDateTime as the PK of the Meeting
table, or just meetingDate if IT IS NEVER CONCEIVABLE to have more than one
meeting in a day. Then you can calculate the identifying code you want to
use as yyyy-nnn where yyyy is the 4 digit year and nnn is the meeting number
within that year. The general goal is storing only base data, and computing
anything that can be calculated. If you store something which can be
calculated it means there are two different ways to know the same fact (the
stored value and the calculation), and eventually you will find two
different answers without any way to know which is correct.

Select
M.meetingDateTime
, year(M.meetingDateTime) & "-" & (1 + count(M2.meetingDateTime) as
MeetingCode
From Meeting as M
Left Join Meeting as M2
On year(M.meetingDateTime) = year(M2.meetingDateTime)
And M2.meetingDateTime < M.meetingDateTime
Group By M.meetingDateTime

In this query, M2 is the list of meetings that occured in the same year as
the M meeting we are looking at, with an earlier date.
We use a Left Join because otherwise we would exclude the first meeting of
the year, which has no predecessors.
So (1 + count(M2.meetingDateTime) is the sequence number for each meeting
within the year of that meeting.

However, I have to say I'm not sure Access accepts that SQL for the join
condition, so you may need to work on that query. If necessary, you can
compute the meeting sequence number in a separate query and join that query
to the Meeting table to get this result.

If instead you want to use your scheme of the meetingDate and the
meetingSequenceNumberInYear, you need to manually assign that sequence
number. You can't use an autonumber.

h2fcell said:
Hello,
I’m new to Access 2007 and would like some expert advice on how to
accomplish a task. I have a form I’m using to enter meeting summaries
into a
table. I give each meeting a unique ID consisting of the year part of the
date the meeting took place followed by a hyphen and a unique number.
Like
2009-1
2009-2
2009-3
2010-4
2009-5
2010-6

I use the following expression in a query to build that meeting ID
MeetingID: DatePart("yyyy",[MeetingDate]) & "-" & [ID]

I currently have the unique number ID created using the “Default Valueâ€
=Nz(DMax("[ID]","Table1"),0)+1

But, I need to have the ID begin at 1 for every different year.
Eventually
my MeetingID list should look like the below:
2009-1
2009-2
2009-3
2008-1
2010-1
2008-2
2009-4
2010-2….

Any suggestions are welcomed.
Click to expand...
Click to expand...
 
You could try renaming the date field. Date is a reserved word and that
could be confusing Access.

Your sample data has 2 meetings on the same date. You have to decide if they
should both hav ethe same sequence number, giving a gap in your sequence
numbers, or if perhaps the one with the lower ID should get the lower
sequence number and the higher ID the next sequence number, avoiding a gap.

Using that approach to avoid duplicates, I tested this SQL and it worked
correctly. It's the same concept as the untested sql I suggested earlier.

SELECT
M.ID
, M.Topic
, M.MeetingDate
, Year([MeetingDate]) AS MeetingYear
, (
Select 1+count(*)
From TestTable as M2
Where
Year(M2.MeetingDate) = Year(M.MeetingDate)
And (
M2.MeetingDate < M.MeetingDate
Or (
M2.MeetingDate = M.MeetingDate
And M2.ID<M.ID
)
)
) AS Sequence
FROM TestTable AS M;

h2fcell said:
Describing this simpler.
I have a table “Table1†with the following Field Names - Data Type
ID - Number --- Primary Key Indexed (No Duplicates)
Topic - Text
Date - Date/Time --- With Format = Short Date

I have a query “Table1 Extended†that uses all the fields in “Table1†and
adds an additional field for each record.
DateYear: Year([Date])

Right now “Table1†contains the following 10 records.
ID Topic Date
1 NewHire 2/25/2008
2 NewHire 2/25/2008
3 VacationRec 3/26/2008
4 NewHire 1/14/2009
5 SickLeave 4/15/2008
6 NewHire 2/10/2009
7 Resolution 5/16/2008
8 Resolution 2/3/2009
9 Bid 4/12/2008
10 Bid 2/15/2009

The datasheet view of the query “Table1 Extended†looks as follows.
ID Topic Date DateYear
1 NewHire 2/25/2008 2008
2 NewHire 2/25/2008 2008
3 VacationRec 3/26/2008 2008
4 NewHire 1/14/2009 2009
5 SickLeave 4/15/2008 2008
6 NewHire 2/10/2009 2009
7 Resolution 5/16/2008 2008
8 Resolution 2/3/2009 2009
9 Bid 4/12/2008 2008
10 Bid 2/15/2009 2009

I would like the query “Table1 Extended†to add an additional field that
gives each record a sequence number by year as follows:

ID Topic Date DateYear SeqNum
1 NewHire 2/25/2008 2008 1
2 NewHire 2/25/2008 2008 2
3 VacationRec 3/26/2008 2008 3
4 NewHire 1/14/2009 2009 1
5 SickLeave 4/15/2008 2008 4
6 NewHire 2/10/2009 2009 2
7 Resolution 5/16/2008 2008 5
8 Resolution 2/3/2009 2009 3
9 Bid 4/12/2008 2008 6
10 Bid 2/15/2009 2009 4

I’ve tried SeqNum: Nz(DMax("SeqNum","Table1 Extended","[DateYear] = " &
Year([Date])),0)+1
But that causes an error.

Any help in building the right expression is greatly appreciated.


Paul Shapiro said:
I would change the design to use meetingDateTime as the PK of the Meeting
table, or just meetingDate if IT IS NEVER CONCEIVABLE to have more than
one
meeting in a day. Then you can calculate the identifying code you want to
use as yyyy-nnn where yyyy is the 4 digit year and nnn is the meeting
number
within that year. The general goal is storing only base data, and
computing
anything that can be calculated. If you store something which can be
calculated it means there are two different ways to know the same fact
(the
stored value and the calculation), and eventually you will find two
different answers without any way to know which is correct.

Select
M.meetingDateTime
, year(M.meetingDateTime) & "-" & (1 + count(M2.meetingDateTime) as
MeetingCode
From Meeting as M
Left Join Meeting as M2
On year(M.meetingDateTime) = year(M2.meetingDateTime)
And M2.meetingDateTime < M.meetingDateTime
Group By M.meetingDateTime

In this query, M2 is the list of meetings that occured in the same year
as
the M meeting we are looking at, with an earlier date.
We use a Left Join because otherwise we would exclude the first meeting
of
the year, which has no predecessors.
So (1 + count(M2.meetingDateTime) is the sequence number for each meeting
within the year of that meeting.

However, I have to say I'm not sure Access accepts that SQL for the join
condition, so you may need to work on that query. If necessary, you can
compute the meeting sequence number in a separate query and join that
query
to the Meeting table to get this result.

If instead you want to use your scheme of the meetingDate and the
meetingSequenceNumberInYear, you need to manually assign that sequence
number. You can't use an autonumber.

h2fcell said:
Hello,
I’m new to Access 2007 and would like some expert advice on how to
accomplish a task. I have a form I’m using to enter meeting summaries
into a
table. I give each meeting a unique ID consisting of the year part of
the
date the meeting took place followed by a hyphen and a unique number.
Like
2009-1
2009-2
2009-3
2010-4
2009-5
2010-6

I use the following expression in a query to build that meeting ID
MeetingID: DatePart("yyyy",[MeetingDate]) & "-" & [ID]

I currently have the unique number ID created using the “Default Valueâ€
=Nz(DMax("[ID]","Table1"),0)+1

But, I need to have the ID begin at 1 for every different year.
Eventually
my MeetingID list should look like the below:
2009-1
2009-2
2009-3
2008-1
2010-1
2008-2
2009-4
2010-2….

Any suggestions are welcomed.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top