How Do I Retrieve Information from another table if Checkbox is True

[melost]

I'm trying to create a report that requires a list of names that are checked. I guess I can do a list on the report, but not sure how to handle the query portion. tbl1 has all the checkboxes, but tbl2 and tbl3 have the names.

Chkbox1 = person1, etc. Chkbox4 = person4, etc.

tbl1
Chkbox1
Chkbox2
Chkbox3
Chkbox4
Chkbox5
Chkbox6

tbl2
ID Name
1 person1 (if chkbox 1 is checked)
2 person2 (if chkbox 2 is checked)
3 person3 (if chkbox 3 is checked)

tbl3
ID Name
1 person4 (if chkbox 4 is checked)
2 person5 (if chkbox 5 is checked)
3 person6 (if chkbox 6 is checked)

 

[dhookom]

You really need to normalize your table structure before proceeding. Is changing your tables a possibility?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[melost]

Hello,

Thanks for responding. Unfortunately, it's a DB created and managed by someone else where I'm unable to make any changes. I could correspond with him. Any ideas on changes to make?

melost

 

[dhookom]

Normalization is the best long term solution. There are links to a number of articles on this at

http://www.ltcomputerdesigns.com/JCReferences.html#DatabaseDesign101.

Also "Name" is a very bad name for a field (or anything else in Access) since every object has a name property.

You might try create a normalized view of your data using a union query:
[tt][blue]
SELECT [Name] as Person
FROM tbl1, tbl2
WHERE Chkbox1=True AND ID = 1
UNION
SELECT [Name]
FROM tbl1, tbl2
WHERE Chkbox2=True AND ID = 2
UNION
SELECT [Name]
FROM tbl1, tbl2
WHERE Chkbox3=True AND ID = 3
UNION
SELECT [Name]
FROM tbl1, tbl3
WHERE Chkbox4=True AND ID = 1
UNION
SELECT [Name]
FROM tbl1, tbl3
WHERE Chkbox5=True AND ID = 2
UNION
SELECT [Name]
FROM tbl1, tbl3
WHERE Chkbox6=True AND ID = 3
[/blue][/tt]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]