Starting a new database

[Chad Cameron]

Hi Everyone,

I thought I had a handle on access and db's. I have been make changes to
our current database and am quite comfortable with it.

My company wants me to create a new database, which I thought would be easy.
It is a simple database with 5 tables. 4 tables never change, they have data
to specify [equipment id numbers], [equipment type], [day/night shift], and
[A,B,C or D crews].

Then I have 1 table that uses the data from the other tables, and adds how
many hours the equipment is up or down.

Sounds easy. I want to create a form inwhich I am able to enter the date,
shift & crew in the form header, and the equipment #, hours up and down in
the detail. I did that, but, a couple of things are happening:

1. When I select say crew B, it seems to overwrite the data in my crew
table, so now it is something like A,B,B,D instead of A,B,C,D
2. When I select my equipment, it overwrites the equipment number with the
equipmentID number (autonumber)
3. I cannot get this data to store in my timecard table. (the table to
store every entry).

I guess my novice status has just dropped to 'doesn't have a clue' status.

Any help would be great.
Chad

 

[Wayne-I-M]

Hi Chad

Sounds like you need a linking table

So you have
tblEquipmnet
EuipID
EuipName
etc
etc

tblTeam
TeamID
TeamName
TeamMemberID - you need another table for this is teams may ever change

Then you add a linking table

tblEuipUses
UsesID
TeamID
EuipID
UseStart
UseEnd
UseDamages
etc
etc

 

[Chad Cameron]

Thanks Wayne,

I am on the right track. My table that holds all the data has the following
fields:
TimeCardID
CrewID
ShiftID
Date
EquipmentID
UpHours
DownHours

When I manually enter the data into the table everything is fine. By that I
mean, when I run my query, everything looks good.

Now, I want to enter the data via a form, not the table. That is where
everything screws up. The crew and shift doesn't change for any given date.
That is why I wanted it seperated, on the form. But I can't every get it to
enter the data into my table with a simple form where I type in everything.

It always seems to change the data in my source tables and not add it to my
final table.

I don't know enough to be able to troubleshoot if I have a wrong property
set somewhere.

Thanks again,
Chad

Hi Chad

Sounds like you need a linking table

So you have
tblEquipmnet
EuipID
EuipName
etc
etc

tblTeam
TeamID
TeamName
TeamMemberID - you need another table for this is teams may ever change

Then you add a linking table

tblEuipUses
UsesID
TeamID
EuipID
UseStart
UseEnd
UseDamages
etc
etc


--
Wayne
Manchester, England.

Hi Everyone,

I thought I had a handle on access and db's. I have been make changes to
our current database and am quite comfortable with it.

My company wants me to create a new database, which I thought would be
easy.
It is a simple database with 5 tables. 4 tables never change, they have
data
to specify [equipment id numbers], [equipment type], [day/night shift],
and
[A,B,C or D crews].

Then I have 1 table that uses the data from the other tables, and adds
how
many hours the equipment is up or down.

Sounds easy. I want to create a form inwhich I am able to enter the
date,
shift & crew in the form header, and the equipment #, hours up and down
in
the detail. I did that, but, a couple of things are happening:

1. When I select say crew B, it seems to overwrite the data in my crew
table, so now it is something like A,B,B,D instead of A,B,C,D
2. When I select my equipment, it overwrites the equipment number with
the
equipmentID number (autonumber)
3. I cannot get this data to store in my timecard table. (the table to
store every entry).

I guess my novice status has just dropped to 'doesn't have a clue'
status.

Any help would be great.
Chad

 

[Wayne-I-M]

Think this is the problem

The link table will only "link" data and update to the table into fields for
"that" table. If you alter anything esle you will overwright the data you
already have.

Try this
Open the form based on your linking table (sorry don't know the correct
english word for it) - add a combo box based on the Team ID and Shift ID
tables. You can the select the Team and Shift (and display any information
from those tables) without altering the records

This is exactley what access was made for - there is a realationship between
the tables and you are adding data the linking tables -


--
Wayne
Manchester, England.

Thanks Wayne,

I am on the right track. My table that holds all the data has the following
fields:
TimeCardID
CrewID
ShiftID
Date
EquipmentID
UpHours
DownHours

When I manually enter the data into the table everything is fine. By that I
mean, when I run my query, everything looks good.

Now, I want to enter the data via a form, not the table. That is where
everything screws up. The crew and shift doesn't change for any given date.
That is why I wanted it seperated, on the form. But I can't every get it to
enter the data into my table with a simple form where I type in everything.

