Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-02-2019, 01:43 PM   #1
smig
Newly Registered User
 
Join Date: Nov 2009
Location: Israel
Posts: 2,043
Thanks: 82
Thanked 134 Times in 125 Posts
smig is on a distinguished road
SQL Query help

Hi

I need some help creating am SQL query

I have 3 tables
tblMaterials - MaterialID, MaterialName. MaterialType
tblProducts - ProductID, ProductName
tblProductsMaterials - ProductID, MaterialID, MaterialPercent (This one is a Many to Many table)

Now I need queries to sum data
1st query - Count Materials for each productID, Where MaterialType=1
2nd query - Sum the MaterialPercent for each productID Where MaterialType=2

Thanks

smig is offline   Reply With Quote
Old 12-02-2019, 01:55 PM   #2
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,413
Thanks: 58
Thanked 1,408 Times in 1,389 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: SQL Query help

Hi. It would be easier if you could post a sample copy of your db with test data, but try the following for Query1:
Code:
SELECT ProductID, Count(*) AS ProductCount
FROM tblProductsMaterials
INNER JOIN tblMaterials
ON tblProductsMaterials.MaterialID=tblMaterials.MaterialID
WHERE MaterialType=1
GROUP BY ProductID
(untested)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 12-02-2019, 02:15 PM   #3
smig
Newly Registered User
 
Join Date: Nov 2009
Location: Israel
Posts: 2,043
Thanks: 82
Thanked 134 Times in 125 Posts
smig is on a distinguished road
Re: SQL Query help

Quote:
Originally Posted by theDBguy View Post
Hi. It would be easier if you could post a sample copy of your db with test data, but try the following for Query1:
Code:
SELECT ProductID, Count(*) AS ProductCount
FROM tblProductsMaterials
INNER JOIN tblMaterials
ON tblProductsMaterials.MaterialID=tblMaterials.MaterialID
WHERE MaterialType=1
GROUP BY ProductID
(untested)
Thank you for the quick reply

To be more exact I need to see all products, even those how has no MaterialType = 1
Actually those products are the most impotent for me

Thanks

smig is offline   Reply With Quote
Old 12-02-2019, 02:26 PM   #4
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,418
Thanks: 11
Thanked 2,285 Times in 2,237 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: SQL Query help

Smells like homework--which is fine. But what have you tried?
plog is offline   Reply With Quote
Old 12-02-2019, 02:33 PM   #5
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,413
Thanks: 58
Thanked 1,408 Times in 1,389 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: SQL Query help

Quote:
Originally Posted by smig View Post
Thank you for the quick reply

To be more exact I need to see all products, even those how has no MaterialType = 1
Actually those products are the most impotent for me

Thanks
Hi. If you want to see "all products," then just take out the WHERE clause. And for that matter, you don't even need to JOIN two tables, you just need one (tblProductsMaterials).
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 12-02-2019, 09:48 PM   #6
smig
Newly Registered User
 
Join Date: Nov 2009
Location: Israel
Posts: 2,043
Thanks: 82
Thanked 134 Times in 125 Posts
smig is on a distinguished road
Re: SQL Query help

Quote:
Originally Posted by plog View Post
Smells like homework--which is fine. But what have you tried?


No it's not.
It's for a project I'm working on
smig is offline   Reply With Quote
Old 12-02-2019, 09:50 PM   #7
smig
Newly Registered User
 
Join Date: Nov 2009
Location: Israel
Posts: 2,043
Thanks: 82
Thanked 134 Times in 125 Posts
smig is on a distinguished road
Re: SQL Query help

Quote:
Originally Posted by theDBguy View Post
Hi. If you want to see "all products," then just take out the WHERE clause. And for that matter, you don't even need to JOIN two tables, you just need one (tblProductsMaterials).
Thanks
I need products that has no MaterialType = 1
I think a left join is the right way

smig is offline   Reply With Quote
Old 12-02-2019, 10:46 PM   #8
smig
Newly Registered User
 
Join Date: Nov 2009
Location: Israel
Posts: 2,043
Thanks: 82
Thanked 134 Times in 125 Posts
smig is on a distinguished road
Re: SQL Query help

