Prev: Employee Training Help
Next: Employee Training Help
From: AccessKay on 27 May 2010 17:28
I'm about to finish my first database and am in the planning stages to create
another. I want to express my gratitude to all that helped me with this. I
learned more with the help of this group in three months than in three years
of reading books and taking classes. I have the utmost respect for all of
you kind people. I just hope that I'll be able to find you all when they
close down this site.

I need some guidance about my table set-up. I'm going to have a data table
that will be updated each month for employee, project and hours worked. I
want to have a master table for projects and would like to have a one-to-many
relationship with my data table so that if a new project pops up in my data
table, I'll know about it. I have two obstacles about doing this.

1. Sometimes the project number in my data table will be truncated. I know
that it will be impossible to establish referential integrity so what's a
good way of handling this. The only idea I have which I have not tried
before is to do an update query on my data table to add the missing parts but
not sure how to go about doing this.

2. My projects that are nonbilliable will remain the same from month to
month but new billable projects will be added monthly. These will not be in
my master project table. Should I separate the data into two tables
(billable and nonbillable)? Would this be best?

Thank you very much for any suggestions.

From: KARL DEWEY on 27 May 2010 19:10
Ok, I'll try to put all the steps in this post.
BACKUP DATABASE BACKUP DATABASE BACKUP
DATABASE

Open Project table in design, insert a row, name it something like ProjID,
set the DataType to Autonumber, and save. This field is automatically
populated.

Open Project Data table in design, insert a row, name it same as you did in
Project table, set the DataType to number - long integer, and save.

Run an update query to copy the contents of the autonumber field to the
Project Data table field. Then run a query to see how many records in the
Project Data table has the new field empty. Fix these by filling in the
correct data.

Open the Relationship window. If you have a relationship set between the two
tables delete it. Create a relationship between the autonumber field that
the number field of the Project Data table, selecting Referential Integerity
and Cascade Update.

Use a form/subform for project/project data and set the Master/Child link on
the autonumber/number fields. When you add records to the subform (project
data table) it will automatically enter the number in the table (the cascade
update does it).


--
Build a little, test a little.


"AccessKay" wrote:

> Hi Karl,
>
> You've answered a bulk of my questions so a special thanks to you. I sure
> appreciate all the contributions you make. You're always helping everyone
> out.
>
> >>project number in my data table will be truncated. I know that it will be
> impossible to establish referential integrity
> >>Use an Autonumber field as primary key field and long integer as foreign key
> to maintain integerity.
>
> *****I was hoping that you could enlighten me about using the autonumber.
> Please keep in mind I'm fairly new to this. To me, the autonumber is just
> some meaningless number. So why would I want to use it as a primary key and
> then take the time to label a field with this number in my data table? It
> will take a considerable amount of time to assign this number to each of my
> projects in the data table. Is there a way to make Access do this for me?
> I've never been able to grasp this concept.
>
> >>Should I separate the data into two tables (billable and nonbillable)?
> >>One table with added field to indicate billable and nonbillable.
> *****Excellent idea about keeping my data in one table and adding a category
> field.
>
> Thanks again for your input.
>
>
> "KARL DEWEY" wrote:
>
> > >>project number in my data table will be truncated. I know that it will be
> > impossible to establish referential integrity
> > Use an Autonumber field as primary key field and long integer as foreign key
> > to maintain integerity.
> >
> > >>Should I separate the data into two tables (billable and nonbillable)?
> > One table with added field to indicate billable and nonbillable.
> >
> > --
> > Build a little, test a little.
> >
> >
> > "AccessKay" wrote:
> >
> > > I'm about to finish my first database and am in the planning stages to create
> > > another. I want to express my gratitude to all that helped me with this. I
> > > learned more with the help of this group in three months than in three years
> > > of reading books and taking classes. I have the utmost respect for all of
> > > you kind people. I just hope that I'll be able to find you all when they
> > > close down this site.
> > >
> > > I need some guidance about my table set-up. I'm going to have a data table
> > > that will be updated each month for employee, project and hours worked. I
> > > want to have a master table for projects and would like to have a one-to-many
> > > relationship with my data table so that if a new project pops up in my data
> > > table, I'll know about it. I have two obstacles about doing this.
> > >
> > > 1. Sometimes the project number in my data table will be truncated. I know
> > > that it will be impossible to establish referential integrity so what's a
> > > good way of handling this. The only idea I have which I have not tried
> > > before is to do an update query on my data table to add the missing parts but
> > > not sure how to go about doing this.
> > >
> > > 2. My projects that are nonbilliable will remain the same from month to
> > > month but new billable projects will be added monthly. These will not be in
> > > my master project table. Should I separate the data into two tables
> > > (billable and nonbillable)? Would this be best?
> > >
> > > Thank you very much for any suggestions.
> > >
From: AccessKay on 28 May 2010 13:01
This is what I needed but I can't try it out yet because I'm going to have to
brush up on update queries. I never really needed to use this query before
but I see how useful it can be. Thanks again Karl and I hope to see your
name out there in the future.