It always seems to change the data in my source tables and not add it to my
final table.

I don't know enough to be able to troubleshoot if I have a wrong property
set somewhere.

Thanks again,
Chad

Hi Chad

Sounds like you need a linking table

So you have
tblEquipmnet
EuipID
EuipName
etc
etc

tblTeam
TeamID
TeamName
TeamMemberID - you need another table for this is teams may ever change

Then you add a linking table

tblEuipUses
UsesID
TeamID
EuipID
UseStart
UseEnd
UseDamages
etc
etc


--
Wayne
Manchester, England.

Hi Everyone,

I thought I had a handle on access and db's. I have been make changes to
our current database and am quite comfortable with it.

My company wants me to create a new database, which I thought would be
easy.
It is a simple database with 5 tables. 4 tables never change, they have
data
to specify [equipment id numbers], [equipment type], [day/night shift],
and
[A,B,C or D crews].

Then I have 1 table that uses the data from the other tables, and adds
how
many hours the equipment is up or down.

Sounds easy. I want to create a form inwhich I am able to enter the
date,
shift & crew in the form header, and the equipment #, hours up and down
in
the detail. I did that, but, a couple of things are happening:

1. When I select say crew B, it seems to overwrite the data in my crew
table, so now it is something like A,B,B,D instead of A,B,C,D
2. When I select my equipment, it overwrites the equipment number with
the
equipmentID number (autonumber)
3. I cannot get this data to store in my timecard table. (the table to
store every entry).

I guess my novice status has just dropped to 'doesn't have a clue'
status.

Any help would be great.
Chad

 

[Wayne-I-M]

just thought I would add to this

The link table will only "link" data and update to the table into fields for
"that" table. If you alter anything esle you will overwright the data you
already have.

of course you "can" update the data in a number of tables from the form -
but in this case you are not wanting to do this -

--
Wayne
Manchester, England.

Think this is the problem

The link table will only "link" data and update to the table into fields for
"that" table. If you alter anything esle you will overwright the data you
already have.

Try this
Open the form based on your linking table (sorry don't know the correct
english word for it) - add a combo box based on the Team ID and Shift ID
tables. You can the select the Team and Shift (and display any information
from those tables) without altering the records

This is exactley what access was made for - there is a realationship between
the tables and you are adding data the linking tables -


--
Wayne
Manchester, England.

Thanks Wayne,

I am on the right track. My table that holds all the data has the following
fields:
TimeCardID
CrewID
ShiftID
Date
EquipmentID
UpHours
DownHours

When I manually enter the data into the table everything is fine. By that I
mean, when I run my query, everything looks good.

Now, I want to enter the data via a form, not the table. That is where
everything screws up. The crew and shift doesn't change for any given date.
That is why I wanted it seperated, on the form. But I can't every get it to
enter the data into my table with a simple form where I type in everything.

It always seems to change the data in my source tables and not add it to my
final table.

I don't know enough to be able to troubleshoot if I have a wrong property
set somewhere.

Thanks again,
Chad

Hi Chad

Sounds like you need a linking table

So you have
tblEquipmnet
EuipID
EuipName
etc
etc

tblTeam
TeamID
TeamName
TeamMemberID - you need another table for this is teams may ever change

Then you add a linking table

tblEuipUses
UsesID
TeamID
EuipID
UseStart
UseEnd
UseDamages
etc
etc


--
Wayne
Manchester, England.



:

Hi Everyone,

I thought I had a handle on access and db's. I have been make changes to
our current database and am quite comfortable with it.

My company wants me to create a new database, which I thought would be
easy.
It is a simple database with 5 tables. 4 tables never change, they have
data
to specify [equipment id numbers], [equipment type], [day/night shift],
and
[A,B,C or D crews].

Then I have 1 table that uses the data from the other tables, and adds
how
many hours the equipment is up or down.

Sounds easy. I want to create a form inwhich I am able to enter the
date,
shift & crew in the form header, and the equipment #, hours up and down
in
the detail. I did that, but, a couple of things are happening:

1. When I select say crew B, it seems to overwrite the data in my crew
table, so now it is something like A,B,B,D instead of A,B,C,D
2. When I select my equipment, it overwrites the equipment number with
the
equipmentID number (autonumber)
3. I cannot get this data to store in my timecard table. (the table to
store every entry).

