Problem in query

[creative1]

Hi Experts
I need your help once again.
I am using this query to calculate total of (price*quantity), and gst grouping the products by main category codes; i.e for all Metals; non metals etc.
Query has no error but the result isn't correct.
It displays double of total and gst for each category; when I print closing summary reports it doubles the total price for all categories.
here is what I have: Text in bold is problematic area.


SELECT control_info.gst, Group_codes.Group_ID,
Group_codes.Name,
SUM(inv_detail.price * inv_detail.quantity) AS price1,
SUM(inv_detail.price * inv_detail.quantity * control_info.gst)
AS gst1
FROM product_codes, catagory_codes, inv_detail, inv_total,
Group_codes, control_info
WHERE group_codes.group_id = catagory_codes.group_id AND
product_codes.cat_code = catagory_codes.cat_code AND
product_codes.Item_code = inv_detail.item_code AND
inv_detail.tran_id = inv_total.tran_id
GROUP BY Group_codes.Group_ID, Group_codes.Name,
control_info.gst

[9815402440]

hi

try same query without grouping and summing. i think fields inv_detail.price and inv_detail.quantity are repeating themselves. problam lies in the join. correcting the join will solve the problam.

regards
manpreet singh dhillon hoshiarpur

[creative1]

Still con't fix it. Please help me

[Killer42]

Still con't fix it. Please help me

I really think you'll get a better response on this in the Access forum. I'm going to move it over there.

[QVeen72]

Hi,

Try to Group by all the fields used in Select :

Group By control_info.gst, Group_codes.Group_ID, 
Group_codes.Name
 

Regards
Veena

[Killer42]

How are we doing on this - any progress?

[creative1]

How are we doing on this - any progress?

I got this query fixed thanks for inquiring. But I had to add a new check with this query.
I am screwed up with a new check in the query thats is failing since last 24 hours.
I think I need help abt that. I'll post it today.

[creative1]

Hi Expert,
Please help me correct this query. I can't get desired results.
Here's what I want to do for accounts's summary report.
here is my query that has no error but can't print what I want.

current GST rate is stored in table control_info(gst)
I want to calculate total of paid amount for group codes present in table .group_code , gst paid from table inv_details.

GST is paid only if following two are true.
for those products that have pay_gst= true in product_codes
vendors that have gst# in table Vendor(gst_no)

SELECT DISTINCT
control_info.gst, Group_codes.Group_ID, Group_codes.Name,
SUM(inv_detail.price * inv_detail.quantity * control_info.gst)
AS gst1
FROM product_codes, catagory_codes, inv_detail, inv_total,
Group_codes, control_info, vendor
WHERE group_codes.group_id = catagory_codes.group_id AND
product_codes.cat_code = catagory_codes.cat_code AND
product_codes.Item_code = inv_detail.item_code AND
inv_detail.tran_id = inv_total.tran_id AND
control_info.status = true AND
product_codes.pay_gst = true AND vendor.gst_no <> NULL
GROUP BY Group_codes.Group_ID, Group_codes.Name,
control_info.gst, vendor.account_no

Problem: the query calculates total of all the transections and does not care about Vendor's gst_no. However pay_gst fields is kep in consideration.
Any help will be highly appreciated.

[QVeen72]

Hi,

Check this:

And (vendor.gst_no IS Not NULL And Trim(vendor.gst_no) <> '')

Regards
Veena

[creative1]

no luck same result
I am worried

[creative1]

Just to let you know, I fixed the problem; Don't ask HOW? ; )
Terrible experience.
Kind Regards