By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,374 Members | 2,993 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,374 IT Pros & Developers. It's quick & easy.

Here's one for ya.

P: n/a
I have a history table with a bunch of IDs. What I need is to collect a
company/project id on the history table that has a particular yes/no
field flagged, then EXCLUDE that ID from a query. I've tried using a
query that lists the flagged dates along with the ID tags associated,
and tying that back to either the company info table or history, with
the criteria

<>[DecDate]![CyID]

Where DecDate is the query that lists the dates and IDs of the flag.
But I can't get this to work.

May 8 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Jaaz wrote:
I have a history table with a bunch of IDs. What I need is to collect a
company/project id on the history table that has a particular yes/no
field flagged, then EXCLUDE that ID from a query. I've tried using a
query that lists the flagged dates along with the ID tags associated,
and tying that back to either the company info table or history, with
the criteria

<>[DecDate]![CyID]

Where DecDate is the query that lists the dates and IDs of the flag.
But I can't get this to work.

I don't know about DecDate or CyID, but if I understand the problem,
here's what I might do. T1, History are the table names I'll use in the
example.

Query/New and add tables T1 and History. Drag relationship line between
T1 and History on the field ID. Now dbl-click the join line and set it
to All records in T1 and those that match in History. Now drag the
fields to display from T1. Now drag the ID field and YesNo field from
History. In the criteria row for ID, enter
Is Null
Right underneath enter
it Is Not Null
On the Is Not Null criteria line, under YesNo enter False or True
depending on whether or not you want toe data for Yes or No values

This is basically saying show all records from T1 that has no history
record or if it does, the YesNo must be Yes or No...your choice.


May 8 '06 #2

P: n/a
You could also try nesting a select statement in the criteria like this;

"SELECT * FROM T1 WHERE T1.ID NOT IN(SELECT T1ID FROM History WHERE
MyYesNoField=True;);"

That will filter out IDs from the Main table that have associated records in
History that have your YesNo field set to true in this case. This also
occurs without having to do any grouping. If you want to filter out IDs
that have the YesNo field set to false, just replace the True in the
preceding statement to False.

"Jaaz" <Ja****@gmail.com> wrote in message
news:11*********************@g10g2000cwb.googlegro ups.com...
I have a history table with a bunch of IDs. What I need is to collect a
company/project id on the history table that has a particular yes/no
field flagged, then EXCLUDE that ID from a query. I've tried using a
query that lists the flagged dates along with the ID tags associated,
and tying that back to either the company info table or history, with
the criteria

<>[DecDate]![CyID]

Where DecDate is the query that lists the dates and IDs of the flag.
But I can't get this to work.

May 9 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.