aussie_user
Registered User.
- Local time
- Today, 20:17
- Joined
- Aug 6, 2002
- Messages
- 32
HI,
I am very
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.
I am very

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.