Auto Populate a serial number in MS Access 2003

[xyzlook]

I have developed a database for my CD collections using ACCESS 2003.

I have several tables including these:

1. tblCategory: a) Easy Listening, b) Jazz, c)Country etc. Each category is uniquely given a 2-letter abbreviation; for example EL is for easy listening, JZ is for Jazz, CL for classic
2. tblArtists: Each singer has been assigned a unique 3-letter. For example, John Denver is JDA, Julio Iglesias is JIB etc.

I am trying to update the serial number of the records automatically. I would like the serial number to come up as:

EL.JDA.01.014.0100

Here EL = Easy Listening
JDA = John Denver
01 = First CD of John Denver in Easy Listening category
014 = Fourteenth CD in Easy Listening Category
0100 = It’s 100th CD of the entire collection


Now when I make the next entry for John Denver in Country collection (7th CD in this category), I want the txtSerial to automatically populate as CO.JDA.01.007.0101.

Or when the next entry is for John Denver in Easy Listening the serial number text box should automatically populate as:

EL.JDA.02.015.0101

How do I do it?

The form I use to enter CD data is named, frmCDDetail that is based on a table called tblCDDetail which has these fields:

MusicCategoryID
ArtistID

Can somebody please help?

 

[MajP]

What have you tried. The CD collection and the video store problem are well known database 101 homework assignments. You will not get much help without showing your cards.

Is this dynamic? If you delete a record, change a category, do you want the numbers to recalculate? Is this to be a primary key (I hope not)?

A dynamic solution can be done simply using a series of dcounts with different where criteria and some concatenation.

 

[xyzlook]

Thanks for your response. I am sorry for not being very clear in my questions. First of all, please understand, I don't have any formal training on Access, I am learning as I go. Count me as a novice.

txtSerialNo is a text box
It's not a primary key

I am not sure what you meant by "dynamic". "If you delete a record, change a category, do you want the numbers to recalculate?" Should I? I may choose to delete and/or combine some categories.

I want the serial number to be automatically populated based on data entered. Here is the details.

I have a frmCDDetail which is used to enter CDs. It has various text boxes, combo boxes etc. One combo box is called cboCategory which defines what category the CD belongs to. This data is pulled from a table called tblCategory. The other is called ArtistID which brings data from a table called tblArtist. Each artist is given a 3-letter ID

When I am entering a new CD by John Denver (JDA) in a Country (CO) category, I want the txtSerial to be populated as:

CO.JDA.

In addition, I want the numbers that follows this two info to be a running count of an artist's CDs in a given category. In this case CO.JDA.02 (for example) followed by running count of total CDs under Country category and a number for the entire collection.

So if I have 112 CDs and 22 in Country and 2 for John Denver, then the new CD serial number should be automatically filled in as

CO.JDA.03.023.0113

How do I do it?

Can you please send me some examples of DCount? How do I tell it,

"If the Category is Country then CO else Jazz, JZ....and if the Artist is John Denver, JDA else if the artist is Julio Iglesias, JIB...."

Please be patient, I am, but a beginner. Many Thanks.

 

[fneily]

First CD of John Denver in Easy Listening"
"Fourteeth CD in Easy Listening"
Access doesn't work that way. The first/fourteenth will change over time - deleting, sorting, additions, internal storage, etc.. So you'll need to keep an entry indicator, in other words, you have to keep count and which position.

If you're not fixed on your serial number, you could make life easier if you wanted something like:
EL.JDA.08/12/2008.102 you'd still have category, artist, date(substituting for order, and placement in collection.

So, the tables would look like:
Assumuptions - I'm saying that the music determines genre, not artist (some artist cross genre but it's still reflected in the music). And, a big assumption, that the collection only grows - no deletions. Hey, I know, fudging.

tblArtist
ArtistID Primary key
other artist info fields

tblMusicCategories
MCID Primary Key
Description

tblCDs
CDID Primary Key
ArtistID
CDName
DateEntered
PlaceNumber (1,2,3,etc just counting)

tblCDGenre
GenreID Primary Key
CDID
MCID

From tblCDs, PlaceNumber would be the last number in your serial number. So get, let's say, 102. You'd also have the artist from artistID, so JDA. And you'd have the "placement" with DateEntered.

