I have data with three ID numbers. One table has two of the ID numbers. Another, table has the other ID numbers. The record numbers reference a product and a quantity. All of the tables have a primary ID. Will access connect the correct record, product and quantity. Also, how can I build a relationship between two fields with different data type.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
access 1
- Thread starter AstroTek
- Start date
-
1
- #2
If you set up the correct type of relationship between the two tables, Access will automatically set up some linkages between the two. How much of the work Access will do for you depends on what kind of link you set up and what version of Access you're running.
You can't set up a relationship between two fields with different data types. If you're relating a product ID field, you should probably set the field type as text, even if the ID is a number. As a general rule, you should only use numeric data types for fields that you're going to be performing mathematical calculations on. Price, quantity, etc should be numeric. Phone numbers and such should be text.
In future posts, you might a more descriptive thread name. "access" doesn't really tell anyone what your question is, and many people skip over threads unless they know it's something they're interested in.
Just muddling my way through.
You can't set up a relationship between two fields with different data types. If you're relating a product ID field, you should probably set the field type as text, even if the ID is a number. As a general rule, you should only use numeric data types for fields that you're going to be performing mathematical calculations on. Price, quantity, etc should be numeric. Phone numbers and such should be text.
In future posts, you might a more descriptive thread name. "access" doesn't really tell anyone what your question is, and many people skip over threads unless they know it's something they're interested in.
Just muddling my way through.
vitaminD12
Programmer
You really don't build relationships between fields. You build relationships between tables based on a field(s) and value in it(them). There are no any reasons why you would want to build relationships on fields of different types.
2 most common relationships between tables is one-to-one and one-to-many (I hope that makes sense) and personally prefer to build on numeric values. Your "left" side -
"one" side - table should have autonumber Id1 and you "right" side table should have its own autonumber Id2 and a foreign key from left side table - Id1. Those 2 (Id1 and Id2) in right table should form its unique key.
That's kind of my scoop........
2 most common relationships between tables is one-to-one and one-to-many (I hope that makes sense) and personally prefer to build on numeric values. Your "left" side -
"one" side - table should have autonumber Id1 and you "right" side table should have its own autonumber Id2 and a foreign key from left side table - Id1. Those 2 (Id1 and Id2) in right table should form its unique key.
That's kind of my scoop........
And some further reading....
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)
Micro$oft's answer...
Where to find information about designing a database in
Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database
Litwin is an easy read. The Mircosoft will provide more meat. Hopefully, you can realte to it ;-)
Fundamentals of Relational Database Design by Paul Litwin
Download document
Read on-line (HTML)
Micro$oft's answer...
Where to find information about designing a database in
Microsoft Access
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database
Litwin is an easy read. The Mircosoft will provide more meat. Hopefully, you can realte to it ;-)
- Status
Similar threads
- Locked
- Question
- Locked
- Question