Customized ID Nos

[faxylady]

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?

 

[BruceM]

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.

 

[faxylady]

Please see the previous posting under faxylady. Thanks.

 

[faxylady]

The data in each table will be appended to a large, massive table. Each
record will have a different ID.

 

[faxylady]

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.

 

[John Vinson]

Do I put this formula =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]

 

[BruceM]

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.

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.

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.

 

[faxylady]

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.

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.

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.

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.

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?

 

[BruceM]

Did you download the sample database?

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.

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.

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.

:

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.

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?

 

[faxylady]

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?

Did you download the sample database?

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.

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.

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.

:

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.

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?

 

[BruceM]

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.

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?

Did you download the sample database?

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.

:

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.

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.

:

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.

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?

 

[faxylady]

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?

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.

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?

Did you download the sample database?

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.

:

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.

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.

:

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.

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?

 

[John Vinson]

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?

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]

 

[BruceM]

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.

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?

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.

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?

:

Did you download the sample database?

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.

:

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.

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.

:

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.

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?

 

[faxylady]

Over ten years ago, I began collecting fax nos for use in marketing. I used
Winfax Pro. Winfax Pro organizes its data using dBase III. The fax nos came
from all sorts of different sources, some from Chamber of Commerce
directories, others from telemarketing. Each source became a different
phonebook which was exported to diskette. Earlier this year, I imported each
of those diskettes into Access which resulted in a table for each phonebook
or source. Hence, all the various sources. New collections of fax nos go
into several particular sources, each easily importable into Access. This is
why the tables are finite and will not be added to.

Each table has a unique ID at this time that I recently created as a text
data type. I have already appended each of the tables into the larger,
comprehensive table called the BIGtable. When doing deletions from the
individual tables, I wanted to simultaneously delete from the BIGtable.
Somehow, from my Access training, i thought I could do this. Perhaps, I was
wrong. This is one of the things I thought a relational database was about.

Please respond. Thanks.

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.

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?

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.

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?

:

Did you download the sample database?

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.

:

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.

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.

:

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.

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?

 

[BruceM]

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.

Over ten years ago, I began collecting fax nos for use in marketing. I
used
Winfax Pro. Winfax Pro organizes its data using dBase III. The fax nos
came
from all sorts of different sources, some from Chamber of Commerce
directories, others from telemarketing. Each source became a different
phonebook which was exported to diskette. Earlier this year, I imported
each
of those diskettes into Access which resulted in a table for each
phonebook
or source. Hence, all the various sources. New collections of fax nos go
into several particular sources, each easily importable into Access. This
is
why the tables are finite and will not be added to.

Each table has a unique ID at this time that I recently created as a text
data type. I have already appended each of the tables into the larger,
comprehensive table called the BIGtable. When doing deletions from the
individual tables, I wanted to simultaneously delete from the BIGtable.
Somehow, from my Access training, i thought I could do this. Perhaps, I
was
wrong. This is one of the things I thought a relational database was
about.

Please respond. Thanks.

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.

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?

:

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.

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?

:

Did you download the sample database?

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.

:

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.

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.

:

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.

message
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?