Query Question

[sanderson]

Hello:

I am using Access 2013.

I have two queries, one that works and one that does not.


This one works...

SELECT qryFindOps.* FROM ( SELECT DISTINCT dbo_labordtl.opcode FROM dbo_jobhead LEFT JOIN dbo_labordtl ON dbo_jobhead.jobnum = dbo_labordtl.jobnum WHERE dbo_labordtl.jcdept="MA" AND (Left(dbo_jobhead.jobnum,5) = "36800" Or Left(dbo_jobhead.jobnum,5) = "36801" Or Left(dbo_jobhead.jobnum,5) = "36802" Or Left(dbo_jobhead.jobnum,5) = "36803" Or Left(dbo_jobhead.jobnum,5) = "36804") UNION SELECT DISTINCT dbo_Qantellabordtl.opcode FROM dbo_QantelJobHead LEFT JOIN dbo_QantelLaborDtl ON dbo_QantelJobHead.jobnum = dbo_QantelLaborDtl.jobnum WHERE dbo_QantelLaborDtl.jcdept="MA" AND (Left(dbo_QantelJobHead.jobnum,5) = "36800" Or Left(dbo_QantelJobHead.jobnum,5) = "36801" Or Left(dbo_QantelJobHead.jobnum,5) = "36802" Or Left(dbo_QantelJobHead.jobnum,5) = "36803" Or Left(dbo_QantelJobHead.jobnum,5) = "36804")) as qryFindOps ORDER BY qryFindOps.opcode

This one fails...

SELECT qryFindOps.* FROM ( SELECT DISTINCT dbo_labordtl.opcode FROM dbo_jobhead LEFT JOIN dbo_labordtl ON dbo_jobhead.jobnum = dbo_labordtl.jobnum WHERE dbo_labordtl.jcdept="MA" AND dbo_labordtl.opcode LIKE '1???' AND (Left(dbo_jobhead.jobnum,5) = "36800" Or Left(dbo_jobhead.jobnum,5) = "36801" Or Left(dbo_jobhead.jobnum,5) = "36802" Or Left(dbo_jobhead.jobnum,5) = "36803" Or Left(dbo_jobhead.jobnum,5) = "36804") UNION SELECT DISTINCT dbo_Qantellabordtl.opcode FROM dbo_QantelJobHead LEFT JOIN dbo_QantelLaborDtl ON dbo_QantelJobHead.jobnum = dbo_QantelLaborDtl.jobnum WHERE dbo_QantelLaborDtl.jcdept="MA" AND dbo_labordtl.opcode LIKE '1???' AND (Left(dbo_jobhead.jobnum,5) = "36800" Or Left(dbo_jobhead.jobnum,5) = "36801" Or Left(dbo_jobhead.jobnum,5) = "36802" Or Left(dbo_jobhead.jobnum,5) = "36803" Or Left(dbo_jobhead.jobnum,5) = "36804")) as qryFindOps ORDER BY qryFindOps.opcode

The query returns a distinct group of four digit string numbers, such as '0007', '0020', '0060', ..., '1000', '1100'. My goal is to only include numbers that start with a 1, so that in the above example only the last two would be considered. I was hoping changing

WHERE dbo_QantelLaborDtl.jcdept="MA"

to

WHERE dbo_QantelLaborDtl.jcdept="MA" AND dbo_labordtl.opcode LIKE '1???'

would have narrowed my list. Instead I have ther query asking me for more information. It is looking for the dbo_labordtl.opcode.

I am guessing something needs to change in the fields selected in the initial SELECT DISTINCT portion of the query.

SELECT DISTINCT dbo_labordtl.opcode FROM

as if it does not weant to select itself or something.

The other query in the statement, qryFindOps, I believe is being created with the statement as some kind of alias. This as well, I do not really understand, if you have any reference you can point me to to help. The point here is, there is no query saved that is being accessed in the database with this name.

Any light you can shed would be much appreciated.

Thanks,

[rpeare]

if you are using ? where you want a wildcard it's just

like '1*'

Are you actually looking for a string value that has question marks in it?

[sanderson]

No, I am looking for all the data that starts with 1. "1???" and "1*" produce the same results, I have tried them both.

[rpeare]

Can you be more specific about 'doesn't work'

With regard to your alias this is a union query and the query qryFindOps is defined within the query itself

