Unexpected results. (1 Viewer)

[mafhobb]

I manage a few properties that need occasional cleanings. Each property can have a few different types of cleanings (full, half, 1 room,...) and the price for each of these services is different for each property.
So I have a main table with the property name (tblProperties), then a second table with the cleaning types and costs for each property (tblCleaningCosts) and finally a third table with the cleaning actually done (tblCleaningsDone).
In order to prepare an invoice, I need a query that brings up the property name (it is a parameter), the date of the cleaning, the cleaning type, and the cost for that cleaning.
This all sounds simple enough, but the query results are not what I expect.
In the files attached you can see the data in tblCleaningCosts, the data in tblCleaningsDone, the query in design mode and the results of the query. These results came out after selecting "Es Tir" as a parameter.
What I expected was to see two records, showing two cleanings at "Es Tir" with the dates that they were done and the cost but instead I get a bunch of repeated results that makes no sense to me.

Is this a table relationship issue? What am I doing wrong?

mafhobb

 

[arnelgp]

why does cleaningCost related to property?
it should not be. and must be related to the Cleaning done table.

 

[mafhobb]

Really? Which fields should I relate?

I tried to relate the "PropertyName" fields on both table and I get a relationship error. Please see image attached

 

[plog]

First and foremost you need to read up on foreign keys.

SQL FOREIGN KEY Constraint

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

You've correctly added primary keys to your tables ([ID), hopefully those are autonumbers. I suggest renaming them from [ID] by prefixing them with what ID they represent (e.g. PropertyID, CostID, etc.).

Then you need to start using them correctly. Looking at tblCleaningCost and tblProperties as an example; you are using PropertyName to link them. Instead you need to remove [ProprtyName] from tblCleaningCost and put a PropertyID field. It will hold the PropertyID value from tblProperties. That's how foreign keys work--you store the ID value in the foreign table, not any other piece of data.

Similarly and next you need to reconfigure tblCleaningsDone. In it you will remove both the PropertyName and CleaningType fields. Then you will add a [CostID] field to hold the ID value of tblCleaningCost.

Finally, to achieve this query you will start with a new blank query object, add tblProperties, tblCleaningCost and tblCleaningsDone. You will join them as such:

tblProperties to tblCleaningCost via PropertyID
tblCleaningCost to tblCleaningsDone via CostID

Then rebuild the bottom section of your query as you have in your screenshot. Run that and it will work as expected.

 

[The_Doc_Man]

My colleagues are giving you specific issues. I will take a different route.

You get multiple instances returned from complex queries because your constraints are somehow incorrect. Either you are incorrectly filtering via a loose WHERE clause or you have a JOIN that is incorrectly managed. This is because ALL modern SQL-based database engines are actually permutation engines. If you ever ran across ANYTHING about set theory in any of your math courses when in school, you know that there is this topic of "Combinations and Permutations." Access has the ACE module, which IS a true SQL engine. If you do a SELECT that takes more than one table at a time, you might find that you see EVERY COMBINATION of records that match your criteria. Even if that isn't what you wanted.

SQL engines at heart only do four main things - SELECT, UPDATE, INSERT, and DELETE records. The rest is formatting and bells and whistles. But the FIRST thing that they do for ALL of those options is to evaluate which records - or which combination of records - meets their criteria so that they can do whatever it is they are going to do. And that means making a list of whatever records COULD possibly match.

The most common mistake for novice users regarding multiple related tables is the attempt to bring together records from two tables and to then fail to properly JOIN them with a well-formed JOIN clause or to limit them properly with a good WHERE clause. If you ever want to test the ability of your SQL engine to do combinations, here is a simple experiment:

Table SUITS: Spaces, Hearts, Diamonds, Clubs (i.e. a single field SUIT for each of four records)
Table RANKS: 2, 3, 4, 5, 6, 7, 8, 9, 10, J, Q, K, A (i.e. a single field RANK for each of 13 records)

SELECT RANK, " of ", SUIT FROM RANKS, SUITS ;

That will give you 52 records that look like: 2 of Spades, ... 9 of Diamonds, ... A of clubs

So if you find that you have too many records, and in particular that some appear to be duplicated, it might be because you have joined together things that have a parent-child relationship and each parent record is being joined with each of its child records. OR you have brought together two tables without a JOIN clause (like my deck-of-cards example).

 

[mafhobb]

Here is a simplified version of the database

My colleagues are giving you specific issues. I will take a different route.

