I need to see total - even if it's 0!

teresamichele

Registered User.
Local time
Today, 00:19
Joined
Oct 18, 2010
Messages
13
Hi all!

I created a database to track time entry for my group. Now we're at fiscal year end and I want to get a big huge Excel spreadsheet together for my boss but I'm encountering a problem.

I have some projects that are worked on every month, without fail. Others, we worked on for a month but then nothing else.

What I want to be able to do is run it so that it will list EVERY code and the hours worked for the month - even if the total hours is 0.

What it does NOW is lists everything for August...provided someone worked on the code that month.

Is there any possible way to modify my query to return ALL results - even if it's 0? :)

Thanks!

Teresa
 
You need to add an empty record for each unique Code in your table.

I don't know what your tables look like so i am guessing here.

Code:
Select Code, 0 as TotalCodes From TableWithAllTheCodes

Union this query with the table with the meaningfull information

Code:
Select Code, Amount From ProductionTable
UNION
Select Code, 0 as TotalCodes From TableWithAllTheCodes

When you store this query (qryCodes) and use it to create the group by or crosstab query you will get the required result.

Enjoy!
 
I feel really silly but I don't understand what you're saying.

A bit more explanation on my database - the data imported contains the employee ID, the project code, the fiscal month, and the time worked.

I DO have a table containing just the project codes. However, every instance of a code also includes a reference to an employee. It's never just "Project 1234 was worked on for 5 hours." It's "Project 1234 was worked on for 5 hours by John Doe."

Are you basically suggesting creating a dummy record of 0 for every code for every month or am I totally misunderstanding you? :)
 
I created a database to track time entry for my group. Now we're at fiscal year end and I want to get a big huge Excel spreadsheet together for my boss but I'm encountering a problem.
I hope your boss reciprocates by giving you a big fat paycheck too ;)

Have a looked into using a Subquery? You can have static Column Headings on there. The Headings could list your dates in August from 1 to 31.

Or you create a table with the values 1 to 31 and link up that field to your Day([DateField]) field via a LEFT JOIN (from your new days table).
 
If no one writes on a certain code you want to see that no one has, do you?
If you create a regular query you will only see the codes that people write on.
So you have to add some dummy records. To make sure that you don't forget any codes i suggested that you create a table containing all the codes and use the first query i described. That will be your unused codes query.

It doesn't matter if you use a new code for every month. The result will be the same. You still want to know the codes no one writes on.

I'll provide you with an example if you post a sample database.

HTH:D
 

Users who are viewing this thread

Back
Top Bottom