Which the best option ?

[an]

Hi!

I need to create a new database, where some fields are common. Ex: Date,
Hour, Type...
I thought about making:

- 5 different and specifics tables, and;
- 1 common table and relate them itself.

In adition, I need to make a relationships to later to make a query to
manage the data on forms.
Which the best option for this situation, please?
Thanks in advance.
an

 

[Jeff Boyce]

Access is a relational database. To get the best of its features and
functions, a well-normalized table structure is important.

Without more description of the domain (subject area), the entities (things
about which you want to store data) and the relationships (how is each
entity related to the others, if at all), it will be hard to offer useful
ideas.

For example, if you said "I am building a database about products I'm
selling, I care about the orders, the items ordered and the customers, and
each customer can have one/more orders, and each order can have one/more
items", we'd have enough to go on to suggest that you look at the Northwind
database that accompanies Access.

Specific questions lead to specific suggestions.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[an]

Thanks for your reply.

It would like to relate the next:
Tbl_Activity1
Taskfields: T11, T12, T13, T14, T15...
Tbl_Activity2
Taskfields: T21, T22, T13...
Tbl_Activity3
Taskfields: T31, T32...
Tbl_Activity4
Taskfields: T41, T42...
Tbl_Activity5
Taskfields: T51, T52, T53...

Tbl_CommonFields:

StartTime
Date
City
XPTO field

In adition:
One Qry_Relations to relate the five Activities tables betweem itself and
CommonFields table.

Next:
Form with 5 combos, one for Activity, where successively the corresponding
registers to each were filtered combo, based in Qry_Relations.

Thank you.
an

 

[Jeff Boyce]

When I see a table definition that includes "repeating fields" (e.g., Task1,
Task2, ...), I am immediately reminded of ... a spreadsheet.

If you want to get the best out of Access, you will need to look into
"normalization". In a spreadsheet, you need to add another column to get
another task. In Access, you can design your table so that another Task is
just another row.

Can you be any more explicit about the actual data, rather than the names of
the fields?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[an]

Thanks, JB.

I think that I wasn't explicit.
When it said tasks wanted to say fields.
TblActivity1 has distinct fields (field 11, field 12, ...) of the actions in
TblActivity2 (field 21, field22, ...) where fields are different between
tables.

Then, I would like to relate these tables with one another of the common
fields.
How to make this?...

Many thanks
an

 

[Jeff Boyce]

Please re-read my previous response. If the fields (whether in a single
table or stretched out among several tables) are "repeating fields" (e.g.,
Task1, Task2, ...), you have a ... spreadsheet, not a well-normalized
relational database.

Just to be sure, are you saying that your table(s) have a field for each
task?

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[an]

Decidedly, I will havn't explained well.
Table of Common Data: City, Local, Date, Time, etc, fields;
Table of People: Name, Address, Telephone, etc, fields;
Equipment table: Machine, Tool, Accessory, etc fields;
Table of Projectos: Project, Drawing, Document, etc fields;
Table of Cars: Mark, Model, Km's, etc fields.
Perhaps thus if it understands better what I intend to relate.

Thank you.
an

 

[mnature]

OK, you want to relate some common data, people, equipment, projects and
cars. This is progressing nicely. However, I don't have a clue how these
things relate to each other. You must figure out how they relate, and then
set up the tables so that there is something in common between the
information on one table compared to another. You have to tell the database
what to do.

 

[Jeff Boyce]

How is Equipment related to People? How are Projects related to People?
How are Cars related to People?

Your answer might be something like:

One Person can have many pieces of Equipment.

One Person can own many Cars.

...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[an]

Thank you for reply.
My doubt will be accurately this: as I must relate these tables.
an

 

[an]

Thank you for reply.

1 - Each person can work with some equipment and the same equipment can be
used by some people;
2 - One projecto can integrate some people and the same people can execute
some projects;
3 - The same car can be used by some people and these people can use some
cars.

(I believe not to be easy. For me...)

Thanks in advance.
an

 

[Jeff Boyce]

It sounds like each of your tables have a many-to-many relationship with
Person. To resolve this using Access, you'd need to create "junction"
tables. For example, if a Person can work with several different pieces of
Equipment, and a piece of Equipment might be used by several different
People, you'd need:

tblPerson
PersonID
... (any other facts about this Person)

tblEquipment
EquipmentID
...(any other facts about this piece of Equipment)

trelPersonEquipment
PersonEquipmentID
PersonID (this is a "foreign key" from the Person table)
EquipmentID (this is a "foreign key" from the Equipment table)
... (any other facts about this Person using this Equipment)

The same type of "junction" table would be needed to resolve Person-Vehicle
and Person-Projects.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[an]

JB,

Very very much for your solution.
an

It sounds like each of your tables have a many-to-many relationship with
Person. To resolve this using Access, you'd need to create "junction"
tables. For example, if a Person can work with several different pieces of
Equipment, and a piece of Equipment might be used by several different
People, you'd need:

tblPerson
PersonID
... (any other facts about this Person)

tblEquipment
EquipmentID
...(any other facts about this piece of Equipment)

trelPersonEquipment
PersonEquipmentID
PersonID (this is a "foreign key" from the Person table)
EquipmentID (this is a "foreign key" from the Equipment table)
... (any other facts about this Person using this Equipment)

The same type of "junction" table would be needed to resolve Person-Vehicle
and Person-Projects.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/