SQL Query

[sajitk]

I have 3 tables in Access namely;

Recd_Items
Item_code - FK
Item_Recd - Numeric

Issue_Items
Item_Code - FK
Issue_Qty- Numeric


Item_Master
Item_Code - PK
Item_Desc - Text
Item_Rol - Numeric


My requirement is I need to write a SQL query which would display the following records for a particular Item_Code selected by the user. the user would select the itemcode and the records would be displayed in the subform.

Item_Desc    Sum of Stk recd     Sum of Stk Issued   StkInHand  Re-Order Level 

Thanking you in advance
Sajit

[beacon]

What have you tried so far?

[sajitk]

What have you tried so far?

Hi beacon,

this is wat i have tried.

SELECT DISTINCT Item_Master.Item_Code, Item_Master.ROL, Sum(Recd_Stk.Recd_Stock) AS SumOfRecd_Stock, Sum(Issue_Mas.Issued_Qty) AS SumOfIssued_Qty
FROM Item_Master, Recd_Stk, Issue_Mas
WHERE Item_Master.Item_Code=[Recd_Stk].Item_Code And Item_Master.Item_Code=Issue_Mas.Item_Code
GROUP BY Item_Master.Item_Code, Item_Master.ROL;

but the result of this query is not showing correct.

Suppose I have the following data in the table

Item_Mas

Item_Code ROL
WS-002 10

Recd_Stock

Item_Code Recd_Stk
WS-002 5
WS-002 5

Issue_Stock

Item_Code Issue_Stk
WS-002 2

the result which i want is this:

Item_Code ROL Recd_Stk Issue_Stk
WS-002 10 10 2

But the result which my query retrieves is this

Item_Code ROL Recd_Stk Issue_Stk
WS-002 10 10 4

The value in the issue stock gets doubled. I know why this is getting doubled, but i am not able to write the SQL for this.

Sajit

[sajitk]

Hi beacon,

looking for an answer to my mail.

I am actually stuck on this. would be great if you could respond

sajit

Hi beacon,

this is wat i have tried.

SELECT DISTINCT Item_Master.Item_Code, Item_Master.ROL, Sum(Recd_Stk.Recd_Stock) AS SumOfRecd_Stock, Sum(Issue_Mas.Issued_Qty) AS SumOfIssued_Qty
FROM Item_Master, Recd_Stk, Issue_Mas
WHERE Item_Master.Item_Code=[Recd_Stk].Item_Code And Item_Master.Item_Code=Issue_Mas.Item_Code
GROUP BY Item_Master.Item_Code, Item_Master.ROL;

but the result of this query is not showing correct.

Suppose I have the following data in the table

Item_Mas

Item_Code ROL
WS-002 10

Recd_Stock

Item_Code Recd_Stk
WS-002 5
WS-002 5

Issue_Stock

Item_Code Issue_Stk
WS-002 2

the result which i want is this:

Item_Code ROL Recd_Stk Issue_Stk
WS-002 10 10 2

But the result which my query retrieves is this

Item_Code ROL Recd_Stk Issue_Stk
WS-002 10 10 4

The value in the issue stock gets doubled. I know why this is getting doubled, but i am not able to write the SQL for this.

Sajit

[FishVal]

Hello, sajitk.

I think this thread will be helpful for you.

Regards,
Fish

[sajitk]

Hi Fish,

That particular thread was helpful. But there is again a problem.If I execute an UNION query the results comes in the following format

Item Code Received Items
WS-002

Hello, sajitk.

I think this thread will be helpful for you.

Regards,
Fish

[sajitk]

Hi Fish,

That particular thread was helpful. But there is again a problem.If I execute an UNION query the results comes in the following format

Item Code Received Items
WS-002 20
WS-002 10

both the issue and received values comes under the same column. I need the result in the following format

Item Code Received Issued

WS-002 20 10


The query which i have written is given below:
SELECT Recd_Stk.Item_Code, NZ(Sum(Recd_Stk.Recd_Stock),0) AS [Received Quantity]
From Recd_Stk
where Recd_Stk.Item_Code = forms!Recd_Stk1.Item_Code.text
Group By Recd_Stk.Item_Code
UNION
Select Issue_Mas.Item_Code, NZ(Sum(Issue_Mas.Issued_Qty),0) As [Issued Quantity]
where Issue_Mas.Item_Code = forms!RecdStk1.Item_Code.text
Group By Issue_mas.Item_Code


The query basically takes an input from the user in the form Recd_Stk1

sajit

Hello, sajitk.

I think this thread will be helpful for you.

Regards,
Fish

[FishVal]

Hi, Sajit.

Try the following:

• Join [Item_Master] and let us say [Issue_Items] with records grouping. Certainly it should be outer join.
• Join thus obtained dataset with the rest table ([Recd_Items]) with records grouping.

Regards,
Fish

[sajitk]

Hi Fish,

Thanks for the prompt response.

Can u help me in writing the SQL. I am a bit confused.

Saji t

Hi, Sajit.

Try the following:

• Join [Item_Master] and let us say [Issue_Items] with records grouping. Certainly it should be outer join.
• Join thus obtained dataset with the rest table ([Recd_Items]) with records grouping.

Regards,
Fish

[FishVal]

Hi Fish,

Thanks for the prompt response.

