merging two queries 1

[fini123]

I have an existing query which goes as follows:

SELECT job.date, job[job id], clients.[company name], cars.car_registration, clients.[contact name], clients.discount, price_list.[customer price], 5*price)list.[customer price] AS total FROM price_list INNER JOIN (clients INNER JOIN (cars INNER JOIN job ON cars.[car id] = job.[car id]) ON clients.company = cars.company) ON price_list.[tyre id] = cars.[tyre id] WHERE (((job.date) Between #1/1/2003# And #2/1/2003#))

I want it to only display the last job for any specific car however. I can do this in a seperate query, but when i try to add any more than one other field to it (so more than just showing date and car) it screws up. I'd much prefer to get this all working in a single SQL query, but if anyone can get it working, whatever method it is, I'll be very glad. Thanks in advance...

fini123

 

[r937]

untested:

select J.date
     , J.[job id]
     , clients.[company name]
     , cars.car_registration
     , clients.[contact name]
     , clients.discount
     , price_list.[customer price]
     , 5*price_list.[customer price] as total 
  from ((
       clients 
inner 
  join cars 
    on clients.company = cars.company
       )
inner 
  join job J
    on cars.[car id] = J.[car id]
       ) 
inner
  join price_list 
    on cars.[tyre id]  = price_list.[tyre id] 
 where J.date = 
       ( select max(date)
           from job
          where car_id = J.car_id
            and date between #1/1/2003# 
                         and #2/1/2003# )

rudy

http://r937.com/

SQL Consulting

 

[fini123]

I presume you meant this (got rid of _'s in some):

SELECT J.date, J.[job id], clients.[company name], cars.car_registration, clients.[contact name], clients.discount, price_list.[customer price], 5*price_list.[customer price] AS total
FROM ((clients INNER JOIN cars ON clients.company = cars.company) INNER JOIN job AS J ON cars.[car id] = J.[car id]) INNER JOIN price_list ON cars.[tyre id] = price_list.[tyre id]
WHERE (((J.date)=(select max(date)
           from job
          where [car id] = J.[car id]
            and date between #1/1/2003#
                         and #2/1/2003# )));

Though this does display jobs between the two dates it doesn't take into account if there have been newer jobs... What I want to do is, once a month, print out letters to people who have a car that has not visited for between 1 year and 1 year and a month ago. I'm making it in VB so I can deal with replacing the dates, but I'm still really struggling to get the rest of this query working properly. Thanks for your help so far and it would be great if you or any one else could help once again so we can finally get this working...

fini

 

[r937]

where J.date =
      ( select max(date)
          from job
         where [car id] = J.[car id]
           and date between #1/1/2003#
                        and #2/1/2003# )
  and J.date between #1/1/2003#
                 and #2/1/2003#

rudy

http://r937.com/

SQL Consulting

 

[fini123]

many thanks, but this code:

select J.date
     , J.[job id]
     , clients.[company name]
     , cars.car_registration
     , clients.[contact name]
     , clients.discount
     , price_list.[customer price]
     , 5*price_list.[customer price] as total
  from ((
       clients
inner
  join cars
    on clients.company = cars.company
       )
inner
  join job J
    on cars.[car id] = J.[car id]
       )
inner
  join price_list
    on cars.[tyre id]  = price_list.[tyre id]
where J.date =
      ( select max(date)
          from job
         where [car id] = J.[car id]
           and date between #1/1/2003#
                        and #2/1/2003# )
  and J.date between #1/1/2003#
                 and #2/1/2003#

still isn't working. Below is some of the jobs table the data is being drawn from:

job id date car id
1 12/12/2003 1
2 03/01/2004 5
3 10/01/2003 7
4 04/01/2004 7
5 05/02/2004 5
6 15/01/2003 1
7 18/01/2003 2
8 19/02/2004 18
9 19/02/2004 11
10 19/02/2004 4
11 19/02/2004 20
12 19/02/2004 23
13 21/02/2004 12
14 21/02/2004 13
15 21/02/2004 12
16 21/02/2004 14
17 21/02/2004 19
18 29/02/2004 2

and here is what it returns:

date job id company name car_registration contact name discount customer price total
10/01/2003 3 Brighton International Summer School PO69CAB Prof. Winston No £39.00 £195.00
15/01/2003 6 Edgar Horns Auctioneers AB03XYZ Edgar Horn Yes £58.50 £292.50
18/01/2003 7 Newline Commerical Interiors AB04JUI Peter Webb No £36.00 £180.00

As you can see job 3 makes an appearance even though the same car came in later in job 4 (04/01/2004)... any ideas whats going on? thanks...

fini123

 

[r937]

oops

take the date check out of the subquery

rudy

http://r937.com/

SQL Consulting

 

[fini123]

if you mean taking the

and J.date between #1/1/2003#
and #2/1/2003#

out of the bottom that makes no effect,,,

fini123

 

[r937]

no, out of the subquery

rudy

http://r937.com/

SQL Consulting

 

[fini123]

Thank you very much I've been trying for what seems like forever to get this query to work and this:

select J.date
     , J.[job id]
     , clients.[company name]
     , cars.car_registration
     , clients.[contact name]
     , clients.discount
     , price_list.[customer price]
     , 5*price_list.[customer price] as total
  from ((
       clients
inner
  join cars
    on clients.company = cars.company
       )
inner
  join job J
    on cars.[car id] = J.[car id]
       )
inner
  join price_list
    on cars.[tyre id]  = price_list.[tyre id]
where J.date =
      ( select max(date)
          from job
         where [car id] = J.[car id] )
  and J.date between #1/1/2003#
                 and #2/1/2003#

finally works. Thank you for the the help you gave me, I'm sure I would have been struggling for a long time to come otherwise...

fini123