Connecting tblCDs to tblCDGenre through CDID, you'd get the first position from MCID, so EL.

So in one query, you could make with a concatenation EL.JDA.08/12/2008.102

Or, you could do it MajP's way, which is the only way to get your "placements", by using DCounts.

Or create an easier serial number.

You'll notice that with the tblCDGenre, a CD can belong to more then one genre. If you have sophisticated taste in music, examples of cross genre are Leon Redbone and, an old neighbor of mine, Frank Zappa.

 

[xyzlook]

Thanks for your suggestions and guidance. But keeping the serial numbers as EL.JDA.08/12/2008.102 does not tell me exactly where that CD will be stored on my shelves. All it says that it is in Easy Lisenting and it is by John Denver and that I have a total of 102 CDs. What does the date do?

Sorry I am a novice and don't know much. Isn't there any way I can keep a running count of all the three things:

1. The category, 2. The artist and 3. the total?

Thanks again.

 

[PHV]

Have a look at the DCount function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

To answer your question and to clarify, the Placement number is not the total CD's you have but the CD's position - as you wanted "0100 = It's 100th CD of the entire collection"
You would like to know when the artist's CD was placed in a category, eg. the first John Denver, the second John Denver. Well, I substituted the date of entry. It's basically the same thing as what you want. What's the difference between it's the second CD of John Denver and it's the CD of John Denver entered on 8/12/2008? Nothing.

The way you store it on your shelves is not the way Access stores it in a computer.

So you're back to the DCount function.

 

[xyzlook]

Thanks again.

If I have 20 CDs by John D, I will have to work a little harder to find the correct place for this CD on the shelf using dates. (please note, John D is not the actual singer on my collections. I have CDs from other parts of the world where I have CDs by a singer that exceeds 20). That's the difference between using dates and a serial number of a given singer. Yes, I know the computer does not care how I shelve my CDs.

Can you please give me examples of DCount since I am not familiar and am trying to learn? Is there an easier solution to my problem?

Thanks.

 

[PHV]

{i]give me examples of DCount[/i]
In the debug window (Ctrl-G) type dcount and press the F1 key.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[fneily]

Why not have your shelves by category and slot#? Example, one shelf is Easy Listening and JDA's CD's are in slot 1, slot 5, slot 47. So the table would look like:
Cat Art Slot CDNumber
EL JDA 001 0005
EL JDA 005 0037
EL JDA 047 0072
HR JAA 001 0002
HR BDB 011 0114

HR = Hard Rock, JAA = Jefferson Airplane, BDB = Bonzo Dog Doh-Dah Band

As you can see, since the shelves are independent, then the slot numbers can repeat.

CDNumber is just a straight count of all your CD's. So far then, you'd have EL.JDA.XX.005.0037

Having built that, I now have a question, which JDA is the first? Maybe I put the first in slot47, the second in slot5 and the third in slot1. How would you tell which JDA is the first? DATE, maybe?
Moot question. This is your thread, not mine. Just a thought.

 

[xyzlook]

Dear fneily:

I sincerely appreciate your patience with me. You are in correct profession.


I was able to create a concatenation of EL.JDA. using IIF statements. But that's because I knew MuscicCategoryID 1=El and ArtistID 1 =JD. This is cumbersome and not feasible. BEcause there are more 13 categories and more than 100 artists.

Is there a way I can use all the categories and artists to simplify this? Like say, "Choose", or DCount or Switch or some other commands? Please suggest. Thanks.

ps: I gave up on automating the counting part. I will just put it on hold now and do it manually. I have already done it for the largest category where I have over 250 CDs. I can survive the smaller categories.

 

[fneily]

MuscicCategoryID 1=El and ArtistID 1 =JD".
Post your table structures. El should be MusicCategoryID and JD should be ArtistID. Unless you are using numbers as your primary keys which is not necessary, not advised.
If your tables are setup correctly, you'd be concatenating in a query and not using IIF. So post your tables and your exact steps.
Or, to simplify the matter, just use comboboxes to build the serial number. Have a combobox for Artist, one for category and then concatenate the selections.

 

[xyzlook]

Dear fneily:

Thanks again.

1. tblArtists
ArtistID - Auto Number
ArtistName - Text
ArtistAbbv - Text

Sample data

1
John Denver
JDA


