Select statement to return specific data from another column?

[Sarnie83]

Hello

I was just wondering if this is a possibility to do in one query or if it has to be run from a number of different queries.

I am currently developing a database from scratch for work (with very little Access experience).

The current query I am trying to run, if linked to a number of tables with different information.

What I am trying to do primarily is link stock to a specific "Host Name", "Serial Number" and "Part Description".

In the "Host Name" there is for example - A1-TX10-10001, B1-TX2-10004, C1-TX-10004 - The latter part of the name is a unique identifier number. The first part is the compartment in which the "stock" sits. So you may have all three components (A1-TX1, B1-TX2, C1-TX3) linked to the same unique identifier (10001 for example)

The serial numbers naturally are different for every single one and of course the srial numbers are linked to the "Part Description" - which will read something like....."C1-TX3 Transmitter", "B1-TX2 Combiner" etc.....

When I run the query like this the Host Name (which is also linked to the unique identifier on its own (10001) it returns everything under "A1-TX1-10001"

What I would ideally like to do is write a statement so that if the "Part Description" contains "A1-TX1" it will only return rows that contain "A1-TX1" in the Host Name and the same for "B1-TX2" and "C1-TX3" in the same query.

Is there a way to do this?

If "Host Name" contains "A1-TX1" to return "Part Description" to contain "A1-TX1"

Sorry I hope I've included enough information in this and it makes enough sense???

Thanks

 

[nfk]

I really dont know if I understood what you're trying to do but if I did, is it a inner join what you need? take a look at that, I think it is.

 

[Sarnie83]

No, that didn't work but whether thats because its linked to another query or not.

Basically if I was to do this in Excel. I would filter on "Host Name" with "Contains A1-TX1" and then a second filter on "Part Descpription" as "Contains A1-TX1".

Because all of this data is in one report I need to find a way to sift it so that all the host names containing "A1-TX1" only bring up "Part Descpriptions" containing "A1-TX1" and so forth (There are only three variances......A1-TX1, B1-TX2 and C1-TX3, there's just a load of other words around these numbers.

I don't know if this is more an IF statement sort of thing and how I would do it in Access? The original query has been done in design view, but i'm slowly (very slowly) starting to pick up SQL Server.

Thanks

 

[Gasman]

Wouldn't a Union query work with Like "*A1-TX1*" for Part Descriptions and Like "*A1-TX1" for Host Name. Repeat for the other 2 categories

That would be the equivalent of your Excel sort (I think).

 

[Sarnie83]

Is "Like" Access's "Contains" just to check as I think I may have it but I dont think it will work with a union

 

[Gasman]

Yes if you use an * either side of the string.

AFAIK as long as you keep the field structure the same a union will work.

However I am NOT an Access expert, so hopefully others will chip in, but it should not take you long to test?

 

[Sarnie83]

OH MY GOODNESS!! It worked! Thank you so much! It didnt at first until I put the asteriks either side!! Brilliant - thats made my evening (oh dear!!)

Thanks again

 

[Sarnie83]

sorry just one more question - if i want to include all the information do I just need to put and Like "B1-TX2" and like "C1-TX3"? Or what do I need to put innstead of "and"?

 

[Gasman]

Put each criteria on a separate row so for host name your would have

Host_Name
Like "*A1-TX1*"
Like "*B1-TX2*"
LIke "*C1-TX3*"

do the same for the part description and then see what sql is generated.

Otherwise use a union with one pair at a time.

The first option *might* give Host_Name A1-TX1 and Part Description B2-TX2 if you are not careful.
Using a union might be less efficient but should produce what I think you are after.

So I would generate the first statement
and copy it and amend the field names, putting a UNION statement between query 2 and 3

This is an example using only one field, you'd have to experiment

SELECT Ship.Ship
FROM Ship
WHERE (((Ship.Ship) Like "Hamp*"));
UNION
SELECT Ship.Ship
FROM Ship
WHERE (((Ship.Ship) Like "Dart*"));
UNION
SELECT Ship.Ship
FROM Ship
WHERE (((Ship.Ship) Like "*Bridge*"));

HTH

 

[Sarnie83]

Thanks this has worked!! Thanks very much for your time!

 

[Gasman]

Thanks this has worked!! Thanks very much for your time!

Glad to be able to give something back.