Tracking number of orders a user makes

[aron.ridgway]

I am after some advice, i want to track how many orders a user makes. So every time they place an order it increments 1 after each order.

I currently have the following tables,
Order
OrderDetail
User

Should i create an UserOrderHistory table?

Eventually i want this information to form part of an order number. eg

196(order primary key) AR (UsersInitials) /23 (number of orders User has placed)

Any advice on the best way to achieve this would be great!

thanks

 

[pr2-eugin]

No need for a table. Create a TOTALS Query. Something like.

SELECT User.UserName, Count(Order.OrderID) As CountOfOrders
FROM User INNER JOIN Order ON User.UserID = Order.UserID_FK
GROUP BY User.UserName;

 

[aron.ridgway]

Worked a treat thank you!

 

[aron.ridgway]

Hi Paul,
How would i get my query to display a 1 if the count = 0? at the minute it needs 1 order being placed before it shows a record. I want it to show all the staff in the query and if they havnt placed an order i want it to show 1?

hope this makes some sense?

 

[pr2-eugin]

Then use LEFT JOIN instead of INNER,

SELECT User.UserName, Count(Order.OrderID) As CountOfOrders
FROM User LEFT JOIN Order ON User.UserID = Order.UserID_FK
GROUP BY User.UserName;

 

[aron.ridgway]

Of course! thank you!