You get multiple instances returned from complex queries because your constraints are somehow incorrect. Either you are incorrectly filtering via a loose WHERE clause or you have a JOIN that is incorrectly managed. This is because ALL modern SQL-based database engines are actually permutation engines. If you ever ran across ANYTHING about set theory in any of your math courses when in school, you know that there is this topic of "Combinations and Permutations." Access has the ACE module, which IS a true SQL engine. If you do a SELECT that takes more than one table at a time, you might find that you see EVERY COMBINATION of records that match your criteria. Even if that isn't what you wanted.

SQL engines at heart only do four main things - SELECT, UPDATE, INSERT, and DELETE records. The rest is formatting and bells and whistles. But the FIRST thing that they do for ALL of those options is to evaluate which records - or which combination of records - meets their criteria so that they can do whatever it is they are going to do. And that means making a list of whatever records COULD possibly match.

The most common mistake for novice users regarding multiple related tables is the attempt to bring together records from two tables and to then fail to properly JOIN them with a well-formed JOIN clause or to limit them properly with a good WHERE clause. If you ever want to test the ability of your SQL engine to do combinations, here is a simple experiment:

Table SUITS: Spaces, Hearts, Diamonds, Clubs (i.e. a single field SUIT for each of four records)
Table RANKS: 2, 3, 4, 5, 6, 7, 8, 9, 10, J, Q, K, A (i.e. a single field RANK for each of 13 records)

SELECT RANK, " of ", SUIT FROM RANKS, SUITS ;

That will give you 52 records that look like: 2 of Spades, ... 9 of Diamonds, ... A of clubs

So if you find that you have too many records, and in particular that some appear to be duplicated, it might be because you have joined together things that have a parent-child relationship and each parent record is being joined with each of its child records. OR you have brought together two tables without a JOIN clause (like my deck-of-cards example).

First and foremost you need to read up on foreign keys.

SQL FOREIGN KEY Constraint

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

You've correctly added primary keys to your tables ([ID), hopefully those are autonumbers. I suggest renaming them from [ID] by prefixing them with what ID they represent (e.g. PropertyID, CostID, etc.).

Then you need to start using them correctly. Looking at tblCleaningCost and tblProperties as an example; you are using PropertyName to link them. Instead you need to remove [ProprtyName] from tblCleaningCost and put a PropertyID field. It will hold the PropertyID value from tblProperties. That's how foreign keys work--you store the ID value in the foreign table, not any other piece of data.

Similarly and next you need to reconfigure tblCleaningsDone. In it you will remove both the PropertyName and CleaningType fields. Then you will add a [CostID] field to hold the ID value of tblCleaningCost.

Finally, to achieve this query you will start with a new blank query object, add tblProperties, tblCleaningCost and tblCleaningsDone. You will join them as such:

tblProperties to tblCleaningCost via PropertyID
tblCleaningCost to tblCleaningsDone via CostID

Then rebuild the bottom section of your query as you have in your screenshot. Run that and it will work as expected.

I followed your steps and suggestions and I think that it now works as it should. I wonder if you'd mind taking a look at the attached db as it is now to confirm.
One question though: My access expertise is obviously very limited and while I see the reason for using ID's as you explain, the reason for me to use the actual names (as in Propertyname) is because this way I can display that name in forms simply by reading the table. In other words; in the table tblcleaningcosts where I now have PropertyID, which is a number, I used to have the actual PropertyName and so in any form (such as the one that I have now added, frmCleaningCosts) I could see the data and know immediately what property it belong to. Also, when entering a new property, I could simply use its name. Now I have a very abstract (at least to me) number.
What do I need to do so the form displays the actual property name both when viewing and also when entering new data (ie clicking on "New record"
Many thanks!
mafhobb

 

[plog]

You need another form. When you have 2 tables in a 1-many relationsip (Properties->CleaningCost) you use a form with a subform:

Create a form that contains a subform (a one-to-many form) - Microsoft Support

Learn how to create a form in Access that contains a subform (also known as a one-to-many form, a parent/child form, or a hierarchical form).

support.microsoft.com

The main form will be based on Properties and the subform will be based on CleaningCost and look similar to what you have now for the form. that way the main form shows all the property info and the subform shows all the cleaning costs.

 

[Pat Hartman]

You have four tables in the query but only three of them are joined. The unjoined table is creating a Cartesian product. I.e. it is multiplying all the returned rows by however many rows are in the type table.