"KARL DEWEY" wrote:

> Ok, I'll try to put all the steps in this post.
> BACKUP DATABASE BACKUP DATABASE BACKUP
> DATABASE
>
> Open Project table in design, insert a row, name it something like ProjID,
> set the DataType to Autonumber, and save. This field is automatically
> populated.
>
> Open Project Data table in design, insert a row, name it same as you did in
> Project table, set the DataType to number - long integer, and save.
>
> Run an update query to copy the contents of the autonumber field to the
> Project Data table field. Then run a query to see how many records in the
> Project Data table has the new field empty. Fix these by filling in the
> correct data.
>
> Open the Relationship window. If you have a relationship set between the two
> tables delete it. Create a relationship between the autonumber field that
> the number field of the Project Data table, selecting Referential Integerity
> and Cascade Update.
>
> Use a form/subform for project/project data and set the Master/Child link on
> the autonumber/number fields. When you add records to the subform (project
> data table) it will automatically enter the number in the table (the cascade
> update does it).
>
>
> --
> Build a little, test a little.
>
>
> "AccessKay" wrote:
>
> > Hi Karl,
> >
> > You've answered a bulk of my questions so a special thanks to you. I sure
> > appreciate all the contributions you make. You're always helping everyone
> > out.
> >
> > >>project number in my data table will be truncated. I know that it will be
> > impossible to establish referential integrity
> > >>Use an Autonumber field as primary key field and long integer as foreign key
> > to maintain integerity.
> >
> > *****I was hoping that you could enlighten me about using the autonumber.
> > Please keep in mind I'm fairly new to this. To me, the autonumber is just
> > some meaningless number. So why would I want to use it as a primary key and
> > then take the time to label a field with this number in my data table? It
> > will take a considerable amount of time to assign this number to each of my
> > projects in the data table. Is there a way to make Access do this for me?
> > I've never been able to grasp this concept.
> >
> > >>Should I separate the data into two tables (billable and nonbillable)?
> > >>One table with added field to indicate billable and nonbillable.
> > *****Excellent idea about keeping my data in one table and adding a category
> > field.
> >
> > Thanks again for your input.
> >
> >
> > "KARL DEWEY" wrote:
> >
> > > >>project number in my data table will be truncated. I know that it will be
> > > impossible to establish referential integrity
> > > Use an Autonumber field as primary key field and long integer as foreign key
> > > to maintain integerity.
> > >
> > > >>Should I separate the data into two tables (billable and nonbillable)?
> > > One table with added field to indicate billable and nonbillable.
> > >
> > > --
> > > Build a little, test a little.
> > >
> > >
> > > "AccessKay" wrote:
> > >
> > > > I'm about to finish my first database and am in the planning stages to create
> > > > another. I want to express my gratitude to all that helped me with this. I
> > > > learned more with the help of this group in three months than in three years
> > > > of reading books and taking classes. I have the utmost respect for all of
> > > > you kind people. I just hope that I'll be able to find you all when they
> > > > close down this site.
> > > >
> > > > I need some guidance about my table set-up. I'm going to have a data table
> > > > that will be updated each month for employee, project and hours worked. I
> > > > want to have a master table for projects and would like to have a one-to-many
> > > > relationship with my data table so that if a new project pops up in my data
> > > > table, I'll know about it. I have two obstacles about doing this.
> > > >
> > > > 1. Sometimes the project number in my data table will be truncated. I know
> > > > that it will be impossible to establish referential integrity so what's a
> > > > good way of handling this. The only idea I have which I have not tried
> > > > before is to do an update query on my data table to add the missing parts but
> > > > not sure how to go about doing this.
> > > >
> > > > 2. My projects that are nonbilliable will remain the same from month to
> > > > month but new billable projects will be added monthly. These will not be in
> > > > my master project table. Should I separate the data into two tables
> > > > (billable and nonbillable)? Would this be best?
> > > >
> > > > Thank you very much for any suggestions.
> > > >
 | 
Pages: 1
Prev: Employee Training Help
Next: Employee Training Help