Another Query assist (1 Viewer)

smig

Registered User.
Local time
Today, 19:22
Joined
Nov 25, 2009
Messages
2,209
I have this Query

Code:
SELECT Materials.MaterialID, Materials.MaterialName, MaterialType_Table.MaterialTypeName
FROM (MaterialType_Table INNER JOIN Materials ON MaterialType_Table.MaterialTypeID = Materials.MaterialTypeID) LEFT JOIN Q1 ON Materials.MaterialID = Q1.MaterialID
WHERE Q1.MaterialID) Is Null

This is Query Q1
How do I put it into the first one, to make a single Query def?
Code:
SELECT Products_Materials.MaterialID
FROM Products_Materials
WHERE Products_Materials.ProductID=[Forms]![Products_Form]![ProductID]

Thank you
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:22
Joined
Oct 29, 2018
Messages
21,358
Hi. Have you tried?


...) LEFT JOIN (insert the entire Q1 SELECT query here) AS Q1 ON...
 

smig

Registered User.
Local time
Today, 19:22
Joined
Nov 25, 2009
Messages
2,209
Hi. Have you tried?


...) LEFT JOIN (insert the entire Q1 SELECT query here) AS Q1 ON...

Thanks
Can I also use

strQ1 = ....

And put into the query as
...) LEFT JOIN (" & strQ1 & ") AS Q1 ON...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:22
Joined
Oct 29, 2018
Messages
21,358
Thanks
Can I also use

strQ1 = ....

And put into the query as
...) LEFT JOIN (" & strQ1 & ") AS Q1 ON...
Hi. Are you talking about VBA now? If so, it would be better to resolve the form reference outside of the SQL statement.
 

vba_php

Forum Troll
Local time
Today, 12:22
Joined
Oct 6, 2019
Messages
2,884
smig,

I'd be interested in seeing your relational structure in this based on what your business model and strategy is. If my suspicion is accurate, you don't even need a LEFT JOIN if you have the right architecture. Just a thought....
 

smig

Registered User.
Local time
Today, 19:22
Joined
Nov 25, 2009
Messages
2,209
Hi. Are you talking about VBA now? If so, it would be better to resolve the form reference outside of the SQL statement.

Yrs, i do talk VBA

What do you mean by resolving the form refernce outside of SQL?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:22
Joined
Oct 29, 2018
Messages
21,358
Yrs, i do talk VBA

What do you mean by resolving the form refernce outside of SQL?
Hi. Thanks for the clarification. So, if the query has something like:

...WHERE FieldName=Forms!FormName.ControlName

You can't just go like this:

...LEFT JOIN (" & strSQL & ") AS Q1 ON...

You will have to break down the query to become like this:

strSQL="SELECT...WHERE FieldName=" & Forms!FormName.ControlName
...LEFT JOIN (" & strSQL ") AS Q1 ON


Hope that makes sense...
 

smig

Registered User.
Local time
Today, 19:22
Joined
Nov 25, 2009
Messages
2,209
smig,

I'd be interested in seeing your relational structure in this based on what your business model and strategy is. If my suspicion is accurate, you don't even need a LEFT JOIN if you have the right architecture. Just a thought....

Thanks, Adam
This db is for Aromatherapy
I need all materials that are not in a specific product

The Products_Matetials is a Many to Many table, that hold the productID and MayetialDB
 

smig

Registered User.
Local time
Today, 19:22
Joined
Nov 25, 2009
Messages
2,209
Hi. Thanks for the clarification. So, if the query has something like:

...WHERE FieldName=Forms!FormName.ControlName

You can't just go like this:

...LEFT JOIN (" & strSQL & ") AS Q1 ON...

You will have to break down the query to become like this:

strSQL="SELECT...WHERE FieldName=" & Forms!FormName.ControlName
...LEFT JOIN (" & strSQL ") AS Q1 ON


Hope that makes sense...
Yes, sure.
I will replace the Form's thing with a variable (It will be sent to the sub), and use the sane technique.
Even if it was a function I would do the same to make sure it will be read once only, and not for every record.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:22
Joined
Oct 29, 2018
Messages
21,358
Yes, sure.
I will replace the Form's thing with a variable (It will be sent to the sub), and use the sane technique.
Even if it was a function I would do the same to make sure it will be read once only, and not for every record.
No worries there. Access is smart enough to only evaluate a value once, rather than multiple times. Good luck!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:22
Joined
Feb 28, 2001
Messages
26,999
Your stated goal from the first post is to make a single query def. You can certainly do that, in the method as noted by others. However, I would be remiss in my obligation as a helper if I didn't point out the implications of making this into a permanent query def.

When it is a named query, you can run it any time by name. But you will get bizarre errors if the named form isn't open at the time.
 

smig

Registered User.
Local time
Today, 19:22
Joined
Nov 25, 2009
Messages
2,209
No worries there. Access is smart enough to only evaluate a value once, rather than multiple times. Good luck!

I won't trust Access to be that smart to read it only once, and not for every record if I put a DLookup() or DCount() into the query :D

I prefer having it outside the query def and make sure I get it as a single value into the query def
 

smig

Registered User.
Local time
Today, 19:22
Joined
Nov 25, 2009
Messages
2,209
Your stated goal from the first post is to make a single query def. You can certainly do that, in the method as noted by others. However, I would be remiss in my obligation as a helper if I didn't point out the implications of making this into a permanent query def.

When it is a named query, you can run it any time by name. But you will get bizarre errors if the named form isn't open at the time.

Thank
Normally I won't point to another form in a query.
In this case it's a form that opened by another form, so it must be opened. In Any case, I send it to the Query def as a string, and not asking the query to go into the form to read it. This is how I prefer doing it.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 10:22
Joined
Oct 29, 2018
Messages
21,358
I won't trust Access to be that smart to read it only once, and not for every record if I put a DLookup() or DCount() into the query :D

I prefer having it outside the query def and make sure I get it as a single value into the query def
Hi. Whether you trust it or not, it does what it does. Good luck with your project.
 

Users who are viewing this thread

Top Bottom