Generated column YES or NO if a value exists in another table...

[Noozer]

SELECT * FROM Choices WHERE Active;

The above query returns all the rows that are active in the Choices table.

The layout for the Choices table is:
ChoicesKey: Autonumber
Choice: text 20char
Active: Boolean

I need to add a column to the above query that is false unless there is an
entry in my Choices2Ticket column for that Choice and a specific TicketKey

The layout for the second table, named Choices2Tickets, is:
TicketKeyLink: Number
ChoiceKeyLink: Number

....so, for Ticket #123 I want all the data from the Choices table PLUS a
column named Checked that is TRUE if there are any entries in the
Choices2Ticket table where the TicketKey is 123 for the current ChoiceKey.

Make sense?

 

[Allen Browne]

Don't put this into your table: it's too messy to maintain.

Instead, get Access to tell you whether which choices are currently in use.

1. Create a query into the Choices table.

2. Type something like this into a fresh column in the Field row:
InUse: EXISTS (SELECT TicketKeyLink FROM Choices2Tickets WHERE
Choices2Tickets.ChoiceKeyLink = Choices.ChoicesKey)

 

[Noozer]

I'm not sure I understand your reply...

Some sample data:

Table Choices: (ChoicesKey, Choice, Active)
1, No Answer, True
2, Message, True
3, Try Later, True
4, Successful, True
5, Offered Special, False

Table Choices2Ticket: (TicketKeyLink, ChoiceKeyLink)
1, 1
1, 3
2, 4
3, 2
3, 5

Result table if I want ticket #1: (ChoiceKey, Choice, Checked)
1, No Answer, True
2, Message, False
3, Try Later, True
4, Successful, False

Result table if I want ticket #2: (ChoiceKey, Choice, Checked)
1, No Answer, False
2, Message, False
3, Try Later, False
4, Successful, True

Notice that Choice #5 never appears because it has FALSE in the Active
column.
Notice that I get all the choices that are active, even if they don't appear
in the Choices2Ticket table for the specified ticket.

My big problem... I don't know how to apply the EXISTS keyword properly
here.

Thx!

 

[Allen Browne]

Okay, so you want every possible combination of Ticket + active-choice, and
a yes/no to indicate if the choice is in use.

Presumably you also have a Ticket table, with TicketKey as primary key.

1. Create a query into both the Ticket and Choices tables.

2. If you see any line joining the 2 tables, in the upper window, delete the
line. This gives you every combination.

3. Drag TicketKey from Ticket into the output grid.

4. Drag ChoiceKey and Choice from Choices into the output grid.

5. Drag Choices.Active into the grid.
Uncheck the Show box.
In the Criteria row, enter:
True
This eliminates any choices that are not active.

6. Type the subquery into a fresh column in the Field row of the output
grid:

Checked: Exists (SELECT TicketKeyLink FROM Choices2Ticket
WHERE Choices2Ticket.TicketKeyLink = Ticket.TicketKey
AND Choices2Ticket.ChoiceKeyLink = Choices.ChoiceKey )

If you need more help on subqueries, see:
http://support.microsoft.com/?id=209066

 

[Noozer]

Nope... No ticket table. The tickets are physical pieces of paper. The
TicketKeyLink is the serial # for each ticket. Eventually there will be a
table, but nobody can agree on what should exist on that table - and I'm not
going to get stuck in the position of "do it this way for now and we'll
change it later". : )

I'm using the query on an ASP webpage to generate a list of all choices with
checkboxes for selection. I want to be able to precheck the boxes that exist
in the Choices2Ticket table.

So, with my data below, if I load the ASP page for ticket #3 I want to see
the four active choices, with choice #2 already checkmarked. Choice #5 won't
display because it is not active.

 

[Noozer]

I figured it out...

SELECT Choices.*, EXISTS(SELECT TicketKeyLink FROM Choices2Ticket WHERE
ChoiceKeyLink=ChoiceKey AND TicketKeyLink=3) AS Checked FROM Choices;

...of course substituting my ticket number for the number 3.

Thanks muchly though... I never really understood the Access query designer
before, but it's a lot clearer now!