Query does not show all data

[RJS76 via AccessMonster.com]

Hi all,

I need a database which shows the number of calls answered, tickets logged
and tickets closed by the different
agents on a particular date.

Calls answered is in an export called ACD Data.
Tickets logged is in an export called SD Opened.
Tickets closed is in an export called SD Closed.

The export calls answered has the following fields:
Agent name, date, answered calls

The export SD Opened has the following fields:
Agent name, date, tickets logged

The export SD Closed has the following fields:
Agent name, date, tickets closed

I tried to create a database in which I did the following:
- I created three tables called ACD Data, SD Opened and SD Closed which
includes the above mentioned fields.
- I then created a query which includes the three tables
- In the query I linked the field "Agent name" from the different tables to
eachother
- In the query I linked the field "Date" from the different tables to
eachother
- I made the query so that it shows the following:
Agent name, Date, Answered calls, Tickets logged, Tickets closed

My problem is that it sometimes occurs that agents did not answer calls, log
tickets or closed any tickets on a particular day.

For example:

John Doe answered 15 calls. He logged 13 calls but closed none.
Jane Doe answered 10 calls, she logged 9 calls, she closed 7 calls.

If I run the query it only shows me the data for Jane Doe. John Doe is not
visible because he didn't close any calls. Is there a way so that it shows
all data?

As you already see I'm not that expierenced with Access.

Any help would be greatly appreciated.

Thanks in advance.

 

[Jeff Boyce]

The gist of what you need to do is show all of one table's rows (that meet
your criteria), plus ANY of the data from other tables.

To do this, open your query (with the tables joined) in design mode. Decide
which table is the "from" table (the one from which you want to see all
rows).

Highlight the join line between that table and one of the others. Change
its property to reflect "all" of table1 and "any" of table2.

Now do the same with any remaining joins.

 

[RJS76 via AccessMonster.com]

Hello Jeff,

Thank you for your reply.

I have tried what you suggested, but I cannot get it to work. Maybe I'm doing
something wrong.

You said :

"Decide which table is the "from" table (the one from which you want to see
all >rows)."

My problem is that each table contains a bit of the information I want to see.
Table ACD Data contains the numbers of call answered. Table SD Opened
contains the tickets logged. Table SD Closed contains the tickets closed. So
I don't have one table from which I want to see all rows. Each table
contains a piece of information that I want to see in one view (query).

What I have tried is that I created a new query. I added the query I already
had and then I added the three tables. I then tried what you said:

">Highlight the join line between that table and one of the others. Change

its property to reflect "all" of table1 and "any" of table2."

Unfortunately this doesn't work. I think I'm doing something wrong (well, I
know for sure).

I hope that I have explained it clear enough.

Thanks in advance for your help.

The gist of what you need to do is show all of one table's rows (that meet
your criteria), plus ANY of the data from other tables.

To do this, open your query (with the tables joined) in design mode. Decide
which table is the "from" table (the one from which you want to see all
rows).

Highlight the join line between that table and one of the others. Change
its property to reflect "all" of table1 and "any" of table2.

Now do the same with any remaining joins.

Hi all,

[quoted text clipped - 43 lines]

Thanks in advance.

 

[Jeff Boyce]

"It doesn't work", and "I think I'm doing something wrong" doesn't give us
much to go on...

Step back from the entire job and try just doing two tables. Can you get
the query (and the join properties) to work with just two tables?

Your data needs to have a starting point, if I understand your original
post. Where (which table) is the data that contains all the IDs (etc) you
want (even if you don't yet have all the fields attached)?

--
Regards

Jeff Boyce
<Office/Access MVP>

Hello Jeff,

Thank you for your reply.

I have tried what you suggested, but I cannot get it to work. Maybe I'm doing
something wrong.

You said :

"Decide which table is the "from" table (the one from which you want to see
all >rows)."

My problem is that each table contains a bit of the information I want to see.
Table ACD Data contains the numbers of call answered. Table SD Opened
contains the tickets logged. Table SD Closed contains the tickets closed. So
I don't have one table from which I want to see all rows. Each table
contains a piece of information that I want to see in one view (query).

What I have tried is that I created a new query. I added the query I already
had and then I added the three tables. I then tried what you said:

">Highlight the join line between that table and one of the others. Change

its property to reflect "all" of table1 and "any" of table2."

Unfortunately this doesn't work. I think I'm doing something wrong (well, I
know for sure).

I hope that I have explained it clear enough.

Thanks in advance for your help.

The gist of what you need to do is show all of one table's rows (that meet
your criteria), plus ANY of the data from other tables.

To do this, open your query (with the tables joined) in design mode. Decide
which table is the "from" table (the one from which you want to see all
rows).

Highlight the join line between that table and one of the others. Change
its property to reflect "all" of table1 and "any" of table2.

Now do the same with any remaining joins.

Hi all,

[quoted text clipped - 43 lines]

Thanks in advance.

 

[RJS76 via AccessMonster.com]

Hello Jeff,

I started again from scratch and followed your directions and now it works
correctly.

Thanks for your help.

"It doesn't work", and "I think I'm doing something wrong" doesn't give us
much to go on...

Step back from the entire job and try just doing two tables. Can you get
the query (and the join properties) to work with just two tables?

Your data needs to have a starting point, if I understand your original
post. Where (which table) is the data that contains all the IDs (etc) you
want (even if you don't yet have all the fields attached)?

Hello Jeff,

[quoted text clipped - 44 lines]