New User

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John Calder - 20 Jun 2006 02:13 GMT

Hi

I Run WIn2k with Access 2K

I am a complete novice with this program so I have a few questions.

Senario

I create a table with employee names (called "Names")

I create another table with employees crews (ie: A Crew, B Crew, C Crew etc)
(Called "Crews")

I create a third table with supplimentary data (ie: date, time, part number
etc)

Questions

In the third table do I have to include a column for "Names" data and a
column for "Crews" data?

If I do, then it seems pointless to have a seperate table for these?

If not, where is this information stored? If the case is that it is linked
through relationships then how do I determine the correct relation ship? Do I
just make the ID Autonumber the relationship between each table?

Also, if I enter the names in the "Names" table in a non-alphabetical order,
a combo box in a form reflects this, however if I then go back and sort the
names table the combo box still shows a non alphabetical order, why is this?
or better still how do I get the data in the combo box to reflect the now
sorted data in the "Names" table?

Any help is much appreciated

John

Reply to this Message

John Vinson - 20 Jun 2006 02:32 GMT

>Hi
>
[quoted text clipped - 5 lines]
>
>I create a table with employee names (called "Names")

What's the structure of the table? Does it have a Primary Key? I'd
suggest a structure like:

Names
PersonID <Autonumber or your own unique ID>
FirstName
LastName
Suffix
<other bio information>

>I create another table with employees crews (ie: A Crew, B Crew, C Crew etc)
>(Called "Crews")

Again... what's the structure?

>I create a third table with supplimentary data (ie: date, time, part number
>etc)
[quoted text clipped - 3 lines]
>In the third table do I have to include a column for "Names" data and a
>column for "Crews" data?

No, certainly not. The whole POINT of a relational database is to
avoid redundancy. You store information once, and then use Queries to
link to it.

I have no idea what real-life entity this third table represents, so I
cannot say for sure how it should be related - but if it refers to
some action carried out by a person, acting as a member of a crew,
then the table should have a PersonID as a link to Names, and a
CrewName or CrewID as a link to the primary key of the Crews table
(whatever that is).

>If I do, then it seems pointless to have a seperate table for these?
>
>If not, where is this information stored? If the case is that it is linked
>through relationships then how do I determine the correct relation ship? Do I
>just make the ID Autonumber the relationship between each table?

No. Each table must have a Primary Key - some field which uniquely
identifies the record; but also it should have separate "foreign key"
fields as links. The third person that you enter into the database
would not, in general, have anything specific to do with the third
crew that you enter!

>Also, if I enter the names in the "Names" table in a non-alphabetical order,
>a combo box in a form reflects this, however if I then go back and sort the
>names table the combo box still shows a non alphabetical order, why is this?
>or better still how do I get the data in the combo box to reflect the now
>sorted data in the "Names" table?

Base the combo, not on the Names table, but on a sorted Query based on
the Names table. Tables should be viewed as data repositories -
unordered "heaps" of data. If you want to see the data in a particular
order, use a Query.

Check out the links at

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

especially the Database Design 101 links.

John W. Vinson[MVP]

Reply to this Message

John Calder - 20 Jun 2006 03:19 GMT

Thanks for your quick response John

The structure of the "Names" table is:

ID (Autonumber) - Primary Key
Names (eg: Smith, John)

The structure of the "Crews" table is:

ID (Autonumber) - Primary Key
Crews (eg: A Crew, B Crew, etc)

The structure of the "Input" table is:

ID (Autonumber) - Primary Key
Date (eg: 12-Aug-06)
Time (eg: 14:56)
Coil Number (eg: A12345)
Damaged? (eg: Yes/No box)
Comments (Memo box)
Operators Comments (Memeo box)

What I hope to end up with is a form that caters for:

Name (combo box)
Crew (combo box)
Date
Time
Coil Number
Damaged? (Yes/No tick box)
Comments (memo box)
Operators Comments (memo box)

Hope this helps

John

> >Hi
> >
[quoted text clipped - 70 lines]
>
> John W. Vinson[MVP]

Reply to this Message

John Vinson - 20 Jun 2006 03:57 GMT

>Thanks for your quick response John
>
>The structure of the "Names" table is:
>
>ID (Autonumber) - Primary Key
>Names (eg: Smith, John)

I'd REALLY recommend using two fields, FirstName and LastName. It's
much easier to concatenate the values like

[LastName] & ", " & [FirstName]

or

[FirstName] & " " & [LastName]

than it is to pick them apart; and you can Index both fields so you
can quickly search or sort by either first or last name, or both.

>The structure of the "Crews" table is:
>
>ID (Autonumber) - Primary Key
>Crews (eg: A Crew, B Crew, etc)

If the [Crews] field is unique within the table, you may not need the
autonumber. A Primary Key needs to be unique, stable, and short; I'd
say that the text string "Crew A" qualifies. This might simply be a
one-field table.

>The structure of the "Input" table is:
>
>ID (Autonumber) - Primary Key
>Date (eg: 12-Aug-06)
>Time (eg: 14:56)

Don't use the reserved words Date or Time for fieldnames: Access
*will* get confused! And you may want to use just a single Date/Time
field. Access stores date/times as numbers, a count of days and
fractions of a day (times) since midnight, December 30, 1899. If
you'll be searching for ranges, it can be much simpler if you just
have one field rather than having to combine two.

>Coil Number (eg: A12345)
>Damaged? (eg: Yes/No box)
>Comments (Memo box)
>Operators Comments (Memeo box)

Does the information in the input table relate to an employee, or to a
crew, in any way? Or are these three independent unrelated tables? I'm
guessing that an Employee is a member of a Crew (or perhaps more than
one crew? I don't know your business structure!); and that an Input
record should indicate either which employee, or which crew, or both,
was involved with this coil.

If so, you need to include a Long Integer (to link to an autonumber,
which is a specially-handled Long) EmployeeID and/or CrewID field, or
if you use my suggestion of a text Crew primary key, just a Crew text
field.

If you do so, use the Relationships window to define a one to many
relationship from Employees to Input, and another from Crews to Input.
This will ensure that you can't enter a nonexistant employee or crew
to the Input table.

>What I hope to end up with is a form that caters for:
>
>Name (combo box)
>Crew (combo box)

These would need to be bound to the Employeeid and Crew (or CrewID)
fields that I'm suggesting that you should add to your table.

>Date
>Time

This could just be one textbox.

>Coil Number

Will this be a new entry for every record? Or do you have a predefined
table of coils?

>Damaged? (Yes/No tick box)
>Comments (memo box)
[quoted text clipped - 3 lines]
>
>John

Should be really straightforward then. You may also want two little
forms to add new Employees and new Crews to their respective tables.

John W. Vinson[MVP]

Reply to this Message

John Calder - 23 Jun 2006 04:00 GMT

Thanks John, got some valuable info from your answer but I have a long way to
go. I am really struggling to understand relationships so I have posted
another question re this.

> >Thanks for your quick response John
> >
[quoted text clipped - 90 lines]
>
> John W. Vinson[MVP]

Reply to this Message