Can u help me in writing the SQL. I am a bit confused.

Saji t

Ok, Sajit.

The problem of redundant records combinations happens because database engine groups records after performing all joins. It is evident that performing records grouping after each join in sequential queries or in subquery will be a red pill.

Regards,
Fish

[sajitk]

Dear FishVal,

I really need to appreciate your way of explaination....this is was just too good.
After reading wat u said, i got the real picture. I have written the SQL and its working fine.

SELECT T1.Item_Code AS [Item Code], NZ(SUM(T1.ITEM_RECD),0) AS [Stock Received], NZ(SUM(T2.ITEM_QTY),0) AS [Stock Issued]
FROM [SELECT ITEM_CODE,SUM(Recd_Stock) AS ITEM_RECD
FROM Recd_Stk GROUP BY ITEM_CODE]. AS T1, [SELECT ITEM_CODE,
SUM(Issued_QTY) AS ITEM_QTY
FROM
ISSUE_Mas
GROUP BY ITEM_CODE]. AS T2
WHERE T1.ITEM_CODE=T2.ITEM_CODE
GROUP BY T1.ITEM_CODE;

But life is not easy as of now. One problem solved and there comes another.

In the Receive_Inventory Form, I have a combo box with Vendor Name and a text box which shows the Vendor Code automatically from the Vendor Master table.

In the form I have a subform, where i will enter all the the receipt details of a particular item. This form will be viewed in Datasheetview. The form will have an Item description, Item code, Received_Qty, Date and Vendor_Code.

The problem is when there are many items received from a particular vendor. I need the Vendor code to be replicated for each row i enter. As of now the vendor code comes only for the first entry. In the second row, the vendor code doesnt comes. I need the vendor code there also.

rite now, i am using the following code in the click event of the combo box.

[Child82].Form![Vendor] = DLookup("Vendor_Code", "Vendor_Master", "Vendor_Name='" & Me.Vendor & "'")

How do i solve this problem. Please suggest.

Ok, Sajit.

The problem of redundant records combinations happens because database engine groups records after performing all joins. It is evident that performing records grouping after each join in sequential queries or in subquery will be a red pill.

Regards,
Fish

[FishVal]

Ok.

There are at least two ways to cure it:

• Instead of setting Value property of [Vendor] control set DefaultValue property.
  

  [Child82].Form![Vendor].DefaultValue = DLookup("Vendor_Code", "Vendor_Master", "Vendor_Name='" & Me.Vendor & "'")

• You may use conventianal master/child binding. For that you need to redesign your combobox.
  • RowSource = "Select Vendor_Code, Vendor_Name ......"
  • ColumnsCount = 2
  • BoundColumn = 1
  • ColumnWidths = "0;1" or any non-zero value instead of "1"
    Thus the combobox will display vendor names, but its Value property will return vendor codes

Regards,
Fish

[sajitk]

Hi Fish,

Sorry for the late reply.I was on travel

Thanks for the advise.

I have another problem now. In the Stock Receiving Form (Main form), I have the followng controls:

1) Vendor_Name - combo-box which shows the list of available vendors
2) A child form which is viewded as Datasheet
- Item_Desc - combo-box which shows the Item_Desc
- Item_Code - corresponding Item_code appears for the selected Item_Desc
- Stock_Recd - No of stock received
- Vendor_Code corresponsing vendor code for the vendor selected in 1.

A user can enter 'n' number of items received from the selected vendor.The data entered in this form is saved in Recd_Stk table.

3) This is where the problem is. I am asked to create a subform (this should also be viewed in Datasheet) which will show the current status of the particular item_code selected in 2. Each time a new item is selected in 2, the status of it should be shown in this subform.

I have the query, but i am not able to trap the item_code in 2.

SELECT T1.Item_Code AS [Item Code], NZ(SUM(T1.ITEM_RECD),0) AS [Stock Received], NZ(SUM(T2.ITEM_QTY),0) AS [Stock Issued]
FROM [SELECT ITEM_CODE,SUM(Recd_Stock) AS ITEM_RECD
FROM Recd_Stk GROUP BY ITEM_CODE]. AS T1, [SELECT ITEM_CODE,
SUM(Issued_QTY) AS ITEM_QTY
FROM
ISSUE_Mas
GROUP BY ITEM_CODE]. AS T2
WHERE T1.ITEM_CODE=T2.ITEM_CODE
GROUP BY T1.ITEM_CODE;


Hope I am able to make you understand.

each time I click on the sub-form icon and drag it in the form, the form opens are Childform and is unbound. the wizard for the sub-forms doent comes. Can you tell me why....????

Looking forward to hear from you

Sajit

Ok.

There are at least two ways to cure it:

• Instead of setting Value property of [Vendor] control set DefaultValue property.
  

  [Child82].Form![Vendor].DefaultValue = DLookup("Vendor_Code", "Vendor_Master", "Vendor_Name='" & Me.Vendor & "'")

• You may use conventianal master/child binding. For that you need to redesign your combobox.
  • RowSource = "Select Vendor_Code, Vendor_Name ......"
  • ColumnsCount = 2
  • BoundColumn = 1
  • ColumnWidths = "0;1" or any non-zero value instead of "1"
    Thus the combobox will display vendor names, but its Value property will return vendor codes

Regards,
Fish