Please help me understanding this Query (1 Viewer)

SachAccess

Active member
Local time
Today, 16:10
Joined
Nov 22, 2021
Messages
389
Hi,

I am new to access. Kindly pardon me for such question.
Am trying to understand below query. I have multiple queries in my DB.
Your guidance will help me to understand rest of the queries more easily.

From below query, my understating is that a LEFT join is used to take result from My_Table_1 and My_Table_2.
Fields My_ID and Product_ID are present in the both the tables.
Quantity field is present only in one table.

I know there is a start date and end date taken as input but am not able to understand ‘[Test]’ this part of the query.

Actual query is as below
Code:
PARAMETERS [Forms]![Test]![Start_Date] DateTime, [Forms]![Test]![End_Date] DateTime;
TRANSFORM Nz(Sum(My_Table_2.Quantity),0) AS SumOfQuantity
SELECT My_Table_1.My_ID, My_Table_1.Product_ID, My_Table_1.Item_Name
FROM My_Table_1 LEFT JOIN My_Table_2 ON (My_Table_1.My_ID = My_Table_2.My_ID) AND (My_Table_1.Product_ID = My_Table_2.Product_ID)
GROUP BY My_Table_1.My_ID, My_Table_1.Product_ID, My_Table_1.Item_Name
PIVOT My_Table_2.Record_Date;

Details of the parameters used in the query.
Quantity = field name from table 'My_Table_2'
My_ID = field name from table 'My_Table_1'
My_ID = field name from table 'My_Table_2'

Product_ID = field name from table 'My_Table_1'
Product_ID = field name from table 'My_Table_2'

Result of the query.
When I click on this query, I get three fields, My_ID, Product_ID, Item_Name and few columns for dates.
My apologies if am asking for too much with very few / incomplete details.
Thanks.

PS - I have edited original query due to security reasons. Original query provides correct result.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 03:40
Joined
Oct 29, 2018
Messages
21,322
Hi. What you have there is a Crosstab Query. It's similar to a Pivot Table in Excel. Are you familiar with those?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:40
Joined
Feb 19, 2013
Messages
16,522
'Test' is the name of the form which is being referenced, however you are not using them in your query

Left join will return all the records from table_1 regardless of whether there is a matching record in table_2
FYI a right join would return all the records in table 2 regardless of whether ther is a matching record in table_1
An inner join returns only those records where there is a matching record in both tables
 

nectorch

Member
Local time
Today, 12:40
Joined
Aug 4, 2021
Messages
41
This section appear to be incomplete , I think you wanted to pick the following:

(1) Product ID
(2) Product Name
(3) My ID ( What is this????????????/)
(4) I cannot see the quantity here

Here is what we do with an SQL like the one you want to do:

Code:
SELECT tblCustomerInvoice.InvoiceID, tblCustomerInvoice.CustomerID, tblLineDetails.ProductID, tblLineDetails.Quantity
FROM tblCustomerInvoice INNER JOIN tblLineDetails ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID;
 

nectorch

Member
Local time
Today, 12:40
Joined
Aug 4, 2021
Messages
41
If possible just post the database we will assist you , we also started from where you are , we leant every trick strategies from others
 

SachAccess

Active member
Local time
Today, 16:10
Joined
Nov 22, 2021
Messages
389
Hi. What you have there is a Crosstab Query. It's similar to a Pivot Table in Excel. Are you familiar with those?
Hi @theDBguy sir, thanks for the help. Yes, I am aware of Pivot Table in Excel. Thanks for the Excel reference which I related easily. :)
 

SachAccess

Active member
Local time
Today, 16:10
Joined
Nov 22, 2021
Messages
389
'Test' is the name of the form which is being referenced, however you are not using them in your query

Left join will return all the records from table_1 regardless of whether there is a matching record in table_2
FYI a right join would return all the records in table 2 regardless of whether ther is a matching record in table_1
An inner join returns only those records where there is a matching record in both tables
Hi @CJ_London sir, thanks a lot for the help. You made it very simple for me.
I have read LEFT join and Right join in the book. But working directly and getting guidance from experts like you really helps a lot to understand.
 

SachAccess

Active member
Local time
Today, 16:10
Joined
Nov 22, 2021
Messages
389
This section appear to be incomplete , I think you wanted to pick the following:

(1) Product ID
(2) Product Name
(3) My ID ( What is this????????????/)
(4) I cannot see the quantity here

Here is what we do with an SQL like the one you want to do:

Code:
SELECT tblCustomerInvoice.InvoiceID, tblCustomerInvoice.CustomerID, tblLineDetails.ProductID, tblLineDetails.Quantity
FROM tblCustomerInvoice INNER JOIN tblLineDetails ON tblCustomerInvoice.InvoiceID = tblLineDetails.InvoiceID;
Hi @nectorch sir, you must be correct, due to security reasons, I have edited original query while posting.
I might have missed something while editing, original query gives correct result. I will re-check and re-post.
Thanks a lot for the help.
 

SachAccess

Active member
Local time
Today, 16:10
Joined
Nov 22, 2021
Messages
389
If possible just post the database we will assist you , we also started from where you are , we leant every trick strategies from others
Hi @nectorch sir, thanks for the encouragement, my apologies, posting database is not allowed due to IS policies.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 10:40
Joined
Feb 19, 2013
Messages
16,522
I have edited original query while posting
If you are going to do this - please say so, otherwise a lot of time is wasted trying to solve a problem that may not exist.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:40
Joined
Feb 19, 2002
Messages
42,872
The Parameters are not necessary since there is no where clause. There may have been at one time but it is gone so the parameters have no use.
 

Users who are viewing this thread

Top Bottom