Need help advanced query (1 Viewer)

[mr moe]

Hi all,
this might seem simple at first but it's really so complicated. I have a table that has records, there is a column for status. The table has like five different statuses. I have an account # feild and a statues field. I want to query all acount # that have two specific statues for example "status start and status run" this table does not have logical statues order or accuracy, there for It's not easy. Ok hope you didn't get lost, I want to query account # that has two records with status "start" and "run". I don't want to query either a record with only "start" or "run" for example: account # 123 status "run"
acount # 123 status "start"
again no date or logical order basically this table was imported from excel, I will be comparing it to another table once I get the query to work.
waiting for your assitance. thank you so much.

 

[raskew]

Hi -

Are you saying that you can have more than one status in the Status field?

Please provide some examples of field content.

Bob

 

[mr moe]

Yes as long as the record has a combination of the two statues, "run" "start", I don't want to query a record that has "run" and "run" or only run or only start, in this table the record can have more than one run or start but I only want to query records with combination statues. Hope i answered your question, thank you so much.

 

[jzwp22]

Maybe I don't understand, but I would think a query like this should work:

SELECT FieldsOfInterest
FROM yourtablename
WHERE statusfield1="run" and statusfield2="start"

If that is not what you are after can you provide the actual field names in your table and some example data?

 

[mr moe]

Ok see attachmemnt it's an example table, the results i'm trying to get is account number 123 since it has status run and status start, other account #'s has status run but don't have status start so I don't want to see them. Thanks.

FYI there is only one field for status. But the value can be start, run, end, ect... so i want to see the same acount that had two statues in the table, for example account 123 in the attachment, it's like i'm trying to have an "and" for the same field. Thanks.

 

[jzwp22]

I could not open your database. Could you zip it and repost it?

 

[mr moe]

sorry i dont have winzip i just changed the file extension to xls, just change it to mdb. thanks.

 

[Rabbie]

I have a solution that uses 2 queries. Havent got the time jut now to convert itto using a sub query

Query1

SELECT Test.account_num, Test.statues
FROM Test
WHERE (((Test.statues) Like "start"));


Query2

SELECT distinct test.account_num, test.statues, Query1.statues
FROM test INNER JOIN Query1 ON test.account_num = Query1.account_num
WHERE (((test.statues) Like "run"));

 

[mr moe]

Thank you so much Sir, great Idea. You saved me a whole bottle of motrin until next week. Thank I got it this should work.

I have a solution that uses 2 queries. Havent got the time jut now to convert itto using a sub query

Query1

SELECT Test.account_num, Test.statues
FROM Test
WHERE (((Test.statues) Like "start"));


Query2

SELECT distinct test.account_num, test.statues, Query1.statues
FROM test INNER JOIN Query1 ON test.account_num = Query1.account_num
WHERE (((test.statues) Like "run"));

 

[Trocergian]

Hello,

Let me try a little thread jack here with a very similar problem. I'm trying to accomplish the same thing, but with multiple criteria (not just two) and I want the criteria to be assigned by the user at the time of the query.

I'm assuming since the number of criteria can fluctuate that this will need to be done in VBA rather than preconstructed queries. Does anyone have any sample code to demonstrate the process I would need to use?

Thanks.

 

[boblarson]

I'm assuming since the number of criteria can fluctuate that this will need to be done in VBA rather than preconstructed queries.

Actually, not necessarily. It all depends on what you are planning for criteria.

Does anyone have any sample code to demonstrate the process I would need to use?

Check out this sample to see if it will help:

http://downloads.btabdevelopment.com/Samples/combos/FormSampleFromMultipleCriteria.zip

and perhaps this one:

http://downloads.btabdevelopment.com/Samples/reports/Sample-GenericReport.zip

 

[Trocergian]

Those look more like they are for criteria over multiple fields. Let me try to describe more closely what I have.

A table with two fields: Job# and Part#

That lists all the parts for each job. I want to be able to enter in various (anywhere from 1 to maybe 10 or so) Part#'s and get a list of all Jobs#'s that have those particular Part#'s in common.

It would be like putting "part-A" and "part-C" and "part-Q" and "part-Z" all in the same criteria space in a query to get a list of jobs that included parts A,C,Q, and Z.

I hope I described that clearly enough! One of those things that makes a lot of sense to me till I try to describe what I'm trying to do.

 

[boblarson]

Actually, why not use a multiselect listbox instead. Then you can iterate through and build the string using an IN clause:

http://www.baldyweb.com/multiselect.htm

 

[Trocergian]

That's probably not really a practical solution being that the current parts table has 6231 items in it and growing. Using that to populate a listbox and then trying to control multiple selections would get a bit unwieldy I think.

 

[Trocergian]

Although, that does give me an idea. Would it be possible to load the multiple criteria into an array and then read that something like the ItemsSelected feature of a listbox?

Or am I out in left field with that line of thinking?

 

[boblarson]

Nope, you are probably on to something. You can then build your IN (x) criteria like that.

 

[Trocergian]

Hmmm, just doing a quick little test using the IN statement I get results that look like it uses the criteria inside the IN(X) as OR's rather than AND's which is what I need.

In other words, I get results listing jobs that contain any of those parts and not just jobs that contain all those parts. Could I be doing something wrong or is this how an IN(x) statement normally works?

 

[boblarson]

An IN is like saying - This OR This OR This. If you need ANDS then you would need to do it the long way:

YourField = This AND Yourfield=This AND YourField = This