A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Question about tables



 
 
Thread Tools Display Modes
  #1  
Old April 30th, 2008, 06:50 PM posted to microsoft.public.access.tablesdbdesign
Fill-in Form General Questions
external usenet poster
 
Posts: 36
Default Question about tables

I am just starting to design a new database. My first one, so please be
patient with me!

I have a "Projects" table that has several fields. #, date, mgr, etc. I need
a field for the Resources (people) that are assigned to work on this project.
The number of people could be from 1-20. My question is:

* Should I create 20 fields, "resource 1", "resource 2", ..... "resource
20". Or is there a way to just create 1 field called resources that "adapts"
and can handle a list of resources.


  #2  
Old April 30th, 2008, 07:19 PM posted to microsoft.public.access.tablesdbdesign
Golfinray
external usenet poster
 
Posts: 1,597
Default Question about tables

One way might be to create another table with resource 1, resource 2, etc and
assign a number to each. This could be an autonumber. Then join the two
tables in a query and when you want resource 12 , type a 12 in the query
criteria.

"Fill-in Form General Questions" wrote:

I am just starting to design a new database. My first one, so please be
patient with me!

I have a "Projects" table that has several fields. #, date, mgr, etc. I need
a field for the Resources (people) that are assigned to work on this project.
The number of people could be from 1-20. My question is:

* Should I create 20 fields, "resource 1", "resource 2", ..... "resource
20". Or is there a way to just create 1 field called resources that "adapts"
and can handle a list of resources.


  #3  
Old April 30th, 2008, 08:36 PM posted to microsoft.public.access.tablesdbdesign
Evi
external usenet poster
 
Posts: 898
Default Question about tables

The sort of structure you will probably want is the ol' 2 to 1

