I am working on an access 2000 db right now. Here is the problem I am
having, and please let me know if I am not explaining it fully.
I have several tables, 3 of which I want to pull information from and
create a summary query that will be displayed via .asp pages built using
frontpage. Each table has a field called toolordernumber that are
related. Second table has entries that list number of hours worked on
this project. Third shows billing to customers for this project. What I
would like to do is this. Create a summary that must:
1. List all toolorders from main table.
2. List,if any, total number of hours worked on this project *25
3. List, if any, amounts billed for each of these orders.
I have created queries for tables 2 and 3 using the unmatched query
wizard and adding IsNull Or IsNotNull criteria to each of these so they
will show all of the orders from the main table and leave a blank field
if there is none.
Finally, I need to be able to add the cost of the hours worked (number
worked*25) to the customer billing total.
The problem I run into is that I have created the summary to pull the
correct info from each query, but when I try to add the two fields, if
one is null, I get another blank field. What is a workaround for this?
Example
Main Table
Toolordernumber
1
2
3
Hours table
Toolordernumber hours
1 4
1 3
3 2
Invoice table
Toolordernumber invoicecost
1 100
2 50
1 50
In this case, I would see totals for toolordernumber 1, but neither of
the other two because they don't have a matching field from the other
table. i.e. total=[invoicetable!invoicecost]+[hourstable!hours]
would not work.
Please help.
