Solved Changed relationships has stopped a form working

Local time
Today, 23:00
Joined
Mar 25, 2024
Messages
75
A form to enter a new member's details has stopped working, after adding an intermediate table.
Previously:
1758965127192.png

now:
1758965297287.png
(the red squiggles mark fields I know are not needed)
the tblContact_Categories was needed between Contacts and tblCategoryTypes as one contact can have more than one CategoryType (ie qualified on more than one type of vehicle)
I have tried to modify the query on which the input form is based, but cannot get the SQL to work (Access shows syntax errors)

FWIW here is the current SQL:

SELECT Contacts.ContactTypeID, Contacts.[CategoryType ID], [Category Types].[Category Type], Contacts.IAMNo, Contacts.FirstName, Contacts.LastName, Contacts.Address, Contacts.Location, Contacts.City, Contacts.StateOrProvince, Contacts.PostalCode, Contacts.MobilePhone, Contacts.Email, Contacts.MWAMRenewal, Contacts.[Date Joined], [Contact Types].ContactType, Contacts.ObserverID, Contacts.ContactID, Contacts.MemberNo
FROM [Contact Types] INNER JOIN ([Category Types] INNER JOIN (Contacts ON [tblCategoryTypes].[CategoryTypeID] = [tblContact_Categories].[ContactID]) ON [Contact Types].[ContactTypeID] = [Contacts].[ContactTypeID])
i.e.
SQL:
SELECT Contacts.ContactTypeID, Contacts.[CategoryType ID], [Category Types].[Category Type], Contacts.IAMNo, Contacts.FirstName, Contacts.LastName, Contacts.Address, Contacts.Location, Contacts.City, Contacts.StateOrProvince, Contacts.PostalCode, Contacts.MobilePhone, Contacts.Email, Contacts.MWAMRenewal, Contacts.[Date Joined], [Contact Types].ContactType, Contacts.ObserverID, Contacts.ContactID, Contacts.MemberNo
FROM [Contact Types] INNER JOIN ([Category Types] INNER JOIN (Contacts ON [tblCategoryTypes].[CategoryTypeID] = [tblContact_Categories].[ContactID]) ON [Contact Types].[ContactTypeID] = [Contacts].[ContactTypeID])

Any help gratefully received!
 
Try this:

SQL:
SELECT
    Contacts.ContactTypeID,
    Contacts.CategoryTypeID,
    tblCategoryTypes.[Category Type],
    Contacts.IAMNo,
    Contacts.FirstName,
    Contacts.LastName,
    Contacts.Address,
    Contacts.Location,
    Contacts.City,
    Contacts.StateOrProvince,
    Contacts.PostalCode,
    Contacts.MobilePhone,
    Contacts.Email,
    Contacts.MWAMRenewal,
    Contacts.[Date Joined],
    [Contact Types].ContactType,
    Contacts.ObserverID,
    Contacts.ContactID,
    Contacts.MemberNo
FROM
    (
        (
            Contacts
            INNER JOIN tblContact_Categories ON Contacts.ContactID = tblContact_Categories.ContactID
        )
        INNER JOIN tblCategoryTypes ON tblCategoryTypes.CategoryTypeID = Contacts.CategoryTypeID
    )
    INNER JOIN [Contact Types] ON [Contact Types].ContactID = Contacts.ContactID;
 
When you don't know how to write SQL, use the QBE to build at least the Select clause and the Joins. It doesn't create pretty strings but it does get the syntax right.

Keep in mind that this query may not be updateable.
 
When you don't know how to write SQL, use the QBE to build at least the Select clause and the Joins. It doesn't create pretty strings but it does get the syntax right.

Keep in mind that this query may not be updateable.
Thanks, I built a new query from scratch, and it now works.
(Do you mean that possibly it cannot be used as an Update query?)
 
(Do you mean that possibly it cannot be used as an Update query?)
It has two parallel relationships. The tables in the query are not hierarchal since each of the 1-m relationships can produce more than one row, the query is a Cartesian Product. The third relationship is a "lookup" so that isn't a problem.
 
tblContact_Categories appears to be a many-to-many table.
correct but each side of it is 1-m and it can produce many records which will be multiplied by the number of records produced by the other 1-m join. So if join 1 produces 3 records and join 2 produces 2 records, the resultset will be 5 records.
 
tblContact_Categories appears to be a many-to-many table.
I've not heard the term 'many to many table' before.
It was created in order to resolve [ie prevent the existence of] a many to many relationship, which I've always understood to break the rules of a relational database.
 
I've not heard the term 'many to many table' before.
It was created in order to resolve [ie prevent the existence of] a many to many relationship, which I've always understood to break the rules of a relational database.
Each category type belongs to zero or more contacts.
Each contact has zero or more contact types.
This is a classic M:M relation, which in many RDBMSes is expressed using a third "junction" table with the two parent tables' PKs in it as FKs, and a PK over the combination of the two fields (expressing that for example a contact cannot have the same contacttype listed twice). Your design in post #1 is showing that correctly.
 
Each category type belongs to zero or more contacts.
Each contact has zero or more contact types.
This is a classic M:M relation, which in many RDBMSes is expressed using a third "junction" table with the two parent tables' PKs in it as FKs, and a PK over the combination of the two fields (expressing that for example a contact cannot have the same contacttype listed twice). Your design in post #1 is showing that correctly.Can a category be of more than 1 type?
 
To the original poster: can a category be of more than 1 type? If yes, give an example.
Sorry, earlier, when I said (referring to your earlier question about what is a contact category):
Problem solved, but it means the type of member - Associate [=as yet unqualified], Member, Friend, Honorary etc
(I inherited it, and some terms still confuse me after 2 years! :( )

I was referring to ContactType.
I misread your question, sorry.
to answer,
rows in the tblContact_Category represent an individual and one [of potentially several] qualification [as a car, or bike, or truck driver]

So, to answer your most recent question, re category type, yes there are several [but not many!] car, bike, truck...
Phew! HTH
 
Last edited:
When you create an entity in a database you should add a single row to a single table. If you want to add a Contact, your SQL should be "SELECT * FROM Contacts." To add one contact, you add one row.
Notice that the schema for a contact contains no category data. A Contact entity has zero knowedge of any Category.
 

Users who are viewing this thread

Back
Top Bottom