Help with SQL statement

[zakster]

Hi all,

I have two tables - Quote and Job (each Job has an associated Quote, but not all Quotes have a Job).

I also have a search form which enables the user to search the quotes/jobs in the system. The search fields include QuoteNumber, JobNumber, Date, Client, etc.

I am having trouble with the query used to produce the search results. The trouble comes in with those quotes that have jobs. This is the code I have in place to query the database...

Dim strSelect1 As String
Dim strSelect2 As String

strSelect1 = "SELECT * FROM (SELECT J.ID as JobID, Q.ID as QuoteID, "
strSelect1 = strSelect1 & "Q.Date, Q.Client, J.Status as Status "
strSelect1 = strSelect1 & "FROM Job AS J, Quote AS Q WHERE "
  
strSelect2 = "SELECT '-' as JobId, Q.ID as QuoteID, Q.Date, "
strSelect2 = strSelect2 & "Q.Client, Q.chrState as Status "
strSelect2 = strSelect2 & "FROM Quote AS Q, Job AS J WHERE "

'Make up the filter string
strFilter = ""
If Not IsNull(Me!QuoteNumber) Then strFilter = strFilter & "Q.ID=" & Me!QuoteNumber & " AND "
If Not IsNull(Me!JobNumber) Then strFilter = strFilter & "J.ID=" & Me!JobNumber & " AND "
If Not IsNull(Me!Date) Then strFilter = strFilter & "Q.Date=" & Chr(34) & Me!Date & Chr(34) & " AND "
If Not IsNull(Me!Client) Then strFilter = strFilter & "Q.Client=" & Me!Client & " AND "

If strFilter <> "" Then
  Dim queryString As String
  If IsNull(Me!JobNumber) Then
    queryString = strSelect1 & strFilter & " J.Quote=Q.ID UNION " & strSelect2 & strFilter
    queryString = queryString & " NOT J.Quote=Q.ID"
    queryString = queryString & ") ORDER BY QuoteID"
  Else
    queryString = strSelect1 & strFilter & " J.Quote=Q.ID "
    queryString = queryString & ") ORDER BY QuoteID"
  End If
  Me.QuoteSearchResults.Form.RecordSource = queryString
End If

The problem is for each quote that has an associated job I get two entries in the results....for example

Job: 4 --- Quote: 2 --- Date: 12/12/07 --- Status: 'NEW'
Job: - --- Quote: 2 --- Date: 12/12/07 --- Status: 'ACCEPTED'

I only want the first row to show; that is - if a quote has a job then both the job and quote number should be shown. If a quote doesn't have a job then just show the quote number.

Any idea how I can achieve this? Sorry if this is hard to understand.

Thanks in advance

 

[zakster]

Can anyone help me with this issue?

 

[Zaeed]

wouldn't it be easier to use the DISTINCT sql function. so only pull out unique quotes

 

[zakster]

I was thinking this - but how can I use the distinct function to get all quotes that have no job associated and also those quotes that do have jobs.

i.e. if i do

select distinct .... from Quote, Job
where
Job.QuoteID=Quote.ID

then I won't get any of the quotes that don't have jobs attached to them yet.

 

[Zaeed]

just do it like you are currently with the IsNull statements..

can you post up the db

 

[zakster]

Unfortunatley I can not post up the database

But....the basic gist is this

Quote table
------
ID
Date
Client
State

Job table
------
ID
QuoteID
State

So there are many quotes in the database, and some of those quotes have associated jobs.

If I use the distinct keyword with my existing sql statement in the first post - I still get two rows for every quote that has a job - because those rows ARE distinct. Since one has no job number '-' and the other has a job number.

So how can I make sure that all quotes with jobs only show up once in the results list, but still show all quotes that do not have an associated job?

 

[Zaeed]

SELECT DISTINCT * FROM QuoteTable WHERE QuoteTable.ID = JobTable.QuoteID AND QuoteTable.ID <> JobTable.QuoteID

Try that.. im not sure if it'll work though.. It would be easier if you could just post up the db.. Is it that you dont know how, or are not allowed to?

*EDIT* change the AND in the WHERE to an OR

 

[zakster]

Ummm can't post because it is too big - is there a way to easily down the size to post it?

 

[Zaeed]

For starters, in access go to Tools -> Database Utilities and click Compact and Repair

Then, close access, Right Click your Database file and goto Send To -> Commpressed (Zipped) Folder. This will create a folder with the same name as your database, with an extension of .zip This file should be much smaller. Then in a post, click the paperclip button and upload the zipped file

 

[Zaeed]

Just a thought, but could you simply add an extra field to your QuoteTable which holds the relating Job, and keep it blank if it doesnt have a job.

 

[Zaeed]

This should work..

SELECT * FROM QuoteTable LEFT OUTER JOIN JobTable ON QuoteTable.ID = JobTable.QuoteID

 

[zakster]

Is it possible to amend that query so that I can also display the job id?

 

[Zaeed]

SELECT QuoteTable.ID, QuoteTable.Date, QuoteTable.Client, QuoteTable.State, JobTable.ID FROM QuoteTable LEFT OUTER JOIN JobTable ON QuoteTable.ID = JobTable.QuoteID

should do the trick

Just a couple of things.. I would change the ID filed in QuoteTable to QuoteID so that it reflects what you have in JobTable. And then create a relationship between the two tables based on QuoteID

 

[zakster]

Thank you so much - it is now working a treat!

I have taken your other suggestions too - thank you for your time

 

[Zaeed]

not a problem