Querying a Table

[Dustin B]

I have a table with 12 columns. The columns i am concerned with right now
are Master File (TEXT) and CAD(Boolean). I know how to do what I want in VBA
but Access does not support VBA in querries so I am lost. I want the query
to check Master file and store if CAD = Y. Next check the next Master File
to see if master file 1 is the same number if it isn't and CAD = Y then show
the entry. If Master1 does = master2 does CAD of Master2 = Y? If so continue
to check for Master1 if found check CAD. Bottom line I want the query to
show only the Master files that for each occurance has CAD w/ an entry of Y.
Please ask for further detail if needed. Thanks.

 

[Mr B]

You can return the value of a Function in a query. So you can write a Public
Function that will do what you need it to do.

 

[Dale Fye]

Dustin,

I may have misunderstood your explaination, but it appears that what you
want is something like:

SELECT [Master File], [CAD]
FROM yourTableName
WHERE [CAD] = -1

This will get you all the records where [CAD] is true (Y). If this is not
exactly what you are looking for, then provide a couple of rows of sample
data, and then provide a couple of rows that indicate the output you are
looking for, given your sample data.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Dustin B]

That is a start to what I am looking to do, but I don't think it will work
for the final goal. Below is sample data. I want to find all Masters where
each ocurrance of the master is CAD=-1.

Master CAD
12345 Y
12345 Y
54321 Y
54321 N
54321 Y
56789 Y
98765 Y
98765 N

From the above I would want the query to return Masters 12345, 56789 only.
The other two masters contain at least one N for CAD.

Dustin,

I may have misunderstood your explaination, but it appears that what you
want is something like:

SELECT [Master File], [CAD]
FROM yourTableName
WHERE [CAD] = -1

This will get you all the records where [CAD] is true (Y). If this is not
exactly what you are looking for, then provide a couple of rows of sample
data, and then provide a couple of rows that indicate the output you are
looking for, given your sample data.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I have a table with 12 columns. The columns i am concerned with right now
are Master File (TEXT) and CAD(Boolean). I know how to do what I want in VBA
but Access does not support VBA in querries so I am lost. I want the query
to check Master file and store if CAD = Y. Next check the next Master File
to see if master file 1 is the same number if it isn't and CAD = Y then show
the entry. If Master1 does = master2 does CAD of Master2 = Y? If so continue
to check for Master1 if found check CAD. Bottom line I want the query to
show only the Master files that for each occurance has CAD w/ an entry of Y.
Please ask for further detail if needed. Thanks.

 

[John Spencer]

SELECT Master
FROM Table
WHERE Master NOT IN
(SELECT Master FROM Table Where Cad = 0)

"Not in" can be slow with large sets of data so you can make that faster with
the following query (Note that your table names and field names can only use
letters, numbers, and the underscore character for this to work in Access)

SELECT A.Master
FROM Table as A LEFT JOIN
(SELECT Master
FROM Table
WHERE CAD = False) as B
ON A.Master = B.Master
WHERE B.Master is Null

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

That is a start to what I am looking to do, but I don't think it will work
for the final goal. Below is sample data. I want to find all Masters where
each ocurrance of the master is CAD=-1.

Master CAD
12345 Y
12345 Y
54321 Y
54321 N
54321 Y
56789 Y
98765 Y
98765 N

From the above I would want the query to return Masters 12345, 56789 only.
The other two masters contain at least one N for CAD.

Dustin,

I may have misunderstood your explaination, but it appears that what you
want is something like:

SELECT [Master File], [CAD]
FROM yourTableName
WHERE [CAD] = -1

This will get you all the records where [CAD] is true (Y). If this is not
exactly what you are looking for, then provide a couple of rows of sample
data, and then provide a couple of rows that indicate the output you are
looking for, given your sample data.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I have a table with 12 columns. The columns i am concerned with right now
are Master File (TEXT) and CAD(Boolean). I know how to do what I want in VBA
but Access does not support VBA in querries so I am lost. I want the query
to check Master file and store if CAD = Y. Next check the next Master File
to see if master file 1 is the same number if it isn't and CAD = Y then show
the entry. If Master1 does = master2 does CAD of Master2 = Y? If so continue
to check for Master1 if found check CAD. Bottom line I want the query to
show only the Master files that for each occurance has CAD w/ an entry of Y.
Please ask for further detail if needed. Thanks.