Table Query Problem URGENT

[pankajinfo]

Dear Experts Please resolve the query i want to create query in this fomat from two different tables.



Query :- Output Parameter is date between 01-01-2014 ~ 07-01-2014

Prod_desc	Total TB	Total_Prod
A	4	44
B	3	21
C

Prod_desc:- Groupby
Total TB:- Sum TB
Total_Prod:- Sum Prod Qty

1. TB Entry Table:-

Date_of_TB	PID	Total_TB
01-01-2014	1	2
04-01-2014	2	3
06-01-2014	1	2

1. Production Table:-

Date_of_Prod	PID	Total_Prod
01-01-2014	1	10
02-01-2014	1	12
03-01-2014	1	15
04-01-2014	2	10
05-01-2014	1	2
06-01-2014	1	5
07-01-2014	2	11

1. Product table

PID	Prod_desc
1	A
2	B
3	C

PID is foreign key to both tb entry as well as production

Query :- Output Parameter is date between 01-01-2014 ~ 07-01-2014

Prod_desc	Total TB	Total_Prod
A	4	44
B	3	21
C

Prod_desc:- Groupby
Total TB:- Sum TB
Total_Prod:- Sum Prod Qty

[June7]

Suggest you do two queries that summarize TB and Production tables. Then join those two queries to Product table.

[pankajinfo]

Please help with example.

Thanks in Advance

[June7]

Do an aggregate (GROUP BY) Totals query for TB.

Do an aggregate (GROUP BY) Totals query for Production.

Do another query that joins those two queries to Products table.

Use the Query Builder. This is basic Access functionality and Access Help and web have guidelines for building these simple queries.

[pankajinfo]

with dates parameter it is not working.

Please help.

Regards
Pankaj

[June7]

Why doesn't it work, what happens - error message, wrong results, nothing?

Post the sql of the attempted queries. Where did you apply date filter?

[pankajinfo]

as you said i create a query named TBSum with

Product_id	SumOfTotal_Defect
DS FCU	8
Cassette	1
7 mm Coil	9

but when i select Date_of_TB filed in this table it does not groupby and display multiple lines.

Product_id	SumOfTotal_Defect	Date_Of_TB
DS FCU	3	12-03-2014
DS FCU	5	13-03-2014
Cassette	1	13-03-2014
7 mm Coil	9	09-03-2014

SELECT Product.Product_Desc, Sum(ProdSum.SumOfQty) AS SumOfSumOfQty, Sum(TBSum.Total_Defect) AS SumOfTotal_Defect
FROM (Product RIGHT JOIN ProdSum ON Product.Product_id = ProdSum.Product_ID) LEFT JOIN TBSum ON Product.Product_id = TBSum.Product_id
GROUP BY Product.Product_Desc;

[June7]

I don't see any date filter in that sql. Apply date filter but don't show the Date_of_TB field. Uncheck the Show box.

[pankajinfo]

I attached a file Please check

Thanks for you attention.

Pankaj

[June7]

I've never seen query builder look like that. How did you get that appearance in the design grid?

Why are there ? marks in the filter criteria?

[pankajinfo]

this is Visual Studio Query Builder referred to MS Access Table. "?" is the parameter for date.
same problem was coming in ms access query builder.

Thanks for taking your time

[June7]

Did you try suggestion in post 8?

[pankajinfo]

Yes i applied but it is showing adding record with reference.

Thanks

I can share database with you if you are online.

[June7]

Don't know what 'showing adding record with reference' means.

I am out of town till 3/26. Cannot review db now. Add post after 3/26 to get my attention. You can attach db to post.

[pankajinfo]

Dear

i have posted database.

I have tried lots of queries but not able to make it in the following format.

this is with date parameter with check box removed.

Please try because i have to complete this project in this weekend.

Query should result like this:-

Product	Total TB	Total Production
DS FCU	8	5
Cassette	1	1
3-8 mm Coil	Null or blank	5
7mm Coil	9	Null or blank