Query to find open jobs by findind empty or blank date field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to find the jobs that I have that have no ending date because
they are not yet finished. I would like to find the total hours, billable
amount(hours * billingrate), total expenses, project name, project ID,
project description. I would like to do this by client as well as project.

I tried to use a query that searched for null values in the ending date
field, but this didn't return all of my open jobs. In fact, it only returned
one open job.

Thanks in advance for your help.
 
I would like to find the jobs that I have that have no ending date because
they are not yet finished. I would like to find the total hours, billable
amount(hours * billingrate), total expenses, project name, project ID,
project description. I would like to do this by client as well as project.

I tried to use a query that searched for null values in the ending date
field, but this didn't return all of my open jobs. In fact, it only returned
one open job.

Thanks in advance for your help.
Click to expand...

Please give us some help here too.

We have NO way to know how your tables or your query are structured.
There's obviously an error in the query; if you can post a description
of your tables and the SQL of the query someone might be able to spot
it. Based on the information above we're in the position of a doctor
whose patient says "I don't feel good, Doc, what should I take?"

John W. Vinson[MVP]
 
Here is the query SQL:

SELECT Projects.ProjectID, Projects.ProjectName,
Projects.ProjectDescription, Projects.ProjectBeginDate,
Projects.ProjectEndDate, [Time Card Expenses].ExpenseAmount, [Time Card
Hours].BillableHours, [Time Card Hours].BillingRate,
[billablehours]*[billingrate] AS [Total Billings]
FROM (Projects INNER JOIN [Time Card Hours] ON Projects.ProjectID=[Time Card
Hours].ProjectID) INNER JOIN [Time Card Expenses] ON Projects.ProjectID=[Time
Card Expenses].ProjectID
WHERE (((Projects.ProjectEndDate) Is Null));
The tables are construct as follows:

The Client table contains fields for the ID, Name, SSN, FEIN, Spouse's Name,
Spouse's SSN, address, city, state, Zip, Country, contact name, contact
title, phone number, mobile phone, fax, email address, referral, notes, and
invoice memeo. I used and input mask for the phone numbers and the ssn and
fein, and the zip.

The project form has the project ID, project name, project description,
client ID, PO, Projected total bill, employee ID, project begin date, project
end date. I used applicable input masks for the dates.

I am trying to modify the template that is on the MS site for use by my
accounting firm. I am very new to access. I did purchase the "Bible" text.
It has been somewhat helpful, but does not really go into the subject deeply
or well enough to resolve my issues. If there is a better reference
available, please let me know and I purchase that as well. Thanks for your
help.
 
Here is the query SQL:

SELECT Projects.ProjectID, Projects.ProjectName,
Projects.ProjectDescription, Projects.ProjectBeginDate,
Projects.ProjectEndDate, [Time Card Expenses].ExpenseAmount, [Time Card
Hours].BillableHours, [Time Card Hours].BillingRate,
[billablehours]*[billingrate] AS [Total Billings]
FROM (Projects INNER JOIN [Time Card Hours] ON Projects.ProjectID=[Time Card
Hours].ProjectID) INNER JOIN [Time Card Expenses] ON Projects.ProjectID=[Time
Card Expenses].ProjectID
WHERE (((Projects.ProjectEndDate) Is Null));
Click to expand...

This will only find those Projects which have data for that ProjectID
in both [Time Card Hours] and in [Time Card Expenses] - if there is no
matching ProjectID in one of these tables, you'll get nothing at all.
If you change the INNER JOIN to LEFT JOIN in both cases, you'll see
the project regardless of the presence or absence of records in the
related tables.

I'm a bit worried about the query even if there is data in these
related tables. Since there is no relationship between [Time Card
Hours] and [Time Card Expenses], you'll get all possible combinations
- that is, if you have 10 records in Hours, and 20 in Expenses for a
given project, you'll get 200 rows! I'd suggest that you may want a
Form with Subforms (or a Report with Subreports) instead of trying to
construct a single Query.

John W. Vinson[MVP]
 
Back
Top