Query Question (Join) (1 Viewer)

jamesave

New member
Local time
Today, 15:54
Joined
Apr 26, 2020
Messages
16
I have tables with the following relationship:

1. tbl_RecipeList with recipeID as PK
2. tbl_MasterRecipe with recipeID as PK and ingredientID as PK
3. tbl_PriceList with ingredientID as PK
4. tbl_CostVersion with CostVersionID as PK and ingredientID as PK and costnumber as one of the field

My goal is to populate the ingredientID with the cost in the tbl_CostVersionID to include even if the costnumber is zero or null or doesn't exist. This way, I can track if the calculated recipe price in another query has no wrong value. Somehow, it seems like the Query Design View may need to be corrected with the proper outer join.

This is the Query so far:

SELECT tbl_MasterRecipe.ingredientID, tbl_RecipeList.recipeID, tbl_PriceList.NamaBBU, tbl_MasterRecipe.Qty_Kg, tbl_CostVersion.CostVersionID, tbl_CostVersion.costnumber

FROM tbl_RecipeList INNER JOIN ((tbl_PriceList LEFT JOIN tbl_CostVersion ON tbl_PriceList.ingredientID = tbl_CostVersion.ingredientID) LEFT JOIN tbl_MasterRecipe ON tbl_PriceList.ingredientID = tbl_MasterRecipe.ingredientID) ON tbl_RecipeList.recipeID = tbl_MasterRecipe.recipeID

WHERE (((tbl_RecipeList.recipeID)="recipenumber") AND ((tbl_CostVersion.CostVersionID)=2)) OR ((tbl_CostVersion.CostVersionID) Is Null)) ;

somehow it doesn't work as it only show the numbers that only exist.. Eventually, the numbers will be populated by the recipeID and CostVersionID.

Any idea on how to properly address the JOIN statement?
 

June7

AWF VIP
Local time
Today, 14:54
Joined
Mar 9, 2014
Messages
5,423
I need to work with data. If you want to provide db for analysis, follow instructions at bottom of my post.
 

plog

Banishment Pending
Local time
Today, 17:54
Joined
May 11, 2011
Messages
11,612
Agree, Things are best demonstrated with data. Please provide 2 sets:

A. Starting data from relevant tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect to end up with when you feed in the data from A.

Again, 2 sets of data; starting and expected results.
 

jamesave

New member
Local time
Today, 15:54
Joined
Apr 26, 2020
Messages
16
Hi June7 and plog

Thanks in Advance for your help. I have attached the databe scrubbed from all the confidential data.. Only Tables and the only Query left..

The SQL may have changed from my original posting since I have been playing around with the QDE and unable to get to the statement that I posted.

From the sample Query, if the cost version is changed from 2 to 4 or vice versa (with the recipe ID = 4), I am hoping to see that any price not recorded to show as 0.
 

Attachments

  • QueryDataSamples.zip
    36.7 KB · Views: 103

plog

Banishment Pending
Local time
Today, 17:54
Joined
May 11, 2011
Messages
11,612
Can you please provide the B data? Please show me the data you expect your query to return.
 

jamesave

New member
Local time
Today, 15:54
Joined
Apr 26, 2020
Messages
16
The data is all there, not sure what do you mean by B data.

Since the data are all over multiple tables, I am trying to query the following records:
- tbl_RecipeList.RecipeID,
- tbl_IngredientList.IngredientName
- tbl_MasterRecipe.Qty_Kg,

even when the following records are null or empty.
tbl_Pricelist.CostDollar, tbl_Pricelist.CostVersionID

Basically, tbl_recipelist and tbl_IngredientList supposed to have a distinct record (for example, 10 ingredients in a recipe) that I would like to pull all even if the CostDollar is null or 0.

The following is the query in the sample file:

SELECT tbl_RecipeList.RecipeID, tbl_IngredientList.IngredientName, tbl_MasterRecipe.Qty_Kg, tbl_Pricelist.CostDollar, tbl_Pricelist.CostVersionID, [Qty_Kg]*[CostDollar] AS [total Cost]

