Query to show records EXCEPT those with matching fields

[nortonm]

Hi; I have created a DB that handles Student Placement Concerns. Some requests come through on an MS Form, but not all (some by email etc).

I append the back end of the form to the main tblPlaceConcALL (All placement concerns) by way of the mediating table tblConcernFormSubmissions, which is populated directly by the MS Forms excel also using an append.

The MS Form submissions need correcting and detail adding before they can be submitted to the main table tblPlaceConcALL.

I want to display all the records in tblConcernFormSubmissions that HAVEN'T got a matching number in the tblPlaceConcALL. This is so the DB users can view/identify the Submissions that HAVEN'T yet been processed and submitted to the tblPlaceConcALL table.

They can then be shown in a report, and individual records corrected and added to, then submitted from the form to tblPlaceConcALL.

I'm making a mess of it, and can't work out the relationship I need between the two tables to do this. It's harder than I thought, or likely I'm just having a dumb day. I've attached the design view of the query i'm using.

I've used Joins where both are =, Join showing all from tblConcernFormSubmissions and only those from tblPlaceConcALL where fields are equal, but I can't get my head around joining all from tblConcernFormSubmission EXCEPT where the fields are equal!

 

[Gasman]

Use the Find Unmatched Query Wizard?

 

[GPGeorge]

Hi; I have created a DB that handles Student Placement Concerns. Some requests come through on an MS Form, but not all (some by email etc).

I append the back end of the form to the main tblPlaceConcALL (All placement concerns) by way of the mediating table tblConcernFormSubmissions, which is populated directly by the MS Forms excel also using an append.

The MS Form submissions need correcting and detail adding before they can be submitted to the main table tblPlaceConcALL.

I want to display all the records in tblConcernFormSubmissions that HAVEN'T got a matching number in the tblPlaceConcALL. This is so the DB users can view/identify the Submissions that HAVEN'T yet been processed and submitted to the tblPlaceConcALL table.

They can then be shown in a report, and individual records corrected and added to, then submitted from the form to tblPlaceConcALL.

I'm making a mess of it, and can't work out the relationship I need between the two tables to do this. It's harder than I thought, or likely I'm just having a dumb day. I've attached the design view of the query i'm using.

I've used Joins where both are =, Join showing all from tblConcernFormSubmissions and only those from tblPlaceConcALL where fields are equal, but I can't get my head around joining all from tblConcernFormSubmission EXCEPT where the fields are equal!

This is what has been called a "frustrated outer join", a term I find amusing and apt.

Change the join to a Left Outer Join, showing all records from tblConcernFormSubmissions and only matching records from tblPlaceConcALL.

Add as a criteria: tblPlaceConcALL.ConcernID Is Null

That will return records from tblConcernFormSubmissions which do not have corresponding records in tblPlaceConcALL

BTW, when sharing SQL problems in forum posts, it's almost always better to switch to SQL view and copy and paste the actual SQL rather than a screenshot of the QBE grid.

 

[nortonm]

Thanks very much George! I had a feeling i'd seen an IsNull entry on something, but I couldn't work out what to do with it. That's excellent, thanks again!!

 

[arnelgp]

you may also try to Paste this on SQL View:

SELECT tblConcernFormSubmissions.* FROM tblConcernFormSubmissions 
LEFT JOIN tblPlaceConcAll ON tblConcernFormSubmissions.ConcernID = tblPlaceConcAll.FormID 
WHERE (tblPlaceConcAll.FormID Is Null)

 

[nortonm]

you may also try to Paste this on SQL View:

SELECT tblConcernFormSubmissions.* FROM tblConcernFormSubmissions
LEFT JOIN tblPlaceConcAll ON tblConcernFormSubmissions.ConcernID = tblPlaceConcAll.FormID
WHERE (tblPlaceConcAll.FormID Is Null)

Ah right, thanks, this helps me to understand the logic of it, got it now.

 

[Ken Sheridan]

I'm rather concerned about the structure of your tblConcernFormSubmissions table. You have as its primary key a ConcernID column, which I'm guessing is an autonumber. That's fine as the table models the Concerns entity type. However, you also have a UserID non-key column, along with FirstName, Surname etc. columns which represent attributes of the Users entity type.

In relational database terms what you have here is a transitive functional dependency on the primary key of the table. A functional dependency is where a value in one column determines the value in another column. In this UserID determines the values in the FirstName, Surname etc. columns. However, UserID is also functionally determined by ConcernID, the table's key, so FirstName, Surname etc are transitively determined by the table's primary key via UserID.

Fundamental to the design of tables in a relational database is the process of Normalization. This is a set of formal 'rules' which govern whether a table is correctly structured or not. In this case we are concerned with Third Normal Form (3NF). The formal definition of this is:

Third Normal Form: A relvar is in 3NF if and only if it is in 2NF and every non-key attribute is nontransitively dependent on the primary key.

