Listing quote information

[bluedollar]

----------------------------------------------------------------------

I have the following tables:

CONTRACT

quote_num (joined with site_comm and progress tables)
contract_num
quote_date
po_date
invoice_date
details

SITE_COMM

site_comm_num
quote_num (joined with contract table)
date_done
done_staff_num
type_num
completed_date
completed_staff_num
client
supplier_num

PROGRESS

quote_num (joined with contract table)
p_1
p_2
p_n

----------------------------------------------------------------------

What I am trying to do?

I am trying to create a query that lists information of ALL quotes (for a relevant contract_num), This would consist of the following information:


contract.* (list all fields in CONTRACT where contract.contract_num = Form.contract_num)

sum of SITE_COMM.done_date <> NULL

sum of SITE_COMM.done_date = NULL

progress (sum of all the fields within PROGRESS that are not NULL, eg. sum of p_1, P_2, p_3....P_20 where field <> NULL)


NB. Not all the quotes have a matching SITE_COMM or PROGRESS record.

----------------------------------------------------------------------

Problems

I have treid to create the query, however when I introduce the Site_Comm and progress tables into the query, the query output is limited only to quotes that have a matching site_comm and progress record. Also I need to know how to find the sum of done_date <> NULL/done_date = NULL for all site_comm records for that relevant quote. And also the sum of p_n fields in the progress table that are Not NULL for the relevant quote.

---------------------------------------------------------------------

Any help would be greatly appreciated.

Thanks

Dan

 

[KenReay]

Hi

You need to make an out join to include all records from teh site_com table even if no records in the otehr table, see my reply to your other post, if you still ahev difficulty understanding what to do, post back

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[bluedollar]

Thanks loads for the help, I have been stuck on these problems for ages now, but have now managed to fully solve two of them. However I am still a bit stuck on this one, I have made a query with all the records from the site_comm table, linked to the quotes table. I then included another copy of the site_comm table with all records from it.

The query looks like the following:

Field: quote_num
Table: quote
Total: Group By

Field: quote_date
Table: quote
Total: Group By

Field: po_date
Table: quote
Total: Group By

Field: invoice_date
Table: quote
Total: Group By

Field: details
Table: quote
Total: Group By

Field: contract_num
Table: quote
Total: Group By

Field: completed_date
Table: site_comm
Total: count
criteria: is not null

The above works fine however when I introduce the next field I get an error message:

Field: completed_date
Table: site_comm_1
Total: count
criteria: is null

The error message is as follows:

The SQL statement could not be completed because it contains amibigious outer joins.

The SQL for the query is as follows:

SELECT quote.quote_num, quote.quote_date, quote.po_date, quote.invoice_date, quote.details, quote.contract_num, Count(site_comm.completed_date) AS CountOfcompleted_date, Count(site_comm_1.completed_date) AS CountOfcompleted_date1
FROM ((quote RIGHT JOIN site_comm AS site_comm_1 ON quote.quote_num = site_comm_1.quote_num) LEFT JOIN progress ON quote.quote_num = progress.quote_num) RIGHT JOIN site_comm ON quote.quote_num = site_comm.quote_num
GROUP BY quote.quote_num, quote.quote_date, quote.po_date, quote.invoice_date, quote.details, quote.contract_num
HAVING (((Count(site_comm.completed_date)) Is Not Null) AND ((Count(site_comm_1.completed_date)) Is Null));

Also I still have not worked out how I am going to link the progress table into the query. Progress is a table that contains 20 date fields (numbered p1..p20) and a quote num. What I need to do is find out the number of not null date fields for each quote from its progress record (not every quote will have a progress record).

Any help would be greatly appreciated.

Thanks

Dan

 

[bluedollar]

Any help on this would be really appreciated, this is the only problem I have left, and am unable to solve it.

Thanks

Dan