FROM tbl_CostVersion INNER JOIN (tbl_RecipeList RIGHT JOIN ((tbl_IngredientList INNER JOIN tbl_Pricelist ON tbl_IngredientList.[IngredientID] = tbl_Pricelist.[IngredientID]) INNER JOIN tbl_MasterRecipe ON tbl_IngredientList.IngredientID = tbl_MasterRecipe.IngredientID) ON tbl_RecipeList.RecipeID = tbl_MasterRecipe.RecipeID) ON tbl_CostVersion.CostVersion = tbl_Pricelist.CostVersionID

WHERE (((tbl_RecipeList.RecipeID)='4') AND ((tbl_Pricelist.CostVersionID)=2));


You can change the costversionID to 3 and 4 to see the difference in the result. (Use tbl_RecipeList.RecipeID ='4')
 

June7

AWF VIP
Local time
Today, 14:54
Joined
Mar 9, 2014
Messages
5,423
B data would be the dataset you want the query to build. If the query is not providing what you expect, then show us what you do want. Build a table in database or tables here in post with the tables tool.
 

plog

Banishment Pending
Local time
Today, 17:54
Joined
May 11, 2011
Messages
11,612
B. Expected results of A. Show us what data you expect to end up with when you feed in the data from A.

I have 5 numbers (A data):

1
5
8
13
9

I expect my query to return this (B data):
36

Your database represents A, please show me the data (the data, the data, the data--not an explanation, but the data) you expect.
 

June7

AWF VIP
Local time
Today, 14:54
Joined
Mar 9, 2014
Messages
5,423
Okay, if I am understanding requirement correctly, cannot do with a single SELECT.

Query1:
SELECT tbl_Pricelist.IngredientID, tbl_Pricelist.CostDollar, tbl_Pricelist.CostVersionID
FROM tbl_Pricelist
WHERE (((tbl_Pricelist.CostVersionID)=4));

Query2:
SELECT tbl_MasterRecipe.RecipeID, tbl_MasterRecipe.IngredientID, Query1.CostDollar, Query1.CostVersionID
FROM Query1 RIGHT JOIN (tbl_RecipeList INNER JOIN (tbl_IngredientList INNER JOIN tbl_MasterRecipe ON tbl_IngredientList.IngredientID = tbl_MasterRecipe.IngredientID) ON tbl_RecipeList.RecipeID = tbl_MasterRecipe.RecipeID) ON Query1.IngredientID = tbl_MasterRecipe.IngredientID
WHERE (((tbl_MasterRecipe.RecipeID)="4"));

Nested:
SELECT tbl_MasterRecipe.RecipeID, tbl_MasterRecipe.IngredientID, Query1.CostDollar, Query1.CostVersionID
FROM (SELECT tbl_Pricelist.IngredientID, tbl_Pricelist.CostDollar, tbl_Pricelist.CostVersionID
FROM tbl_Pricelist
WHERE (((tbl_Pricelist.CostVersionID)=4))) AS Query1 RIGHT JOIN (tbl_RecipeList INNER JOIN (tbl_IngredientList INNER JOIN tbl_MasterRecipe ON tbl_IngredientList.IngredientID = tbl_MasterRecipe.IngredientID) ON tbl_RecipeList.RecipeID = tbl_MasterRecipe.RecipeID) ON Query1.IngredientID = tbl_MasterRecipe.IngredientID
WHERE (((tbl_MasterRecipe.RecipeID)="4"));
 

jamesave

New member
Local time
Today, 15:54
Joined
Apr 26, 2020
Messages
16
Thanks June! I've been looking around for this solution but not many documents or discussions talk about nested SQL in access. I've been pulling my hair out to use simply join statements
 

jamesave

New member
Local time
Today, 15:54
Joined
Apr 26, 2020
Messages
16
I have 5 numbers (A data):

1
5
8
13
9

I expect my query to return this (B data):
36

Your database represents A, please show me the data (the data, the data, the data--not an explanation, but the data) you expect.

I understand this part now by what it means with B data.
 

jamesave

New member
Local time
Today, 15:54
Joined
Apr 26, 2020
Messages
16
Hi June,