TblProject
ProjID (Autonumber, Primary key)
ProjecNo (don't use the # symbol as a field name - it has other uses in
Access. You can put whatever you like in Forms and Reports but stick to
Short Simple field names - no spaces or symbols and use the Description
column in the tables (this will show up in the Status bar when you click on
the field
PDate (don't use Date - it's a reserved word and will cause problems
Mgr
fields only about the project and nothing about the resource
Add as many projects as you wish but add each one only once


TblResource
ResID (Autonumber, Primary key)
ResNo (1, 2, 3)
RFirstName
RSurName
fields only about the resource and nothing about the project

Add as many names to this as you wish but each person is added only once


TblProjectResource
ProResID (Autonumber, Primary key)
ProjID (linked from TblProject's ProjID)
ResID (linked from TblResource)

fields about that Resource with that Project

Link the fields as indicated in the Relationships Window


To put them together

Click on TblProject

Go to Insert
Form
Autoform - Columnar

Open this form in Design View

Drag TblProjectResource from the Database Window onto this form
The wizard should suggest linking the 2 forms by ProjectID

I've found it best to call the main form something like FrmResourceProject
and the Subform FrmResourceProjectSub (so you can tell which subform belongs
with which main)

Open your new subform in Design View. Go to Format, Hide Column and Hide the
Primary Key Field and ProjID

Add a combo box based on TblResource. Add to it ResID and the other fields
you require to identify the Resource (later you'll learn how to combine
FirstName and Surname in your combo but this will do for now)

Choose to Store the Value in ResID

Open the main form and you can choose which resource to use with which
project

Evi






"Fill-in Form General Questions"
. com wrote in message
...
I am just starting to design a new database. My first one, so please be
patient with me!

I have a "Projects" table that has several fields. #, date, mgr, etc. I

need
a field for the Resources (people) that are assigned to work on this

project.
The number of people could be from 1-20. My question is:

* Should I create 20 fields, "resource 1", "resource 2", ..... "resource
20". Or is there a way to just create 1 field called resources that

"adapts"
and can handle a list of resources.




  #4  
Old April 30th, 2008, 09:41 PM posted to microsoft.public.access.tablesdbdesign
Fill-in Form General Questions
external usenet poster
 
Posts: 36
Default Question about tables

Evi,

Thanks so much for the detailed description. I have created a test database
with the tables just as you mentioned. I was able to create the table and
establish the relationships.
I am stuck on the "Open this form in Design View". I was able to create the
form FrmResourceProject, but when I try to drag a table onto it, nothing
happens.

Maybe I'm doing it wrong...

Thanks,
Maurice



"Evi" wrote:

The sort of structure you will probably want is the ol' 2 to 1

TblProject
ProjID (Autonumber, Primary key)
ProjecNo (don't use the # symbol as a field name - it has other uses in
Access. You can put whatever you like in Forms and Reports but stick to
Short Simple field names - no spaces or symbols and use the Description
column in the tables (this will show up in the Status bar when you click on
the field
PDate (don't use Date - it's a reserved word and will cause problems
Mgr
fields only about the project and nothing about the resource
Add as many projects as you wish but add each one only once


TblResource
ResID (Autonumber, Primary key)
ResNo (1, 2, 3)
RFirstName
RSurName
fields only about the resource and nothing about the project

Add as many names to this as you wish but each person is added only once


TblProjectResource
ProResID (Autonumber, Primary key)
ProjID (linked from TblProject's ProjID)
ResID (linked from TblResource)

fields about that Resource with that Project

Link the fields as indicated in the Relationships Window


To put them together

Click on TblProject

Go to Insert
Form
Autoform - Columnar

Open this form in Design View

Drag TblProjectResource from the Database Window onto this form
The wizard should suggest linking the 2 forms by ProjectID

I've found it best to call the main form something like FrmResourceProject
and the Subform FrmResourceProjectSub (so you can tell which subform belongs
with which main)

Open your new subform in Design View. Go to Format, Hide Column and Hide the
Primary Key Field and ProjID

Add a combo box based on TblResource. Add to it ResID and the other fields
you require to identify the Resource (later you'll learn how to combine
FirstName and Surname in your combo but this will do for now)

Choose to Store the Value in ResID

Open the main form and you can choose which resource to use with which
project

Evi






"Fill-in Form General Questions"
. com wrote in message
...
I am just starting to design a new database. My first one, so please be
patient with me!

I have a "Projects" table that has several fields. #, date, mgr, etc. I

need
a field for the Resources (people) that are assigned to work on this

project.
The number of people could be from 1-20. My question is:

* Should I create 20 fields, "resource 1", "resource 2", ..... "resource
20". Or is there a way to just create 1 field called resources that

"adapts"
and can handle a list of resources.





  #5  
Old April 30th, 2008, 11:07 PM posted to microsoft.public.access.tablesdbdesign
Evi
external usenet poster
 
Posts: 898
Default Question about tables

Good for you to have got this far! I don't know if you are using Acc2007. If
yes, then my description of what follows may be different because the
buttons have all been disguised er.. I mean Organized.

To open a form in Design view, if it is closed, click on it in the db window
and click on the button that says Design (a blue set-square)

Use the window's restore button so that you can see both the form and the
database window, (if the form is still too large to resize, go to Window on
the Menu bar and choose Cascade then drag the windows into a suitable
position). Drag TblProjectResource from the database window and drop it onto
the form's Detail section ie on the part of the grey grid that has a bar
with the word Detail on it.

If the Wizard does not begin, ensure that you have pressed the Wizard button
(looks like a magic wand) try again but even so, it doesn't matter - we
don't need him and its his loss!

If the wizard doesn't link the 2 forms, click on the subform, click the
Properties button, Click on the Data tab and next to Link Master Fields and
Link Child Fields
type
ProdID

Evi


"Fill-in Form General Questions"
. com wrote in message
...
Evi,

Thanks so much for the detailed description. I have created a test

database
with the tables just as you mentioned. I was able to create the table and
establish the relationships.
I am stuck on the "Open this form in Design View". I was able to create

the
form FrmResourceProject, but when I try to drag a table onto it, nothing
happens.

Maybe I'm doing it wrong...

Thanks,
Maurice



"Evi" wrote:

The sort of structure you will probably want is the ol' 2 to 1

TblProject
ProjID (Autonumber, Primary key)
ProjecNo (don't use the # symbol as a field name - it has other uses in
Access. You can put whatever you like in Forms and Reports but stick to
Short Simple field names - no spaces or symbols and use the Description
column in the tables (this will show up in the Status bar when you click

on
the field
PDate (don't use Date - it's a reserved word and will cause problems
Mgr
fields only about the project and nothing about the resource
Add as many projects as you wish but add each one only once


TblResource
ResID (Autonumber, Primary key)
ResNo (1, 2, 3)
RFirstName
RSurName
fields only about the resource and nothing about the project

Add as many names to this as you wish but each person is added only once


TblProjectResource
ProResID (Autonumber, Primary key)
ProjID (linked from TblProject's ProjID)
ResID (linked from TblResource)

fields about that Resource with that Project

Link the fields as indicated in the Relationships Window


To put them together

Click on TblProject

Go to Insert
Form
Autoform - Columnar

Open this form in Design View

Drag TblProjectResource from the Database Window onto this form
The wizard should suggest linking the 2 forms by ProjectID

I've found it best to call the main form something like

FrmResourceProject
and the Subform FrmResourceProjectSub (so you can tell which subform

belongs
with which main)

Open your new subform in Design View. Go to Format, Hide Column and Hide

the
Primary Key Field and ProjID

Add a combo box based on TblResource. Add to it ResID and the other

fields
you require to identify the Resource (later you'll learn how to combine
FirstName and Surname in your combo but this will do for now)

Choose to Store the Value in ResID

Open the main form and you can choose which resource to use with which
project

Evi






"Fill-in Form General Questions"
. com wrote in message
...
I am just starting to design a new database. My first one, so please

be
patient with me!

I have a "Projects" table that has several fields. #, date, mgr, etc.

I
need
a field for the Resources (people) that are assigned to work on this

project.
The number of people could be from 1-20. My question is:

* Should I create 20 fields, "resource 1", "resource 2", .....

"resource
20". Or is there a way to just create 1 field called resources that

"adapts"
and can handle a list of resources.







  #7  
Old May 1st, 2008, 02:28 PM posted to microsoft.public.access.tablesdbdesign
Evi
external usenet poster
 
Posts: 898
Default Question about tables

Hi Mauricio. Actually my email address is fairly simple to DeSpam - take a
look at it. I really wouldn't advise recording your own unspammed email
addy - you'll be bombed with spam.

As you rightly sense, sending a database to someone isn't really ideal.
I've had some bad experiences with being sent databases - they always end
up being much more complex than when they were first depicted and my
intended quick fling always ends up in a settled relationship with the
in-laws dropping in regularly for coffee!

Can you say where you got stuck? What were you trying to do and what
happened?

Evi



"Fill-in Form General Questions"
. com wrote in message
news
Evi.

Thanks again. I got a little further, but I am still stuck somewhere. Can

I
send it to you for a quick look and then we can publish the results here

for
others to see. I will give you my email address since I am assuming you
probably don't want to publish yours...
Please send me an email so I can forward you the database and maybe you

can
help me a little more, I promise not to bug you ever again directly...

Thanks
Mauricio

"Fill-in Form General Questions" wrote:

I am just starting to design a new database. My first one, so please be
patient with me!

I have a "Projects" table that has several fields. #, date, mgr, etc. I

need
a field for the Resources (people) that are assigned to work on this

project.
The number of people could be from 1-20. My question is:

* Should I create 20 fields, "resource 1", "resource 2", ..... "resource
20". Or is there a way to just create 1 field called resources that

"adapts"
and can handle a list of resources.






  #8  
Old May 1st, 2008, 02:33 PM posted to microsoft.public.access.tablesdbdesign
Evi
external usenet poster
 
Posts: 898
Default Question about tables

Hi mauricio, I've just tried out your email addy but it bounced straight
back. De-spam my address and email me.
Evi

"Fill-in Form General Questions"
. com wrote in message
news
Evi.

Thanks again. I got a little further, but I am still stuck somewhere. Can

I
send it to you for a quick look and then we can publish the results here

for
others to see. I will give you my email address since I am assuming you
probably don't want to publish yours
Please send me an email so I can forward you the database and maybe you

can
help me a little more, I promise not to bug you ever again directly...

Thanks
Mauricio

"Fill-in Form General Questions" wrote:

I am just starting to design a new database. My first one, so please be
patient with me!

I have a "Projects" table that has several fields. #, date, mgr, etc. I

need
a field for the Resources (people) that are assigned to work on this

project.
The number of people could be from 1-20. My question is:

* Should I create 20 fields, "resource 1", "resource 2", ..... "resource
20". Or is there a way to just create 1 field called resources that

"adapts"
and can handle a list of resources.




  #9  
Old May 1st, 2008, 03:12 PM posted to microsoft.public.access.tablesdbdesign
Fill-in Form General Questions
external usenet poster
 
Posts: 36
Default Question about tables

. The database I put together is the basic
3-table one that you gave me. It's not complicated and I promise not to drop
by for coffee. I can promise you a drink in NY if you're ever over here....

"Evi" wrote:

Hi mauricio, I've just tried out your email addy but it bounced straight
back. De-spam my address and email me.
Evi

"Fill-in Form General Questions"
. com wrote in message
news
Evi.

Thanks again. I got a little further, but I am still stuck somewhere. Can

I
send it to you for a quick look and then we can publish the results here

for
others to see. I will give you my email address since I am assuming you
probably don't want to publish yours
Please send me an email so I can forward you the database and maybe you

can
help me a little more, I promise not to bug you ever again directly...

Thanks
Mauricio

"Fill-in Form General Questions" wrote:

I am just starting to design a new database. My first one, so please be
patient with me!

I have a "Projects" table that has several fields. #, date, mgr, etc. I

need
a field for the Resources (people) that are assigned to work on this

project.
The number of people could be from 1-20. My question is:

* Should I create 20 fields, "resource 1", "resource 2", ..... "resource
20". Or is there a way to just create 1 field called resources that

"adapts"
and can handle a list of resources.





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.