Ok here's what I have:
Right now starting out I have 7 Tables as follows:
CustAcct
---AcctNumber PK
---DateCreate
---FirstName
---LastNameBusiness
---SecondContact
---Relationship
---Flag
---HowHearAbout
CustFlag
---FlagNumber PK
---AccountNumber FK CustAccount
---FlagDetails
---FlagDescription
---FlagDate
---FlagName
---FlagID
FlagType
---FlagID PK
---FlagType
CustAddr
---AcctNumber PK/FK CustAcct
---BillAddr
---ShipAddr
---PrevAddr
---PrevAddrDate
---PrevAddr2
---PrevAddrDate2
---SecondAddr
---City
---State
---Zip
---Country
CustPhone
---AcctNumber PK/FK CustAcct
---PhoneType1
---PhoneNum1
---PhoneType2
---PhoneNum2
---SecondPhoneType
---SecondPhoneNum
CustEmail
---AcctNumber PK/FK CustAcct
---Email1
---Email2
---Newsletter
PhoneType
---PhoneID PK
---PhoneType
Which is gonna change because I made some mistakes but I will run into the same issue once I make the changes. I think I may have just screwed up on the relationships or something. The Type tables will store info to create a drop down selection for that item which is working perfectly, but my problem is I want to be able to put multiple fields from different tables that will store plain text in the database into one form and add the data from the one page into multiple tables. Here is my current SQL query:
SELECT
custacct.AcctNumber,
custacct.DateCreate,
custacct.FirstName,
custacct.LastNameBusiness,
custacct.SecondContact,
custacct.Relationship,
custacct.Flag,
custacct.HowHearAbout,
custaddr.BillAddr,
custaddr.ShipAddr,
custemail.Email1,
custemail.Email2,
custemail.Newsletter,
custphone.PhoneType1,
custphone.PhoneNum1,
custphone.PhoneType2,
custphone.PhoneNum2,
custphone.SecondPhoneType,
custphone.SecondPhoneNum
FROM custacct
INNER JOIN custaddr ON custacct.AcctNumber = custaddr.AcctNumber
INNER JOIN custemail ON custacct.AcctNumber = custemail.AcctNumber
LEFT OUTER JOIN custflag ON custacct.AcctNumber = custflag.AcctNumber
INNER JOIN custphone ON custacct.AcctNumber = custphone.AcctNumber
That is what I want to display when adding a new record into the DB. I don't have that much experience with SQL. The error I get when trying to add the entry on the built form is:
<<< Record was NOT added >>>
Unknown column 'custaddr.BillAddr' in 'field list'
So now at this point I'm lost. Like I said though I am going to be changing the tables to better organize what I want to do but I just hit this snag and I know I'm just missing something stupid. Please any help would be greatly appriciated.
Cody