subform and one to many relationship

aussie_user

Registered User.
Local time
Today, 20:17
Joined
Aug 6, 2002
Messages
32
HI,

I am very :confused:

I created a database to record various projects. For each project there may be up to 12 payments. I had setup a table to record each of those 12 payments and related information (date, GST). When I was trying to solve another problem, which I have now solved I kept having people suggest I normalize my database.

From these suggestions I was able to understand that rather than have 12 payment fields (and 12 dates and 12 GST's) I would have one payment field where each project could have several payments.

I exported all my existing data to excel, and am trying to fix up my database with no luck.

I created a PAYMENT table with the following fields:
PmtID
Project ID (which is the main field to link my various tables)
PmtAmt
PmtDate
GST

I setup a subform with these fields. I can enter the first payment information for a project but Access will not let me enter a second payment for a project. I have tried every conceivable relationship between the main Project table and the Payment table with not luck.

I understand it should be a one-to-many relationship..that is one ProjectID can have many PmtID's related to it.

If I try linking the ProjectID field Access will only allow a one-to-one relationship even though in the Payment table I would like to have Project 02OVER04 be able to have 7 different payments. The first one goes in no problem, but Access will not allow the 2nd payment.

I removed the ProjectID link and tried linking the ProjectID from PROJECTS with the PmtID from PAYMENTS and still no luck. I put PmtID into the PROJECT table as a lookup and tried to link the two PmtID fields. I could get a one to many join, but still the subform would not allow me to have more than one payment for each project.

Can anyone make sense of what I have tried to explain and tell me where I am going wrong?


I've tried looking thru Access help but can't seem to find what I need.

Thanks.
 
Are you able to post a screen shot of your relationships?
 
If the master/child links are not properly set for the subform, the ProjectID won't be populated and that could cause the problem.
 
screen shot of relationships

Here's a screen shot of the relationships
 

Attachments

  • relate2.jpg
    relate2.jpg
    99.1 KB · Views: 176
screen shot or master child link

here's a screen shot of the master child links
 

Attachments

  • master.jpg
    master.jpg
    44.5 KB · Views: 158
You will notice from you relationships window, that you have only a one-to-one relationship between table Projects and table Payments.

Thus your subform is adhering to this rule, and only permitting one record in Payments for each Projects record.


Until you correct the relationship, you will have no luck with the subform.

Brad.
 
can't change to one to many

I understand that I need a one to many relationship between the two tables but can't seem to achieve that.

I need to relate the ProjectID field in both tables as that is where the link is. I've tried every combination that the join type will allow but it always keeps coming up One - to - One..

I must be doing something wrong not being able to make it a one-to-many but I don't know what.
 
Check your table structures:

Tbl_Projects:
ProjectID (Autonumber) PrimaryKey
ProjectName (etc etc etc)


Tbl_Payments
PaymentID (Autonumber) PrimaryKey
ProjectID (Number) Will be ForeignKey-Relates to Tbl_Projects
PaymentDate etc


No need for PaymentID in Tbl_Projects


Brad
 
finally got it...

don't know why but finally figured it out..

and it was in my table structures

in My Project table I had ProjectID indexed as Yes no duplicates

I had the same in my Payments table. Once I changed it to Yes Duplicates allowed I was able to create the one to many relationship and now my subform works properly.


Thanks to everyone for their help.
 
You have a repeating group in the cost table. To be properly normalized, the format of your costs table should be:

tblCost
CostID
CostTypeID
Amount
other info?

Each of the fields of the current table is actually a costType.
 

Users who are viewing this thread

Back
Top Bottom