Designing multiple table queries

D

DES

I am a complete novice at Access, looking for any help I can get! I have
created a database composed of 3 tables. I have not defined a primary key in
any of the tables, nor have I established any relationships between the
tables. All 3 tables have the same fields, Pick #, Overall Pick #, Team,
Player, Position and finally, College. The database is about the NFL Draft,
each table representing one round of the draft. I would like to learn how to
design a multiple table query, i.e.…how many quarterbacks were drafted in the
3 rounds? I know how to do this one round at a time, but what do I need to do
to design a query that would give me the answer I’m looking for in just one
query?

I’m also curious as to whether or not it is possible to perform a
calculation in a table…if you had fields for the quantity and the amount, can
you calculate the total in another field in that table? If anyone could help
me, it would be greatly appreciated.
 
D

Dennis

1 - Calculations are NEVER done in tables. Period.

2 - Sounds like a homework assignment to me (insert smiling devil emoticon
here...)
 
G

George Nicholson

All 3 tables have the same fields, Pick #, Overall Pick #, Team,
Player, Position and finally, College.
Click to expand...

Sounds like a very faulty data structure, so its probably not homework
unless your teacher likes trick questions. :).

Much better off to have just one table with an additional field: Round #
(Team and College are redundant, btw)

If you stay with the 3 table structure then you'd probably need to create a
UNION query to combine data from all 3 tables (including a field containing
Round #) and then base any data crunching on the UNION. Note that the UNION
would simply be a substitute for the single-table structure you choose not
to use.

Complexity leads to more complexity. Simpliest design that meets the
requirements is the best.
 
S

Steve Schapel

DES,

I agree with George. You need to get the tables correct (i.e. according
to database design principles) before thinking about queries. You have
fallen into the "tables as data" trap. In paricular, "rounds" is a data
element, i.e. which round the data pertains to is a characteristic of
the data you are working with. As such, it should be modelled into your
data base as a field in your table, rather than modelled as a separate
table.

By the way, as an aside, it is not a good idea to use a # as part of the
name of a field.
 
Top