what is query

[Kutty]

I have two table.
Table1 : ID#
name_of_staff

Table2 : File_No (Auto number)
Document_Name
Forwarded_To

The above two table have to connect together. This is a filing system for
my day today papers which is each one is going to files after process. I
will put File_No as I get from the secnd table on the respective papers to
file.
What is data type of ID# and File_No

 

[Jeff Boyce]

Kutty

Unless the two tables have a common field they share, how will Access know
which records belong together?

I'm not seeing a shared field ...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Kutty]

I have two table.
Table1 : ID#
name_of_staff

Table2 : File_No (Auto number)
Document_Name
Forwarded_To

The above two table have to connect together. This is a filing system for
my day today papers which is each one is going to files after process. I
will put File_No as I get from the secnd table on the respective papers to
file.
What is data type of ID# and File_No

You are right. Common field in table1 and table2 are ID#
How data type should be in both table?

 

[Tom Wickerath]

Hi Kutty,

What is data type of ID# and File_No

Only you can answer that question, as none of us can see your database.

With two tables, you have two possible relationships in a properly designed
database, assuming there is a relationship between these tables. Both
relationships would be considered "One-to-many" (1:M). A third possible
relationship, "One-to-one" (1:1) is very rare to find in databases, and if
this was the case, it would likely be better to have all the fields in a
single table. In English, these relationships could be stated as:

Possibility #1:
A staff member can deal with zero to many documents. A given document is
assigned to one (and only one) staff member.

or

Possibility #2
A document can include zero to many staff members. A staff member is
assigned to one (and only one) document.

In either case, you will need what is known as a foreign key field in the
many side table. A foreign key is a primary key (or uniquely indexed) field
from another table.

If you need what is known as a Many-to-many (M:N) relationship, then you
need to have a third join or linking table. A M:N relationship is made by
having two 1:M relationships, with the join table having the foreign key of
both relationships. In English, a M:N relationship would be stated as:

A staff member can deal with zero to many documents and a document can
include zero to many staff members.

Defining relationships between tables
http://support.microsoft.com/?id=304467

I encourage you to avoid the use of any special characters (spaces, # signs,
hyphens, etc.) or reserved words for things in Access that you assign a name
to. Scroll to the bottom of this Microsoft Knowledge Base (KB) article:

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

For reserved words, avoid using any of the words in this extensive listing:

Problem names and reserved words in Access
http://allenbrowne.com/AppIssueBadWord.html

Some good papers on the topic of database design:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

I highly recommend reading the first two papers written by Michael
Hernandez, author of the book "Database Design for Mere Mortals". The second
paper listed is only four pages in length.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[John W. Vinson]

You are right. Common field in table1 and table2 are ID#
How data type should be in both table?

They can be any data type (except Memo or OLE Object) that you choose, but
they must be the *same* datatype.

If one of them is an Autonumber then that table must be the "one" side of the
one to many relationship, and the linking field in the other table must be a
Long Integer.

I'm perplexed though: you do not have a field named ID# in Table2 but you say
it's the link.

Please explain what kind of Entity (real-life person, thing, or event) is
represented in each table, and how those entities are related in real life. It
*APPEARS* that Table1 is a list of staff members and Table2 a list of
documents - right? How do staff members relate to documents? Is each staff
member responsible for many documents, or does each document refer to many
staff members, or what? In other words, what is the real-world process that
you are trying to record in this database?

 

[Victoria]

this survice suck

 

[Kutty]

Now I have an error as below once I join the table1 and table2.
error : The database could not lock table 'table2' because it is already in
use by another person or process.

table1 : ID, Staff_Name ID field data type is number
Table2 : ID, FILE_NO, FORWARDED_ON, NOTES here ID field data type is
auto
I need these tables one to many. But I can see this as one to one after
I connected. Why?

 

[Jeff Boyce]

Not too specific ... anything in particular? (remember, this is a volunteer
newsgroup, not a paid subscription service)

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

table1 : ID, Staff_Name ID field data type is number
Table2 : ID, FILE_NO, FORWARDED_ON, NOTES here ID field data type is
auto
I need these tables one to many. But I can see this as one to one after
I connected. Why?

You will get this effect if you join the Primary Key to the Primary Key. The
field in the "many" table should NOT be the primary key; the many side table
should have its own primary key, and *also* a field matching the one side
table's primary key, as a link.

