Need help in making a query to sort out information

[bhowerton]

Aleb, I need your Help again......Need help in making a query to sort out information

Hello Everyone,

If you do not understand this thread please read it in the attached Word Document - hopefully it will make more sense there.

I have a database with a table consisting of many different fields. One of those fields contains “identification numbers” such as:

Activity Identification #
ZAM1128D1269
CQR23V0D4362 <-- it is not displayed because it
begins with a ‘C’ which is not one of
the authorized criteria listed below
XAM2300D1282
VJM105101290
TJM112899269
PJM1128D1269
MJM1128D1269
HJM230099282
GJM230099282
FJM2300D1282
BVM112899269
AVM2300D1282
6VM2300D1282
8VM2300D1282
TRAINING 8EJ41P94P462
TRAINING 8UN41P912834
TRAINING 8UN410501295 <-- it is training but not displayed
because ‘P9’ is not in the 6th & 7th position
TRAINING 8EN41SY21293 <-- it is training but not displayed
because ‘P9’ is not in the 6th & 7th position

I need to create a query that will display all the identification numbers that meets the following criteria:
The ID number [1st character] MUST begin with a ‘A’, ‘B’, ‘F’, ‘G’, ‘H’, ‘M’, ‘P’, ‘T’, ‘V’, ‘X’, ‘Z’, ‘6’ or ‘8’

The other criteria that must be met is that [after the 1st criteria is met] then if the 2nd character is either an ‘E’ or a ‘U’ *** AND *** the 6th and 7th characters is ‘P9’ then display this identification number also.

*** All other ID numbers that do not meet the criteria should not be displayed ***


Could someone please tell me how to accomplish this?

Could someone also please provide me the code or the details of what to put in the ‘query – criteria’ cells or wherever that it should go?

Thanks,

Bobby Howerton

 

[pbaldy]

Have a look at:

Left()
IN()
Mid()

 

[aleb]

Create new query, then go to the sql view of it and replace the code with this code:

SELECT distinct Yourtable.Yourfield
FROM Yourtable
WHERE (((Mid([yourfield],1,2)) Not In ("Ae","Be","Fe","Ge","He","Me","Pe","Te","Ve","Xe","Ze","Au","Bu","Fu","Gu","Hu","Mu","Pu","Tu","Vu ","Xu","Zu","6e","6u","8E","8U") And (Mid([yourfield],1,2)) Not Like ("c*")))
UNION select distinct Yourtable.Yourfield
FROM Yourtable
WHERE (((Mid([yourfield],2,1) & Mid([yourfield],6,2)) In ("ep9","up9")))
order by 1;

simply paste it, do not forget to change the "yourtable" and "yourfield" with your table name and your field name.

 

[bhowerton]

Aleb, I need additional help...Please help me!!

Aleb,
I did as you said. Here is my code with my fields included, but I am getting an error (see the bottom of this post).

SELECT distinct RAWDATA.MISSION_ID
FROM RAWDATA
WHERE (((Mid([MISSION_ID],1,2)) Not In ("Ae","Be","Fe","Ge","He","Me","Pe","Te","Ve","Xe","Ze","Au","Bu","Fu","Gu","Hu","Mu","Pu","Tu","Vu ","Xu","Zu","6e","6u","8E","8U") And (Mid([MISSION_ID],1,2)) Not Like ("c*")))
UNION select distinct RAWDATA.MISSION_ID
FROM RAWDATA
WHERE (((Mid([MISSION_ID],2,1) and Mid([MISSION_ID],6,2)) In ("ep9","up9")))
ORDER BY 1;

I am getting an error that says " Data Type Mismatch In Criteria Expression "

What does this mean and how do I fix it?

Thanks again,

Bobby

 

[aleb]

You are actually seeking on a number field (mission_ID) which is probably linked to some other table where your identification text is stored with unique number.
If so then there are couple of ways. You can go to that table, add new field which will indicate what type of value is it (training yes/no) and then create your queries based on that or you can post a printscreen image of your table relationship window and we will see how to correct the sql

Example may help you

 

[bhowerton]

Reply to your Last Post...

Hello again,
First, thank you for all of your help!!!

Second, I have attached a couple of jpeg pictures of my database to this post. Is this what you were asking about….I mean what you wanted to see?

My table is named “RAWDATA”. The first is a picture of the RAWDATA TABLE DESIGN. The second picture is showing the additional details of the MISSION_ID field which are located at the bottom. The third is showing the Relationship Window (located under the tools menu---there is not any relationship). Should there be a relationship?

I do not know what to do to fix my database so any help that you can give me would be greatly appreciated.

Also, I saw in the code that you wrote previously you had written:
WHERE (((Mid([MISSION_ID],1,2)) Not In ("Ae","Be","Fe","Ge","He","Me","Pe","Te","Ve","Xe","Ze","Au","Bu","Fu","Gu","Hu","Mu","Pu","Tu","Vu ","Xu","Zu","6e","6u","8E","8U") And (Mid([MISSION_ID],1,2)) Not Like ("c*")))

It is just not only like “C*” but every other letter or number (not listed in the following (A, B, F, G, H, M, P, T, V, X, Z, 6, 8)) that should not be displayed.

The only MISSION_IDs that should be displayed are ones that begin with (A, B, F, G, H, M, P, T, V, X, Z, 6, 8).

I know that this is probably very complicated,…but thank you for taking the time to help me on this.

Is it possible to communicate with you through personal e-mail?

Feel free to e-mail me at bobbyhowerton@earthlink.net and bobby.howerton@park.edu
(please use both e-mail addresses)

Thank you,

Bobby Howerton

 

[KKilfoil]

Whenever I am trying to INCLUDE complex groupings of records with diverse criteria, I often flip the problem around to look at it from an EXCLUDE standpoint.

If you can create a query with all of the records you DO NOT want, then you can have a second query use this and the "NOT IN" statement to 'filter' your records.

The advantage of this is you can use a series of OR's (on different lines of the query design grid) to descibe different types of things to exclude, rather than a series of complex AND's of things to include.