By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,841 Members | 1,704 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,841 IT Pros & Developers. It's quick & easy.

Problem in query

100+
P: 274
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
Dec 9 '07 #1
Share this Question
Share on Google+
10 Replies


9815402440
100+
P: 180
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
Dec 9 '07 #2

100+
P: 274
Still con't fix it. Please help me
Dec 10 '07 #3

Expert 5K+
P: 8,434
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.
Dec 10 '07 #4

QVeen72
Expert 100+
P: 1,445
Hi,

Try to Group by all the fields used in Select :

Expand|Select|Wrap|Line Numbers
  1. Group By control_info.gst, Group_codes.Group_ID, 
  2. Group_codes.Name
  3.  
Regards
Veena
Dec 10 '07 #5

Expert 5K+
P: 8,434
How are we doing on this - any progress?
Dec 12 '07 #6

100+
P: 274
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.
Dec 13 '07 #7

100+
P: 274
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.
Dec 13 '07 #8

QVeen72
Expert 100+
P: 1,445
Hi,

Check this:

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

Regards
Veena
Dec 14 '07 #9

100+
P: 274
no luck same result
I am worried
Dec 14 '07 #10

100+
P: 274
Just to let you know, I fixed the problem; Don't ask HOW? ; )
Terrible experience.
Kind Regards
Dec 16 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.