Do you have a recommendation of where should I read regarding building such complex Queries? Was it because of the table schema as well? I am trying to understand the thought processes that you can come up with such solution.. I have been looking at this for quite a while.

so my understanding is Query 1 is to pull all the price from the cost version

and Query 2 pulls all the data from the recipe and the union with results from Query 1.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:54
Joined
Feb 19, 2002
Messages
42,976
Although multi-field PK's are allowed in RDBMS including Access, you might find that using a single autonumber proves to be easiest in the long run. For the junction tables where you are taking PK from tbl1 and PK from tbl2, you can still use an autonumber as the PK, you would just need to create a compound unique index of the two fields to implement the business rule that the combination must be unique. So, if you are never going to ever create a child of a junction table, the compound PK won't get in the way but as soon as you need a child table, you won't be able to use combos or listboxes to select items from this table because those controls only work correctly when there is a single, unique PK field.
 

jamesave

New member
Local time
Today, 15:54
Joined
Apr 26, 2020
Messages
16
Although multi-field PK's are allowed in RDBMS including Access, you might find that using a single autonumber proves to be easiest in the long run. For the junction tables where you are taking PK from tbl1 and PK from tbl2, you can still use an autonumber as the PK, you would just need to create a compound unique index of the two fields to implement the business rule that the combination must be unique. So, if you are never going to ever create a child of a junction table, the compound PK won't get in the way but as soon as you need a child table, you won't be able to use combos or listboxes to select items from this table because those controls only work correctly when there is a single, unique PK field.

This is quite interesting point of view. The way the tables are designed with all the normalization effort and anyone without access background would have thought nothing about this autonumber as PK. I wonder if there is any reading that I can read further about the technique of creating a compound unique index? Is it the limitation of the listbox or combobox in Access?
 

Isaac

Lifelong Learner
Local time
Today, 15:54
Joined
Mar 14, 2017
Messages
8,738
The way the tables are designed with all the normalization effort and anyone without access background would have thought nothing about this autonumber as PK
In true relational database management systems, and also in Microsoft Access, using a surrogate primary key is what has almost always served me best. Trying to use only a key which has embedded business meaning of some kind goes into a hundred rabbit holes as business meaning changes over time and opens you up to the very undesirable situation of having business management try to dictate how your database is designed.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:54
Joined
Feb 19, 2002
Messages
42,976
Since the general recommendation is to use an autonumber, most experts will not even warn you that you might need to also create a unique index (single or multi-field) to enforce your business rules and unless you've actually tried to use a combo or listbox with a RowSource bound to a table with a multi-field PK, you'll never expect there to be a problem.

I have never seen anyone else ever post on this topic and I've never seen it in a book and I have dozens. That doesn't mean you won't find any. I just haven't seen any.

I've been working with relational databases since the early 70's. Yep, I'm old. My preference before Access (early 90's) was always to use natural keys when they were available because they would actually minimize joins and of course my CICS forms didn't have combos or listboxes so having a multi-field pk was never a problem, it just made the joins more complex. For example, if your state table has an autonumber PK instead of using the state abbreviation, you will need to always join to the state table to pick up the abbreviation. Since it is unlikely that a state will ever change its name, we are not in much jeopardy of having Access have to cascade a change to all related tables so I still use a natural key for this table.

Access limits the number of columns in an index or PK to 10. Other relational databases allow more. To make a multi-field PK is easy. You just use Shift-Click to select all the fields and then press the big Key icon. The only way to make a multi-field index is to use the indexes dialog. Only single field indexes can be made in the table design view. Notice in the picture below that the first line of the unique index (like my "creative" name? Only PrimaryKey is a reserved word, name the index whatever makes sense.) has a name but the following rows do not. The first line also defines the properties - primary = no, unique = yes, and Ignore Nulls = no. This index is for example purposes since in the real world, given a large enough population, you could easily have two employees with the same first, middle, and last names especially if you employ family members.

Here's a picture to help you.
uniqueIDX2.JPG
 

Users who are viewing this thread

Top Bottom