Querying a Table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dustin B - 06 May 2008 22:13 GMT

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.

Reply to this Message

Mr B - 07 May 2008 01:04 GMT

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.

Signature

HTH

Mr B
askdoctoraccess dot com

> 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
[quoted text clipped - 5 lines]
> show only the Master files that for each occurance has CAD w/ an entry of Y.
> Please ask for further detail if needed. Thanks.

Reply to this Message

Dale Fye - 07 May 2008 13:36 GMT

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.

Signature

HTH
Dale

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

email address is invalid
Please reply to newsgroup only.

Reply to this Message

Dustin B - 07 May 2008 14:14 GMT

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,
>
[quoted text clipped - 19 lines]
> > show only the Master files that for each occurance has CAD w/ an entry of Y.
> > Please ask for further detail if needed. Thanks.

Reply to this Message

John Spencer - 07 May 2008 14:56 GMT

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
[quoted text clipped - 36 lines]
>>> show only the Master files that for each occurance has CAD w/ an entry of Y.
>>> Please ask for further detail if needed. Thanks.

Reply to this Message