Ken & Jeff -
I totally understand your logic for wanting me to stop. However, let me
give you more details about the records that we will be capturing. With each
record, there will be a corresponding file (many binders, etc.) that go along
with the record. These binders, etc. will be filed in a large library
according to their tracking numbers. Each track of hard copy records, if my
plan for numbering works, will be filed in different areas. In order to make
finding records easier, I was hoping to assign sequential tracking numbers
for each track of records (Category A and Category B). So it wasn't just for
counting purposes, but for overall organizational purposes of both physical
records and digital records. BUT, I suppose that assigning the A-xxxx vs.
B-xxxx will do just that as well, without having to violate the integrity of
a database. I can also just assign the numbers to each record after entering
all the data as well, instead of having Access do it for me. Once I have
them all logged in Access, it will be simple to do something like that.
I really really really appreciate your expertise in leading me in the right
direction, gentlemen! So sorry to take up so much of your valuable time!!
Sharon
"KenSheridan via AccessMonster.com" wrote:
As Jeff says the key question here is why? As the numbers are, like a true
autonumber, arbitrary, it's difficult to see what purpose they fulfil. The
column cannot be the primary key obviously as there will be duplication
between categories. The primary key would have to be a composite one of the
category and serial number columns, it being a bad idea, as Jeff has
explained, to encode the category in the 'number' as this introduces
redundancy and the possibility of inconsistent data.
If there is a real reason why you need to serially number each row per
category in the order in which they are inserted into the table then I'd
suggest an alternative approach, which is to include a column DateTimeStamp
with a DefaultValue property of Now(). This will automatically contain the
date and time when each row is inserted. You can then number the subsets of
each category in date/time order in a query, e.g.
SELECT COUNT(*) AS SerialNumber, T1.Category, T1.SomeField
FROM YourTable AS T1 INNER JOIN YourTable AS T2
ON T2.DateTimeStamp = T1.DateTimeStamp
AND T2.Category = T1.Category
GROUP BY T1.Category, T1.SomeField;
You can of course include other columns from the table, but they must be
included in the GROUP BY clause as well as the SELECT clause.
Note that the numbering is dynamic; if a row is deleted from the table the
SerialNumber values computed by the query will reflect the number of existing
rows following the deletion rather than leaving a gap as would be the case if
you assign values to a column in the table.
Ken Sheridan
Stafford, England
Sharon_wv wrote:
Jeff -
I have a field to collect the category. The choices are in a separate table
(Category Choice) w/ just two records (Category A and Category B). The
person doing data entry will select one. That's the easy part for me.
The difficulty comes in the next step -- if the user selects Category A in
that record, I want it to the assign the next sequential number for that
Category (e.g. A0121 or A0122). If the user selects Category B, I want it to
assign the next sequential number that that category (e.g. B1825 or B1921,
etc.). I will probably use the Category Choice table to capture the A and B
by adding another column to that table. Then I will use that field in a
query to concantenate to give me the Identifier.
The problem I'm having is figuring out how to have Access give me a
sequential numbers for both Category A and Category B (dependent on user
selection on form).
Does that make better sense of my scenario and what I am attempting to do?
I find it difficult to capture all my thoughts and needs for you in a
message. I appreciate your patience with me.
Sharon
Sharon
[quoted text clipped - 121 lines]
.
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201006/1
.