New Record, form query

[StuartBombay]

I have a company table and a sponsor table. Not all sponsors belong with a company, so I created a company called '*No Company' (asterisk so it will show first in list). My form is based on a query. The form has 2 combo boxes for selecting a record. Company (defaults to '*No Company) and Last Name. Last Name is filtered by Company.

The form is also used to add new sponsor records. My problem is with trying to create a new sponsor in the same company. If '*No Company' is the current company, then when I create a sponsor a new company record called '*No Company' is created.

I would like to add Companies on another form, and add sponsors to existing companies. I'm completely confused right now, any suggestions would be great!

The sql for the query under the form is here:

SELECT Sponsors.SponsorID, Company.CompanyID, Sponsors.Active, Sponsors.LastName, Sponsors.FirstName, Sponsors.Address, Sponsors.City, Sponsors.State, Sponsors.ZIP, Sponsors.email, Sponsors.HomePhone, Sponsors.MobilPhone, Sponsors.Notes, Company.CompanyName, Company.CompanyPhone
FROM Company RIGHT JOIN Sponsors ON Company.CompanyID = Sponsors.CompnayID
WHERE (((Sponsors.Active)=True))
ORDER BY Sponsors.LastName;

 

[fneily]

Some reading:
Fundamentals of Relational Database Design

http://r937.com/relational.html

A company may have many sponsers. A sponser may belong to many companies. A classic many-to-many relationship. Once again, relational databases, such as Access, doesn't like this. So you have to create what's known as a junction table. At minimum, it contains the primary keys of the two tables. And any COMMON data. So you have tblCompany, tblSponser and tblComSpon. tblComSpon is a junction table and would have fields:
ComSponID CompanyID SponserID CommonFields
CompSponID is the primary key of this table. CompanyID and SponserID are just common fields. Now a CompanyID can have many sponsers. A sponser can have many Companies.
In effect, you created two one-to-many relationships. You can now connect the tables for a query, or use comboboxes to pick companies and sponsers, and use subforms to enter data.
First fix your tables. And Read the reference.