What I'm trying to do is to put query Q1 into:
Code:
SELECT [Products].[ProductID]
FROM [Products] LEFT JOIN [q1] ON [Products].[ProductID] = [q1].[ProductID]
WHERE [q1].[ProductID] Is Null
Q1 query
Code:
SELECT [Products_Materials].[ProductID]
FROM [Products_Materials] INNER JOIN [Materials] ON [Products_Materials].[MaterialID] = [Materials].[MaterialID]
WHERE [Materials].[MaterialTypeID]=1 
GROUP BY [Products_Materials].[ProductID]
smig is offline   Reply With Quote
Old 12-03-2019, 07:43 AM   #9
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,413
Thanks: 58
Thanked 1,408 Times in 1,389 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: SQL Query help

Hi. I can't tell if you still need any help. If so, you might consider posting some sample data and a mockup of the result you're trying to get out of it. Cheers!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 12-03-2019, 11:35 AM   #10
smig
Newly Registered User
 
Join Date: Nov 2009
Location: Israel
Posts: 2,043
Thanks: 82
Thanked 134 Times in 125 Posts
smig is on a distinguished road
Re: SQL Query help

Quote:
Originally Posted by theDBguy View Post
Hi. I can't tell if you still need any help. If so, you might consider posting some sample data and a mockup of the result you're trying to get out of it. Cheers!
Hi, Thanks
Yes, I do need some help

As I wrote in my last post I would like to "integrate" Q1 into the other, but have it all written as a single SQL.

If this is not possible I will create Q1 as a built query.

Thanks
smig is offline   Reply With Quote
Old 12-03-2019, 11:50 AM   #11
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,413
Thanks: 58
Thanked 1,408 Times in 1,389 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: SQL Query help

Quote:
Originally Posted by smig View Post
Hi, Thanks
Yes, I do need some help

As I wrote in my last post I would like to "integrate" Q1 into the other, but have it all written as a single SQL.

If this is not possible I will create Q1 as a built query.

Thanks
Hmm, is this what you mean?
Code:
SELECT [Products].[ProductID] 
FROM [Products] 
LEFT JOIN (SELECT [Products_Materials].[ProductID] 
        FROM [Products_Materials] 
        INNER JOIN [Materials] 
            ON [Products_Materials].[MaterialID] = [Materials].[MaterialID] 
        WHERE [Materials].[MaterialTypeID]=1  
        GROUP BY [Products_Materials].[ProductID]) [q1] 
    ON [Products].[ProductID] = [q1].[ProductID] 
 WHERE [q1].[ProductID] Is Null
(untested)
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 12-03-2019, 12:13 PM   #12
smig
Newly Registered User
 
Join Date: Nov 2009
Location: Israel
Posts: 2,043
Thanks: 82
Thanked 134 Times in 125 Posts
smig is on a distinguished road
Re: SQL Query help

Quote:
Originally Posted by theDBguy View Post
Hmm, is this what you mean?
Code:
SELECT [Products].[ProductID] 
FROM [Products] 
LEFT JOIN (SELECT [Products_Materials].[ProductID] 
        FROM [Products_Materials] 
        INNER JOIN [Materials] 
            ON [Products_Materials].[MaterialID] = [Materials].[MaterialID] 
        WHERE [Materials].[MaterialTypeID]=1  
        GROUP BY [Products_Materials].[ProductID]) [q1] 
    ON [Products].[ProductID] = [q1].[ProductID] 
 WHERE [q1].[ProductID] Is Null
(untested)
This one made my head spin
I'll test it and let you know if it works.

Thanks
smig is offline   Reply With Quote
Old 12-03-2019, 12:16 PM   #13
theDBguy
I知 here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,413
Thanks: 58
Thanked 1,408 Times in 1,389 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: SQL Query help

Quote:
Originally Posted by smig View Post
This one made my head spin
I'll test it and let you know if it works.

Thanks
Hi. If it doesn't work, we'll really need to know what you're trying to do and see some sample data to be able to tell you how to construct the proper SQL statement to get what you want. Good luck!

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
smig (12-03-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 11:32 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World