In the language of the database relational model a relvar (relation variable) is very loosely equivalent to a table, or more precisely, a table definition. In plain English the above means that each non-key column in a table must be determined solely by the whole of the table's primary key.

A table is normalized by the process of decomposition. In this case, all columns which are functionally determined by UserID should be moved to a Users table. The UserID column should be retained in tblConcernFormSubmissions as a foreign key referencing the UserID primary key column in Users. This can be very easily done by means of a simple 'append' query which inserts DISTINCT values of UserID and all columns determined by it into rows in the Users table.

The UserID column in Users will usually be an autonumber for convenience. People are often surprised to find that you can insert specific values into an autonumber column by means of an 'append' query. While you cannot insert a value into such a column manually, doing so by means of an 'append' query is perfectly legitimate.

Once you have decomposed the tblConcernFormSubmissions table in this way, you can safely drop the appended columns (apart from UserID) from tblConcernFormSubmissions. I must stress, however, that, before undertaking these sort of operations it is imperative that you back up the database to at least one, and preferably more, safe locations.

As regards the other columns in tblConcernFormSubmissions which are not determined by UserID, I suspect that further decomposition will also be required. I'd guess that TrustName and HospitalName should be moved to Trusts and Hospitals tables. In the UK, only a HospitalID foreign key column would be retained in tblConcernFormSubmissions as hospital determines trust, so the Hospitals table would include a TrustID foreign key column. This may differ elsewhere however.

tblPlaceConcALL is almost certainly in need of decomposition also, though I'd need a greater familiarity with the reality being modelled to say more than that.

 

[nortonm]

I'm rather concerned about the structure of your tblConcernFormSubmissions table. You have as its primary key a ConcernID column, which I'm guessing is an autonumber. That's fine as the table models the Concerns entity type. However, you also have a UserID non-key column, along with FirstName, Surname etc. columns which represent attributes of the Users entity type.

In relational database terms what you have here is a transitive functional dependency on the primary key of the table. A functional dependency is where a value in one column determines the value in another column. In this UserID determines the values in the FirstName, Surname etc. columns. However, UserID is also functionally determined by ConcernID, the table's key, so FirstName, Surname etc are transitively determined by the table's primary key via UserID.

Fundamental to the design of tables in a relational database is the process of Normalization. This is a set of formal 'rules' which govern whether a table is correctly structured or not. In this case we are concerned with Third Normal Form (3NF). The formal definition of this is:

Third Normal Form: A relvar is in 3NF if and only if it is in 2NF and every non-key attribute is nontransitively dependent on the primary key.

In the language of the database relational model a relvar (relation variable) is very loosely equivalent to a table, or more precisely, a table definition. In plain English the above means that each non-key column in a table must be determined solely by the whole of the table's primary key.

A table is normalized by the process of decomposition. In this case, all columns which are functionally determined by UserID should be moved to a Users table. The UserID column should be retained in tblConcernFormSubmissions as a foreign key referencing the UserID primary key column in Users. This can be very easily done by means of a simple 'append' query which inserts DISTINCT values of UserID and all columns determined by it into rows in the Users table.

The UserID column in Users will usually be an autonumber for convenience. People are often surprised to find that you can insert specific values into an autonumber column by means of an 'append' query. While you cannot insert a value into such a column manually, doing so by means of an 'append' query is perfectly legitimate.

Once you have decomposed the tblConcernFormSubmissions table in this way, you can safely drop the appended columns (apart from UserID) form tblConcernFormSubmissions. I must stress, however, that, before undertaking these sort of operations it is imperative that you back up the database to at least one, and preferably more, safe locations.

As regards the other columns in tblConcernFormSubmissions which are not determined by UserID, I suspect that further decomposition will also be required. I'd guess that TrustName and HospitalName should be moved to Trusts and Hospitals tables. In the UK, only a HospitalID foreign key column would be retained in tblConcernFormSubmissions as hospital determines trust, so the Hospitals table would include a TrustID foreign key column. This may differ elsewhere however.

tblPlaceConcALL is almost certainly in need of decomposition also, though I'd need a greater familiarity with the reality being modelled to say more than that.

 

[nortonm]

Thanks Ken, that looks like really useful info. The table tblConcernFormSubmissions contents are actually continually deleted and repopulated, as it works mainly to get data from the MS Form, by means of this table, into tblPlaceConcALL. It's a temp table in that respect. It gets populated by a separate link to ConcernFormSubmissions.xlsx (the actual form records). I decided against having the primary key as an automumber, as the Id field is actually relevant only in the Excel back end of the form, and only some of the records have a 'FormID' - the number the MS Form gives the record, which stays the same, other records are entered manually from emails that are sent in. Really interesting stuff though, and iactually 'm really an upgraded desktop technician rather than a programmer or developer, so any advice is extremely welcome!