Struggles with an update query ;(

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cath - 27 Apr 2007 21:54 GMT

Hi There,
I would really appreciate some assistance, I am designing a database (low
key) but I am trying to get my face known in an org (nothing to do with
database thank goodness!) but I volunteered to do this database and have hit
a major snag and I am not prepared to accept defeat.

So this is my issue, if there is a lifesaver out help ! ;)
So this is what I have at the moment.
1) A personal detail type records table
2)An evaluation form for projects attended.
3) What they would like is that when indivduals attend a project the project
name is updated in the details form under "projects attended".
4) So I need to organise it so that when an indivudals name appears on the
evaluation sheet, this will kick-start an auto-population of the projects
attended field in their personal details record with the project name from
the evaluation sheet.

I know this is a mess and I need help to unpick it and fix it.

Many thanks
Cath

Reply to this Message

Smartin - 27 Apr 2007 22:58 GMT

> Hi There,
> I would really appreciate some assistance, I am designing a database (low
[quoted text clipped - 17 lines]
> Many thanks
> Cath

Hi Cath

Is it required to have two separate forms? The information you are
trying to display lends itself nicely to the form/subform concept. You
would create a form based on People, with a subform based on Projects.
As long as Projects has a foreign key to the People table, the subform
will automagically display all the relevant Project information for the
Person on display.

Signature

Smartin

Reply to this Message

Cath - 17 Jun 2007 21:27 GMT

Hi Smartin,

Many many thanks for your post in response to my plea for assistance. I am
now revisiting the database and have followed your advice. However I have hit
a snag... I have the subform set up now, and tried to do the foreign key set
up, which ended up being "Project ID# which was an auto-generated number for
the project table and I added this to the people table/form" which meant I
could now see the link but it is not segregating the information, for each
person it is displaying all the projects that have been run. What need to
achieve is when an individuals name appears in one of the volunteer name
fields (1 - 10) then it will populate in the subform arrangement.

Do you think you could still help me out? I am hopelessly lost....

Many thanks for your time
Cath

> > Hi There,
> > I would really appreciate some assistance, I am designing a database (low
[quoted text clipped - 26 lines]
> will automagically display all the relevant Project information for the
> Person on display.

Reply to this Message

Steve Schapel - 18 Jun 2007 11:20 GMT

Cath,

I haven't tracked back to the previous discussions about this. However,
this is not correct. Project ID should not be a field in the People
table. That would imply that each Person is ever associated with only
one Project, which I gather is not the case.

The relationship between the main form and the subform, as suggested by
Smartin, would be based on the Person ID. But even there, you would not
have a Person ID field in the Projects table either. You need a third
table, let's call it Attendances, to record each instance of a Person
attending a Project.

It appears you have 10 fields for Volunteers. This is also incorrect.
Each Volunteer should be a separate *record* in a related table, not a
separate *field* in a base table.

It may help you to have a look through this article:
http://accesstips.datamanagementsolutions.biz/many.htm

By the way, as an aside, it is not a good idea to use a # as part of the
name of a field or control.

Signature

Steve Schapel, Microsoft Access MVP

> Hi Smartin,
>
[quoted text clipped - 7 lines]
> achieve is when an individuals name appears in one of the volunteer name
> fields (1 - 10) then it will populate in the subform arrangement.

Reply to this Message