SELECT DISTINCT dbo_Qantellabordtl.opcode FROM dbo_QantelJobHead LEFT JOIN dbo_QantelLaborDtl ON dbo_QantelJobHead.jobnum = dbo_QantelLaborDtl.jobnum WHERE dbo_QantelLaborDtl.jcdept="MA" AND dbo_labordtl.opcode LIKE '1???' AND (Left(dbo_jobhead.jobnum,5) = "36800" Or Left(dbo_jobhead.jobnum,5) = "36801" Or Left(dbo_jobhead.jobnum,5) = "36802" Or Left(dbo_jobhead.jobnum,5) = "36803" Or Left(dbo_jobhead.jobnum,5) = "36804")) as qryFindOps

Try breaking your union query into it's two components and see which one, or both of them are not working as you intend.

This is the first part:

SELECT qryFindOps.* FROM ( SELECT DISTINCT dbo_labordtl.opcode FROM dbo_jobhead LEFT JOIN dbo_labordtl ON dbo_jobhead.jobnum = dbo_labordtl.jobnum WHERE dbo_labordtl.jcdept="MA" AND dbo_labordtl.opcode LIKE '1???' AND (Left(dbo_jobhead.jobnum,5) = "36800" Or Left(dbo_jobhead.jobnum,5) = "36801" Or Left(dbo_jobhead.jobnum,5) = "36802" Or Left(dbo_jobhead.jobnum,5) = "36803" Or Left(dbo_jobhead.jobnum,5) = "36804")

The above is the alias I would try that aliased query first to see if it produces results, work on the separate parts that are directly related to the tables.

[sanderson]

Specifically, there are two queries. The one that works produces data...


The second query produces this...



My goal with the second query is to exclude numbers starting with leading zeros, and start with specifically, '1000'.

In the first query, I added what is shown in bold red below:

SELECT qryFindOps.* FROM ( SELECT DISTINCT dbo_labordtl.opcode FROM dbo_jobhead LEFT JOIN dbo_labordtl ON dbo_jobhead.jobnum = dbo_labordtl.jobnum WHERE dbo_labordtl.jcdept="MA" AND dbo_labordtl.opcode LIKE '1???' AND (Left(dbo_jobhead.jobnum,5) = "36800" Or Left(dbo_jobhead.jobnum,5) = "36801" Or Left(dbo_jobhead.jobnum,5) = "36802" Or Left(dbo_jobhead.jobnum,5) = "36803" Or Left(dbo_jobhead.jobnum,5) = "36804") UNION SELECT DISTINCT dbo_Qantellabordtl.opcode FROM dbo_QantelJobHead LEFT JOIN dbo_QantelLaborDtl ON dbo_QantelJobHead.jobnum = dbo_QantelLaborDtl.jobnum WHERE dbo_QantelLaborDtl.jcdept="MA" AND dbo_labordtl.opcode LIKE '1???' AND (Left(dbo_jobhead.jobnum,5) = "36800" Or Left(dbo_jobhead.jobnum,5) = "36801" Or Left(dbo_jobhead.jobnum,5) = "36802" Or Left(dbo_jobhead.jobnum,5) = "36803" Or Left(dbo_jobhead.jobnum,5) = "36804")) as qryFindOps ORDER BY qryFindOps.opcode

What am I missing?


Thanks for the help.

[John_G]

The prompt box suggests that the table dbo_labordtl does not contain a field called opcode. Is that the case?

[sanderson]

This field is in the SELECT DISTINCT in both queries, and what I have returned in the first example for data!

[sanderson]

Note, if I enter in any valid opcode and job number at runtime, it works. However, I do not have to do this in the first query. Something else needs to be added to the query to make it work.

[John_G]

Look at the second part of your query, after the UNION.

The table dbo_labordtl is not part of the FROM (it has dbo_QantelJobHead left join dbo_QantelLaborDtl), so when Access sees dbo_labordtl in the WHERE, it doesn't know what it is, so it prompts for the value.

Is Opcode in one of the two tables in the FROM part?

[sanderson]

The concern is, the same logic works in the first query. I will play around with it.

[John_G]

It works in the first query because you don't reference table dbo_labordtl in the WHERE clause of the sub-query following the UNION. Go up and look a post #5 in this thread; you have highlighted two parts in red, and it is the second one that is causing the problem.