creating forms from normal tables

[mjm2001]

i've been trying to create forms for this simple database and have no luck.. so been entering info manually. the problem is.. i have three tables
awards-projectsid,title,date,amount
recipients-recipientid,fn,mi,ln
projects_recipients-awardsid,recipientsid

i cant seem to get all the forms to talk to each other. i want to have the main form open to the award then a subform or command button to have a lookup of the recipients and if not in list i can add them. i hope this makes sense. i was told this was simple but i've been working on this since monday with no luck. thanks.

 

[gusbrunston]

Hi:

I don't see any common fields to relate your tables except "recipientid" and "recipientsid".
[tt]
awards recipients projects_recipients
projectsid recipientid <--> recipientsid
title fn awardsid
date mi
amount ln[/tt]

The form/subform construct is ideal for tables that have a one-to-many relationship.

The way your tables are now, you could relate fields in two of the tables (if they are the same data type), so a form would work for data entry/edit. But the table &quot;awards&quot; is an orphan.

It would be worthwhile to study the normalization of tables, especially relationships...that's why what we build are &quot;relational&quot; databases.

When you have the tables properly related, you could use the Wizard to build your forms until you were comfortable building them from scratch.

Also, at this early stage, it would be a good idea to look at the &quot;Table Analyzer&quot; under the &quot;tools&quot; tab in database view.

Another quick solution would be to put all the data in one table. There may very well be good reasons not to, but with the information you've provided, it's not clear.

You'll get a lot of help on this forum if you stick with it.

Cheers,




[tt]Gus Brunston - Access2000(DAO)
Skill level based on 1-10: 7, on the way to 6, and beyond!
Webmaster: www.rentdex.com[/tt]

 

[mjm2001]

Sorry about that. It looked better on paper until i started changing things.

its suppose to be

awards awards_recipients recipients
awardsid <---> awardsid
title recipientsid <---> recipientsid
date fn
amount ln

yes, recipients can have more than one awards and one award can have one or many recipients. that's why i have the awards_recipients table. i tried the form wizard and it doesnt let me enter an award and then a list of recipients or look up in a list.

 

[randy700]

I would recommend you change the structure of your tables. As they stand now, each award is always presented on the same date for the same amount. Consider this:

Awards Join Recipients
AwardsID <-----> AwardsID
Title RecipientsID <-----> RecipientsID
DateAwarded FirstName
AmountAwarded LastName