Auto fill part of a form from a project list table?

[Nomad53]

Hi,

Could I start my saying I'm not a programmer, my experence is all 'self thought (and two one day courses!). I do all the stuff in design view mode and have never done any sql, so I'm looking for a simple design view advice. This may have been ask a number of times already, but I've not been able to find anything from a search, so thought I'd post something

I have a master list of projects, with project reference number, project name, and nature of project.

I have also got a form for individuals to fill in details of project events, with date, time, name, and two or three other fields - also included are project reference and name. I'd like the name field to be auto filled when the user selects the project reference from a combo box; I think? (the list only shows open projects).

I'd did something similar some years ago in Access 2003 (I think) but cannot figure it out in the version I'm currently using 2010.

 

[jdraw]

A few suggestions:

a) Write a 4-5 line overview of WHAT exactly is the issue/opportunity you are trying to resolve. Clearly state the "THINGS" that are involved. Writing it down will help clarify your thoughts, and will be helpful as you develop your application, or ask for assistance.

b) Get your Tables and relations designed before you get too far into forms.

Read this article, and excellent overview of database design, when you get a moment.
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

 

[Nomad53]

Many thanks for the advice and after reading the article I would admit to maybe not thinking about the data enough before starting building it.
What I’m trying to build is a simple Insiders system, based on multiply clients (clients table), with each client having multiply projects (projects table), with each project having multiply insiders (insiders table). I intended splitting the db with one front end.
What I’m trying to do is in the form for insiders get the project name ‘autofilled' from the project table.
I was thinking of trying to do something like a subform, in which they could but in a ‘Like’ query, which would produce a list of open projects for that client, from which they could select the relevant project, which in turn would put the required information in the required field in the insiders form/table.
The last thing I want to happen is for them to try and remember the project name and let the user have a ‘free text’ field to type it in!!
Sorry if this is not clear – and for being a little more then 4 – 5 lines!

 

[jdraw]

Many thanks for the advice and after reading the article I would admit to maybe not thinking about the data enough before starting building it.
What I’m trying to build is a simple Insiders system, based on multiply clients (clients table), with each client having multiply projects (projects table), with each project having multiply insiders (insiders table). I intended splitting the db with one front end.
What I’m trying to do is in the form for insiders get the project name ‘autofilled' from the project table.
I was thinking of trying to do something like a subform, in which they could but in a ‘Like’ query, which would produce a list of open projects for that client, from which they could select the relevant project, which in turn would put the required information in the required field in the insiders form/table.
The last thing I want to happen is for them to try and remember the project name and let the user have a ‘free text’ field to type it in!!
Sorry if this is not clear – and for being a little more then 4 – 5 lines!

I have identified the major subjects in green.
What exactly is an INSIDER?

You should probably have tables for each of the things in green.

So in overview:

-You have a number of Clients
-Can a Client exist if he/she has no Projects?
-Can a Project exist if there is no Client
-to identify which Client has which Project you will need a Junction Table
-does every project require an insider??

see https://www.youtube.com/watch?v=7XstSSyG8fw for junction table and resolving 1 to Many

 

[Nomad53]

I have identified the major subjects in green.
What exactly is an INSIDER?

You should probably have tables for each of the things in green.

So in overview:

-You have a number of Clients
-Can a Client exist if he/she has no Projects? (Yes - not all our clients may undertake a project)
-Can a Project exist if there is no Client (NO - before undertaking a project we must have an engagement letter in place and therefore be a client)
-to identify which Client has which Project you will need a Junction Table (thanks I'll have a look at the link)
-does every project require an insider?? (good question! there are some projects for which there will be no insiders - therefore no project entry would be made on the db?)

see (removed link in order to post) for junction table and resolving 1 to Many

I do have tables for the items identified in green.

 

[jdraw]

To update the overview:

-You have a number of Clients
-A Client may have 0,1 or More Projects
-A Project must have a Client
-A Project may have 0,1 or More Insiders

Not sure I follow this statement.

(good question! there are some projects for which there will be no insiders - therefore no project entry would be made on the db?)

Please post a jpg of your tables and relationships.

 

[Nomad53]

To update the overview:

-You have a number of Clients
-A Client may have 0,1 or More Projects
-A Project must have a Client
-A Project may have 0,1 or More Insiders

Not sure I follow this statement.


Please post a jpg of your tables and relationships.

The overview update is correct - I'm afraid that as I do not have 10 posts I'm not allowed to post jpegs!

At the moment (just created!!) I have the Client name in the client table connected to the client name in the project table, and the project name (in the project table) linked to the project name in the insiders table - hope this helps.

And my I say many thanks for your help.

 

[YNWA]

The overview update is correct - I'm afraid that as I do not have 10 posts I'm not allowed to post jpegs!

At the moment (just created!!) I have the Client name in the client table connected to the client name in the project table, and the project name (in the project table) linked to the project name in the insiders table - hope this helps.

And my I say many thanks for your help.

You want clientID in Client tbl and ProjectID in Project table and InsiderID in Insider tbl.

You then want a clientID field in Project tbl and a ProjectID field in Insider tbl.

Then your links are from clientID (client tbl) to clientID (project tbl), then ProjectID (Project tbl) to ProjectID (Insider tbl).

These are primary key to foreign key relationships. 1 to many relationships as such.

When you create the forms and subforms, your master and child links on the forms will be these IDs.

Client names, project names etc... are not unique fields as a client could have the same name etc...

Use Autonumber on the PK's in each table, then have a field called Client Name etc...

 

[Nomad53]

Thanks YNWA,

Just leaving the office for the weekend, so will look at over the weekend.