Microsoft Access query sorting issue and parameter prompt

Data and Databases
1

I’m stuck with a problem in my Microsoft Access database. My query isn’t sorting right and it’s asking for input I didn’t expect.

Here’s what’s bugging me:

  1. The sorting part of my query doesn’t seem to work.
  2. When I run it, Access asks for a value for SalesOrder.PID. I type 1 and then it shows the data.

I’m trying to count orders, add up prices, and total items for each product. Here’s my current query:

SELECT 
  P.ProductID, 
  P.ProductCode,
  COUNT(SO.OrderID) AS OrderCount,
  SUM(TotalPrice) AS Revenue, 
  SUM(Quantity) AS TotalItems
FROM Products P
JOIN SalesOrders SO ON P.ProductID = SO.ProductID
GROUP BY P.ProductID, P.ProductCode
ORDER BY TotalItems DESC;

Any ideas what I’m doing wrong? Thanks for any help!

2

hey dancingbutterfly, sounds like a tricky one! for the sorting issue, make sure ur TotalItems field is actually in the query. sometimes access gets wonky with aliases. As for the prompt, check if theres a criteria or filter somewhere in the query design that’s asking for SalesOrder.PID. good luck!

3

I’ve encountered similar issues before. The sorting problem might be due to the ORDER BY clause using an alias. Try replacing ‘TotalItems’ with ‘SUM(Quantity)’ in the ORDER BY statement. As for the unexpected parameter prompt, it’s likely caused by a saved parameter in the query properties. Open the query in design view, go to the Properties sheet, and check for any parameters listed there. Remove any you don’t need. If these steps don’t resolve the issue, consider rebuilding the query from scratch, as sometimes Access can retain hidden settings that cause unexpected behavior.

4

hey there! have you checked if your SalesOrders table has a PID field? that might explain the unexpected prompt. for sorting, try adding HAVING COUNT(SO.OrderID) > 0 after the GROUP BY. it could filter out products with no orders, making the sort work better. let me know if that helps!