How Many Field Per Table

[Dion Vincent]

1. How many fields can be placed into one table?

2. Can you use more than one Primary Key when using
the relationship function between 5 or more tables?

3. Is it better to create forms and sub forms from
queries developed from tables with relationships or
create the from directly from the table?

 

[arubikdoan]

Originally posted by Dion Vincent

1. How many fields can be placed into one table?

2. Can you use more than one Primary Key when using
the relationship function between 5 or more tables?

3. Is it better to create forms and sub forms from
queries developed from tables with relationships or
create the from directly from the table?

I am just a newbie, yet below is what i think I know

1. Max 255 fields.
2. Yes, it is possible. You can apply primary key for multiple fields.
3. If your form needs to display some caculated field, you must
use query.
For example, in the hotel reservation system, you use query to caculate
days customers use the room based on [Arrival Date] and [Departure Day].
Then, if you need the form to display how long the guest use a room,
you need query. You can not (as I know) do the same thing using only
table in this case .

Hope this helps,


Arubikdoan

 

[TC]

1. How many fields can be placed into one table?

255. But read this article first:
http://support.microsoft.com/support/kb/articles/Q100139.ASP

2. Can you use more than one Primary Key when using
the relationship function between 5 or more tables?

Sorry, the question really doesn't make sense. Each table, considered in
isolation from any other table, must have a primary key defined. The primary
key is that field, or combination of fields, whose values are present - and
unique - for every record in that table. See the article noted above.

3. Is it better to create forms and sub forms from
queries developed from tables with relationships or
create the from directly from the table?

Forms &/or subforms are based on tables &/or queries, depending on the
particular requirements of each situation. There is no general rule saying
"use tables, not queries" or vice versa.

HTH,
TC

 

[Tim Ferguson]

Originally posted by Dion Vincent

2. Yes, it is possible. You can apply primary key for multiple fields.

Just to clarify: one primary key (that's why it is primary!) but a PK can
be made up of up to ten fields. The implication is that the _combination_
of all those fields must be unique: e.g. BookName + Author, while each may
not be unique on their own.

3. If your form needs to display some caculated field, you must
use query.

Just to clarify again: you can calculate values on the form or in the
query, and it is usually fairly evenly-balanced to decide which one is more
convenient in any particular situation. For example, use a join query:

SELECT OneTable.Blah, OneTable.Blahblah, OtherTable.etc
FROM OneTable
LEFT JOIN AnotherTable
ON OneTable.FKey = AnotherTable.PKey
ORDER BY OneTable.Blah

or alternatively just set the controlsource of a textbox to:

DLookUp("Etc", "OtherTable", "PKey=" & Me!FKey)

It's generally a good idea to base a form on a query, even if only to limit
the number of records transferred: for example

SELECT * FROM Depots WHERE City="Vegas"

rather than the whole table. Sometimes it's more convenient to build a
complex query and make the form simple; other times it's the other way
round. Ain't it fun being a developer!


Hope that helps


Tim F