Question about tables

[Fill-in Form General Questions]

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.

 

[Golfinray]

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.

 

[Evi]

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"

 

[Fill-in Form General Questions]

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]

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"

 

[Fill-in Form General Questions]

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... (e-mail address removed)
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

 

[Evi]

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"

 

[Evi]

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"

 

[Fill-in Form General Questions]

(e-mail address removed). 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....