Query Question!

A

alex

I'm trying to create a query based on two tables.

The first table (tbl1), populated via a form, is filled with several
generic text columns and about 12 yes/no datatype columns named A,B,C,...
etc.

I named these columns A,B,C, ...etc., because they stand for something
else and I couldn't fit that something else into the column headings.

So, e.g., If column A has a check, or a yes, it means a yes to
particular question/response.

I now have another table (tbl2) that I'm trying to marry to tbl1.
Tbl2 has the information that relates to what column A,B,C, ...etc.
actually means.

What I'm trying to do is create a query that will bring these two
tables together. I can keep all of the column headings in tbl1, but
instead of the yes/no response, I want it replaced with a record in
tbl2...something like this: iif([A] = yes, [some value in tbl2], null).
Keep in mind that I can manipulate tbl2 however I want-it's not that
big.

If this is impossible or a complete head scratcher, I could program my
form to populate the specific value (instead of a yes/no) based on the
response from the user; e.g., if a user checks the box then 'some
value' is populated else null, instead of a yes/no value. I didn't
research how to do this initially because I didn't want my table to be
too cumbersome.

Thanks for any thoughts.

alex
 
A

alex

Alex

You've set up a ... spreadsheet! Using "repeating columns" (your A,B,C,...)
to hold information may be how you have to do it in Excel, but in a
relational database like Access, this approach works AGAINST you.

Take a look at Duane Hookom's AtYourSurvey as a model for data structure.

See:

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Regards

Jeff Boyce
Microsoft Office/Access MVP




I'm trying to create a query based on two tables.
The first table (tbl1), populated via a form, is filled with several
generic text columns and about 12 yes/no datatype columns named A,B,C,...
etc.
I named these columns A,B,C, ...etc., because they stand for something
else and I couldn't fit that something else into the column headings.
So, e.g., If column A has a check, or a yes, it means a yes to
particular question/response.
I now have another table (tbl2) that I'm trying to marry to tbl1.
Tbl2 has the information that relates to what column A,B,C, ...etc.
actually means.
What I'm trying to do is create a query that will bring these two
tables together. I can keep all of the column headings in tbl1, but
instead of the yes/no response, I want it replaced with a record in
tbl2...something like this: iif([A] = yes, [some value in tbl2], null).
Keep in mind that I can manipulate tbl2 however I want-it's not that
big.
If this is impossible or a complete head scratcher, I could program my
form to populate the specific value (instead of a yes/no) based on the
response from the user; e.g., if a user checks the box then 'some
value' is populated else null, instead of a yes/no value. I didn't
research how to do this initially because I didn't want my table to be
too cumbersome.
Thanks for any thoughts.
alex- Hide quoted text -

- Show quoted text -

Thanks Jeff for the response. I took a look at Duane's mdb.

You're right that my table is more of a spreadsheet than a database,
but with only one unique identifier per record; I don't see how else
to do it.
I also answered my question above by doing a simple subquery that
selects my identifier from tbl2 where the record in tbl1 = 'YES'.
Thanks again.

alex
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top