Use of checkboxes in query

[GeirA]

First of all, english is not my mother language - so please excuse my spelling mistakes.



I have 3 tables in a database (MS Access 2010)



I would like to display all equipment for one operator and checkboxes to indicate if they are authorized.



I have tried different join syntaks, but no luck. Any one who can guide me in the right directions??

Thanks!

[June7]

Conventional approach would be to only save a record in Authorized for valid operator/equipment combination.

Should build a form for data entry/edit, not work directly with tables and queries. Options:

1. single form bound to Authorized with comboboxes to select operator and equipment

2. main form bound to Operators and subform bound to Authorized with combobox to select equipment

3. main form bound to Equipment and subform bound to Authorized with combobox to select operator

[pbaldy]

Conventional approach would be to only save a record in Authorized for valid operator/equipment combination.

That appears to be the case already.

GeirA, I think it can be done with 2 queries. First a query with no join to get all combinations (the Cartesian product):

SELECT Operators.ID, Equipment.ID
FROM Equipment, Operators
ORDER BY Operators.ID, Equipment.ID;

Then a query based on that one and the authorized table:

SELECT qryOperatorsAndEquip.Operators.ID, qryOperatorsAndEquip.Equipment.ID, IIf(IsNull([authorized].[EquipmentID]),False,True) AS Authorized
FROM qryOperatorsAndEquip LEFT JOIN Authorized ON (qryOperatorsAndEquip.Equipment.ID = Authorized.EquipmentID) AND (qryOperatorsAndEquip.Operators.ID = Authorized.OperatorID)
ORDER BY qryOperatorsAndEquip.Operators.ID, qryOperatorsAndEquip.Equipment.ID;

Base a form or report on that and you should have your result.

[June7]

Okay, get it now. Want to show all available equipment regardless of status and then highlight equipment authorized for operator. Yep, Paul's suggestion is common approach.

[GeirA]

Thanks a lot! I had no idea I could use 2 queries.

[pbaldy]

Happy to help! By the way your English is very good. One correction, it's "syntax" rather than "syntaks".

[GeirA]

One correction, it's "syntax" rather than "syntaks".

Thanks - syntaks is a norwegian word, and is the same as syntax in english. Sometimes when words are so similar, it slips into my writing :-)

[pbaldy]

Ah! Well, you're doing a lot better than I would be if I tried to speak Norwegian!

[GeirA]

Hmmm - I thought I understood the second query. But when I try to get the Equipment name into the report - Ms Access ask me for a parameter for the eqname field.

I have done this:
SELECT Operators.ID, Equipment.ID, Equipment.EqName
FROM Equipment, Operators
ORDER BY Operators.ID, Equipment.ID;

SELECT qryOperatorsAndEquip.Operators.ID, qryOperatorsAndEquip.Equipment.ID, qryOperatorsAndEquip.Equipment.EqName, IIf(IsNull([authorized].[EquipmentID]),False,True) AS Authorized
FROM qryOperatorsAndEquip LEFT JOIN Authorized ON (qryOperatorsAndEquip.Equipment.ID = Authorized.EquipmentID) AND (qryOperatorsAndEquip.Operators.ID = Authorized.OperatorID)
ORDER BY qryOperatorsAndEquip.Operators.ID, qryOperatorsAndEquip.Equipment.ID;

But when I try to run this last query I get this:

[JoeM]

I have done this:
SELECT Operators.ID, Equipment.ID, Equipment.EqName
FROM Equipment, Operators
ORDER BY Operators.ID, Equipment.ID;

When you have two fields with the same name in the same query, it is best to give them aliases, i.e.

SELECT Operators.ID as OperatorID, Equipment.ID as EquipmentID, Equipment.EqName
FROM Equipment, Operators
ORDER BY Operators.ID, Equipment.ID;

Then when you reference this in your second query, you would reference them like:
qryOperatorsAndEquip.OperatorID
qryOperatorsAndEquip.EquipmentID
(I don't think it like it when you have two periods in a single field like you did before).