Great!! Now we're getting somewhere. Several weeks ago, I posted to this
forum questions as to how to add a personalized prefix to automated ID nos.
No one gave me an answer that I could understand. I will include that
thread. What I figured out on my own was to created the ID as an autonumber.
Then change that autonumber to a text data type. After that, I used an
update query to add a text prefix to the autonum ID. Now I want to use the
ID to segment a 4400 entry file into a 1000 entry query or table, so it can
be reasonably exported to Winfax. As things stand now, the entire 4400 entry
table has the prefix ZC before the ID.
Here is the thread from several week ago:
faxylady 7/13/2006 9:34 AM PST
Question
As you have seen before, I have several tables of contact numbers in my
database. For each table, I want to put in a customized ID beginning with the
name or short description of the table. For example, the newspaper table
would begin with an ID of nwp1, nwp2 and so on for each record in the table.
Another example would be govt1, govt2, govt3, etc for each record in the
table. These tables have no autonumber ID now or any type of ID. My intention
is to append each table to a comprehensive table and connect them
relationally. This means each table id will be in the comprehensive table and
joined. How do I make customized IDs?
Was this post helpful to you?
Reply Top
BruceM 7/13/2006 10:10 AM PST
If I have seen before that you have several tables, etc. I have forgotten
it. If you are seeking help it's best not to send people on a search for the
problem. You could add an autonumber field, which will number in the order
you want for your existing records. Then, change the data type to Number, and
increment it for new records with something like the method here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
I can't even guess what you mean by appending the records into a
comprehensive table and connecting them relationally. I can say that each
table should contain information about a single entity. For instance, in a
school database Students and Courses need to be in separate tables.
Click to show or
hide original message or reply text.
Did this post answer the question?
Reply Top
faxylady 7/13/2006 8:36 PM PST
The data in each table will be appended to a large, massive table. Each
record will have a different ID. "BruceM" wrote: Click to show or hide
original message or reply text.
Was this post helpful to you?
Reply Top
faxylady 7/13/2006 8:40 PM PST
Please see the previous posting under faxylady. Thanks. "BruceM" wrote:
Click to show or hide original message or reply text.
Was this post helpful to you?
Reply Top
faxylady 7/15/2006 6:32 PM PST
Do I put this formula =Dmax["ProductID", "Product"]+1 in the criteria row
of a query? I did this and got an error message saying The expression you
entered contains invalid syntax or you need to enclose your text data in
quotes. You may have entered an invalid comma or omitted quotation marks... I
looked up your reference and followed your directions as to creating an
autonumber field, then changing the data type to number. Then I entered the
expression from your reference in the criteria row, hit run and got this
error message. Please help. Thanks. "BruceM" wrote: Click to show or hide
original message or reply text.
Was this post helpful to you?
Reply Top
John Vinson 7/15/2006 10:07 PM PST
On Sat, 15 Jul 2006 18:33:01 -0700, faxylady
=Dmax["ProductID", "Product"]+1 in the criteria row of >a query? Reread the
reference. Bruce did not suggest that syntax or anything resembling it. John
W. Vinson[MVP]
Did this post answer the question?
Reply Top
BruceM 7/17/2006 5:17 AM PST
In the database from Roger's site is the suggestion to put that expression
into the default value property of a text box bound to that field. The
default value comes into play only for a new record. Once the record has been
saved (by moving to another record, for instance, the number is a part of the
record unless you specifically change it. Syntax aside, using the top value
plus one as the criteria must logically fail, as far as I can see. "faxylady"
Click to show or
hide original message or reply text.
Did this post answer the question?
Reply Top
faxylady 7/17/2006 7:36 AM PST
Maybe something went wrong with my download. Somehow, I missed this
information. I visited the site you suggested, but found only minimal
information, including the statement I quoted. I will check again. thanks.
: Click to show or hide original message or reply text.
Was this post helpful to you?
Reply Top
BruceM 7/17/2006 7:47 AM PST
Did you download the sample database? "faxylady"
Click to show or
hide original message or reply text.
Did this post answer the question?
Reply Top
faxylady 7/18/2006 9:48 PM PST
What I downloaded appeared to be a database. When I clicked on it, the
above discussed information came up. Since I found a way to get the
customized ID and thought I had joined each iD to the ID in the BIGtable, why
aren't the records being deleted from the BIGtable when I delete them from
the individual tables? In order for this to happen, does the ID have to be an
autonumber? "BruceM" wrote: Click to show or hide original message or reply
text.
Was this post helpful to you?
Reply Top
BruceM 7/19/2006 4:48 AM PST
When you open the database there is a button for a single-user example and
for a multi-user example. Clicking on either one will open a form that
included an Explanation button. Clicking that button produces the instruction
to put the expression into the default value of a text box bound to the
incrementing field. If you duplicated records when you placed them into the
Big Table you need to delete them from both tables. There is probably code
that can accomplish this, but if you are duplicating records there is almost
certainly a flaw in your design. If you had joined tables with a query,
deleting records from the table would have removed them from the query.
Without knowing anything about the purpose and structure of your database it
is impossible to offer specific advice. "faxylady"
Click to show or
hide original message or reply text.
Did this post answer the question?
Reply Top
faxylady 7/19/2006 9:30 PM PST
Yes, there obviously is a flaw in my design. That is what I am trying to
correct. What I have done is created ID nos customized to each table. The way
this was done was by converting the ID field from the autonumber data/type to
the text data/type. While this ID field is now a text field, I joined it to
the ID field in the BIGTable, which I also converted to a text field. What I
seem to have created are child fields. What I want to do is create a
relational database where records can be deleted from both tables at the same
time. What advice can you give me here? By the way, these tables are finite,
no more data will be added to them. New data will be added by means of new
tables in this database. The information to be added to the default value
will be useful if I append tables together. Also, is there any way I could
send you a copy of my database so you could better understand what I am
trying to do? "BruceM" wrote: Click to show or hide original message or reply
text.
Was this post helpful to you?
Reply Top
John Vinson 7/19/2006 10:43 PM PST
On Wed, 19 Jul 2006 21:31:02 -0700, faxylady
relational database where records can be deleted from both tables at the
same >time. What advice can you give me here? I would advise you to GIVE UP
ON THE IDEA of storing data redundantly in two tables, and expecting to be
able to magically delete it from one table when you delete it from a
different table. That is *not how relational databases work*. Databases use
the "Grandmother's Pantry Principle": "A place - ONE place! - for everything,
everything in its place". If you have data stored in multiple tables, I'd
really suggest that you import it (using append queries) into one master
table - with *its own unique ID, not dependent on any of the existing IDs,
which almost surely have duplicates* - and then delete the multiple tables,
using your single master table henceforward. John W. Vinson[MVP]
Did this post answer the question?
Reply Top
BruceM 7/20/2006 5:01 AM PST
As for looking at a copy of the database, I'm heading on vacation soon,
but in any case I would have to say sorry, no. I have asked several times
about the purpose and structure of the database. Here's a way you could
describe the purpose (I will have to use an example that is almost certainly
unrelated to your situation). "I would like to build a database to keep track
of on-the-job training. The training is limited to employees of the company.
Instructors may be other employees, or they may be from the outside. I have a
table with Employee ID numbers and names, and another for the details of the
training session (topic, instructor, and so forth). There may be from one to
one hundred employees at a training session. How do I store information about
attendance in such a way that I can see a report about all of the training an
employee has received during the year?" To describe the structure, you may do
something like this: tblEmployee EmployeeID (primary key) FirstName LastName
Department tblTraining TrainingID (autonumber PK) Topic Instructor
tblAttendance (junction table) AttendanceID (PK) EmployeeID (foreign key)
TrainingID (foreign key) TrainingDate "There is a one-to-many relationship
between the primary keys of the first two tables and the foreign key fields
with the same name in the junction table. I have built a form based on
tblTraining, with a subform based on tblAttendance. How can I select the
employee names from a drop-down list in the subform?" Random examples, but
the sort of information that can lead to a targeted response. If you are
shutting down tables (no new data) it is helpful to explain why. For
instance, if you are creating a new table for each month or calendar year,
you will surely be advised to proceed differently. If you are appending data
from one table to another (there are lots of valid reasons to do so), the
tables need to have the same fields. If the tables are dissimilar you could
maybe use a union query, but there needs to be a common element between the
tables. There are all sorts of options. However, right now it's a bit like
trying to answer the question "Which exit do I take from the highway?"
without knowing which highway you're on or where you want to go. "faxylady"
Click to show or
hide original message or reply text.
BruceM 7/21/2006 5:57 AM PST
If your Access training included duplicating data and then deleting it
from both places then it was ineffective training. If you have related
records then you can set up Cascade Delete so that if in a sales database you
delete an order from your Orders table tyou also delete the individual line
items from the OrderDetails table. If your database were set up with a Source
table (phonebooks from Chamber of Commerce, etc.) and a related Contacts
table (name, fax number, etc.), you could set it up so that deleting a source
also deletes all of the contacts related to that source. I expect that each
fax number has a company name and other such information along with the fax
number. If so, each table has the same structure (fields, labels, and so
forth) as the other tables. As John Vinson mentioned, in the relational
database world each entity is stored once. This can be taken to the extent of
having, for instance, a State table (for address records) or an area code
table, but that level of separation is not used all that often, at least not
in relatively small intra-company projects. If I understand, you want the
list separated by source (Chamber of Commerce, etc.). Does the same number
ever appear in two different listings? Are companies involved, so that there
may be several listings for a company? In general, you should have a
Phonebook table and a FaxNumber: tblPhonebook PhonebookID (autonumber primary
key, or PK) Source (Chamber of Commerce, etc.) Other fields as needed to
describe the phonebook tblFaxNumber FaxID (PK) PhonebookID (Long Integer
datatype if Phonebook ID in tblPhonebook is autonumber) FirstName LastName
FaxNumber Other contact information Create a relationship between the two
PhonebookID fields. Click Tools > Relationships. Add the two tables, and drag
one PhonebookID field on top of the other. Click Enforce Referential
Integrity. Create a record (one only) for the C of C in tblPhonebook. Take
the existing C of C table, and add the PK number from the C of C record in
tblPhonebook to each individual FaxNumber record. Create a form based on
tblPhonebook, with a subform based on tblFaxNumber. The form wizard may help
get you started with this. Now when you look at the C of C record you can see
all of the related FaxNumber records. Repeat for other phoneboods. If you
want to see a listing of all phone numbers you can do that by way of a query.
That is one general approach, but others can advise you better than I, so I'm
going to suggest that you start a new thread. For one thing I don't really
have time to pursue this any further right now. For another it will attract
more attention than this thread, and increase the chance that a person who is
well-qualified to suggest the best approach will actually see it. In that
thread, include the fields in your current tables, in similar manner to how I
suggested in my previous posting. Most importantly, describe what you want
from this database. Do you want to keep a record of faxes sent? Do you want
to see listings by company? By phonebook? Both? Help people help you be
remembering they don't know the details. "faxylady"
Click to show or
hide original message or reply text.
