###Query help needed

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

Guest

Hi

I need help writing a query. Im trying to use the access wizards but i cannot get it to work
This is what i need to do
I want to retrieve all the details of the last inserted booking from my database. I can do this by selecting the Max(BookingNo). I dont know if there is a better way

Here is the tables im using and the query i have trie
Booking Table Caravan Booking Table Equipment Booking tabl
BookingNo (PK) BookingNo (PK) BookingNo (PK
InvoiceNo, Caravan_Inv_No (PK) Caravan_Inv_No (PK
Dt_Of_Arrival, Equip_Inv_No (PK
Length_Of_Sta

SELECT MAX(dbo.Booking.BookingNo) AS MaxBookingNo,
dbo.Booking.InvoiceNo,
dbo.Booking.Dt_Of_Arrival,
dbo.Booking.Length_Of_Stay,
dbo.Caravan_Booking.Caravan_Inv_No,
dbo.Equipment_Booking.Equip_Inv_No

FROM dbo.Booking INNER JOI
dbo.Caravan_Booking ON dbo.Booking.BookingNo = dbo.Caravan_Booking.BookingNo
LEFT OUTER JOI
dbo.Equipment_Booking ON dbo.Caravan_Booking.BookingNo =
dbo.Equipment_Booking.BookingNo AND
dbo.Caravan_Booking.Caravan_Inv_No = dbo.Equipment_Booking.Caravan_Inv_N

GROUP BY dbo.Booking.BookingNo, dbo.Booking.InvoiceNo,
dbo.Booking.Dt_Of_Arrival, dbo.Booking.Length_Of_Stay,
dbo.Caravan_Booking.Caravan_Inv_No, dbo.Equipment_Booking.Equip_Inv_N

HAVING (dbo.Booking.BookingNo = MaxBookingNo

Can someone plz help me??
 
Bhavna said:
Hi,

I need help writing a query. Im trying to use the access wizards but i cannot get it to work.
This is what i need to do.
I want to retrieve all the details of the last inserted booking from my
Click to expand...
database. I can do this by selecting the Max(BookingNo). I dont know if
there is a better way.
Here is the tables im using and the query i have tried
Booking Table Caravan Booking Table Equipment Booking table
BookingNo (PK) BookingNo (PK) BookingNo (PK)
InvoiceNo, Caravan_Inv_No (PK) Caravan_Inv_No (PK)
Dt_Of_Arrival, Equip_Inv_No (PK)
Length_Of_Stay
Click to expand...

SELECT dbo.Booking.BookingNo
dbo.Booking.InvoiceNo,
dbo.Booking.Dt_Of_Arrival,
dbo.Booking.Length_Of_Stay,
dbo.Caravan_Booking.Caravan_Inv_No,
dbo.Equipment_Booking.Equip_Inv_No

FROM dbo.Booking
INNER JOIN
dbo.Caravan_Booking ON dbo.Booking.BookingNo =
dbo.Caravan_Booking.BookingNo
LEFT OUTER JOIN
dbo.Equipment_Booking ON dbo.Caravan_Booking.BookingNo =
dbo.Equipment_Booking.BookingNo AND
dbo.Caravan_Booking.Caravan_Inv_No =
dbo.Equipment_Booking.Caravan_Inv_No

WHERE dbo.Booking.BookingNo = (SELECT MAX(dbo.Booking.BookingNo) FROM
dbo.Booking)

David
 
Hi

I tried the following query and it seems to work fine and returns the correct result in access but when i try and place this query in crystal reports to create a virtual table when using a command in the database expert,i get the following error message

Failed to open a rowset
Details: Ado Error Code: 0x80040e1
Source: Microsoft OLE DB Provider for SQL Serve
Description: Line 16: Incorrect syntax near ')'
SQL State: 4200
Native Error: 17

this is my query. Can anyone help me in debuggin this?

SELECT dbo.Booking.BookingNo, dbo.Booking.ClientID, dbo.Booking.InvoiceNo, dbo.Booking.Dt_Of_Arrival, dbo.Booking.Length_Of_Stay,
dbo.Caravan_Booking.Caravan_Inv_No, dbo.Caravan_Inv.Caravan_Model, dbo.Caravan_Inv.Length, dbo.Caravan_Details.Cost,
dbo.Equipment_Booking.Equip_Inv_No, dbo.Equip_Inv.Equip_Name, dbo.Equipment_Details.Equip_Cos
FROM dbo.Caravan_Inv INNER JOI
dbo.Booking INNER JOI
dbo.Caravan_Booking ON dbo.Booking.BookingNo = dbo.Caravan_Booking.BookingNo ON
dbo.Caravan_Inv.Caravan_Inv_No = dbo.Caravan_Booking.Caravan_Inv_No INNER JOI
dbo.Caravan_Details ON dbo.Caravan_Inv.Caravan_Model = dbo.Caravan_Details.Caravan_Model AND
dbo.Caravan_Inv.Length = dbo.Caravan_Details.Length LEFT OUTER JOI
dbo.Equipment_Details INNER JOI
dbo.Equip_Inv ON dbo.Equipment_Details.Equip_Name = dbo.Equip_Inv.Equip_Name INNER JOI
dbo.Equipment_Booking ON dbo.Equip_Inv.Equip_Inv_No = dbo.Equipment_Booking.Equip_Inv_No ON
dbo.Caravan_Booking.BookingNo = dbo.Equipment_Booking.BookingNo AND
dbo.Caravan_Booking.Caravan_Inv_No = dbo.Equipment_Booking.Caravan_Inv_N
WHERE (dbo.Booking.BookingNo
(SELECT MAX(dbo.Booking.BookingNo)
 
Back
Top