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.

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/conrad...abaseDesign101

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/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"Tony" wrote:

> 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.

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/ex...tributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

"Tony" wrote:

> Yes I did mean a table with 20 columns...
>
> I kinda knew that I had a design problem there...but I'm not sure of a
> better way to assign a task
> code to a person for a particular event. I could have the task type as a
> field name but then multiple people do the same task at the same event so I
> would need to have multiple names
> for one event record against a single task.
>
> I'll have a look at the suggested links though in any case and try to
> redesign the tables. It'll be
> annoying though as I have already written the queries and forms etc to suit
> the requirements
> of the capturing and reporting. This new issue is a result of needing to
> query in a way which
> was not previously envisaged....(of course)