Right now, I have a Calc spreadsheet where I used Fill, Series for the ID Common Name. I only used Fill because last night Base would not allow me to set the primary key when I pasted into it.
The Table in base has a Common ID column, and Common Name column both set to primary key. I had to set Auto Value to No just to create the table, Field Type is Integer. Common Name is a Text Field, and right now the database is fine because I am using a small subset of possible Seeds, but I will be adding to it in the future. Is it possible to tell base that the Integer it uses for Common ID should start with 1000 for any Seed Name that begins with A, 2000 for Seed Name that starts with B, 3000 for Seed Names that starts with C, etc all the way to Z? That way I will be able to turn Auto Value on and any new Seed Names will be assigned an integer acoording to the first letter of it’s Common Name when I enter new seeds in the future using Forms.
AutoValue will only have a look of the last inserted Integer and create the next Integer if you insert a new row.
Why do you want to start for every character with a separate ID-range?
What do you want to do if this range isn’t big enough and had inserted 2999 for last “B”?
I am only attempting to have some organization for an unruly bunch of data. Going forward, ID will not be in any kind of order with auto value, but if I add a numbering system, then saw an ID by itself, I would have some idea of what that seed might be without stopping to look it up.
I did think about running out of room, so I could name it 10000, 20000, etc., but honestly it is a personal database to help me. Right now, I have to go back and forth between websites, click on this item to open it to get price information, scroll here to get characteristics. Do USDA, CDFA, CALflora, state university searches for other info.
I’m getting old, I can feel my mind not being as sharp as it used to be. Concentration is not as intense. If I can put everything in one place and pull reports, it will help me be less flustered and frustrated. Cover crops are pretty limited right now so I don’t think I myself will ever need to look at more than 26,000 seeds, but I was thinking that if it turned out to be useful, I could share it, at least with the other organic farmers in my area.
Organic is just another layer of frustration trying to find seeds that meet regulations. Finding seeds that fit the regions climate, that work in the soil that each person has on their farm, etc. If I can get it to work, then I can expand it and help others. I could put the database out there and let others add to it and share knowledge with each other.
There are just so many seeds, and every seed supplier is different. I spent weeks doing research, only to learn that a $900.00 order would cost $1762.00 to ship because it was coming from Georgia. Now I have to start over again and I need a better way to do cost analysis along with soil benefit analysis. Writing this just makes me want to cry the experience has been so daunting and awful. Anyway, I’m just trying to organize it better.
You don’t really need to abuse the primary key like that. You can make a simple query to display all the seed names sorted alphabetically, or that start with a particular letter, or that have more than n letters in the name or…
The primary key is used by the database for a number of purposes including referential integrity between tables, and as such you shouldn’t need to care about it except to ensure it is there. For example, at a later date you might decide you need to categorise seeds by (whatever seeds might be categorised by) and use a lookup table with a list of the category items, which list would be linked to your seed table by the category ID.
And from what you say, there may be several different categories - you mention climate and soil types, for example.
Thank you for your insight.