Please help me understanding this Query

[SachAccess]

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

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.

 

[theDBguy]

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]

'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]

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:

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

 

[nectorch]

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]

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]

'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]

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:

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]

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]

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.

 

[theDBguy]

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.

With regards to query joins, I also have this blog article. Hope it helps...

theDBguy's Access Blog: Query Join Basics

 

[SachAccess]

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.

I agree sir and will not repeat this mistake going forward.

 

[SachAccess]

With regards to query joins, I also have this blog article. Hope it helps...

theDBguy's Access Blog: Query Join Basics

Sure sir, I will read this, thanks a lot.
@theDBguy sir, read the blog sir, really helpful, will keep reading as a reference point till am clear on JOINs. Thanks.

 

[Pat Hartman]

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.

 

[SachAccess]

Hi @Pat Hartman sir, thanks a lot for the help. Have a nice day ahead.