Query problem - is it Joins ?

[ray147]

I have two tables...'Suppliers' and 'Despatches'.

I'd like to create a query that shows all the suppliers that are in the Suppliers table as well as the last date of despatch (if any). Thus, this would be the maximum date that relate to that supplier ID. However, despatches that have a quantity of 0 should be excluded.

Currently I have the following, it's working fine but suppliers who doesn't have a corresponding despatch are not being listed. I need the report to list all the suppliers.

SELECT [Suppliers].[fldSupplierFullName], [Suppliers].[ID], Max([Despatches].[DespDate]) AS MaxOfDespDate
FROM Despatches LEFT JOIN Suppliers ON [Despatches].[SupplierRef]=[Suppliers].[ID]
WHERE ((([Despatches].[QtyLoaded])<>0))
GROUP BY [Suppliers].[fldSupplierFullName], [Suppliers].[ID];

Can anyone give me any direction?

Thanks

 

[ray147]

nobody can give me a hand here?

 

[Rickster57]

Link

It is your link (Join Property). Use the "Select All records From Supplier and only those...."

 

[ray147]

Thanks Rickster, but can you give me some indication of how to write the SQL query as I'm having trouble with this ..
thanks a lot

 

[ray147]

guys, any help here appreciated coz i'm really lost in this...
tnx

 

[Rickster57]

Query

Open the query in design view that has the 2 tables linked. Right click on the link (The line that joins the 2 tables). Click on Join Properties and then select the option as I described.

Use a LEFT JOIN instead of INNER JOIN

 

[StevenS]

Go to the design view of your query, at the top of the view you'll find your tables...
double-click on the line that links them, there you'll find the Join Properties Rickster57 was talking about...
StevenS

 

[ray147]

hi guys.
thanks for your input, but i don't think it's a matter of the join properties within design view coz i've tried playing with these quite a lot and didn't manage...i'm attaching a Zipped sample database file with two tables and a query...the two tables are Suppliers and Despatches. What I'd like to get from Query1 is: a list of all the suppliers in the Suppliers table along with the last despatch date for each supplier (if any), but those that have a despatch qty of 0 should be excluded...however suppliers that do not have any despatches are still to be listed in the query, showing no despatch date....

hope this is clear and looking forward to receiving some of yr feedback coz i really dunno what i'm gonna do with this query!

thanks

 

[Jon K]

Run QueryThree in the attachment to see if I understood your problem correctly.
.

 

[ray147]

Done!

Jon,

Thanks a lot for yr feedback. That solved my problem....yr idea of setting different queries and joining them together really solves a lot of problems....you understood my problem perfectly and now i can go on and put the results on a report...

 

[ray147]

It's working fine...but is there a better way?

I now implemented that scenarios to what I'm doing and have got three queries to get what I want, query QS-3 is the end-result which I want and works just fine...but is there any way how can i combine all these three into one query? i mean is it a matter of how I perform the joins? I seem to think that access doesn't allow all possible joins???

Anyway, below are my three queries (SQL):


Query QS-1:

SELECT Despatches.SupplierRef, Max(Despatches.DespDate) AS MaxOfDespDate
FROM Despatches
WHERE (((Despatches.QtyLoaded)<>0))
GROUP BY Despatches.SupplierRef;


Query QS-2:

SELECT Bookings.SupplierRef
FROM Bookings INNER JOIN Status_Codes ON Bookings.Status = Status_Codes.StatusID
WHERE (((Status_Codes.StatusName)="Booking Confirmed"));


Query QS-3:

SELECT Stockholding_Temp.SupplierFullName, Suppliers.ID, [QS-1].MaxOfDespDate, Count([QS-2].SupplierRef) AS CountOfSupplierRef
FROM [QS-2] RIGHT JOIN (Stockholding_Temp LEFT JOIN ([QS-1] RIGHT JOIN Suppliers ON [QS-1].SupplierRef = Suppliers.ID) ON Stockholding_Temp.SupplierFullName = Suppliers.fldSupplierFullName) ON [QS-2].SupplierRef = [QS-1].SupplierRef
GROUP BY Stockholding_Temp.SupplierFullName, Suppliers.ID, [QS-1].MaxOfDespDate
HAVING (((Stockholding_Temp.SupplierFullName) Not Like "Argos*"));


Any ideas with this appreciated...this is just a matter of finding a better way how to do it..

thanks