Another Query assist

[smig]

I have this Query

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?

SELECT Products_Materials.MaterialID
FROM Products_Materials
WHERE Products_Materials.ProductID=[Forms]![Products_Form]![ProductID]

Thank you

 

[theDBguy]

Hi. Have you tried?


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

 

[smig]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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

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

 

[smig]

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 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

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.