I guess my novice status has just dropped to 'doesn't have a clue'
status.

Any help would be great.
Chad

 

[Chad Cameron]

I guess I am going to have to walk through every step. How frusterating.

1. My main forms record source: should it be my linked table or my query?
My linked table only has the ID fields of all my tables, the query has the
actual data fields.

If the record source is set to the linked table, I can only see the
Autonumbered ID fields. This doesn't work because I want to be able to see
the field with the actual names in it.

So, I change the record source to the query. Now I can select the actual
names, but I am unable to type in any of my textboxes. Access just beeps at
me. Comboboxes seem to work fine.

Chad

Think this is the problem

The link table will only "link" data and update to the table into fields
for
"that" table. If you alter anything esle you will overwright the data you
already have.

Try this
Open the form based on your linking table (sorry don't know the correct
english word for it) - add a combo box based on the Team ID and Shift ID
tables. You can the select the Team and Shift (and display any
information
from those tables) without altering the records

This is exactley what access was made for - there is a realationship
between
the tables and you are adding data the linking tables -


--
Wayne
Manchester, England.

Thanks Wayne,

I am on the right track. My table that holds all the data has the
following
fields:
TimeCardID
CrewID
ShiftID
Date
EquipmentID
UpHours
DownHours

When I manually enter the data into the table everything is fine. By
that I
mean, when I run my query, everything looks good.

Now, I want to enter the data via a form, not the table. That is where
everything screws up. The crew and shift doesn't change for any given
date.
That is why I wanted it seperated, on the form. But I can't every get it
to
enter the data into my table with a simple form where I type in
everything.

It always seems to change the data in my source tables and not add it to
my
final table.

I don't know enough to be able to troubleshoot if I have a wrong property
set somewhere.

Thanks again,
Chad

Hi Chad

Sounds like you need a linking table

So you have
tblEquipmnet
EuipID
EuipName
etc
etc

tblTeam
TeamID
TeamName
TeamMemberID - you need another table for this is teams may ever change

Then you add a linking table

tblEuipUses
UsesID
TeamID
EuipID
UseStart
UseEnd
UseDamages
etc
etc


--
Wayne
Manchester, England.



:

Hi Everyone,

I thought I had a handle on access and db's. I have been make changes
to
our current database and am quite comfortable with it.

My company wants me to create a new database, which I thought would be
easy.
It is a simple database with 5 tables. 4 tables never change, they
have
data
to specify [equipment id numbers], [equipment type], [day/night
shift],
and
[A,B,C or D crews].

Then I have 1 table that uses the data from the other tables, and adds
how
many hours the equipment is up or down.

Sounds easy. I want to create a form inwhich I am able to enter the
date,
shift & crew in the form header, and the equipment #, hours up and
down
in
the detail. I did that, but, a couple of things are happening:

1. When I select say crew B, it seems to overwrite the data in my crew
table, so now it is something like A,B,B,D instead of A,B,C,D
2. When I select my equipment, it overwrites the equipment number with
the
equipmentID number (autonumber)
3. I cannot get this data to store in my timecard table. (the table
to
store every entry).

I guess my novice status has just dropped to 'doesn't have a clue'
status.

Any help would be great.
Chad

 

[John W. Vinson]

Then I have 1 table that uses the data from the other tables

THAT is your problem.

It would appear that you're misunderstanding the entire rationale of
relational databases! Perhaps you're so close to the trees that it's hard to
see the forest!

You would NOT have a table to contain data from other tables. Instead, each
table would store its own kind of data, and you would use a Query, or other
tools such as a form with combo boxes and other controls, to *display* data
pulled from multiple tables on the same screen. It is neither necessary nor
appropriate (nor, fortunately, very easy <g>) to store data redundantly from
one set of tables into another table.

Perhaps I'm misinterpreting but if not you do need to get this straight right
away!

 

[Chad Cameron]

I didn't think was duplicating data, except for my ID Fields. Here is a
complete breakdown.
Table Crew
CrewID
Crew

Table Shift
ShiftID
Shift

etc.

Table TimeCardData
Date
CrewID
ShiftID
etc
HoursUp
HoursDown
HoursIdle
Comments

 

[John W. Vinson]

I didn't think was duplicating data, except for my ID Fields.

My apologies. I misunderstood. You're doing it right.

You won't be able to edit the text in combo boxes on the form, other than by
using the combo box's Not In List event (perhaps to open a maintenance form to
add a new record to the combo's rowsource table).