Using a query to search data instead of primary key

[ambersd12]

Right now I have a a query that searches 4 tables based on the primary key. It returns any values associated with that primary key. The primary key is [Metal OS].

This is my query:
SELECT [HMBA B101, 25C].[Metal OS], [HMBA B101, 25C].*, [HMBA B102,25C].*, [HMBA B103, 25C].*, [HMBA B104, 25C].*
FROM (([HMBA B101, 25C] INNER JOIN [HMBA B102, 25C] ON [HMBA B101, 25C].[Metal OS]=[HMBA B102, 25C].[Metal OS]) INNER JOIN [HMBA B103, 25C] ON ([HMBA B101, 25C].[Metal OS]=[HMBA B103, 25C].[Metal OS]) AND
([HMBA B102, 25C].[Metal OS]=[HMBA B103, 25C].[Metal OS])) INNER JOIN [HMBA B104, 25C] ON ([HMBA B102, 25C].[Metal OS]=[HMBA B104,
25C].[Metal OS]) AND ([HMBA B103, 25C].[Metal OS]=[HMBA B104, 25C].[Metal OS]) AND ([HMBA B101, 25C].[Metal OS]=[HMBA B104, 25C].[Metal OS])
WHERE ((([HMBA B101, 25C].[Metal OS])=[Specify Metal(OS)]));

How would I create a query that searches a range of values? regardless of the primary key, meaning it will return any [Metal OS] with that value?

 

[SkipVought]

hi,

WHERE [HMBA B101, 25C].[Metal OS] IN (YOUR LIST OF VALUES);

where YOUR LIST OF VALUES looks like this...
[tt]
'value1','value2'
[/tt]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PWise]

not sure what you want perhaps an union query

Select *
from(
Select *
from [HMBA B101, 25C] 
union
Select *
from [HMBA B102, 25C]
Union
Select *
from [HMBA B103, 25C]
Union
Select *
from [HMBA B104, 25C]
)uq
WHERE [uq].[Metal OS])=[Specify Metal OS]

 

[ambersd12]

So some of the values are actually text, instead of numbers, will that make it not work? Will that only search the one table, instead of all 4?

 

[ambersd12]

The "where" code doesn't work, or isn't returning anything.

The union query, while works, and might be cleaner than what I was using, doesn't do what I need. I don't want to search by [metal os]. I want to search by values that are listed in the columns of a table. So I want the query to return all the [Metal OS] that contain a field within a range of values. But the fields in my tables are not numbers they are text, does that make a difference, how would you distinguish between text and number?

 

[PWise]

can you please provide a sample of the table and of the values that you want returned

 

[ambersd12]

I'm not quite sure how u can attach a file to these threads. The step 3 things only says type or paste full url, and the files are saved to my desktop computer...

 

[ambersd12]

Maybe this will work?

 

[ambersd12]

So the mediafire thing works, the other ones do not. There are the four tables in the excel document, from those tables I want to be able to search the "?=" columns. So for example I'm interested in any [Metal OS] that has a value between say 2 and 4, so it should return and metal at any "?=" between 2 and 4. Does that make sense?

 

[SkipVought]

Company security prevents me from downloading, but somethin like...

WHERE [uq].[Metal OS] Between 2 AND 4
  AND [?=] Between 2 AND 4

assuming that BOTH these columns are NUMERIC.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[ambersd12]

Yea, the columns aren't numeric, I have the error in so a number in the table looks like 3.62(4), so in access I have them as text. I have 6 columns of "[?=]" [?=1E-1], 5E-1, 1, 2, 5, 0. so would you just do an AND statement for all the columns?

 

[SkipVought]

So did you try TEXT rather than NUMBERS?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]