creating a query that only returns columns based on prompted respo

[Guest]

Looking for a little help on this brain teaser....

I have an access db with around 20 columns.
Of these 20 columns 10 of them are user names.
The remaining are tasks completed.
what I would like to do is have a query that show all the data for the first
10 columns but only where there is a value in the column that is related to
the user asking the question.
ie ask them for their name and the query will return all rows for columns
1-10 and the column with their name on top.
This will allow them to see their performance at the task if they were
actually there or not and ultimately give them a % of attendance number.

 

[Guest]

Hi Tony,

I have an access db with around 20 columns.

I suspect that you meant to say that you have a table in an Access
application that has around 20 columns. Databases don't have columns; tables
do.

Of these 20 columns 10 of them are user names.

Big red flag. This is not considered a properly normalized design. You have
a multi-valued field design, which is going to cause you no end of problems
until you redesign this database. Here is a link that includes several DB
design articles:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

Also recommended: Find the copy of Northwind.mdb that is probably already
installed on your hard drive. Study the relationships between the various
tables (Tools > Relationships...)

Does you present design require that you add a new field (column) to
accomodate a new person? If so, you definately have a design problem with
this application. Something to remember: Fields are expensive; Records are
cheap.
In other words, the design should be such that you can add and remove people
simply by adding or removing records (no design changes required).

what I would like to do is have a query that show all the data for the first
10 columns but only where there is a value in the column that is related to
the user asking the question.

It sounds like you might want a crosstab query type recordset. You can
create a crosstab query that is based on a parameter, such as a person's
name. Here is a tutorial for crosstab queries, however, you need to start
with a properly normalized DB first:

Crosstab Queries
http://www.access.qbuilt.com/html/crosstab_queries.html




Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Hi Tony,

It sounds like you have the following relationships:

1.) An event can include many tasks (1:M)

2.) A task can be performed by many people (1:M)
3.) A person can perform many tasks (1:M)

The 2nd and 3rd relationships define a many-to-many (M:N) relationship. You
need a linking table (also known as a join or intersection table) to create
this relationship.

So, off hand, it looks like you would have at least (4) tables, minimum:
Events, Tasks, People, and the linking table in-between People and Tasks.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________