Take a look at Crystal's tutorial, especially the chapter on relationships:


A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

 

[Kutty]

You will get this effect if you join the Primary Key to the Primary Key. The
field in the "many" table should NOT be the primary key; the many side table
should have its own primary key, and *also* a field matching the one side
table's primary key, as a link.

Take a look at Crystal's tutorial, especially the chapter on relationships:


A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

The
field in the "many" table should NOT be the primary key; the many side table
should have its own primary key, and *also* a field matching the one side
table's primary key, as a link. Please explain this.

 

[John W. Vinson]

The
field in the "many" table should NOT be the primary key; the many side table
should have its own primary key, and *also* a field matching the one side
table's primary key, as a link. Please explain this.

Let's use a real example. If each Staff person can have zero, one or many
FILE_NO records, I'd use tables such as:

Staff
StaffID <Autonumber or Long Integer primary key>
LastName
FirstName
<other biographical data>

Notes
NoteID <Autonumber or other Primary Key>
StaffID <Long Integer, link to the Staff table>
FILE_NO
FORWARDED_ON
NOTES

There is a unique ID on NoteID; a Primary Key is always by definition unique
within the table. There is *not* a unique ID on StaffID because an individual
staff member may have more than one note - that's why you can't use the
Primary Key of the Notes table as the linking field, because it's unique, and
you would not be able to have multiple notes.

 

[Kutty]

Let's use a real example. If each Staff person can have zero, one or many
FILE_NO records, I'd use tables such as:

Staff
StaffID <Autonumber or Long Integer primary key>
LastName
FirstName
<other biographical data>

Notes
NoteID <Autonumber or other Primary Key>
StaffID <Long Integer, link to the Staff table>
FILE_NO
FORWARDED_ON
NOTES

There is a unique ID on NoteID; a Primary Key is always by definition unique
within the table. There is *not* a unique ID on StaffID because an individual
staff member may have more than one note - that's why you can't use the
Primary Key of the Notes table as the linking field, because it's unique, and
you would not be able to have multiple notes.

 

[Kutty]

Let's use a real example. If each Staff person can have zero, one or many
FILE_NO records, I'd use tables such as:

Staff
StaffID <Autonumber or Long Integer primary key>
LastName
FirstName
<other biographical data>

Notes
NoteID <Autonumber or other Primary Key>
StaffID <Long Integer, link to the Staff table>
FILE_NO
FORWARDED_ON
NOTES

There is a unique ID on NoteID; a Primary Key is always by definition unique
within the table. There is *not* a unique ID on StaffID because an individual
staff member may have more than one note - that's why you can't use the
Primary Key of the Notes table as the linking field, because it's unique, and
you would not be able to have multiple notes.

What is the difference of Primary Key and Autonumber?
Why I ask you is Primary key has unique number and autonumber also have
unique number.

 

[John W. Vinson]

What is the difference of Primary Key and Autonumber?
Why I ask you is Primary key has unique number and autonumber also have
unique number.

That's why an Autonumber is often used as a primary key: it meets all the key
<ahem> requirements. It's unique; it's stable (cannot be edited); and it's
small (32 bits).

But there are other kinds of primary keys. The only *absolute* requirement of
a primary key is that it must be unique within the table. You can, for
instance, perfectly well use a Text field as a primary key; if you define it
as such, Access will create a unique Index preventing duplicates. I'll often
use a table of US States and and Territories and Canadian Provinces, using the
two-letter Postal Service state abbreviation as the Primary Key.

It's not even essential that the primary key consist of a single field; it can
be up to *ten* fields. This gets into controversial areas of discussion; some
people will use "natural keys" (multifield keys consisting of the real-life
data actually in the table) whenever possible, others will put in an
autonumber "surrogate key" routinely. There are good points on both sides of
the discussion.

 

[Tom Wickerath]

Hi John,

The only *absolute* requirement of
a primary key is that it must be unique within the table.

There's one other absolute requirement of a primary key: It cannot not be
null.
And, yes, I know that you knew that, but the OP may not.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Jeanette Cunningham]

Hi Tom
gotta watch those double negatives <grin>.
Quote<It cannot not be null.>end quote.

I assume you meant <it cannot be null>.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Tom Wickerath]

Hi Jeanette,

I assume you meant <it cannot be null>.

Yes! Geez. Thanks for catching that. That's what I get for answering a
question after midnight.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________