2. tblMusicCategory
MusicCategoryID - Auto Number
MusicCategoryName - Text
MusicCategoryAbbv - Text

Sample Data
1
Easy Listening
EL

I made some progress and was able to use a text box to automate the category part using "CHOOSE" function.

When I did the same for the artists, I got an error message saying "the text you used is too complex". This I don't understand. Because if I am able to use the Choose function for "n" times then why this error? Here is the syntax for category which worked.

=Choose([MusicCategoryID],"RS","NG","CL","IN","AP","BH","DE","FO","GH",
"MO","PL","RK","RE","RV","MS","EL","JZ","UH","PA","DD","MI","PR","MP")


Here is the syntax for Artists which DID NOT work

=Choose([RecordingArtistID],“AAA”,”AJA”,"ATA","AMA","ABA","ABB","AGA",
"ACA","AGB","ABC","EHA","ASA","AGC","AJB","APA","ASB","AMB","ASC","ACB",
"ABD","ASD","BGA","BSA","BGB","BAA","BMA","BHA","BBA","CCA","CLA","AMA",
"DPA","DBA","DXA","DBB","DBC","DMA","DCA","AMC","DMC","NJA","FPA","FQA",
"FBA","GHA","GBA","GDA","GGA","CBA","GDB","GGB","HSA","HMA","IKA","ISA",
"ISB","JMA","JMB","JHA","JDA","KSA","KSB","JDB","KCA","KDA","KBB","KKA",
"KKB","KDB","KCC","LMA","LMB","MGA","MSA","MMA","MGB","MDA","MRA","MCB",
"NCA","NGA","NSA","PMA","PBA","PGA","PSA","ABA","PXA","WHB","PBD","PGB",
"PSB","PMC","CVA","HCA","RLA","SSA","SKA","JIA","PDA","PDB","RBA","RKA",
"RTA","RRA","RSA","RBB","RGA","RRB","SKB","SBA","SSB","SSC","SMA","SKC",
"SSD","SSE","SRB","SMB","SRC","SRD","SRF","SXA","SBB","SMD","SHA","SME",
"SAA","SSH","SMF","SBC","SXB","SRG","SSI","SSJ","SMJ","SCC","SSK","SCD",
"SSL","SPA","SGC","SCE","SDA","SGD","TMA","TBA","NDA","TMC","ARB","AKB",
"BKA","GKA","AKA","USA","UCA","ZHA","MHA","KBA","MHB","PMB","MBC","SSG",
"RMA","MBA","TMB","WHA","SGA","IAA","SRE","FKA","ARA","KCB","SYA","PMD",
"NYA","MBB","SCB","FAA","KSC","ASA","SSF","LKA","SRA","SCA","MCA","PBB",
"DTA","SSM","DNA","SGB","PBC","CRA","AGD","SMC","KRA","PBE","KKC","NBA",
"SMH","ABG","ADA","ABF","AME","BAB","CDA","CGA","EPA","EFA","ZRA","JTA",
"JCA","JGA","LPA","MJA","MCC","NCB","NJB","PUA","PSC","RDA","RCA","RCB",
"RPA","SDB","SKD","SBD","SNA","BXB","YXA","PMB","IBA","SGE","AHA","RBC",
"FRA")


Any help you can provide will be appreciated. Thanks.

ps: I am still researching how to automate the numbering potion of my serial number.

 

[fneily]

You don't need the autonumber fields in tblArtists and tblMusicCategories. Use ArtistAbbv and MusicCategoryAbbv as your Primary Keys for those tables.
I wouldn't use Choose. I'd use a combobox. Just create a combobox and follow the Combobox wizard. You'd base both comboboxes on their respective tables.
Have to admit, never saw a Choose like that before. Too much typing.

One last thing, "Dear Fneily"???? Oh man, I'm sure there's a few thousand Tek-tip users out there laughing now. It's nice, but just Fneily will do. Or Omnipotent One. Actually, it's better not to address a single person because more knowledgable people may then not offer their answers.

 

[xyzlook]

fneily:

I will try what you suggested about the primary keys. Thanks.

My apologies for being 'polite'. No hidden agenda was there nor did I intend you to be "laughed at". I regret all of it.

Secondly, I addressed your name to make it easier to identify the person to whom I am directing my responses. By no means was it to exclude any one else who may have a few other suggestions.

Thanks.