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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Question about tables
|
Thread Tools | |
Display Modes | |
|
|